Streaming in and out of an SQL Server 2005 image field with C# ?

Question

After having checked extensively for an answer to this question, I still cannot find a satisfying solution. So here goes.

I need to store possibly large amount of data in a column of an SQL Server 2005 table. I absolutely need to work in a streaming fashion, so that :

1. When writing, the data is sent in chunks to the database. Is there some builtin way to achieve this from C# client-code using the classes in System.Data.SqlClient? Or do I need to resort to using ADODB.Net Stream object ? Not sure how to mix the two concepts (especially with regards to participating in the current SqlTransaction.

Alternatively, is there a way for a T-SQL stored procedure to append data to an existing column. With this approach, the stored procedure would be called multiple times from the client code and this would achieve the streaming requirement.

1. When reading, the data should be read one chunk at a time, in a streaming fashion.

Alternatively, is there a way for a T-SQL stored procedure to provide sequential or even random access to the contents of an image field?

Answer 1

Well, answering my own question.

The truth is... there is actually no way to do what I want. Either from pure client code or from server side stored procedures in T-SQL. Until we switch to SQL Server 2008, we will have to find another soluton.

However, there is actually a way to simulate this behavior, so that the streaming requirement is achieved. The solution lies in a collaboration between client and server code.

The server database should, for instance, expose the entire contents to be streamed as a set of records in a fragments table. Each record, representing a chunk of the entire contents to be streamed. Whereas in the client, the stream is read in sequence, and each chunk is then sent to the database to fill one record.

With suitable bookkeeping, the reading of the stored data can also be done in a streaming fashion.

Incidentally, that's what Microsoft BizTalk Server is doing, and that's how I find out.

Answer 2

Actually, there is a way, it just hurts a bit.

SQL Server 2005 supports updating part of a column: http://technet.microsoft.com/en-us/library/ms177523(SQL.90).aspx

And you can do a read of part of a column with substring (yes, even binary - it'll cheerfully return an array of bytes).

The caveats here are that it's only an update - so you'd have to create a value in the varbinary(max) field first, then update it. Other than it, it's absolutely possible to deal with as though you're streaming data to/from SQL Server. I wrapped the retrieval / update functionality with a stream class to make my life easier.

Hope this helps.