How can I use SqlBulkCopy with binary data (byte[]) in a DataTable?

Question

I'm trying to use SqlBulkCopy to import a bunch of data to our website. In most of the other areas we're using Entity model which uses byte arrays to represent binary data in SQL. However, SqlBulkCopy seems to be confusing byte[] with string. Everything seems to be working fine except for this one binary column which throws an exception: "The given value of type String from the data source cannot be converted to type binary of the specified target column."

I've created a small test case to illustrate the problem:

using System.Data;
using System.Data.SqlClient;

namespace SqlBulkCopyTest
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable table = new DataTable("BinaryData");
            table.Columns.Add("Data");

            for (int i = 0; i < 10; i++)
            {
                var row = table.NewRow();
                row["Data"] = new byte[5] { 1, 2, 3, 4, 5 };
                table.Rows.Add(row);
            }

            using (var connection = 
                new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=TestBulkCopy;Integrated Security=True"))
            {
                connection.Open();
                using (var copier = new SqlBulkCopy(connection))
                {
                      copier.DestinationTableName = table.TableName;
/* EXCEPTION HERE: */ copier.WriteToServer(table);
                }
            }
        }
    }
}

This uses a test database with a BinaryData table which has a single binary(5) column named Data.

Any help would be greatly appreciated

Andomar · Accepted Answer · 2010-01-04 20:19:45Z

13

Instead of:

table.Columns.Add("Data");

Add the "Data" column as a binary:

table.Columns.Add("Data", typeof(Byte[]));

answered Jan 4, 2010 at 20:19

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I use SqlBulkCopy with binary data (byte[]) in a DataTable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related