title: "Streaming" read of over 10 million rows from a table in SQL Server
body: <p>What is the best strategy to read millions of records from a table (in SQL Server 2012, BI instance), in a streaming fashion (like SQL Server Management Studio does)?</p>
  
  <p>I need to cache these records locally (C# console application) for further processing. </p>
  
  <p><strong>Update</strong> - 
  Sample code that works with SqlDataReader</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Data;
  using System.Data.SqlClient;
  using System.Threading;
  
  
  namespace ReadMillionsOfRows
  {
      class Program
      {
          static ManualResetEvent done = new ManualResetEvent(false);
  
  
          static void Main(string[] args)
          {
  
            Process();
            done.WaitOne();
          }
  
          public static async Task Process()
          {
              string connString = @"Server=;Database=;User Id=;Password=;Asynchronous Processing=True";
              string sql = "Select * from tab_abc";
  
              using (SqlConnection conn = new SqlConnection(connString))
              {
                  await conn.OpenAsync();
                  using (SqlCommand comm = new SqlCommand(sql))
                  {
                      comm.Connection = conn;
                      comm.CommandType = CommandType.Text;
  
                      using (SqlDataReader reader = await comm.ExecuteReaderAsync())
                      {
                          while (await reader.ReadAsync())
                          {
                              //process it here
                          }
                      }
                  }
              }
  
              done.Set();
          }
  
      }
  }
  </code></pre>