Wednesday, March 31, 2010

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))

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)