How to specify foreign keys between two DataTables in SQL Bulk Copy operation?
asp.net c# datatable sqlbulkcopy
Question
I have two DataTables A and B. A is the parent table. Rows in B contain a field ParentId that references A.
I want to bulk insert A and B separately. After inserting A, how do I set the ParentId in the corresponding child rows in B?
Update:
I have the following code for creating the DataTables:
var a= new DataTable("A");
DataColumn id= new DataColumn("Id", typeof(int));
billablePriceMapId.AutoIncrement = true;
billable.Columns.Add(id);
DataColumn fee = new DataColumn("Fee", typeof(decimal));
billable.Columns.Add(fee);
DataColumn[] keys = new DataColumn[1];
keys[0] = id;
billable.PrimaryKey = keys;
For the child table:
var b= new DataTable("B");
DataColumn id= new DataColumn("Id", typeof(int));
billablePriceMapId.AutoIncrement = true;
billable.Columns.Add(id);
DataColumn parentId= new DataColumn("ParentId", typeof(int));
billable.Columns.Add(parentId);
DataColumn[] keys = new DataColumn[1];
keys[0] = id;
billable.PrimaryKey = keys;
I have not set up any relationship between the two tables. I would like to know how I could do that the right way.
Following is the code that I use for bulk insert:
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
try
{
bulkCopy.DestinationTableName = "dbo.A";
bulkCopy.WriteToServer(a);
bulkCopy.DestinationTableName = "dbo.B";
bulkCopy.WriteToServer(b);
}
catch (Exception ex)
{
Logger.Error(ex, "Bulk copy operation failed.");
}
}
Expert Answer
SqlBulkCopy doesn't return the identity values. So unless your table A have another unique column such as a "Code" to allow to perfect a SELECT you will not be able to retrieve the generated identity values.
Disclaimer: I'm the owner of the project Bulk Operations
This library is not free but allows to output identity values. It uses under the hood SqlBulkCopy with Temporary Table to allow this feature
using (var bulkCopy = new BulkOperation(connection))
{
bulkCopy.Transaction = transaction;
bulkCopy.DestinationTableName = "dbo.A";
bulkCopy.ColumnMappings.Add("Id", ColumnMappingDirectionType.Output);
bulkCopy.ColumnMappings.Add("Col1");
bulkCopy.ColumnMappings.Add("Col2");
bulkCopy.BulkInsert(a);
// Copy identity value in `Id` from table A to `ParentId` in table B
// ...code...
}
Popular Answer
I would not use the identity to link the two tables, rather, I would use a GUID to link them. Like this :
var a= new DataTable("A");
DataColumn guid= new DataColumn("Guid", typeof(Guid));
billable.Columns.Add(Guid.NewGuid());
DataColumn id= new DataColumn("Id", typeof(int));
billablePriceMapId.AutoIncrement = true;
billable.Columns.Add(id);
DataColumn fee = new DataColumn("Fee", typeof(decimal));
billable.Columns.Add(fee);
DataColumn[] keys = new DataColumn[1];
keys[0] = id;
billable.PrimaryKey = keys;
And then for the children
var b= new DataTable("B");
DataColumn id= new DataColumn("Id", typeof(int));
billablePriceMapId.AutoIncrement = true;
billable.Columns.Add(id);
DataColumn parentId= new DataColumn("ParentGuid", typeof(Guid));
billable.Columns.Add(parentGuid);
DataColumn[] keys = new DataColumn[1];
keys[0] = id;
billable.PrimaryKey = keys;
This means that you know what the link will be before inserts, before the identify is generated.