Inserting a byte array larger than 8k bytes

Question

I'm using the code

cmd.Parameters.Add("@Array", SqlDbType.VarBinary).Value = Array;

The SqlDbType.VarBinary description states that it can only handle array's upto 8 K bytes. I have a byte array that represents an image and can go upto 10k bytes.

How do I store that in a varbinary(max) column using C#?

I have no trouble creating the array. I'm stuck at this 8k limit when trying to execute the query.

Edit: Let me clarify, on my machine even pictures upto 15k bytes get stored on the database in the varbinary(MAX) column when I run the asp.net application locally but once I deployed it the pictures would not get stored. I then resorted to drastically resizing the images to ensure their size was less that 8K and now the images get stored without any problem.

Why don't you store the image in the harddrive and save the path in db? — Bharadwaj, Mar 01 '14 at 06:38
Which version of SQL Server? There have been some changes. (Also, if you get an error, make sure to include the exact message - this will also make the problem easier for others to find, and maybe to find duplicates.) — user2864740, Mar 01 '14 at 06:44
Even [Modifying Large-Value (max) Data in ADO.NET](http://msdn.microsoft.com/en-us/library/vstudio/bb399384(v=vs.100).aspx) seems silent on "how" one might be do this for a VARBINARY(MAX) column .. — user2864740, Mar 01 '14 at 06:52
But [Working with Binary Large Objects (BLOBs) Using SQL Server and ADO.NET](http://www.codeguru.com/csharp/csharp/cs_data/cachingandpeformance/article.php/c15417/Working-with-Binary-Large-Objects-BLOBs-Using-SQL-Server-and-ADONET.htm) (which creates a Parameter object *first*, and then adds it, makes no mention of an 8k size restriction) .. are you *sure* your originally presented code *doesn't work*? If it doesn't, do post the error/exception. — user2864740, Mar 01 '14 at 06:57
I can't store path because I will be receiving image from client Machine and then saving it on the server machine. Secondly, when I use an image larger than 8 kb the image does not get stored on my data base. (I use WCF to transport the image through silverlight to my server and that process finishes without any problem). — WackStr, Mar 01 '14 at 12:51

Chris Moutray · Answer 1 · 2014-03-01T20:01:18.177

0

Perhaps you could look at the Sql Server FILESTREAM feature since its meant for storing files. It basically stores a pointer to your file and the file is stored directly in the filesystem (in the databases data directory).

I like FILESTREAM since you it means you continue to use the interface to the database (SQLClient for example) rather then breaking out to an adhoc method to read/write files to the harddrive. This means security is managed for you in that your app doesn't need special permissions to access the filesystem.

Quick google gave this acticle on using filestream in c# but I'm sure there are many others.

UPDATE following OP EDIT

So once deployed to other server the upload fails? Perhaps the problem is not the sql insert but that there is a http request content length limit imposed - for example in your web.config the httpRuntime element has the maxRequestLength attribute. If this is set to a low value perhaps this is the problem. So you could set to something like this (sets max to 6MB well over the 10kb problem):

<system.web>
    <httpRuntime maxRequestLength="6144" />

The only thing here is the limit it 4MB buy default :|

edited Mar 01 '14 at 20:01

answered Mar 01 '14 at 06:47

Chris Moutray

18,029
7
45
66

While FILESTREAM is a handy new type, I'm not sure it would be well-suited here (and was only introduced in 2008). The OP says that the maximum data is about 10k, which is not where using a FS would excel. (The FS type basically provides a nicer layer over "storing the filename and reading the file" manually.) – user2864740 Mar 01 '14 at 06:48
Yes I missed the 10k limit, I read "i store images"... The way I have used this in the past is to use a hybrid storing in the db if less that a limit and using filestream if greater... – Chris Moutray Mar 01 '14 at 07:00
I'm interested in: 1. Why there is a VARBINARY(MAX) limitation in ADO.NET (and if so what it is), or 2. If not, why the OP thinks/reports there is such a limitation in his/her program. – user2864740 Mar 01 '14 at 07:02
1

@user2864740 for what its worth it works for me when trying images of different sizes (even greater than 1.5MB) https://gist.github.com/chrismoutray/9286664 – Chris Moutray Mar 01 '14 at 07:52
So now the entire question/premise is suspect. Those are the best >_ – user2864740 Mar 01 '14 at 07:57
Perhaps it's the version of sql or .net the op is using or they simply havent tried it (having read documentation). Others can repo issues http://stackoverflow.com/a/12083128/81053 – Chris Moutray Mar 01 '14 at 08:09
Please look at my Edit to my OP. – WackStr Mar 01 '14 at 12:54
@WackStr see update but i don't think it will help but perhaps there is other config that limits uploads... – Chris Moutray Mar 01 '14 at 20:03

score -1 · Answer 2 · answered Mar 01 '14 at 06:44

No, this is what the description actually says:

Array of type Byte. A variable-length stream of binary data ranging between 1 and 8,000 bytes. Implicit conversion fails if the byte array is greater than 8,000 bytes. Explicitly set the object when working with byte arrays larger than 8,000 bytes.

I would assume that what that actually means is that you cannot use AddWithValue to have a parameter infer the type as VarBinary if the byte array is over 8000 elements. You would have to use Add, specify the type of the parameter yourself and then set the Value property, i.e. use this:

command.Parameters.Add("@MyColumn", SqlDbType.VarBinary).Value = myByteArray;

rather than this:

command.Parameters.AddWithValue("@MyColumn", myByteArray);

The "use this" is what the OP is using. – user2864740 Mar 01 '14 at 06:45 — user2864740, Mar 01 '14 at 06:45

score -1 · Answer 3 · answered Nov 20 '15 at 00:16

Adding the length of data seems to be the fix

var dataParam = cmd.Parameters.AddWithValue("@Data", (object)data.Data ?? DBNull.Value);
                if (data.Data != null)
                {
                    dataParam.SqlDbType = SqlDbType.VarBinary;
                    dataParam.Size = data.Data.Length;
                }

Inserting a byte array larger than 8k bytes

3 Answers3

Linked