CODEWITHSUNDEEP
sql-serverasp.net-mvc-3model-view-controllerblob
TMan
TMan

Reputation: 1315

How to store and retrieve documents as blobs in database?

In my MVC 3 / SQL Server based app I want to store a pdf document as a blob in the database. In my model I have this property:

public byte[] PdfData { get; set; }

Reading the file and getting the bytes from a filestream is fairly simple, but when trying to save the changes I get the error

Byte array truncation to a length of 4000

This of course makes sense, because the document is larger than the default 4000 bytes limit. But I am not allowed to set the maxlength property to more than 8000, which is way too small. I know I can get around the issue by using the image data type like this:

[Column(TypeName = "image")]
public byte[] PdfData { get; set; }

But as I understand it, the image data type is there just for backwards compatibility, and may disappear anytime. What I want to do, is to use the varbinary(max) data type. But how do I set up my model to generate a column of this datatype in SQL Server?

As a side note, I am currently running on SQL Server CE in my dev environment, but the app will be targeting a regular SQL Server 2008 database when deployed. Not sure if this is relevant.

Upvotes: 0

Views: 3758

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294227

See Download and Upload images from SQL Server via ASP.Net MVC, you can simply use it to store PDFs instead of images. Also FILESTREAM MVC: Download and Upload images from SQL Server for a filestream based implementation. The gist of it is that you want not only to store and retrieve large blob, but also absolutely avoid the conversion to and from byte[] because that implies the entire document must be first copied in live memory in the ASP process and it will kill the server under even moderate load. The examples above show how to do the upload and download using a streaming interface.

Upvotes: 1

Related Questions