Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

bulkcopy with primary key not working

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,

Note

when I delete my primary key in the database, everything works perfectly

like image 564
Marco Dinatsoli Avatar asked Nov 21 '14 11:11

Marco Dinatsoli


2 Answers

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");
like image 186
Marco Dinatsoli Avatar answered Nov 09 '22 17:11

Marco Dinatsoli


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.

like image 42
Remus Rusanu Avatar answered Nov 09 '22 16:11

Remus Rusanu