sqldatareader code review

Topics: Data Access Application Block
Aug 27 at 6:10 PM
Hi - Im using the following code to create a sql data reader and then expose it as a generic list. It works, but to be honest Im not sure what is going on with the dr=(SqlDataReader) line, so I have a vague feeling Im setting myself up for potential pooling problems. Is there a cleaner way to do this (don't say ef!)
        public IEnumerable<Movie> GetMovies()
        {
            List<Movie> lmo = new List<Movie>();
            SqlDataReader dr = null;
            using (var reader = DataAccessLayer.GetReader("pMoviesGet"))
            {
                dr = (SqlDataReader)((RefCountingDataReader)reader).InnerReader;
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
... create instance and add to list
                    }
                }
            }
            return lmo;
        }
        
        public static IDataReader GetReader(string spName)
        {
            GetInstance();
            var dbCommand = _dalDb.GetStoredProcCommand(spName);
            return _dalDb.ExecuteReader(dbCommand);
        }   
Editor
Aug 28 at 3:04 AM
I'm not sure why you need to cast to get to the InnerReader. Usually you would just access the IDataReader interface:
using (var reader = DataAccessLayer.GetReader("pMoviesGet"))
{
    while (reader.Read())
    {
        ... create instance and add to list
     }
}
Also, you could think about using an IRowMapper<Movie> to perform the conversion form an IDataReader to a Movie object.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Aug 28 at 2:53 PM
Wow, so much cleaner and maintainable. Thanks much, Randy! I'll look into IRowMapper (when I have time)