Dapper to insert multiple rows into two tables using stored procedure

Question

I am using Dapper (https://github.com/StackExchange/Dapper) in asp.net core myweb api project. I have a requirement to create a master record and insert a set of rows into a child table.

Ex. OrdersMaster table
    create an order id in this table

    OrderDetails table
    ordermasterid
    order items

How can I do this using Dapper? Please share some code snippets if possible.

You should probably define a stored procedure to do this all inside of a transaction, and then call that procedure from your .NET application. Do you have access to add stored procedures? — JamesFaix, Apr 03 '18 at 18:14
yes, I can create a stored procedure, but I need to enter IEnumerable into a table. I wanted to do this with dapper. — blue, Apr 03 '18 at 18:19

JamesFaix · Answer 1 · 2018-04-03T18:38:43.997

I would implement the insert operations as a transactional stored procedure, and then call that from your .NET application.

You may need a table-valued type to pass in a list of data, like this:

CREATE TYPE List_Of_Items AS TABLE (
    ItemID INT NOT NULL,
    Quantity INT NOT NULL
)

The procedure might look like this

CREATE PROC Insert_Order_With_Details (
     @CustomerId INT,
     @Items List_Of_Items
) AS
BEGIN
    BEGIN TRANSACTION
        INSERT INTO OrdersMaster (CustomerId) VALUES @CustomerId

        DECLARE @OrderID INT
        SET @OrderID = SCOPE_IDENTITY() --last assigned id

        INSERT INTO OrderDetails (OrderId, CustomerId, ItemId, Quantity)
            SELECT @OrderID, @CustomerID, ItemID, Quantity
            FROM @Items
    COMMIT
END

Then in C#, I would suggest creating methods for creating your TVP. It is not as simple as you might like. This requires the using Microsoft.SqlServer.Server and using Dapper.Tvp.

    //This is a shell to create any kind of TVP
    private static void AddTableCore<T>(
        this DynamicParametersTvp dp,
        string tvpTypeName,
        Func<T, SqlDataRecord> valueProjection,
        IEnumerable<T> values,
        string parameterTableName)
    {
        var tvp = values
            .Select(valueProjection)
            .ToList();

        //If you pass a TVP with 0 rows to SQL server it will error, you must pass null instead.
        if (!tvp.Any()) tvp = null;

        dp.Add(new TableValueParameter(parameterTableName, tvpTypeName, tvp));
    }

    //This will create your specific Items TVP
    public static void AddItemsTable(this DynamicParametersTvp dp, IEnumerable<Item> items, string parameterTableName = "Items")
    {
        var columns = new[]
        {
            new SqlMetaData("ItemID", SqlDbType.Int)
            new SqlMetaData("Quantity", SqlDbType.Int)
        };

        var projection = new Func<Item, SqlDataRecord>(item =>
        {
            var record = new SqlDataRecord(columns);
            record.SetInt32(0, item.Id);
            record.SetInt32(1, item.Quantity);
            return record;
        });

        AddTableCore(dp, "Items", projection, items, parameterTableName);
    }

and then where you need to query you might do:

using (var cn = new SqlConnection(myConnectionString))
{
    var p = new DynampicParametersTvp(new {
        CustomerId = myCustomerId
    });
    p.AddItemsTable(items);

    cn.Execute("Insert_Order_With_Details", p, commandType: CommandType.StoredProcedure);
}

The commandType argument is super important. It defaults to plain SQL text and will error if you send the name of a proc.

If you want to put in multiple orders at once, you'll need to use table-valued parameters and the Dapper.Tvp package.

See this SO question Using Dapper.TVP TableValueParameter with other parameters as well as this documentation on TVP's from Microsoft https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine. I don't think all SQL vendors support TVPs.

What kind of data do you need per-row in the details table? That may work as a TVP in your proc, with one insert to `OrderMaster` and then you can get the order ID with `SCOPE_IDENTITY` and use that for the rows in `OrderDetails`. — JamesFaix, Apr 03 '18 at 18:30
I get following compiler error for dp.Add(new TableValueParameter(parameterTableName, tvpTypeName, tvp)); Error => "Reference to Type 'SqlDataRecord' claims it to be defined in 'System.Data' but it could not be found" — blue, Apr 03 '18 at 22:40
This question may be helpful for Core https://stackoverflow.com/a/43825757/4415493 — JamesFaix, Apr 04 '18 at 12:03

score 1 · Accepted Answer · answered Apr 04 '18 at 07:27

Using stored procedure as mentioned in other answer is good solution. My answer implements the same without Stored Procedure.

You have to use transaction. This way, either all the changes will be committed or rolled back. Code below assumes you are using Identity as Primary Key. Please refer this question for discussion about @@IDENTITY that I have used in below code.

Though code is not complete, I have put detailed comments to explain steps.

using (var connection = new SqlCeConnection("connection_string"))
{
    connection.Open();

    //Begin the transaction
    using (var transaction = connection.BeginTransaction())
    {
        //Create and fill-up master table data
        var paramMaster = new DynamicParameters();
        paramMaster.Add("@XXX", ...);
        ....

        //Insert record in master table. Pass transaction parameter to Dapper.
        var affectedRows = connection.Execute("insert into OrdersMaster....", paramMaster, transaction: transaction);

        //Get the Id newly created for master table record.
        //If this is not an Identity, use different method here
        newId = Convert.ToInt64(connection.ExecuteScalar<object>("SELECT @@IDENTITY", null, transaction: transaction));

        //Create and fill-up detail table data
        //Use suitable loop as you want to insert multiple records.
        //for(......)
        foreach(OrderItem item in orderItems)
        {
            var paramDetails = new DynamicParameters();
            paramDetails.Add("@OrderMasterId", newId);
            paramDetails.Add("@YYY", ...);
            ....

            //Insert record in detail table. Pass transaction parameter to Dapper.
            var affectedRows = connection.Execute("insert into OrderDetails....", paramDetails, transaction: transaction);
        }

        //Commit transaction
        transaction.Commit();
    }
}

Dapper to insert multiple rows into two tables using stored procedure

2 Answers2