I have some data that I am serializing. I need to save and restore this from a VarBinary column using ADO.NET (also using Enterprise Library) under .Net 3.5 (not 4.0 yet).
The only interface I seem to be able to find and get working is by using a byte[]. This requires allocating a large array to do the transfer.
It seems impossible that ADO.NET doesn't support Streams for these columns. Or am I overlooking something?
Thanks
I've been looking for the same answer. I found an article at EggHeadCafe that provides a solution using a command like this for reads:
SELECT substring(DataColumn, @offset, @length) FROM BlobTable WHERE ID = @key
And a command like this for writes:
UPDATE BlobTable SET DataColumn.write(@buffer, @offset, @length) WHERE ID = @key
You can find the complete solution (with a custom stream class) here: http://www.eggheadcafe.com/software/aspnet/29988841/how-to-readwrite-chunked-to-varbinarymax-in-c.aspx.
For my purposes though, I think I'm going to make use of the FileStream data type that's available as of Sql Server 2008. Once you insert a record (sans binary data), Sql Server allocates a physical file on the disk for reading and writing the record data to. You then run a command to get the path name:
SELECT DataColumn.PathName() FROM BloblTable WHERE [Id] = @key
Once you have that, you can read and write to the file using a traditional stream class, like System.IO.FileStream.
There is considerable overhead, but FileStream becomes faster as the files get larger (about 10mb). For smaller files, you can treat the FileStream column as a varbinary column with much less overhead. This article at CodeProject explains the details: http://www.codeproject.com/KB/database/SqlFileStream.aspx
