title: How to read large number of records from SQL table in batches using Task Parallel library
body: <p>I have a sql table called <strong>Accounts</strong> in SQL server where data is sorted with respect to date. The table has 20 columns including <code>AccountId</code>.<br>
  I want to read the records for each day (<strong>Around 200K records</strong>). This way I would have to read the data of 6 months for each day.</p>
  
  <p>What I need to do is to fetch records from Accounts Table for 6 Months of data. So I planned my code to get the data from SQL Server <code>Accounts</code> table inside a do while loop for each day.</p>
  
  <p>Now, each day consists of fetching 200K records from the database. So I break this 200K records for one day into batches (let's say 10000 or 20000 records in one read which makes around 10 batches of records for one day). Once I get these 10k or 20k records I want to get those values fetched from database and convert the same into a csv file and export the csv file to a location.</p>
  
  <p>Now, my problem is that this procedure is taking too much time (Around 50 minutes for fetching records for one day and I need to fetch the records for 6 months of data. So you can imagine how much time it will take).</p>
  
  <p>I am thinking to make use of TPL to break the code and processing into tasks but not sure how to go about it. </p>
  
  <p>Please suggest how do I make use of Task parallel library to enhance the performance so that I can easily get 6 months of data.</p>
  
  <p>My C# code looks like below:</p>
  
  <pre><code>public void Main()
  {
      do
      {
          done = true;
          var accountsTableRecors = ReadsDatabaseForADay(lastId);
          foreach (var accountsHistory in accountsTableRecors)
          {
              if (accountsHistory.accountsId != null) lastId = (long)accountsHistory.accountsId;
              done = false;
              recordCount++;
          }
          var flatFileDataList = ProcessRecords();
      } while (!done);
  }
  </code></pre>
  
  <p>The <code>ProcessRecords</code> method above in <code>Main()</code> parses some xml and converts to fetched data into csv.</p>
  
  <pre><code>private IEnumerable&lt;AccountsTable&gt; ReadsDatabaseForADay(long lastId)
  {
      var transactionDataRecords = DatabaseHelper.GetTransactions(lastId, 10000);
      var accountsTableData = transactionDataRecords as IList&lt;AccountsTable&gt; ?? transactionDataRecords.ToList();
      ListAccountsTable.AddRange(accountsTableData);
      return accountsTableData;
  }
  </code></pre>
  
  <p><code>DatabaseHelperClass</code>:</p>
  
  <pre><code>internal static IEnumerable&lt;AccountsTable&gt; GetTransactions(long lastTransactionId, int count)
  {
      const string sql = "SELECT TOP(@count) [accounts_id],[some_columns],[some_other_columns]. .....[all_other_columns] " 
                      + "FROM AccountsTable WHERE [accounts_id] &gt; @LastTransactionId AND [creation_dt] &gt; DATEADD(DAY,-1, GETDATE())" +
                     " ORDER BY [accounts_id]";
      return GetData(connection =&gt;
      {
          var command = new SqlCommand(sql, connection);
          command.Parameters.AddWithValue("@count", count);
          command.Parameters.AddWithValue("@LastTransactionId", lastTransactionId);
          return command;
      }, DataRecordToTransactionHistory);
  }
  
  private static IEnumerable&lt;T&gt; GetData&lt;T&gt;(Func&lt;SqlConnection, SqlCommand&gt; commandBuilder, Func&lt;IDataRecord, T&gt; dataFunc)
  {
      using (var connection = GetOpenConnection())
      {
          using (var command = commandBuilder(connection))
          {
              command.CommandTimeout = 0;
              using (IDataReader reader = command.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      var record = dataFunc(reader);
                      yield return record;
                  }
              }
          }
      }
  }
  </code></pre>