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?

"Is my parallel approach is wrong?" - quite possibly.

Mitch Wheat
– Mitch Wheat

2012-04-20 05:48:02 +00:00
Commented Apr 20, 2012 at 5:48 — Mitch Wheat
– Mitch Wheat, Commented Apr 20, 2012 at 5:48

Jon Skeet · Accepted Answer · 2012-04-20 06:04:38Z

9

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.

answered Apr 20, 2012 at 6:04

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Renushi Over a year ago

thank you sir for your quick response .I will try the second approach .(open and close the connection once per operation)

user2849221 · Accepted Answer · 2018-08-24 16:40:16Z

0

I don't have the reputation to comment but it seems I can answer. That is a bit strange Anyway my comment was that temporary tables are per connection, so opening a new connection means you cannot see the temporary tables created by the other task.

Global temporary tables might be the answer but you either have to a) use a single global temp table and partition the data using some key b) use uniquely named tables which means dynamic sql

All a bit of a mess really

answered Aug 24, 2018 at 16:40

user2849221

1138 bronze badges

Collectives™ on Stack Overflow

SqlConnection with Parallel programming

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related