title: Parallel.Foreach SQL querying sometimes results in Connection
body: <p>I need to speed up performing 12 queries in my application.  I switched from a regular foreach to Parallel.ForEach.  But sometimes I get an error saying "ExecuteReader requires an open and available connection  The connection's current state is connecting."  It is my understanding that since many of the 12 queries are using the same InitialCatalog, there is not really a new connection for of the 12 and that may be the problem?  How can i fix this?  "sql" is a list of type "Sql"- a class is just a string name, string connectiona and a List of queries.  Here is the code:</p>
  
  <pre><code> /// &lt;summary&gt;
      /// Connects to SQL, performs all queries and stores results in a list of DataTables
      /// &lt;/summary&gt;
      /// &lt;returns&gt;List of data tables for each query in the config file&lt;/returns&gt;
      public List&lt;DataTable&gt; GetAllData()
      {
          Stopwatch sw = new Stopwatch();
          sw.Start();
          List&lt;DataTable&gt; data = new List&lt;DataTable&gt;();
  
           List&lt;Sql&gt; sql=new List&lt;Sql&gt;();
  
          Sql one = new Sql();
           one.connection = "Data Source=XXX-SQL1;Initial Catalog=XXXDB;Integrated Security=True";
           one.name = "Col1";
           one.queries.Add("SELECT Name FROM [Reports]");
           one.queries.Add("SELECT Other FROM [Reports2]");
           sql.Add(one);
  
          Sql two = new Sql();
           two.connection = "Data Source=XXX-SQL1;Initial Catalog=XXXDB;Integrated Security=True";
           two.name = "Col2";
           two.queries.Add("SELECT AlternateName FROM [Reports1]");
           sql.Add(two);
  
           Sql three = new Sql();
           three.connection = "Data Source=YYY-SQL2;Initial Catalog=YYYDB;Integrated Security=True";
           three.name = "Col3";
           three.queries.Add("SELECT Frequency FROM Times");
           sql.Add(three);
  
  
          try
          {
              // ParallelOptions options = new ParallelOptions();
              //options.MaxDegreeOfParallelism = 3;
              // Parallel.ForEach(sql, options, s =&gt;
              Parallel.ForEach(sql, s =&gt;
              //foreach (Sql s in sql)
              {
                  foreach (string q in s.queries)
                  {
                      using (connection = new SqlConnection(s.connection))
                      {
                          connection.Open();
                          DataTable dt = new DataTable();
                          dt.TableName = s.name;
                          command = new SqlCommand(q, connection);
                          SqlDataAdapter adapter = new SqlDataAdapter();
                          adapter.SelectCommand = command;
                          adapter.Fill(dt);
                          //adapter.Dispose();
  
                          lock (data)
                          {
                              data.Add(dt);
                          }
                      }
                  }
              }
              );
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.ToString(), "GetAllData error");
          }
  
          sw.Stop();
          MessageBox.Show(sw.Elapsed.ToString());
  
          return data;
      }
  </code></pre>
  
  <p>Here's the Sql class I made that you'd need:</p>
  
  <pre><code>/// &lt;summary&gt;
  /// Class defines a SQL connection and its respective queries
  /// &lt;/summary&gt;
  public class Sql
  {
      /// &lt;summary&gt;
      /// Name of the connection/query
      /// &lt;/summary&gt;
      public string name { get; set; }
      /// &lt;summary&gt;
      /// SQL Connection string
      /// &lt;/summary&gt;
      public string connection { get; set; }
      /// &lt;summary&gt;
      /// List of SQL queries for a connection
      /// &lt;/summary&gt;
      public List&lt;string&gt; queries = new List&lt;string&gt;();
  }
  </code></pre>