Is there a more efficient way to insert an array of data inside my database? Asp.net-core + Dapper

user_12952563 0
Viewed 421

Is there any way to make this more efficient? It takes too long when I insert an array of 50+ items.

The method gets called by my API controller.

Thanks in advance, I'm still new to this stuff.

DataAccess data = new DataAccess();

[HttpPost]
public void Post(Contract[] contractList)
{
    data.insertContracts(contractList);
}

public class DataAccess
{
    public  void insertList(Contract[] contractList)
    {
        using (IDbConnection connection = new SqlConnection(connectString))
        {
            foreach (var item in contractList)
            {
                 Execute("dbo.spInsertContract @contract_Id, @color, @contract_Type, @ne_Type, @storn, @dispo_Id, @kw, @create_Date, @termin_Date", item);
            }
        }    
    }
}
dapper asp.net-core asp.net-core-webapi c#
1 Answers

This code works fine and its performance is comparable to the regular approach. So it is not really a way to insert big amounts of data. An ideal way to go here is to use SQL Bulk Copy and forget about Dapper.

public async Task InsertList(Contract[] contractList)
{
    using (var connection = new SqlConnection("ConnectionString"))
    {
        var parameters = contractList.Select(u =>
        {
            var tempParams = new DynamicParameters();
            tempParams.Add("@contract_Id", u, DbType.Int32, ParameterDirection.Input);
            tempParams.Add("@color", u, DbType.String, ParameterDirection.Input);
            tempParams.Add(" @contract_Type", u, DbType.String, ParameterDirection.Input);
            ...
            return tempParams;
        });

        await connection.ExecuteAsync("INSERT INTO [Contract] (contract_Id, color, contract_Type, ...) VALUES ( @contract_Id, @color, @contract_Type,...)", 
            parameters).ConfigureAwait(false);
    }
}
user_13856140
Related