How to handle Parallel Execution C# for Database connection

Question

I have following code which will fire query on each database in SQL SERVER 2008R2,

public DataTable GetResultsOfAllDB(string query)
        {
            SqlConnection con = new SqlConnection(_ConnectionString);
            string locleQuery = "select name from [master].sys.sysdatabases";
            DataTable dtResult = new DataTable("Result");
            SqlCommand cmdData = new SqlCommand(locleQuery, con);
            cmdData.CommandTimeout = 0;

            SqlDataAdapter adapter = new SqlDataAdapter(cmdData);
            DataTable dtDataBases = new DataTable("DataBase");
            adapter.Fill(dtDataBases);

            // This is implemented for sequential           
            foreach (DataRow drDB in dtDataBases.Rows)
            {
                locleQuery = " Use [" + Convert.ToString(drDB[0]) + "]; " + query;
                cmdData = new SqlCommand(locleQuery, con);
                adapter = new SqlDataAdapter(cmdData);
                DataTable dtTemp = new DataTable();
                adapter.Fill(dtTemp);
                dtResult.Merge(dtTemp);
            }

            //Parallel Implementation
            Parallel.ForEach(dtDataBases.AsEnumerable(), drDB =>
                {
                    locleQuery = " Use [" + Convert.ToString(drDB[0]) + "]; " + query;
                    con = new SqlConnection(_ConnectionString);
                    cmdData = new SqlCommand(locleQuery, con);
                    cmdData.CommandTimeout = 0;
                    adapter = new SqlDataAdapter(cmdData);
                    DataTable dtTemp = new DataTable();
                    adapter.Fill(dtTemp);
                    dtResult.Merge(dtTemp);
                }
            );

            return dtResult;

        }

Now the problem is when i use the second loop i.e. Parallel ForEach loop it gives me different errors at the line adapter.Fill(dtTemp); as following,

Yes of-course these are expected errors.

Connection is closed
Connection is opening,
Data reader is closed
reader is connecting.. Blha Blha ... All connection related errors.

Note : Some time it works like charm i mean no errors.

And absolutely the first loop i.e. Sequential foreach loop works fine but the performance is not that good looking which i fall in Love with it :)

Now my question is if i want to use parallel foreach loop for the same, then How should i do this? Is there any cosmetics which will help the Parallel Foreach loop good looking ;)

Thanks in Advance.

Guffa · Accepted Answer · 2012-03-07 06:40:57Z

10

The database connection can only run one query at a time, so when a thread tries to run a query while the connection is busy, you get an error. If you want to run queries in parallel each thread needs its own database connection.

answered Mar 7, 2012 at 6:40

Guffa

703k111 gold badges760 silver badges1k bronze badges

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

4 Comments

Darshan Over a year ago

Yes, You are right, And yes needs its won DB connection for that i handled by creating a new connection in Parallel loop as con = new SqlConnection(_ConnectionString); which is not in sequential foreach loop, am i missing something at the time of doing connection? Thanks for your reply.

Guffa Over a year ago

@Darshan: You need to close the database connections also, otherwise you will eventually run out of available connections.

Darshan Over a year ago

Connection closing and opening is handled by SqlDataAdapter itself. i am not opening any connection or closing it manually. am i suppose to do it manually?

Guffa Over a year ago

@Darshan: Yes, you should do it manually. You should open the connection once instead of letting the data adapter opening it for every query, and dispose the connection and command objects when you are done with them.

j0k · Accepted Answer · 2013-01-14 14:57:24Z

3

Though you are creating a new SqlConnection object for each iteration, all the objects are using the same physical database connection. This is because of connection pooling used by the .NET framework. In your case you need to manually configure the behavior of the connection pool. For example you can disable connection pooling; this will have performance implications. Read about connection pooling at MSDN.

edited Jan 14, 2013 at 14:57

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Jan 14, 2013 at 14:33

imahama

511 bronze badge

Collectives™ on Stack Overflow

How to handle Parallel Execution C# for Database connection

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related