How to Stream data from/to SQL Server BLOB fields?

Question

For the background to this question, see “How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?” that now has a large bounty on it.

I wish to be able to use a Stream object to read/write data to/from a BLOB field in a SQL Server row without having to put the all the data into a temp buffer.

---

If the above can be done...

As the Streams class has lot of CanXXX() methods, not all streams can be used by all methods take accept stream inputs/outputs.

So how capable does a stream have to be to work with ADO.NET when sending data to/from SQL Server?

---

I am looking to have a standard Stream to which I can pass it on to other APIs.

Also the two answers so far only covers getting data form SqlServer, not sending the data to SqlServer.

Answer 1

Here's an example for reading data in chunks:

    using (var conn = new SqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "select somebinary from mytable where id = 1";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                byte[] buffer = new byte[1024]; // Read chunks of 1KB
                long bytesRead = 0;
                long dataIndex = 0;
                while ((bytesRead = reader.GetBytes(0, dataIndex, buffer, 0, buffer.Length)) > 0)
                {
                    byte[] actual = new byte[bytesRead];
                    Array.Copy(buffer, 0, actual, 0, bytesRead);
                    // TODO: Do something here with the actual variable, 
                    // for example write it to a stream
                    dataIndex += bytesRead;
                }
            }

        }
    }

Answer 2

You wouldn't put all the data into a buffer; you would typically run a loop, buffering some multiple of 8040 bytes (related to the page size), appending the BLOB each time WRITETEXT / UPDATETEXT for image, or UPDATE.WRITE for varbinary(max). Here's an older example (uses image, sorry).

Likewise, when reading data out you would hopefully be pumping data in a small buffer to some other destination (an http response, a network, a file, etc). Something like this (although I don't quite like how he handles his EOF / chunking; I'd check +ve bytes read).