I am attempting to use SqlBulkCopy to import a lot of data to the website. In the majority of the the areas we are using Entity model which utilizes byte arrays to represent binary data in SQL. However, SqlBulkCopy appears to become confusing byte[] with string. Everything appears to become working fine aside from that one binary column which throws the best: "The given worth of type String in the databases can't be transformed into type binary from the specified target column."

I have produced a little test situation as one example of the issue:

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 utilizes a test database having a BinaryData table with a single binary(5) column named Data.

Any help could be greatly appreciated

Rather than:

table.Columns.Add("Data");

Add the "Data" column like a binary:

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