I am consuming a large amount of JSON data and storing that in a SQL Server database via the SQLBulkCopy class.
SqlBulkCopy bulkInsert = new SqlBulkCopy(conn); bulkInsert.DestinationTableName = "dbo.Orders"; conn.Open(); bulkInsert.WriteToServer(rows.ToArray());
rows is a list of type DataRow.
The data I am consuming is orders, so the bulk of the data is being inserted into the orders table, but some other tables need to be inserted into as well, however these inserts must have a reference to the rows inserted into the orders table.
Is this possible with SQLBulkCopy or do I have to manually insert each row, query the database for the inserted row to get the reference field back and then use that value in future inserts?
SqlBulkCopy cannot return the inserted identity value.
So using only SqlBulkCopy is impossible but it can be achieved by using some trick with a temporary table.
Disclaimer: I'm the owner of the project Bulk Operations
This project overcome SqlBulkCopy limitation by adding some features like:
var bulk = new BulkOperation(connection) bulk.DestinationTableName = "dbo.Orders"; bulk.ColumnMappings.Add("OrderId", ColumnMappingDirectionType.Output); bulk.ColumnMappings.Add("Price"); bulk.ColumnMappings.Add("Quantity"); bulk.BulkInsert(rows);