10

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?

casperOne
  • 73,706
  • 19
  • 184
  • 253
kei
  • 20,157
  • 2
  • 35
  • 62

1 Answers1

15

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.

casperOne
  • 73,706
  • 19
  • 184
  • 253
  • 2
    +1 Well found! It would be clearer if the code that sends the `DataTable` off to SQL Server threw an exception for a `typeof(byte[])` column. The SQL Server error message is anything but clear. – Andomar Sep 20 '12 at 19:45
  • @casperOne - Thank you for posting this answer - it really helped me out! Unfortunately, [I've encountered a similar problem in an edge case](http://stackoverflow.com/questions/35782496/empty-datatable-causes-errors-when-table-valued-parameter-has-varbinary-types). I'd love to hear if you have any thoughts :-) – Dan Forbes Mar 03 '16 at 21:01
  • This is a little gem. Still saving people from much pain over 10 years later! The issue of datablob handling still hasn't improved. – VariableSquid Oct 10 '22 at 11:10