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!" :-)]

Comments

  1. Anonymous10:33 AM

    Concise and to the point...but - this I suspect reads the whole byte[] into memory - is it possible to stream the data chunk at a time from the column?

    ReplyDelete
  2. http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlbytes.stream.aspx

    "Remarks

    Getting or setting the Stream property loads all the data into memory. Using it with large value data can cause an OutOfMemoryException."

    ReplyDelete
  3. my suggestion is this:

    public static void getBlob(..., Stream result)
    {
    using (System.Data.SqlClient.SqlDataReader r = ...) {
    if (r.Read()) {
    SqlBytes b = r.GetSqlBytes(0);
    byte[] buffer = new byte[8040 * 4];
    long offset = 0;
    long read = 0;
    while ((read = b.Read(offset, buffer, 0, buffer.Length)) > 0) {
    offset += read;
    result.Write(buffer, 0, (int)read);
    }
    }
    }
    }

    ReplyDelete

Post a Comment

Popular posts from this blog

FIREFOX / IE Word-Wrap, Word-Break, TABLES FIX

Some observations on Script Callbacks, "AJAX", "ATLAS" "AHAB" and where it's all going.

IE7 - Vista: "Internet Explorer has stopped Working"