Error converting string to byte[] in c# DataTable

Question

I am reading in .csv data from various files into a C# DataTable object, respectively. As it is .csv, I am reading all data as string initially. Afterwards I want to write it to a SQL Server table using SqlBulkCopy class.

I need to convert the columns to the target data type in the destination and wanted to do it like this by implicitly letting DataTable doing the conversion:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace SQLBulkTest
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable("dt");

            DataColumn item = new DataColumn("item", typeof(String));
            dt.Columns.Add(item);

            DataColumn type = new DataColumn("type", typeof(String));
            dt.Columns.Add(type);

            DataColumn bin = new DataColumn("bin", typeof(String));
            dt.Columns.Add(bin);

            DataColumn floatItem = new DataColumn("floatItem", typeof(String));
            dt.Columns.Add(floatItem);

            // Add five items.
            DataRow NewRow;
            var teststring = "0x0050568F00041ED783A79F94B797E9B6";

            for (int i = 0; i < 5; i++)
            {
                NewRow = dt.NewRow();
                NewRow["item"] = i.ToString();
                NewRow["type"] = "Type " + i;
                NewRow["bin"] = teststring;
                NewRow["floatItem"] = i.ToString() + "," + i.ToString();
                dt.Rows.Add(NewRow);
            }

            DataTable dtCloned = dt.Clone();
            dtCloned.Columns[0].DataType = typeof(Int32);
            dtCloned.Columns[2].DataType = typeof(System.Byte[]);
            dtCloned.Columns[3].DataType = typeof(double);

            foreach (DataRow row in dt.Rows)
            {
                var mappedRow = dt.NewRow();
                mappedRow[0] = row[0];
                mappedRow[1] = row[1];
                mappedRow[2] = Encoding.UTF8.GetBytes((string)row[0]);
                mappedRow[3] = row[3];
                dtCloned.Rows.Add(mappedRow.ItemArray);
            }
        }
    }
}

When running the code I get the following error:

System.ArgumentException: 'Type of value has a mismatch with column typeCouldn't store <System.Byte[]> in bin column. Expected type is Byte[].'

What could I do to also import binary data? In SQL Server, it needs to be stored as varbinary.

Answer 1

There is an error in your code.

You should create the mappedRow from the dtCloned table. Because this one is the DataTable with the Byte[] column datatype

var mappedRow = dtCloned.NewRow();

Also there is no need to pass the ItemArray when you add the new row to the dtCloned table. You could just use mappedRow

dtCloned.Rows.Add(mappedRow);