Call be masochistic, but I kinda like nuts and bolts programming. I enjoy finding ways of optimizing the code that runs (or should run) completely behind the scenes that provides the infrastructure and support to the “higher-level” objects - whether they be business object, UI, etc. How many out there use data readers, like the SqlDataReader, OracleDataReader? Like almost everything, they have their place and are frequently (mis)used. I fear that many developers (myself included) have written code similar to the following in a generic 'data access' class:
internal sealed class DataAccess {
internal static SqlDataReader QueryReader(SqlCommand cm) {
// ...include the code to retrieve data reader and return it
}
}
internal sealed class EntryPoint{
public static void Main() {
using ( SqlDataReader dr = DataAccess.QueryReader(cm) ) {
// ...iterate over the reader, utilizing the data
}
}
}
This, I believe, is a fairly common pattern that developers employ. Is there anything wrong with it? Well, I think there is.
First of all, there are no constraints on how the data reader is used and consumed. In other words, the consumer class (in this case the EntryPoint class) could hold on to the SqlDataReader indefinitely and thereby maintain a connection to the database indefinitely (the data readers are tied to a connection to the database).
Second, your code that walks the reader's contents is tightly coupled with the flow of the procedure. You have to include your code to iterate the reader inline (or at least delegate the reader to a worker method) to extract the data and it's not as reusable in that fashion.
So what's a better solution? What do I propose? Well, I prefer a slightly different approach to how a data reader gets passed back to a consumer that puts tighter constraints on the lifetime of the reader (though it can still be abused by malicious code) and helps compartmentalize how a reader is used.
I suggest using a delegate (a safe function pointer class) as a vehicle to return the data reader out. Let's take a look at a simple example and then discuss how to augment the functionality to make it even better, and then we'll step it up a notch for .NET 2.0 and we'll leverage some functionality therein. On to the simple example. Disclaimer: Please bear in mind that there are other methodologies and objects that provide an ability to get data from a database in a disconnected manner (from DataSets, XmlSerialized objects, etc). I am merely illustrating how I prefer to work with readers in a semi-practical example.
Simple Example:
Here we create a delegate that has as its parameter a data reader. The consumer sets up the function to be called via the delegate and then invokes the data access class. The data access class then takes a command object, executes it, and then calls the callback function (via the delegate), passing the reader. Once the callback function finishes, resources are cleaned up and the data access method exits.
internal delegate void DataReaderCallback(IDataReader dr);
internal sealed class DataAccess {
private static SqlConnection getConnection() {
SqlConnection cn = new SqlConnection(“Data Source=(local); Initial Catalog=AdventureWorks; Integrated Security=SSPI“);
cn.Open();
return cn;
}
internal static void QueryReader(SqlCommand cm, DataReaderCallback callback) {
SqlConnection cn = getConnection();
SqlDataReader dr = null;
try {
cm.Connection = cn;
dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
if ( null != callback ) callback(dr);
}
finally {
if ( null != dr ) ((IDisposable)dr).Dispose();
if ( null != cn ) cn.Dispose();
cm.Connection = null;
}
}
}
internal sealed class Worker {
private ArrayList _list;
internal void DoWork() {
SqlCommand cm = new SqlCommand(“SELECT Name FROM Production.Product WHERE Name LIKE '[A-C]%' ORDER BY Name“);
DataAccess.QueryReader(cm, new DataReaderCallback(this.callbackFn));
for ( int i = 0; i < _list.Count; i++ )
Console.WriteLine(“{0}: {1}“, i, _list[i]);
}
internal void callbackFn(IDataReader dr) {
_list = new ArrayList();
while ( dr.Read() )
_list.Add(dr.GetString(0));
}
}
This solution is pretty slick but it has, in my eyes, a drawback: there must be shared state between the callback function and the method that invokes the reader. Namely, in this example, the ArrayList that gets built in the callback function. Let's make a simple enhancement to our delegate and see where that takes us:
Enhanced Example:
This example has a delegate that not only passes the data reader, but returns an object. In effect, the data access method will call back on the delegate which will build up its state and return it. The return value is then forwarded on to the consumer as the return value from the QueryReader method as a populated object.
internal delegate object DataReaderCallback(IDataReader dr);
internal sealed class DataAccess {
private static SqlConnection getConnection() {
SqlConnection cn = new SqlConnection(“Data Source=(local); Initial Catalog=AdventureWorks; Integrated Security=SSPI“);
cn.Open();
return cn;
}
internal static void QueryReader(SqlCommand cm, DataReaderCallback callback) {
SqlConnection cn = getConnection();
SqlDataReader dr = null;
try {
cm.Connection = cn;
dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
return ( null != callback ) ? callback(dr) : null;
}
finally {
if ( null != dr ) ((IDisposable)dr).Dispose();
if ( null != cn ) cn.Dispose();
cm.Connection = null;
}
}
}
internal sealed class Worker {
internal void DoWork() {
SqlCommand cm = new SqlCommand(“SELECT Name FROM Production.Product WHERE Name LIKE '[A-C]%' ORDER BY Name“);
ArrayList list = DataAccess.QueryReader(cm, new DataReaderCallback(this.callbackFn)) as ArrayList;
for ( int i = 0; i < list.Count; i++ )
Console.WriteLine(“{0}: {1}“, i, list[i]);
}
internal object callbackFn(IDataReader dr) {
ArrayList list = new ArrayList();
while ( dr.Read() )
list.Add(dr.GetString(0));
return list;
}
}
Note, the major change here is that there is no build up of shared state and once the QueryReader returns, the caller has a fully disconnected set of data with which to work. This is all well and good, and in fact, I very much like this approach. It gives the caller the ability to write lean, tight code and still interact with the data reader in the fashion the caller wants. Very cool. However, as you may quickly realize, there's no real constraint set on the type of object returned from the callback function - it's an object. Therefore, the caller has to know the type of object to be returned (in this case, an ArrayList).
With the looming advent of .NET 2.0, there's a very slick way to ensure type compatibility and strongly typed and verified method signatures: generics. Let's explore a technique that mimics the example above, but utilizes generics to accomplish the goal.
.NET 2.0 Generic Example:
internal delegate T DataReaderCallback<T>(IDataReader dr);
internal static class DataAccess {
// same getConnection() method as above
internal static T QueryReader<T>(SqlCommand cm, DataReaderCallback<T> callback) {
SqlConnection cn = getConnection();
SqlDataReader dr = null;
try {
cm.Connection = cn;
dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
return ( null != callback ) ? callback(dr) : default(T);
}
finally {
if ( null != dr ) dr.Dispose();
if ( null != cn ) cn.Dispose();
cm.Connection = null;
}
}
}
internal sealed class Worker {
internal void DoWork() {
SqlCommand cm = new SqlCommand(“SELECT Name FROM Production.Product WHERE Name LIKE '[A-C]%' ORDER BY Name“);
ArrayList list = DataAccess.QueryReader(cm, new DataReaderCallback<ArrayList>(this.callbackFn));
for ( int i = 0; i < list.Count; i++ )
Console.WriteLine(“{0}: {1}“, i, list[i]);
}
internal ArrayList callbackFn(IDataReader dr) {
ArrayList list = new ArrayList();
while ( dr.Read() )
list.Add(dr.GetString(0));
return list;
}
}
Notice that pretty much all of the code is identical except for a few things:
- the delegate signature is generic; you must specify the type return type upon creation.
- the QueryReader signature is also generic, but the type only needs to be specified on the DataReaderCallback parameter instance.
- we can longer return null from the QueryReader method, but instead return default(T), because the method is generic.
- you no longer need to cast a DataReader to IDisposable to call Dispose. Ok, that was unrelated, but I like the enhancement.
All in all, I really like this approach. It makes consuming data readers much more specific, isolated, wrapped, and contained, and empowers the data access layer in ensuring that its resources are properly cleared.
Happy coding!