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.




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