How to use Dapper for bulk insert with foreign keys?

Question

I have a C# data structure like so:

public class Invoice
{
    public string InvoiceNumber {get;set;}
    public List<SalesPerson> SalesPersons {get;set;}
}

public class SalesPerson
{
    public string Name {get;set;}
}

This corresponds to two tables: Invoice and SalesPerson. An invoice can have any number of these associations.

Now, I'm creating a bulk upload facility, taking in a CSV and creating a collection of Invoices (which each having some number of associated SalesPerson records).

I can think of a naive way to do this with Dapper: loop through each Invoice, insert it, get identity (an integer ID column), loop through each SalesPerson in that invoice, insert using the identity I got as a foreign key. But that seems like it's going to be a lot of unnecessary traffic to/from my SQL server.

I'm aware of the 'bulk' insert where I can execute an insert command multiple times, but I don't see how that would work in this case, since I don't know the foreign key at the time I'm constructing the parameters.

Is there a better way to do this insert than the naive way I've outlined above? Is Dapper the wrong tool for this? (I'm using SQL Server; if there's some tSQL command(s) that can help me, I'm unaware of them)

Possible duplicate of [SQL - Inserting a row and returning primary key](http://stackoverflow.com/questions/8479315/sql-inserting-a-row-and-returning-primary-key) — Rob, Feb 19 '16 at 04:05
Take a look at marc_s' answer - this will depend on your database, not on Dapper. — Rob, Feb 19 '16 at 04:05
An alternative would be to create a normalized table, which contains the columns for *both* entities. Insert the rows as a batch, and from there, split into their destination tables. — Rob, Feb 19 '16 at 04:08

score 3 · Accepted Answer · edited May 23 '17 at 10:30

Dapper aims to be a general purpose ADO.NET tool, with some minimal support for provider-specific features. Bulk insert is not a general API - the is no generic bulk insert class. If you are using SQL Server, SqlBulkCopy would be the obvious choice. When combining that with a class-based object-model, FastMember is a good ally. For example:

var data = ... // some List<Foo> where Foo has Id, Name and Description
using(var bcp = new SqlBulkCopy(connection)) // SqlBulkCopy
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description")) // FastMember
{
    // these bits are part of the SqlBulkCopy API; WriteToServer accepts *either* a
    // DataTable or an IDataReader; we're providing the latter via FastMember
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(reader);
}

In terms of dapper, there is support for table-valued-parameters, but that requires you to create a named type in SQL Server. But if you want to consider that route, see Does Dapper support SQL 2008 Table-Valued Parameters?

One advantage of the table-valued-parameters option is that you can combine your INSERT statement with the OUTPUT clause to get all the ids back in one go. By comparison: with SqlBulkCopy you would need to query the data after the insert (essentially: nothing comes back from a SqlBulkCopy operation).

This is helpful information, and I certainly can use something besides dapper, but I'm missing something conceptually--it's two tables that I have to insert into, and the 2nd table needs the key from the 1st table (which is auto-generated for me by SQL Server). How do I coordinate that? — Matthew Groves, Feb 19 '16 at 13:43
@mgroves in that case, you probably want to look at the table-valued-parameter and `OUTPUT` clause usage — Marc Gravell, Feb 19 '16 at 13:47

How to use Dapper for bulk insert with foreign keys?

1 Answers1