CODEWITHSUNDEEP
c#sql-serverbulkinsertdapper
Matthew Groves
Matthew Groves

Reputation: 26096

How to use Dapper for bulk insert with foreign keys?

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)

Upvotes: 3

Views: 3076

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062550

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

Upvotes: 3

Related Questions