How to specify foreign keys between two DataTables in SQL Bulk Copy operation?

Question

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.");
                        }
                    }

Nimish David Mathew

View more on Stack Overflow

WynDiesel · Accepted Answer · 9/13/2018 11:58:38 AM

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.

Jonathan Magnan · Accepted Answer · 9/13/2018 11:57:44 AM

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...
}

How to specify foreign keys between two DataTables in SQL Bulk Copy operation?

Question

Fastest Entity Framework Extensions

Accepted Answer

Expert Answer

Performance

Expression Evaluator