title: Invalid Attempt To Call Read when reader is closed .NET
body: <p>I currently have a DB library used for database access and I use it across several projects. I currently use the following code to get a recordset.</p>
  
  <blockquote>
    <p>METHODS</p>
  </blockquote>
  
  <pre><code>    public static IDataReader GetRs(string sql)
      {
          using (var con = NewSqlConnection())
          {
              con.Open();
              return GetRs(sql, con);
          }
      }
      public static IDataReader GetRs(string sql, SqlConnection dbconn)
      {
          using (var cmd = new SqlCommand(sql, dbconn))
          {
              int tries = 1;
  
              while (tries &lt;= 3)
              {
                  try
                  {
                      if (dbconn.State == ConnectionState.Closed)
                      {
                          dbconn.Open();
                      }
  
                      DataTable myTable = new DataTable();
  
                      var reader = cmd.ExecuteReader();
  
                      myTable.Load(reader);
  
                      return myTable.CreateDataReader();
  
                      //return cmd.ExecuteReader();
                  }
                  catch (SqlException ex)
                  {
                      if (ex.Message.Contains("Timeout expired") || ex.Number == 1205) // Deadlock 
                      {
                          Thread.Sleep(1000);
                          if (tries == 3)
                          {
                              throw ex;
                          }
  
                          tries += 1;
                          cmd.CommandTimeout *= 10;
  
                      }
                      else
                      {
                          throw ex;
                      }
                  }
              }
          }
  
          throw new Exception("Could not get RecordSet");
      }
  </code></pre>
  
  <blockquote>
    <p>USAGE</p>
  </blockquote>
  
  <pre><code>public static void Test()
  {
      using(var reader = GetRs("SELECT Col FROM TABLE"))
      {
          while(reader.Read())
          {
              // do stuff with data here e.g. var value = reader[0];
          }
      }
  }
  </code></pre>
  
  <p>While this method works, as you can see it loads the entire dataset into memory thus causing issues with scaling.</p>
  
  <p>I tried replacing the following code in the <code>GetRs(string sql, SqlConnection con)</code> method </p>
  
  <pre><code>DataTable myTable = new DataTable();
  
  var reader = cmd.ExecuteReader();
  
  myTable.Load(reader);
  
  return myTable.CreateDataReader();
  </code></pre>
  
  <p>and tried returning just the <code>return cmd.ExecuteReader();</code></p>
  
  <p>However an error is thrown on the <code>while (reader.Read())</code> - <code>Invalid attempt to call read when the reader is closed</code>. I am guessing this is because the SqlConnection property is disposed (and hence closed) after returning the IDataReader.</p>
  
  <p>I'm aware that I can wrap the <code>GetRs</code> method with a new sql connection but this means rewriting a lot of my code, and I was hoping that I would be able to dispose the reader AND the connection with my <code>using(var reader = GetRs())</code> method.</p>
  
  <p>Is there any way I can still use these methods without loading the whole dataset into memory?</p>