Are there any pitfalls to using an IEnumerable<T> return type for SQL data?

Question

My question is concerning SQL connection status, load, etc. based on the following code:

public IEnumberable<MyType> GetMyTypeObjects()
{
  string cmdTxt = "select * from MyObjectTable";

  using(SqlConnection conn = new SqlConnection(connString))
  {
    using(SqlCommand cmd = new SqlCommand(cmdTxt, conn))
    {
      conn.Open();
      using(SqlDataReader reader = cmd.ExecuteReader())
      {
         while(reader.Read())
         {
            yield return Mapper.MapTo<MyType>(reader);
         }
       }
    }
  }
  yield break;
}

I can see this possibly being a problem if there are many processes running similar code with long execution times between iterations of the IEnumerable object, because the connections will be open longer, etc. However, it also seems plausible that this will reduce the CPU usage on the SQL server because it only returns data when the IEnumerable object is used. It also lowers memory usage on the client because the client only has to load one instance of MyType while it works rather than loading all occurrences of MyType (by iterating through the entire DataReader and returning a List or something).

• Are there any instances you can think of where you would not want to use IEnumerable in this manner, or any instances you think it fits perfectly?

• What kind of load does this put on the SQL server?

• Is this something you would use in your own code (barring any mention of NHibernate, Subsonic, etc)?

-

Answer 1

This is not a pattern I would follow. I would not worry as much about load on the server as I would about locks. Following this pattern integrates the data retrieval process into your business logic flow, and that seems like an all-around recipe for trouble; you have no idea what happens on the iteration side, and you're inserting yourself into it. Retrieve your data in one shot, then allow the client code to enumerate over it once you've closed the reader.