C# bulkcopy to SQL table type issue

c# datagridview sql sqlbulkcopy

Question

Using C#, .net 4, Visual Studio 2010, and SQL Server 2008.

I currently have a table called NewPeriodPareto contains the following columns:

  • PG nvarchar(50)
  • Part nvarchar(50)
  • Sales float
  • LostSales float
  • Pareto6 int
  • p6 image
  • Pareto5 int
  • p5 image
  • Pareto4 int
  • p4 image
  • Pareto3 int
  • p3 image
  • CurrentPareto int
  • pNew Image
  • NewPareto int

The following is my code that try's to fill the table:

private void CreateNewPeriod()
{
        DataSet dsDG = new DataSet();
        DataTable dt = new DataTable();

        dsDG = (DataSet)ParetoGrid.DataSource;
        dt = dsDG.Tables[0];

        string ThetableName = "NewPeriodPareto";
        BulkInsertDataTable(myConn, ThetableName, dt);
}

public static void BulkInsertDataTable(string connectionString, string tableName, DataTable table)
{
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlBulkCopy bulkCopy =
                new SqlBulkCopy
                (
                connection,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                );

            bulkCopy.DestinationTableName = tableName;
            connection.Open();

            bulkCopy.WriteToServer(table);
            connection.Close();
        }
}

As you can see the code provided attempts to copy the data from my datatable. The datatable is bound to the My data grids source.

The following is the error I get:

@bulkCopy.WriteToServer(table);
The given value of type Int32 from the data source cannot be converted to type image of the specified target column.

Now the first thing I thought is that There was a type set wrong in one of my fields of my SQL table, but after checking many times, there appears to be nothing wrong.

So what I'm after is a solution to this little problem, or even an alternative/simpler way of achieving the same goal, that is to fill an SQL table from a programs DataTable.

Accepted Answer

[Short of time, so i am going to hive you a hint]

you should look into ColumnMappings of the SqlBulkCopy. This allows you to explicitly map the column of your source to the target. There might be some extra columns like an ID or similar that are in the way right now.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why