Discussion on: Bulk Insert in Dapper into MS SQL

[Evaldas Buinauskas]

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

[Zoltan Halasz]

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.

[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.

[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! :)

[Evaldas Buinauskas]

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