getting Stream in and out of VarBinary(MAX) column? (SQL Server)

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