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)) {
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)) {
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.

Comments (16)

  1. RichB says:

    Reminds me of VB3 with DAO. Late bound recordset column access.

  2. Christian says:

    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.

  3. Ming says:

    Why make a simple works to more complex??

  4. Alexander says:

    I think it is valid to use ‘dynamic’ to access dictionary with the property syntax. It’s just a syntax sugar, but still nice.

  5. Jalpesh says:

    HI David,

    What about the performance of the code. It will be fast or slow. I am newbie to 4.0.



  6. Guy says:

    I feel like we are going backwards simply due to boredom with the features that take us forwards

  7. developer says:

    what are you using to format code in this article. Is there special visual studio add-in or theme?


  8. David Ebbo says:

    @developer: I use SyntaxHighlighter for the code snippets. Read Scott Hanselman talk about it:

  9. tobi says:

    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.

  10. David Ebbo says:

    @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.

  11. Manu says:

    Would that work with Intellisense?

  12. David Ebbo says:

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

  13. None says:

    A yield return within a


  14. Richard says:

    ToList is just one of the subtle bugs in this code. How about:

    bool fail = false;

    dynamic prev = null;

    foreach (var row in SimpleQuery.Execute("…", "SELECT DISTINCT UserID FROM Users"))


       if (null != prev && prev.UserID == row.UserID)


           fail = true;



       prev = row;


    if (fail)





    If the query returns two or more records, the result will be the message "FAIL:", followed by an exception.

    The fix is quite simple:

    using (SqlDataReader reader = command.ExecuteReader())


       return reader.Cast<IDataRecord>()

            .Select(r => new DataRecordDynamicWrapper(r));


    The SqlDataReader.GetEnumerator method returns a new DbEnumerator, which copies the meta-data and values for each record to a new DataRecordInteral class.

  15. Richard says:

    Sorry, that won’t work either. It should be:

    using (SqlDataReader reader = command.ExecuteReader())


       foreach (IDataRecord record in reader)


           yield return new DataRecordDynamicWrapper(record);



  16. David Ebbo says:

    @Richard: thanks for catching this.  I’ll fix the post soon!

Skip to main content