Return DataReader from DataLayer in Using statement

by

Once again, the act of composing my thoughts for the question reveals the answer. Specifically, the last sentence where I wrote “one row at a time”. I realized I don’t really care that it’s a datareader, as long as I can enumerate it row by row. That lead me to this:

public IEnumerable<IDataRecord> GetSomeData(string filter)
{
    string sql = "SELECT * FROM [SomeTable] WHERE SomeColumn= @Filter";

    using (SqlConnection cn = new SqlConnection(GetConnectionString()))
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@Filter", SqlDbType.NVarChar, 255).Value = filter;
        cn.Open();

        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return (IDataRecord)rdr;
            }
        }
    }
}

This will work even better once we move to 3.5 and can start using other linq operators on the results, and I like it because it sets us up to start thinking in terms of a “pipeline” between each layer for queries that return a lot of results.

The down-side is that it will be awkward for readers holding more than one result set, but that is exceedingly rare.

Update
Since I first started playing with this pattern in 2009, I have learned that it’s best if I also make it a generic IEnumerable<T> return type and add a Func<IDataRecord, T> parameter to convert the DataReader state to business objects in the loop. Otherwise, there can be issues with the lazy iteration, such that you see the last object in the query every time.

More Related Contents:

  1. How do I get and set Environment variables in C#?
  2. How to create a simple proxy in C#?
  3. Is there a .NET/C# wrapper for SQLite? [closed]
  4. Workaround for lack of ‘nameof’ operator in C# for type-safe databinding?
  5. Editing dictionary values in a foreach loop
  6. How do I create a HashCode in .net (c#) for a string that is safe to store in a database?
  7. Is there an alternative to string.Replace that is case-insensitive?
  8. How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?
  9. Convert bitmaps to one multipage TIFF image in .NET 2.0
  10. Casting List – covariance/contravariance problem
  11. Watermark in System.Windows.Forms.TextBox
  12. Compare two Lists for differences
  13. XmlSerializer – There was an error reflecting type
  14. SqlConnection.Close() inside using statement
  15. Difference between the System.Array.CopyTo() and System.Array.Clone()
  16. Received an invalid column length from the bcp client for colid 6
  17. End of Stream encountered before parsing was completed?
  18. file exists by file name pattern
  19. How to compare only Date without Time in DateTime types?
  20. Control.AddRange(…) is slow
  21. How to get xpath from an XmlNode instance
  22. How to unit test if my object is really serializable?
  23. How to create Microsoft Access database in C# programmatically?
  24. How do I prevent print screen
  25. Writing driver class generic for any database support
  26. How does the “Using” statement translate from C# to VB?
  27. What is the fastest way to read data from a DbDataReader?
  28. Could not load file or assembly ‘MySql.Data, Version=6.2.2.0
  29. Retrieve List of Tables in MS Access File
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment