title: Parallel reading from SQL Server
body: <p>I was looking through dozens of articles, but couldn't find a solution.</p>
  
  <p>Here is the logic :</p>
  
  <p>I have a Winform (VS2010) application, that needs to read data from SQL Server 2008 R2 Express table <code>A</code>, process some calculations and store in a different table <code>B</code>.</p>
  
  <p>I want to use parallel <code>ForEach</code> in order to shorten execution time (otherwise the calculation + SQL process takes days.....)</p>
  
  <p>I have to read from SQL, because the database has over 5 million rows, each read returns a few hundreds rows.</p>
  
  <p>Lists are defined as :</p>
  
  <pre><code>BindingList&lt;ItemsClass&gt; etqM = new BindingList&lt;ItemsClass&gt;();
  BindingList&lt;ItemsClass&gt; etqC = new BindingList&lt;ItemsClass&gt;();
  </code></pre>
  
  <p>The parallel execution :</p>
  
  <pre><code>Parallel.ForEach(etqC, cv =&gt; {
              readData(ref etqM, "tableA", "WHERE ID LIKE '" + cv.Name + "%'");
              IList&lt;ItemsClass&gt; eResults = etqM.OrderBy(f =&gt; f.ID).ToList();
  
              foreach (ItemsClass R in eResults)
              {
                  //calculations comes here
  
                  etqM[rID] = R;
              }
  
              Parallel.ForEach(etqM, r =&gt; {
                  // part 2 of calculations comes here
                  }
              });
              exportList(etqM, "tableB", true);
          });
  </code></pre>
  
  <p>The SQL Read function :
  The function gets a List, Table name + conditions for the SQL
  read from SQL the records, and transform them to the List format.</p>
  
  <pre><code>public void readData&lt;T&gt;(ref BindingList&lt;T&gt; etqList, string tableName, string conditions)
      {
          SqlConnection myConnection = new SqlConnection();
          SqlCommand myCommand = new SqlCommand();
          myCommand.CommandTimeout = 0;
          myCommand.Connection = myConnection;
  
          etqList.Clear();            
          openConn(myConnection);
          SqlDataReader myReader = null;
  
          try
          {
              int totalResults;
              myCommand.CommandText = "SELECT COUNT (*) FROM " + tableName + " " + conditions;
              totalResults = (int)myCommand.ExecuteScalar();
  
              if (totalResults &gt; 0)
              {
                  myCommand.CommandText = "SELECT * FROM " + tableName + " " + conditions;
                  myReader = myCommand.ExecuteReader();
                  etqList = ConvertTo&lt;T&gt;(convertReaderToDT(myReader));
              }
          }
          catch (Exception ex) { }
          finally
          {
              try { myReader.Close(); }
              catch { }
          }
          closeConn(myConnection);
      }
  </code></pre>
  
  <p>The SQL export function : this function exports the given list to the table name.</p>
  
  <pre><code>private void exportListToSql&lt;T&gt;(IEnumerable&lt;T&gt; etqList, string tableName)
  {
          SqlConnection myConnection = new SqlConnection();
          SqlCommand myCommand = new SqlCommand();
          myCommand.CommandTimeout = 0;
          myCommand.Connection = myConnection;
          openConn(myConnection);
  
          try
          {
              actionTotalCount++;
              DataTable dt = new DataTable(tableName);
              dt = ToDataTable(etqList);//List Name
              var bulkCopy = new SqlBulkCopy(myConnection,
                  SqlBulkCopyOptions.TableLock |
                  SqlBulkCopyOptions.FireTriggers |
                  SqlBulkCopyOptions.UseInternalTransaction,
                  null
                  );
              bulkCopy.DestinationTableName = tableName;
              bulkCopy.BatchSize = BATCH_SIZE;
              bulkCopy.WriteToServer(dt);
          }
          catch (Exception ex) {  }
          finally { closeConn(myConnection); }
      }
  </code></pre>
  
  <p>SQL <code>openConn</code> and <code>closeConn</code> :</p>
  
  <pre><code>void openConn(SqlConnection myConnection)
  {
          if (myConnection.State == ConnectionState.Open) return;
          myConnection.ConnectionString = "Data Source=" + DB_NAME + ";Initial Catalog=APPDB;Integrated Security=True;Connect Timeout=120;Asynchronous Processing=true;";
  
          try { myConnection.Open(); actionTotalCount++; }
          catch (System.Exception ex) { MessageBox.Show(ex.Message); }
  }
  
  void closeConn(SqlConnection myConnection)
  {
          if (myConnection.State == ConnectionState.Fetching || myConnection.State == ConnectionState.Executing || myConnection.State == ConnectionState.Connecting) return;
          try { myConnection.Dispose(); }
          catch (System.Exception ex) { MessageBox.Show(ex.Message); }
  }
  </code></pre>
  
  <p>The problem is : once I execute, I get this message :</p>
  
  <blockquote>
    <p><em>ExecuteScalar requires an open and available connection. The connection's current state is closed.</em></p>
  </blockquote>
  
  <p>This message arrives for all threads, except the first one.</p>
  
  <p>Any ideas ?</p>