Multiple row insertion in asp.net core with dapper package

Question

I want to perform multiple row insertion in asp.net core where dapper package is used. Here I use a model class, controller and another class. The result stored in database is a datatable which is converted from a list. Here I use a stored procedure with type.

public class PlanDetailsInputModel
{
    public int MembershipPlanDetailsId { get; set; }
    public int MembershipPlanId { get; set; }
    public int CreatedBy { get; set; }
    public string Flag { get; set; }
    public List<dtPlanFeatureListModel> dtPricePlanDetails { get; set; }
}

public class dtPlanFeatureListModel
{
    public long PricePlanFeatureId { get; set; }
}

This is my model class. Help me create my controller and the function.

score 0 · Answer 1 · answered Jul 03 '19 at 14:46

If you have an insert statement and a List of objects that match up with the fields of the insert statement you can do something like the following.

public void UpdateDB()
{
    List<Entity> entities = null;

    entities = GetData();

    var sql = @"INSERT INTO TABLE_01 ( Id, FieldA, FieldB ) VALUES ( @id, @FieldA, @Fieldb )";

    using (var conn = (IDbConnection) GetDatabaseConnection())
    {
        if (conn.State == System.Data.ConnectionState.Closed)
            conn.Open();

        using (var tx = conn.BeginTransaction())
        {
            //conn.Execute
            conn.Execute(sql, entities, tx);
            tx.Commit();
        }
    }

}

The code above will loop over all the rows in entities and perform the insert statements. If you leave out the transaction, then it will commit after each insert. But you include the transaction, it will commit after all the rows are inserted.

You can find more about it here

Although, your question doesn't say which class will be inserted. I don't think Dapper can handle your class PlanDetailsInputModel with the List inside it. You will have to flatten it to a DTO Class and use that DTO class in the Dapper command instead or use two dapper insert commands. One for PlanDetailsInputModel and another one for the List of dtPlanFeatureListModel.

HTH

Multiple row insertion in asp.net core with dapper package

1 Answers1