Freigeben über

Using C# Dynamic to simplify ADO.NET Data Access

  • Artikel

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

Comments

  • Anonymous
    October 29, 2009
    Reminds me of VB3 with DAO. Late bound recordset column access.

  • Anonymous
    October 29, 2009
    I think, this isn't a good example of using "dynamic". everyone else who sees this code is in a false positive thinking of using an ORM.

  • Anonymous
    October 29, 2009
    Why make a simple ado.net works to more complex??

  • Anonymous
    October 29, 2009
    I think it is valid to use 'dynamic' to access dictionary with the property syntax. It's just a syntax sugar, but still nice.

  • Anonymous
    October 29, 2009
    HI David, What about the performance of the code. It will be fast or slow. I am newbie to 4.0. Thanks, Jalpesh

  • Anonymous
    October 29, 2009
    I feel like we are going backwards simply due to boredom with the features that take us forwards

  • Anonymous
    October 29, 2009
    what are you using to format code in this article. Is there special visual studio add-in or theme? Thanks

  • Anonymous
    October 30, 2009
    @developer: I use SyntaxHighlighter for the code snippets. Read Scott Hanselman talk about it: http://www.hanselman.com/blog/BestCodeSyntaxHighlighterForSnippetsInYourBlog.aspx

  • Anonymous
    October 30, 2009
    unfortunately this does not work when .ToList() is called on the result set. thats because all wrapper objects share the same data reader. a solution would be to return a copy of the row data in a dictionary.

  • Anonymous
    October 30, 2009
    @tobi: good point, ToList() would not work based on how the code is written here. I really didn't test this much outside of the scenario above, and it's really just a proof of concept that may need some work before be put to real use.

  • Anonymous
    October 31, 2009
    Would that work with Intellisense?

  • Anonymous
    November 01, 2009
    @Manu: you will not get Intellisense when you use C# dynamic, since the set of valid properties is dynamic and not known until runtime.

  • Anonymous
    November 02, 2009
    A yield return within a connection.open? lol...

  • Anonymous
    November 03, 2009
    The comment has been removed

  • Anonymous
    November 04, 2009
    Sorry, that won't work either. It should be: using (SqlDataReader reader = command.ExecuteReader()) {    foreach (IDataRecord record in reader)    {        yield return new DataRecordDynamicWrapper(record);    } }

  • Anonymous
    November 07, 2009
    The comment has been removed