What SqlDbType maps to varBinary(max)?

Question

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.

Answer 1

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:

• Download and Upload images from SQL Server via ASP.Net MVC
• FILESTREAM MVC: Download and Upload images from SQL Server

Answer 2

Try this:

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

See if that works