Streaming directly to a database

Question

I'm using c#, and have an open tcpip connection receiving data. Is it possible to save the stream to an ms sql server database as I'm receiving it, instead of receiving all the data then saving it all? If the stream could be sent to the database as it's being received, you wouldn't have to keep the entire chunk of data in memory. Is this at all possible?

Answer 1

Why not just write the buffer to a file as you receive packets, then insert to the database when the transfer is complete?

If you stream directly to the database, you'll be holding a connection to the database open a long time, especially if the client's network connection isn't the best.

Answer 2

See here and here for exmaples of working with streams and databases. Essentially, you need to pass repeated buffers (ideally of multiples of 8040 bytes in SQL Server). Note that the examples are based on SQL 2000; with SQL 2005, varbinary(max) would be easier. Very similar, though.

Answer 3

Are you writing to the DB as a BLOB, or translating the data in some form, then executing inserts for each row?

Your answer in the comments has me confused. Writing a stream to a BLOB column is vastly different then getting the data then translating it into inserts for separate rows.

Regardless, streaming into a BLOB column is possible by first creating the row with the blob column that you need to insert into, the repeatedly calling an update statement:

update myTable set myColumn.Write(@data, @offset, @length) where someid = @someId

for chunks of bytes from the stream.

Perfect example located here.

Answer 4

SQL Server 2005 supports HTTP endpoints (without requiring IIS) so one solution would be to create a web service on SQL Server to directly receive the streamed data.

These links explain setting one up: http://codebetter.com/blogs/raymond.lewallen/archive/2005/06/23/65089.aspx http://msdn.microsoft.com/en-us/library/ms345123.aspx