DEV Community

Zoltan Halasz
Zoltan Halasz

Posted on • Edited on

Bulk Insert in Dapper into MS SQL

I am collecting costs by employee from 6 sources and then insert them into a a GeneratedDetail table. But the collected data has 1000+ rows, and the insertion one by one row is very slow. I needed to find a bulk insert solution.

I am using Dapper as ORM in my C# project, and MS SQL Database.
According to research, my approach would be as below. It works now quite fast, I am satisfied with the speed of execution. Feel free to comment and propose a better solution.
note: spGeneratedDetailInsert is a stored procedure with many parameters.

    public  void GeneratedDetailInsertBulk1(List<GeneratedDetail> DetailList)
    {
        using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
        {
            var sql = "";           
            foreach (var mydet in DetailList)
            {               
                sql = sql + "Exec spGeneratedDetailInsert '" + mydet.BillingCode + "', " +
                    mydet.Emp_id + ", '" +
                    mydet.Emp_name + "', " +
                    mydet.HrId + ", " +
                    mydet.Entity_id + ", " +
                    mydet.BU_id + ", '" +
                    mydet.Currency + "', '" +
                    mydet.Item + "', " +
                    mydet.ExchangeRate + ", " +
                    mydet.InitValueRON + ", " +
                    mydet.InitValueCurr + ", " +
                    mydet.MUValueCurr + ", " +
                    mydet.TotalRon + ", '" +
                    mydet.ServiceType + "', '" +
                    mydet.CostCategory + "', '" +
                    mydet.UpdatedBy + "', '" +
                    mydet.UpdatedAt + "', '" +
                    mydet.PeriodId + "' ; ";                                   
            }
             connection.Execute(sql);
        }
    }
Enter fullscreen mode Exit fullscreen mode

Note: I am not using Dapper Plus, and I don't want to.

Top comments (8)

Collapse
 
buinauskas profile image
Evaldas Buinauskas • Edited

Why not simple?

public void GeneratedDetailInsertBulk1 (List DetailList)
{
    using (var connection = new System.Data.SqlClient.SqlConnection (connectionString))
    {
        const string sql = "spGeneratedDetailInsert";

        connection.Execute (sql, DetailList, commandType : CommandType.StoredProcedure);
    }
}

What you have there is a SQL injection vulnerability, don't concatenate strings to build SQL statements.

Also Dapper should support a list of parameters and be able to execute that stored procedure for each list parameter.

If it actually doesn't, foreach will definitely work:

public void GeneratedDetailInsertBulk1 (List DetailList)
{
    using (var connection = new System.Data.SqlClient.SqlConnection (connectionString))
    {
        const string sql = "spGeneratedDetailInsert";

        foreach (var detail in DetailList)
        {
            connection.Execute (sql, detail, commandType : CommandType.StoredProcedure);
        }
    }
}

The better way would be to not use stored procedures and if you need an actual bulk insert, convert your list to a DataTable, using a method, such as in here and then do the actual bulk insert, take sample code from this StackOverflow question

Collapse
 
zoltanhalasz profile image
Zoltan Halasz • Edited

I appreciate your response. Structurally your solution is more beautiful, but mine is 2x faster. I have 850 records, and my solution inserts them in 15 sec, whereas the proposed solution is around 33 sec. Speed is very important here, as there will be around 1500 - 2000 records on a monthly basis, and we will use an Azure SQL database to store the data, so updates are a bit slow.
Thank you for your suggestion anyway, it's great way to learn and try.

Collapse
 
buinauskas profile image
Evaldas Buinauskas

850 records in 15 seconds is still really, really slow :)

Are your inserts complicated so that you need a stored procedure?

With a proper bulk insert you could do all of that in less than a second.

Thread Thread
 
zoltanhalasz profile image
Zoltan Halasz

Yes, I checked your suggestion with the proper SQL bulk insert. It now works, indeed around 1 second. I needed to convert the List to Datatable using the suggested method. Thank you! :)

Thread Thread
 
buinauskas profile image
Evaldas Buinauskas

There you go, way better than initial way you had it. Also tons of times faster.

Collapse
 
mikependon profile image
Mike Pendon (Zym) • Edited

Hi, had you tried RepoDb.InsertAll(DetailList)? That is a "packed" statements. The first run could be slower by 1 sec due to compilation. The second run would be extremely fast.

Otherwise, RepoDb.BulkInsert which is using the SqlBulkCopy underneath that does the real bulk operation. That would only take 1 millisecond, I suppose.

Please let us hear it if you have done so. Thanks

Collapse
 
zoltanhalasz profile image
Zoltan Halasz

Thanks Michael, I figured it out already, see my post: dev.to/zoltanhalasz/bulk-insert-wi...

Collapse
 
mikependon profile image
Mike Pendon (Zym)

Great and thanks!