3/23/2007

Read / Write Large Value (Blob) Types in SQL Server 2005 with ADO.NET 2.0

I saw somebody's post on the C# newsgroup recently where they had posted some chunked code to use a do / while GetBytes pattern to read from a Varbinary(MAX) column. The code was fine, but it sure was a lot of code, because the individual who posted it was accumulating the entire block of data in memory anyway and then proceeding to use it.

There is a much simpler way, with SQL 2005 and ADO.NET 2.0:

//-- READ - reading a varBinary(MAX) columm...

System.Data.SqlTypes.SqlBytes myBytes;
System.Data.SqlClient.SqlDataReader dr;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess); // SequentialAccess allows chunked reading with offsets, etc.

myBytes = dr.GetSqlBytes(1); // return a SqlBytes type containing the entire contents of the column
System.IO.Stream s = myBytes.Stream; // get the stream property

Bitmap B = new Bitmap(s);
B.Save("MyPicture.bmp");



//-- WRITE

byte[] byteBlobData;
System.IO.FileStream fs = new System.IO.FileStream("myPhoto.jpg",System.IO.FileMode.Open);
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
byteBlobData = br.ReadBytes((int)fs.Length);
cmd.Parameters.Add("@BLOB", SqlDbType.Image, 5000).Value = byteBlobData;
... etc.

Here is the documentation page that summarizes.

[And yes, I did close my streams, my FileStream, BinaryReaders, my SqlDataReader and my Connection. Closed the door, flushed the toilet, put out the dog. I even called Dispose afterward. As Dom Deluise would say, "I hope it's enough!" :-)]