I've been thrown back to using plain old ADO.NET for a bit in a legacy project I'm helping one of my customers with and in the process am finding a few new ways to take advantage of .NET 4 language features to make a number of operations easier. Specifically I'm finding that the new Dynamic type in .NET 4.0 can make a number of operations easier to use and considerably cleaner to read and type. A couple of weeks ago I posted an example of a DynamicDataRow class that uses dynamic to expose DataRow column values as properties.

In this post I do something similar with for the ADO.NET DataReader by exposing a custom dynamic type that retrieves values from the DataReader field collection and exposes them as properties.

Why hack the DataReader?

DbDataReader is one of the lowest level ADO.NET data structures - it returns raw firehose data from the database and exposes it via a simple reader interface that relies on simple looping and internal collection of field keys and values.

Here's a small example that demonstrates the basics using a DataAccess helper SqlDataAccess from Westwind.Utilities from the West Wind Web Toolkit to keep this example small.

[TestMethod]
public void BasicDataReaderTimerTests()
{
    var data = new SqlDataAccess("WebStore_ConnectionString");
    var reader = data.ExecuteReader("select * from wws_items");
    Assert.IsNotNull(reader, "Query Failure: " + data.ErrorMessage);
    
    StringBuilder sb = new StringBuilder();
    
    Stopwatch watch = new Stopwatch();
    watch.Start();
    
    while (reader.Read())
    {
        string sku  = reader["sku"] as string;
        string descript = reader["descript"] as string;

        decimal? price;
        object t = reader["Price"];
        if (t == DBNull.Value)
            price = null;
        else
            price = (decimal)t;
        
        
        sb.AppendLine(sku + " " + descript + " " + price.Value.ToString("n2"));                    
    }

    watch.Stop();

    reader.Close();

    Console.WriteLine(watch.ElapsedMilliseconds.ToString());
    Console.WriteLine(sb.ToString());                                
}

The code is pretty straight forward. SqlDataAccess takes a connection string or connection string name from the config file as a parameter in the constructor to initialize the DAL. Once instantiated you can run a number of data access commands to create a DataReader (or DataSet/DataTable) or execute commands in various ways on this connection. Here I use ExecuteReader() to produce a DataReader I can loop through. The code then loops through the records using reader.Read() which returns false when the end of the result set is reached.

Inside of the loop I can then access each of the fields. Notice that each field has to be explicitly cast to a specific type (as string or (decimal) here). In addition if you support NULL values in the database you also have to explicitly check for DBNull values showing up in the DataReader fields which is messy.

It's not terribly complicated to do any of this - just a bit of extra typing and - aesthetically - the code looks a bit messy.

Using DynamicDataReader

Personally I prefer standard object.property syntax when dealing with data and a custom dynamic type can actually make that easy. While it won't give me a full strongly typed .NET type, I can at least get the standard object syntax with this implementation.

Here's the code that does exactly the same thing using the DynamicDataReader:

[TestMethod]
public void BasicDynamicDataReaderTimerTest()
{
    var data = new SqlDataAccess("WebStore_ConnectionString");
    var reader = data.ExecuteReader("select * from wws_items");

    Assert.IsNotNull(reader, "Query Failure: " + data.ErrorMessage);

    dynamic dreader = new DynamicDataReader(reader);

    Stopwatch watch = new Stopwatch();
    watch.Start();


    while (reader.Read())
    {
        string sku = dreader.Sku;
        string descript = dreader.Descript;
        decimal? price = dreader.Price;

        sb.AppendLine(sku + " " + descript + " " + price.Value.ToString("n2"));
    }

    watch.Stop();    reader.Close();

    Console.WriteLine(watch.ElapsedMilliseconds.ToString());
    Console.WriteLine(sb.ToString());
}

The code is nearly the same except in the two places highlighted in bold: a new DynamicDataReader() instance is created and the values assigned are read from this dreader instance as property values.

Even though this is a simple example that only uses 3 fields, it still is quite a bit cleaner than the first example:

• It uses cleaner object.property syntax
• All the type casting is gone
• The DBNull to .NET NULL assignment is automatic

Good News, Bad News

It's important to understand that what you're seeing is a dynamic type, not a strongly typed .NET type. Dynamic means you get to use object.property syntax in this case, and you get automatic casting, but you do not get strong typing and so no compiler type checking or Intellisense on those properties. They are dynamic and so essentially syntactic sugar around dynamic invocation and Reflection implemented through the Dynamic Language Runtime (DLR).

Because the type is dynamic there's also a performance penalty. Specifically first time access of the dynamic properties tends to be slow. Once the DLR is spun up and a dynamic type created from the DataReader and you've iterated over each property once, the parsing is fairly swift on repeated calls/conversions. In informal testing it looks like the dynamic code takes roughly three times as long as the raw code from a cold start, and is a little over 1.5 times slower once the dynamic type has been created once. Not sure why that is because the implementation just does look ups into the DataReader field collection (no Reflection caching for PropertyInfo data), but nevertheless repeated requests are significantly faster than first time access.

Even through performance was nearly twice as slow using the dynamic type, the numbers were still very fast taking less than 8 milliseconds for rendering 500 records compared to 4-5 with raw DataReader access. Hardly a deal breaker in all but the most critical scenarios especially when you figure in the cost of data access (which the example code doesn't for the timings).

How does DynamicDataReader work?

The DLR makes it very easy to abstract data structures and wrap them into an object based syntax. Using DynamicObject as a base class to implement custom types, you can basically implement 'method missing' or 'property missing' functionality by simply overriding the TryGetMember() method and TryInvokeMember() methods.

Here's the implementation of DynamicDataReader:

/// <summary>
/// This class provides an easy way to use object.property
/// syntax with a DataReader by wrapping a DataReader into
/// a dynamic object.
/// 
/// The class also automatically fixes up DbNull values
/// (null into .NET and DbNUll)
/// </summary>
public class DynamicDataReader : DynamicObject
{
    /// <summary>
    /// Cached Instance of DataReader passed in
    /// </summary>
    IDataReader DataReader;
    
    /// <summary>
    /// Pass in a loaded DataReader
    /// </summary>
    /// <param name="dataReader">DataReader instance to work off</param>
    public DynamicDataReader(IDataReader dataReader)
    {
        DataReader = dataReader;
    }

   /// <summary>
   /// Returns a value from the current DataReader record
   /// If the field doesn't exist null is returned.
   /// DbNull values are turned into .NET nulls.
   /// </summary>
   /// <param name="binder"></param>
   /// <param name="result"></param>
   /// <returns></returns>
    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        result = null;

        // 'Implement' common reader properties directly
        if (binder.Name == "IsClosed")            
            result = DataReader.IsClosed;                            
        else if (binder.Name == "RecordsAffected")            
            result = DataReader.RecordsAffected;                         
        // lookup column names as fields
        else
        {
            try
            {
                result = DataReader[binder.Name];
                if (result == DBNull.Value)
                    result = null;                    
            }
            catch 
            {
                result = null;
                return false;
            }
        }

        return true;
    }

    public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
    {
        // Implement most commonly used method
        if (binder.Name == "Read")
            result = DataReader.Read();
        else if (binder.Name == "Close")
        {
            DataReader.Close();
            result = null;
        }
        else
            // call other DataReader methods using Reflection (slow - not recommended)
            // recommend you use full DataReader instance
            result = ReflectionUtils.CallMethod(DataReader, binder.Name, args);

        return true;            
    }
}

As you can see the implementation is super simple. The implementation inherits from Dynamic object and overrides TryGetMember() and TryInvokeMember(). The constructor is set up so it passes in a DataReader instance which is stored internally. TryGetMember() is then called when an 'unknown' property is accessed on the dynamic type and tries to find a matching value in the DataReader field collection based on the binder.Name property.

The class also implements a couple of DataReader's common properties (IsClosed, RecordsAffected) and methods (Read, Close) to match a full DataReader's functionality so it can behave like a full DataReader instance, so you can write code like this:

dynamic reader = new DynamicDataReader(data.ExecuteReader("select * from wws_items"));
while (reader.Read()) 
{ … }

The key feature however is that values from the DataReader fields collection are turned into properties which is handled by

try
{
    result = DataReader[binder.Name];
    if (result == DBNull.Value)
        result = null;
}
catch { result = null; return false; }

Note that the code handles the null conversion and the assignment of the result value the value from the DataReader field. TryGetMember() expects an out parameter for result and the value set is what effectively becomes the property value that is used when accessing the object.property syntax.

It's neat how easy it is to implement custom behavior in TryGetMember(). Note that I can check for explicit values (like IsClosed and RecordsAffected) as well as checking the fields collection for matching values. IOW, you can have a single dynamic return values for properties from any number of 'data sources' easily, be it from static translations, an internal data structure like a DataReader or DataRow, an XML document or even via Reflection from additional properties on the object. You have full control over this implementation which is very powerful and opens up many more avenues to simplify structured data access.

Even easier: Get a DynamicDataReader directly from the DAL

With DynamicDataReader available, it's now a cinch to extend my DAL to directly return a dynamic data reader instance. We earlier saw the SqlDataAccess.ExecuteReader() method which returned a DataReader. In the SqlDataAccessBase class I can now implement a dynamic version of ExecuteReader that directly returns a DynamicDataReader instance as a dynamic type:

/// <summary>
/// Executes a Sql statement and returns a dynamic DataReader instance 
/// that exposes each field as a property
/// </summary>
/// <param name="sql">Sql String to executeTable</param>
/// <param name="parameters">Array of DbParameters to pass</param>
/// <returns></returns>
public virtual dynamic ExecuteDynamicDataReader(string sql, params DbParameter[] parameters)
{
    var reader = ExecuteReader(sql, parameters);
    return new DynamicDataReader(reader);
}

which directly returns me a dynamic DataReader instance. Note that the type returned from ExecuteDynamicDataReader() is not DynamicDataReader but dynamic!

I can now fire off a query simply like this and use my object.property syntax without any conversion:

var data = new SqlDataAccess("WebStore_ConnectionString");
dynamic reader = data.ExecuteDynamicDataReader("select * from wws_items");

StringBuilder sb = new StringBuilder();

while (reader.Read())
{
    string sku = reader.Sku;
    string descript = reader.Descript;
    decimal? price = reader.Price;

    sb.AppendLine(sku + " " + descript + " " + price.Value.ToString("n2"));
}
reader.Close();

Note that Read() and Close() work on the dynamic because I explicitly implemented them in TryInvokeMember() based on the method name.

Summary

Sweet. This makes it super easy and transparent to access data with clean syntax! Personally I much prefer object.property syntax over collection syntax plus type casting and so I'm sold on this concept of using custom dynamic types for wrapping non object data structures into object syntax dynamic types.

Clearly this is not something you want to use for all occasions. Were performance is is of utmost importance, raw DataReader access is still a better choice. But for smallish result sets or one-off queries in application (especially admin interfaces) this can be a nice enhancement to make code easier to read and maintain.

Also, direct DataReader access - for me at least - seems to be going the way of the Dodo, with ORM based data access mostly replacing raw ADO.NET data access. But there are occasions even with an ORM that I fall back to DataReaders with complex queries or maintenance tasks where mapping a one of query to a type is simply overkill. Using this class though I can at least have ORM-like syntax in my code even if strong typing is not available.

And as I can attest to at the moment - old code dies only slowly - and I still find myself digging around in 10 year old code from time to time that uses low level ADO.NET data access and it's nice to have some tools to modernize that old code with, with minimal effort. This tool fits the bill for me.

Resources

• Westwind.Utilities from NuGet
• West Wind Web & AJAX Toolkit