DataTable with a byte[] field as parameter to a stored procedure
The representation for a binary string in .NET is the SqlBinary
structure.
You want to add your column like this:
dt.Columns.Add("BinaryMessage", typeof(SqlBinary));
The SqlBinary
class has an explicit conversion to a byte array and an implicit conversion from a byte array, so the value from a byte array to the column is a simple matter of assignment, while getting a byte array from the column requires an explicit cast.
Related videos on Youtube
kei
Updated on July 30, 2022Comments
-
kei almost 2 years
I've been reusing this method of using a DataTable as a parameter to a stored procedure and it's been working great. This is the simplified working code:
using (dbEntities dbe = new dbEntities()) { var dt = new dataTable(); dt.Columns.Add("ID"); dt.Columns.Add("Message"); dt.Columns.Add("CreatedOn", typeof(DateTime)); foreach (var row in randomDataSource) { dt.Rows.Add( row.id, row.message, DateTime.Now ); } var tableType = new SqlParameter("tableType", SqlDbType.Structured); tableType.Value = dt; tableType.TypeName = "[dbo].[RandomTableType]"; dbe.ExecuteStoreCommand( "EXEC [dbo].[SaveTable] @tableType", new object[] { tableType } ); }
The problem arises when the field I want to add is of a binary type. i.e.:
dt.Columns.Add("BinaryMessage", typeof(byte[]));
The corresponding column in the database is
varbinary(MAX)
by the way. When I try to run this, I get this error:Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.
How do I modify what I have to make this work?
-
Andomar over 11 years+1 Well found! It would be clearer if the code that sends the
DataTable
off to SQL Server threw an exception for atypeof(byte[])
column. The SQL Server error message is anything but clear. -
Dan Forbes about 8 years@casperOne - Thank you for posting this answer - it really helped me out! Unfortunately, I've encountered a similar problem in an edge case. I'd love to hear if you have any thoughts :-)