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.
Add a comment
|
3 Answers
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:
2 Comments
Johan
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.
Zeek2
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.
Try this:
SqlParameter blobParam = new SqlParameter("@blob", SqlDbType.VarBinary, buffer.Length);
blobParam.Value = buffer;
cmd.Parameters.Add(blobParam);
See if that works
7 Comments
dmg
what was the solution, just out of curiousity?
John Saunders
What is the type of
buffer?
StuartQ
@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;
John Saunders
Thanks, @StuartQ, but that doesn't tell me the .NET type of
buffer.Zeek2
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.]
|
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.
