7

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.

Community
  • 1
  • 1
Ian Ringrose
  • 51,220
  • 55
  • 213
  • 317
  • 3
    I arrived at this question when I was searching for the same thing a few days ago. Found complete working solution here: http://www.syntaxwarriors.com/2013/stream-varbinary-data-to-and-from-mssql-using-csharp/ – JensB Jan 31 '13 at 09:21

2 Answers2

5

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;
                }
            }

        }
    }
Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • I am looking to have a standard Stream to I can pass it on to other APIs. – Ian Ringrose Jan 20 '10 at 14:03
  • Actually, it is documented as being ideal to work in chunks of 8040 bytes. I'm also not sure what you are trying to do by copying the buffer each iteration. – Marc Gravell Jan 20 '10 at 21:56
  • I'm copying the buffer to the `actual` variable depending on the actual bytes read as it may contain less bytes than its size. – Darin Dimitrov Jan 21 '10 at 07:22
1

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).

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • The example read data from a FileStream then reads it to SqlServer, I with have a Stream I can pass to other APIs that write to the Stream and have the data put on the SqlServer – Ian Ringrose Jan 20 '10 at 14:06
  • @Ian - in that case, have you investigated `SqlFileStream` and SQL Server 2008? Perhaps see http://www.aghausman.net/dotnet/saving-and-retrieving-file-using-filestream-sql-server-2008.html – Marc Gravell Jan 20 '10 at 21:54
  • I don't think we will be able to get all our customers to update to Sql Server 2008, otherwise SqlFileStream would work very well for us. – Ian Ringrose Jan 23 '10 at 12:14