How to Transfer Binary Data to SQL Server

Question

I have a web service written in ASP.NET MVC with a SQL Server backend. Customers can send big data when making calls to the web service in the amount of about 10 megabytes.

On the server side, I want to read the stream of HTTP requests and write it to the VARBINARY field in the SQL table. How to do this without reading the entire stream into an array in memory ?

The reason I'm worried about memory is because this system needs to scale to 1000 concurrent clients (at least).

I am currently using Linq to SQL, and it doesn't look like it supports data streams, because the Linq binary type can only be initialized with a byte array.

Is there any other way to do this using Linq to SQL?

If this is not possible using Linq to SQL, how can this be done using ADO.NET or some other approach?

Answer 1

You can transfer data to disk, and then use OPENROWSET to bulk import data. For instance:

INSERT INTO YOURTABLE(binaryColumnName) 
SELECT * FROM 
OPENROWSET(BULK N'C:\data.bin', SINGLE_BLOB)

You can use the temp file API to avoid having to manage the lifetime of the data on disk ... it will be deleted once more in use.

Answer 2

, .WRITE UPDATE . LINQ, . (INSERT) , . - UPDATE, .WRITE. . , UPDATE , "".

INSERT INTO XrayImages (HeaderId, ImageBytes) VALUES (@headerId, @imageValue)

UPDATE XrayImages SET ImageBytes.WRITE(@imageChunk, NULL, @chunkLength WHERE ImageId = @imageId;

MSDN: http://msdn.microsoft.com/en-us/library/bb399384.aspx

---

, ( ). , Request.Files , , . , , - . , MVC, , , , .

, . 1000 , , GB nics db -. - , , .

, -. , SqlServer FileStream , .

, !