SqlConnection with Parallel programming

Question

This is my existing code which save some data to several tables

using (SqlConnection conn = new SqlConnection("myConnString"))
{
   DoWork1(conn);
   DoWork2(conc);
   DoWork3(conn);
}

In order to speed my code up so i try to get .net TPL support and i rehanged my code as below

using (SqlConnection conn = new SqlConnection("myConnString"))
{
   ParallelOptions pw = new ParallelOptions();
   pw.MaxDegreeOfParallelism = Environment.ProcessorCount;

   Parallel.Invoke(pw,()=> DoWork1(conn),()=> DoWork2(conc),()=> DoWork3(conn));
} 

But this throws me an Internal connection fatal error exception from the ExecuteNonQuery() method in my data access layer.Is my parallel approach is wrong?

Answer 1

Well, there are ways it could potentially be made to work using MARS - but I would suggest a different approach. (I don't know whether MARS supports using the same connection across multiple threads, even though it allows multiple concurrent operations.)

Instead of trying to reuse one connection in all the parallel tasks, make each task open (and close) a connection for itself, and let connection pooling handle the efficiency side of that. That's general best practice in .NET whether you're using parallelism or not: open the connection, do some work, close the connection.