I needed to do Bulk insert with dapper rainbow

Question

I am using the dapper rainbow database.cs extensions,

private void insertList(IEnumerable<myObject> list)
    {
          using (SqlConnection conn = new SqlConnection(connectionString))
        {
            var db = myDB.Init(conn, commandTimeout: 100);
            db.myTable.tableName = "ds.myTable";
            Parallel.ForEach(dsList, a => db.myTableInsert(a)
                        );
            db.Dispose();
        }
    }

This won't work, i think i need to open and close the connection inside the Parallel.ForEach. Is that the write way to do it?

I wanted to use this extension, its very helpful and hand, but having this problem of inserting a list. I could not find anything online about using this extension and using a list as well.

Answer 1

Generally database connections are NOT thread safe, so doing inserts in parallel on the same connection like that is bound to cause trouble.

So I would say that yes, you would need to open and close the connection inside the Parallel.ForEach(). You might want to benchmark that as well. I'm not entirely convinced that doing inserts in parallel like that with multiple database connections would be any faster than doing them in a regular loop on a single connection.