18

What SqlDbType maps to varBinary(max)? SqlDbType.VarBinary says that it is limited to 8K. SQL Server documentation says that varbinary(max) can store aprrox. 2GB. But SqlDbType.VarBinary says that it is limited to 8K.

Amit
  • 21,570
  • 27
  • 74
  • 94

3 Answers3

29

SqlDbType.VarBinary with length -1 is the equivalent of VARBINARY(MAX), at least in theory. But the problem is a bit more complex, as there is also a type (not an enum value), namely SqlTypes.SqlBytes which can be used. And there is SqlTypes.SqlFileStream which can be used also for VARBINARY(MAX) types, when they have the FILESTREAM attribute.

But the problem is that none of these enums or types cover the real issue with working with VARBINARY(MAX) columns in ADO.Net: memory consumption. All these types, when used 'out-of-the-box', will create copies of the value allocated as a single array in memory, which is at best unperformant, but as content gets larger becomes down right impossible to use because allocation failures. I have a couple of articles that show the proper way to handle VARBINARY(MAX) values in ADO.Net using streaming semantics that avoid the creation of in-memory copies of the entire content:

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Really strange that the above did not work for me. I had to write: dtProducts.Columns.Add(new DataColumn("Cover", typeof(byte[])) { AllowDBNull = true }); notice the byte[]...this is what worked for me. – Johan Oct 29 '13 at 07:22
  • Misread this at first. First sentence should be "with length set to -1". Didn't solve my issue but good to know. Also there is a SqlType for varbinary now (2022): SqlDbType.VarBinary. – Zeek2 Jul 01 '22 at 10:06
7

Try this:

SqlParameter blobParam = new SqlParameter("@blob", SqlDbType.VarBinary, buffer.Length);
blobParam.Value = buffer;
cmd.Parameters.Add(blobParam);

See if that works

dmg
  • 608
  • 8
  • 15
  • 1
    what was the solution, just out of curiousity? – dmg Apr 28 '11 at 21:25
  • 1
    What is the type of `buffer`? – John Saunders Apr 29 '11 at 02:15
  • @JohnSaunders Although not the author, I've encountered a buffer when working with SqlGeometry. You can get SqlBytes out of the SqlGeometry type, so in my case the preceding lines would read something like: System.Data.SqlTypes.SqlBytes sqlBytes = sqlGeo.STAsBinary(); byte[] buffer = sqlBytes.Buffer; – StuartQ Oct 16 '17 at 11:24
  • Thanks, @StuartQ, but that doesn't tell me the .NET type of `buffer`. – John Saunders Oct 16 '17 at 11:29
  • @JohnSaunders It's byte[]. Should be showing in my latest edit. – StuartQ Oct 16 '17 at 11:31
  • @JohnSaunders the SqlParameter constructor takes a size argument (int) so I assume buffer is a byte array – plyawn Jul 12 '18 at 16:01
  • 1
    Could you pass "-1" instead of buffer.length. Where -1 implies "max" (2Gbytes?). If buffer is a byte-array (variable length?) rather than a fixed length buffer? [I am trying to read a varbinary from DB that I already wrote to DB.] – Zeek2 Jul 01 '22 at 10:12
0

This might help: http://msdn.microsoft.com/en-us/library/a1904w6t.aspx

As ugly as it might be, SqlDbType.Image appears to be the way to go. Looks like I need to update my ORM, since anything over 8k would currently break it.

Brian MacKay
  • 31,133
  • 17
  • 86
  • 125