Dapper how to insert multiple rows with same foreign key

Question

I have a child table that rerferences a parent table with a 1 to many relationship. PK -> multiple foreign keys. The reason is the FK table are a list of objects related to an ID on the parent table.

How can I use dapper to insert multiple rows with the same FK?

assessmentModel.locationInformationModels = new List<LocationInformationModel>();

string sqlStatement = @"INSERT INTO LocationInformation
                                ([LocationInformationId]                          
                                ,[Address1]
                                ,[Address2]
                                ,[City]
                                ,[State]
                                ,[Zip])
                                VALUES(
                                @LocationInformationId,@Address1,@Address2,@City,@State,@Zip)";            
         
using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var ts = connection.BeginTransaction())
                {
                    var retVal = await connection.ExecuteAsync(sqlStatement, assessmentModel.locationInformationModels, transaction:ts);
                    ts.Commit();
                    return retVal;
                }        
            }

So if I have 1000 rows. I call the database 1000 times? That makes no sense — CodeMan03, Feb 19 '23 at 05:25
Seems I get the question wrong :o) - Can you add some more information about the table structure where we can see the PK and FK and the design of the two related classes where we also see the PK and FK you like to use. — Sir Rufo, Feb 19 '23 at 07:51
There is a solution with Dapper-Plus https://dapper-plus.net/then-bulk-insert#then-bulk-insert-with-one-to-many-relation — Sir Rufo, Feb 19 '23 at 08:09
Are you talking about Dapper-Plus or "add some more information"? — Sir Rufo, Feb 22 '23 at 05:44
@SirRufo "Dapper"-Plus has nothing to do with Dapper. Someone hijacked the name and poisoned search indexes so their faulty non-docs and closed source products appear instead of the real Dapper. The creators of Dapper have some very strong thoughts on this, starting with `At least fix the doc errors!`. — Panagiotis Kanavos, Feb 22 '23 at 08:58
@CodeMan03 what's the actual question? How to retrieve the `LocationInformation.Id` value? `connection.ExecuteAsync(...,locationInformationModels)` already inserts each item, one at a time. Dapper won't rewrite your SQL query to accept multiple rows. If you want to retrieve the ID you need to use the `OUTPUT` clause and use `QueryAsync` instead of `ExecuteAsync` — Panagiotis Kanavos, Feb 22 '23 at 09:09

score 3 · Accepted Answer · answered Feb 22 '23 at 09:41

The question is a bit unclear. The code shows inserting a list of items without anything that looks like a foreign key. In any case, Dapper is a microORM, it doesn't handle relations between entities, or auto-update keys. It won't change the supplied SQL statement to handle multiple objects either.

I suspect the real question is how to return a generated value from the database, eg an IDENTITY key, although the code shows that LocationInformationId is supplied by the application. To do this, the query will have to be modified to return any generated value, eg with the OUTPUT clause.

Returning generated values

For example, this will return the auto-generated ID :

var sql = """
    INSERT INTO LocationInformation
        ([Address1]
        ,[Address2]
        ,[City]
        ,[State]
        ,[Zip])
    OUTPUT inserted.LocationInformationId
    VALUES(@Address1,@Address2,@City,@State,@Zip)
    """;

The question's code already uses ExecuteAsync(sql, IEnumerable<T>,...) to insert multiple items. That executes the query once for every item but doesn't return any results. There's no equivalent for QueryAsync, so the application will have to execute the INSERTs explicitly :

foreach(var item in items)
{
    var newId=await connection.QueryAsync(sql,item);
    item.Id=newId;
}

Row constructors

It's possible to insert multiple rows with a single INSERT using row constructors, but that requires generating the SQL query dynamically :

    INSERT INTO LocationInformation
        ([Address1]
        ,[Address2]
        ,[City]
        ,[State]
        ,[Zip])
    VALUES
    OUTPUT inserted.LocationInformationId
    (@Address1_01,@Address2_01,@City_01,@State_01,@Zip_01),
    (@Address1_02,@Address2_02,@City_02,@State_02,@Zip_02),
    (@Address1_03,@Address2_03,@City_03,@State_03,@Zip_03),
    ...

All item values will have to be flattened by the application to a list of parameters too. Not very helpful.

Table valued parameters

Another option is to use a table-valued parameter type parameter to insert multiple values passed as a DataTable at once. This requires creating a table valued parameter type in the database and converting the data into a DataTable, eg :

CREATE TYPE dbo.LocationTableType AS TABLE  
    ( Address1 nvarchar(50), ...  )

The query should change to an INSERT ... SELECT using the table parameter :

var sql = """
    INSERT INTO LocationInformation
        ([Address1]
        ,[Address2]
        ,[City]
        ,[State]
        ,[Zip])
    OUTPUT inserted.LocationInformationId
    SELECT Address1,Address2,City,State,Zip 
    FROM @table
    """;

MoreLinq's ToDataTable can be used to convert the input items to a DataTable. After that, a QueryAsync call will execute the INSERT and return the list of outputs :

var table=items.ToDataTable();
var newIds=await connection.QueryAsync(sql,table);

score 0 · Answer 2 · answered Feb 22 '23 at 08:53

0

If I understand you correctly you want to do inserts on the table in batches. Pure Dapper does not have any built in way to do bulk inserts, so your options would be either using Dapper-Plus or SqlBulkCopy for all bulk actions you want to perform.

With SqlBulkCopy you could set up a dynamic columnmapping using reflection to avoid having to define all columns explicitly.

answered Feb 22 '23 at 08:53

FinneVirta

374
1
4
14

[Here](https://stackoverflow.com/a/9947259/13525924) is one way to set up SqlBulkCopy which could be modified for inserts instead of deletes. – FinneVirta Feb 22 '23 at 08:57

Dapper how to insert multiple rows with same foreign key

2 Answers2