Streaming uploaded files directly into a database table

Question

I have several applications that allow users to upload attachments that are stored inside a database table. This has worked fine for several years because it was initially intended for smallish image files, but now they want to upload very large files (~80MB). This is causing the server to run out of memory (and the upload to fail) because, in order to write the binary data to a database, I am loading the entire content into a byte array. Many, if not most, online examples for file uploads use this method (see http://www.aspnettutorials.com/tutorials/database/Save-Img-ToDB-Csharp.aspx as an example).

Now, the question is, can I somehow stream the binary content to the database instead of loading the entire content into a byte array and then setting the byte array as a parameterized value? Switching to file-based instead of database-based storage would be a big deal at this point...

Answer 1

I don't think it would be practical to store files that large in a database, especially by uploading (I'm assuming the upload is through a browser.)

At a minimum, you'd want to upload the file to a holding folder on the server and move it into the database once it's fully uploaded. Otherwise you'd have to figure out how to chunk the bytestream into the database using multiple updates. I don't think you can append to a BLOB that's already in a record.

UPDATE: It looks like I may have been wrong about the chunking. SQL Server supports UPDATETEXT.

I still think uploading files that large and "streaming" them into the database is fragile and presents reliability issues.

Answer 2

Maybe this article can point you to the right direction.

Answer 3

You're not giving much information on the environment you use, other than the tags you attached to your post.

What you should do is use the streaming features of your database server (and the connection layer you use) to send the data through a stream.

It seems that only seekable streams are supported, so you should write the uploading file to a temporary file first (using streams, don't buffer the file in memory) and then use a FileStream to insert the file into the database.

Answer 4

You'll need a client application (Silverlight, Java Applet, Flash, etc) which break your file in small chunks and send that to server;

At server, you'll need to call your database and save that chunk; in my tests, best performance came from UPDATETEXT

Note you'll need to do something similar to provide file downloads; I suggest to read about READTEXT function, pretty same than UPDATETEXT, and to use it inside an IHttpAsyncHandler