The given value of type String from the data source cannot be converted to type nvarchar of the specified target column

Question

I've tested it with and without explicit conversion in the datatable columns, but it kept throwing me the exception System.InvalidOperationException.

FYI, those columns with typeof(string) are all nvarchar in my database. I'm passing in a List called test which is of a user-defined type.

I wasn't going to use sqlbulkcopy initially, but ever since porting over to azure database, I need a faster insertion query.

Any advice would be appreciated.

var dt = new DataTable();
dt.Columns.Add("LinkID", typeof(string));
dt.Columns.Add("RoadName", typeof(string));
dt.Columns.Add("RoadCategory", typeof(string));
dt.Columns.Add("SpeedBand");
dt.Columns.Add("MinimumSpeed", typeof(string));
dt.Columns.Add("MaximumSpeed", typeof(string));
dt.Columns.Add("StartLatitude");
dt.Columns.Add("StartLongitude");
dt.Columns.Add("EndLatitude");
dt.Columns.Add("EndLongitude");
dt.Columns.Add("Distance", typeof(string));

for (int i = 0; i < test.Count; i++)
{
    dt.Rows.Add(test[i].LinkID, test[i].RoadName, test[i].RoadCategory, 
       test[i].SpeedBand, test[i].MinimumSpeed, test[i].MaximumSpeed, 
       test[i].StartLatitude, test[i].StartLongitude, test[i].EndLatitude, 
       test[i].EndLongitude, test[i].Distance);
}

string sqlConnectionString = "//secret";

using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
    try
    {
        conn.Open();
    }
    catch (Exception e)
    {
        Debug.WriteLine(e);
    }

    using (var sqlBulk = new SqlBulkCopy(conn))
    {
        //sqlBulk.BatchSize = 1000;
        sqlBulk.DestinationTableName = "dbo.TrafficSpeedBands";

        try
        {
            // Write from the source to the destination.
            sqlBulk.WriteToServer(dt);
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.Message);
        }
    }
}

Answer 1

Your problem lies here:

var dt = new DataTable();
//...
dt.Columns.Add("SpeedBand");
//...
dt.Columns.Add("StartLatitude");
dt.Columns.Add("StartLongitude");
dt.Columns.Add("EndLatitude");
dt.Columns.Add("EndLongitude");

By default, the method Add(string columnName) will add a column of type string. You therefore have a type mismatch with the underlying db table and the bulk copy fails. Read here for more details.

Your solution with DataAdapter works because the adapter reads the schema of the underyling table and sets up the DataTable with compatible types for you.

Answer 2

i've switched to using a dataAdapter and it works now

string sqlConnectionString = "//secret";

            using (SqlConnection conn = new SqlConnection(sqlConnectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e);
                }
                Debug.WriteLine("Connection opened");
                var table = new DataTable();

                // read the table structure from the database
                using (var adapter = new SqlDataAdapter($"SELECT TOP 0 * FROM dbo.TrafficSpeedBands", conn))
                {
                    adapter.Fill(table);
                };

                Debug.WriteLine("Filling in rows");
                for (var i = 0; i < test.Count; i++)
                {
                    var row = table.NewRow();
                    row["LinkID"] = test[i].LinkID;
                    row["RoadName"] = test[i].RoadName;
                    row["RoadCategory"] = test[i].RoadCategory;
                    row["SpeedBand"] = test[i].SpeedBand;
                    row["MinimumSpeed"] = test[i].MinimumSpeed;
                    row["MaximumSpeed"] = test[i].MaximumSpeed;
                    row["StartLatitude"] = test[i].StartLatitude;
                    row["StartLongitude"] = test[i].StartLongitude;
                    row["EndLatitude"] = test[i].EndLatitude;
                    row["EndLongitude"] = test[i].EndLongitude;
                    row["Distance"] = test[i].Distance;

                    table.Rows.Add(row);
                }

                using (var sqlBulk = new SqlBulkCopy(conn))
                {
                    Debug.WriteLine("Ready to load live");
                    sqlBulk.DestinationTableName = "dbo.TrafficSpeedBands";

                    try
                    {
                        // Write from the source to the destination.
                        sqlBulk.WriteToServer(table);
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(ex.Message);
                    }                                        

                    Debug.WriteLine("Done");
                }

                conn.Close();
            }