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.

1. Connection is closed
2. Connection is opening,
3. Data reader is closed
4. 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.

Answer 1

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.

Answer 2

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.