Problem with storing large files in database

Question

I have used linq to sql to store files in a varbinary(max) field. Filestream is activated too, but when I try to store files with 400 or 500 MB I get this error:

Exception of type 'System.OutOfMemoryException' was thrown

My Code is:

Dim ByteArray() As Byte = File.ReadAllBytes(OpenFileDialog1.FileName)
Dim tb As New tb_1()
tb._id = System.Guid.NewGuid()
tb._Blob = New System.Data.Linq.Binary(ByteArray)
tb._text = Date.Now
db.tb_1s.InsertOnSubmit(tb)
Dim tb2 As New tb_2
tb2._id = System.Guid.NewGuid
tb2._Master = tb._id
tb2._text = 2
db.tb_2s.InsertOnSubmit(tb2)
db.SubmitChanges()

What could be the reason I get this exception and how can I avoid it?

Answer 1

I think you can use Image data type (in SQL Server )to store large files in your database.If you are using another DBMS other that SQL Server it should has a data type equivalent to Image data type An Image is a array of bytes so you should not change your code

Answer 2

Simple answer: If your only tool is a hammer, every problem looks like a nail.

Linq2SQL is an O/R mapper. it i not made to handle hugh amounts of binary data in an object. There is no way to do what you want the way you want it. Drop Linq2SQL for this specific part and use the SQL special syntax for partial read / writes (you can basically write only part of the blob / read part of the blob every SQK command you do).

The same is true, btw., for pretty much every other ORM out there - BLOB storage is not something you have in mind when you create an ORM (been there, done that).