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!" :-)]
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!" :-)]
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?
ReplyDeletehttp://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlbytes.stream.aspx
ReplyDelete"Remarks
Getting or setting the Stream property loads all the data into memory. Using it with large value data can cause an OutOfMemoryException."
my suggestion is this:
ReplyDeletepublic 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);
}
}
}
}