Reading Images From SQL Server using SqlDataReader.GetBytes

Searching around on the internet I found it difficult to find some example C#/.NET code on using SqlDataReader.GetBytes to read image data from a varbinary field in a SQL Server 2008 database. Eventually I found a solution that worked for me, so I thought I would share it with the world.

My solution returns an IEnumerable<T> and uses yield allowing the handling of very large datasets, as each avatar object is returned as the SqlDataReader reads each row, rather than reading in all the rows and processing them all into image data at once.

Anyway, here is the code:

/// <summary>
/// Get Enumerable List Of Avatar Images From User Table
/// </summary>
/// <param name="connectionString">
/// Connection String For Database
/// </param>
/// <returns>
/// Enumerable List Of UserAvatar type.
/// </returns>
IEnumerable<UserAvatar> GetAvatarImages(string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = @"SELECT [ID], [Avatar] FROM [User] WHERE [Avatar] IS NOT NULL";

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    //create a memory stream in which to write the raw data
                    using (var ms = new MemoryStream())
                    {
                        var buffer = new byte[1024];

                        //this call (with a null buffer paramater)
                        //will tell us the size of the data in the field
                        var dataSize = reader.GetBytes(1, 0, null, 0, 0);
                        var dataRemaining = dataSize;
                        while (dataRemaining > 0)
                        {
                            var bytesToRead = (int)(buffer.Length < dataRemaining ? buffer.Length : dataRemaining);
                            //fill the buffer
                            reader.GetBytes(1, dataSize - dataRemaining, buffer, 0, bytesToRead);
                            //write the buffer to the MemoryStream
                            ms.Write(buffer, 0, bytesToRead);
                            dataRemaining -= bytesToRead;
                        }

                        //Move the position of the MemoryStream
                        //Back to the beginning
                        ms.Seek(0, SeekOrigin.Begin);

                        //create a temporary placeholder image
                        //from the MemoryStream data
                        using (var tempImage = Image.FromStream(ms))
                        {
                            yield return new UserAvatar
                            {
                                ID = reader.GetInt32(0),
                                //Copy the temporary image data
                                //to a new bitmap. Disposing of
                                //this new bitmap becomes the 
                                //responsibilty of the caller.
                                Image = new Bitmap(tempImage)
                            };
                        }
                    }
                }
            }
        }
    }        
}