I needed to do Bulk insert with dapper rainbow

.net c# dapper dapper-extensions 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.

Accepted Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why