Insert many rows with Dapper

Question

what's the best solution to insert many rows into the SQL Server with Dapper? When I have got about 8k records my databaseconnection timeouts and fails.

In my application at the end stage I have got lets say a list of tables, where each table got list of rows.

What i do is creating transaction and foreaching on each table and then foreaching on each row and conn.CreateCommand, filling parameters and executeNonQuery. So if I got 9k records I am actually doing 9k executeNonQuery operations.

Is there any better option for me?

Answer 1

Agree with DaniCE, SqlBulkCopy is the way to go here. Been in that situation lately where I did most of the data work with dapper but with larger amounts of data, in my case millions of records, Dapper was for once not my best friend.

    private void BulkCopy(SqlConnection sqlConnection,string tableName, DataTable dataTable)
    {
        using (var bulkCopy = new SqlBulkCopy(sqlConnection))
        {
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = 50000;
            bulkCopy.BulkCopyTimeout = 60; //seconds

            bulkCopy.WriteToServer(dataTable);
        }
    }