title: Return SqlDataReader with closed connection
body: <p>I have created a Sql helper class which handles most of my needs.</p>
  
  <p>Amongst those, I have a function which executes a SQL statement and returns a SqlDataReader as follows:</p>
  
  <pre><code>public static SqlDataReader ExecuteCommand(string cmdText, bool useParameters)
  {
      using (var sqlConnection = new SqlConnection(_connectionString.ConnectionString))
      {
          sqlConnection.Open();
          using (var sqlCommand = new SqlCommand(cmdText, sqlConnection))
          {
              if (useParameters &amp;&amp; SqlParameterCollection.Count &gt; 0)
              {
                  sqlCommand.Parameters.AddRange(SqlParameterCollection.ToArray());
              }
  
              using (var sqlDataReader = sqlCommand.ExecuteReader())
              {
                  return sqlDataReader;
              }
          }
      }
  }
  </code></pre>
  
  <p>The problem with this is, obviously, the fact that it returns a sqlDataReader which requires an open connection and the fact that the connection is closed.</p>
  
  <p>I have looked at returning a SqlDataAdapter instead, but after reading the following thread <a href="https://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader">SqlDataAdapter vs SqlDataReader</a> it doesn't sound like such a good idea as a general function to be used in every single scenario when you have absolutely no idea of the amount of data it is supposed to load.</p>
  
  <p>So... What's a good alternative?</p>
  
  <p>The only thing I can think of off the top of my head is to cycle through the rows in the SqlDataReader and do a <code>yield return IEnumerable&lt;IDataRecord&gt;</code>. </p>
  
  <p>Is there any better way of achieving this or is this pretty much it?</p>