Using C# Dynamic to simplify ADO.NET Data Access

Update (11/7/09) : fixed Execute() method per Richard’s suggestion to wrap IDataRecord instead of Reader.

Recently, I started playing around with C# dynamic, and blogged how it could be used to call static class members late bound.  Today, I was talking to Phil Haack, who I think had talked to ScottGu, and he mentioned that it would be cool to use dynamic to simplify data access when you work directly with SQL query.  So I thought I’d play around with that, and it didn’t take much code to make it work nicely.

So the scenario is that you’re not using any fancy O/R mapper like LINQ to SQL or Entity Framework, but you’re directly using ADO.NET to execute raw SQL commands.  It’s not something that I would personally do, but there are a lot of folks who prefer this over the higher level data access layers.

So let’s look at an example of what we’re trying to improve.  Let’s borrow an MSDN sample about SqlCommand:

 string commandText = "SELECT OrderID, CustomerID FROM dbo.Orders;";
using (var connection = new SqlConnection(Settings.Default.NorthwindConnectionString)) {
    using (var command = new SqlCommand(commandText, connection)) {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader()) {
            while (reader.Read()) {
                Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
            }
        }
    }
}

And now let’s assume that we’re only ever interested in making one select query at a time, which lets us abstract out some of the details about the SQL Connection.  By writing some nice little helpers that make use of dynamic, we’re able to write something much simpler:

 string commandText = "SELECT OrderID, CustomerID FROM dbo.Orders;";
foreach (var row in SimpleQuery.Execute(Settings.Default.NorthwindConnectionString, commandText)) {
    Console.WriteLine(String.Format("{0}, {1}", row.OrderID, row.CustomerID));
}

A few things to note:

  • We pretty much just make one method call, and directly get back objects that we can work with.  Contrast this with having to deal with SqlConnection, SqlCommand and SqlDataReader.
  • We use a standard enumeration pattern, while SqlDataReader makes you call reader.Read() on every iteration, which looks ugly.
  • And the big one: we get to access the properties directly on the row object, thanks to dynamic!  e.g. we can write row.OrderID instead of reader[0] (or reader[“OrderID”])

So how does it all work?  First, let’s take a look at the SimpleQuery.Execute helper method:

 public static IEnumerable<dynamic> Execute(string connString, string commandText) {
    using (var connection = new SqlConnection(connString)) {
        using (var command = new SqlCommand(commandText, connection)) {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader()) {
                foreach (IDataRecord record in reader) {
                    yield return new DataRecordDynamicWrapper(record);
                }
            }
        }
    }
}

So it’s basically the same as the MSDN code, except that it wraps the reader that it returns in a DataRecordDynamicWrapper, which is what makes the dynamic magic work.  Also, note that the method returns IEnumerable<dynamic>, which is why we’re able to just use ‘var row’ in the test code (which I think looks nicer than ‘dynamic row’).

So now all that’s left to look at is DataRecordDynamicWrapper, which is incredibly simple:

 public class DataRecordDynamicWrapper : DynamicObject {
    private IDataRecord _dataRecord;
    public DataRecordDynamicWrapper(IDataRecord dataRecord) { _dataRecord = dataRecord; }

    public override bool TryGetMember(GetMemberBinder binder, out object result) {
        result = _dataRecord[binder.Name];
        return result != null;
    }
}

All it does is index into the data record to get the value for a given property name.

I think what I did with static methods in my last post was probably a bit of an abuse of dynamic, because we were dealing with statically types objects, and there are alternatives that would have avoided the need for dynamic.  But here, it’s I think a more legitimate use, because we’re dealing with data record objects that are intrinsically untyped.  While dynamic of course doesn’t give us strong typing, it at least makes it more pleasant to deal with.

One last thing worth noting is that to make this real, we should add support for SQL parameters, which makes it easier to write SQL code that is not vulnerable to SQL-injection attacks.  That could easily be done by passing additional params to SimpleQuery.Execute.  This sample is more of a proof of concept and an excuse to mess around with dynamic :)

Zipped sample is attached to this post.

DataReaderWithDynamic.zip