title: Return DataReader from DataLayer in Using statement
body: <p>We have a lot of data layer code that follows this very general pattern:</p>
  
  <pre><code>public DataTable GetSomeData(string filter)
  {
      string sql = "SELECT * FROM [SomeTable] WHERE SomeColumn= @Filter";
  
      DataTable result = new DataTable();
      using (SqlConnection cn = new SqlConnection(GetConnectionString()))
      using (SqlCommand cmd = new SqlCommand(sql, cn))
      {
          cmd.Parameters.Add("@Filter", SqlDbType.NVarChar, 255).Value = filter;
  
          result.Load(cmd.ExecuteReader());
      }
      return result;
  }
  </code></pre>
  
  <p>I think we can do a little better.  My main complaint right now is that it forces all the records to be loaded into memory, even for large sets.  I'd like to be able to take advantage of a DataReader's ability to only keep one record in ram at a time, but if I return the DataReader directly the connection is cut off when leaving the using block.</p>
  
  <p>How can I improve this to allow returning one row at a time?</p>