Streaming a large file into a database BLOB field

Question

I have a large file on disk whose contents I need to store in a SqlServer database as a VARBINARY(MAX) field. By "contents", I mean using something like File.ReadAllBytes(). (I understand the pitfalls of this, but it's the current mode of operations, so I have to deal with it for now.)

I found this answer, which provides a way to stream a large Byte[] by using UPDATE.WRITE:

How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?

However, simply reading the contents of a large file into a Byte[] in memory leads to this problem:

OutOfMemoryException when I read 500MB FileStream

I might be overlooking something obvious, but I just can't work out how should I go about getting from a large file on disk, to the resulting storage into the database.

Your original title will not help you get answers - it said nothing about what you were trying to achieve. — Jamiec, Oct 21 '15 at 16:03
Gotta chunk it: http://stackoverflow.com/questions/2101346/how-to-stream-data-from-to-sql-server-blob-fields — p e p, Oct 21 '15 at 16:07

score 0 · Accepted Answer · edited May 23 '17 at 12:33

Using some hints I got from these two pages, I have an answer that works:

http://www.syntaxwarriors.com/2013/stream-varbinary-data-to-and-from-mssql-using-csharp/ (this looks a lot like the serialize SO answer, but there's more here...not sure who copied who!).

How do I copy the contents of one stream to another?

Basically, it uses the same methodology as the answer about serializing Blobs, but instead of using BinaryFormatter (a class I'm not fond of anyhow), it creates a FileStream that takes the path to the file, and an extension method to copy that stream into the target stream, or BlobStream, as the example named it.

Here's the extension:

public static class StreamEx
{
    public static void CopyTo(this Stream Input, Stream Output)
    {
        var buffer = new Byte[32768];
        Int32 bytesRead;
        while ((bytesRead = Input.Read(buffer, 0, buffer.Length)) > 0)
            Output.Write(buffer, 0, bytesRead);
    }
}

So the trick was to link two streams, copying the data from one to another in chunked fashion, as noted in the comments.

Streaming a large file into a database BLOB field

1 Answers1