title: Exception of type 'System.OutOfMemoryException' was thrown. C# when using IDataReader
body: <p>I have an application in which I have to get a large amount of data from DB.
  Since it failed to get all of those rows (it's close to 2,000,000 rows...), I cut it in breaks, and I run each time the sql query and get only 200,000 rows each time.</p>
  
  <p>I use DataTable to which I enter all of the data (meaning - all 2,000,000 rows should be there).</p>
  
  <p>The first few runs are fine. Then it fails with the OutOfMemoryException.</p>
  
  <p>My code works as following:</p>
  
  <pre><code>private static void RunQueryAndAddToDT(string sql, string lastRowID, SqlConnection conn, DataTable dt, int prevRowCount)
      {
          if (string.IsNullOrEmpty(sql))
          {
              sql = generateSqlQuery(lastRowID);
          }
  
          if (conn.State == ConnectionState.Closed)
          {
              conn.Open();
          }
  
          using (IDbCommand cmd2 = conn.CreateCommand())
          {
              cmd2.CommandType = CommandType.Text;
              cmd2.CommandText = sql;
              cmd2.CommandTimeout = 0;
  
              using (IDataReader reader = cmd2.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      DataRow row = dt.NewRow();
                      row["RowID"] = reader["RowID"].ToString();
                      row["MyCol"] = reader["MyCol"].ToString();
                      ... //In one of these rows it returns the exception.
  
                      dt.Rows.Add(row);
                  }
              }
          }
  
          if (conn != null)
          {
              conn.Close();
          }
  
          if (dt.Rows.Count &gt; prevRowCount)
          {
              lastRowID = dt.Rows[dt.Rows.Count - 1]["RowID"].ToString();
              sql = string.Empty;
              RunQueryAndAddToDT(sql, lastRowID, conn, dt, dt.Rows.Count);
          }
      }
  </code></pre>
  
  <p>It seems to me as if the reader keeps collecting rows, and that's why It throws an exception only in the third or second round.</p>
  
  <p>Shouldn't the Using clean the memory as its done?
  What may solve my problem?</p>
  
  <p><em>Note: I should explain - I have no other choice but get all of those rows to the datatable, Since I do some manipulation on them later, and the order of the rows is important, and I can't split it because sometimes I have to take the data of some rows and set it into one row and so on and so on, so I can't give it up.</em></p>
  
  <p>Thanks.</p>