I have a database table, with columns and a priamary key.
I want to do the bulkcopy, from a datatable in my c#.
When I have primary key in my table, I got exception because the table has 6 columns, while my datatable has just 5.
what should I do please?
Should I add the primary key to my datatable in my c#?
(if you need any code tell me pleae)
this is the datatable
private DataTable getBasicDataTable()
{
DataTable dataTable = new DataTable();
dataTable.Clear();
dataTable.Columns.Add("customerID", typeof(int));
dataTable.Columns.Add("firstName", typeof(string));
dataTable.Columns.Add("lastName", typeof(string));
dataTable.Columns.Add("showsNumber", typeof(int));
dataTable.Columns.Add("visitNumber", typeof(int));
dataTable.Columns.Add("cancellation", typeof(int));
return dataTable;
}
but in my database table, I have the exact same columns, but with extra ID
primary key,
when I delete my primary key in the database, everything works perfectly
I found the solution myself
bc.ColumnMappings.Add("customerID", "customerID");
sbc.ColumnMappings.Add("firstName", "firstName");
sbc.ColumnMappings.Add("lastName", "lastName");
sbc.ColumnMappings.Add("showsNumber", "showsNumber");
sbc.ColumnMappings.Add("visitNumber", "visitNumber");
sbc.ColumnMappings.Add("cancellation", "cancellation");
Use SqlBulkCopy.ColumnMappings
:
Column mappings define the relationships between columns in the data source and columns in the destination.
...
If the data source and the destination table have the same number of columns, and the ordinal position of each source column within the data source matches the ordinal position of the corresponding destination column, the ColumnMappings collection is unnecessary. However, if the column counts differ, or the ordinal positions are not consistent, you must use ColumnMappings to make sure that data is copied into the correct columns.
See the Example at SqlBulkCopyColumnMapping
for how to use it.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With