Replace SqlDataReader with a DynamicSqlDataReader

Introduction

This is a very simple example of how the dynamic feature of .NET 4.0 can help you on the borders between typed and untyped. It wraps a SqlDataReader in a DynamcSqlDataReader which allows you to access the fields as properties instead of passing string values with the column names to methods.

Using the code

Some code reads like poetry, and doesn't need a lot of explaining. By looking at the test fixture, you should be able to get an idea of the intended use. The database used to prove the concept contains a table Person with the following fields:

• Id: uniqueidentifier (PK)
• Name: nvarchar(50) non null
• Country: nvarchar(50) null
• Age: smallint null

The test fixture:

[TestClass]
public class PersonDataAccessTests
{
    public void Theory_Read_Person_Table(Action<dynamic> assertion)
    {
        using (var connection = new SqlConnection(@"CONNECTION STRING HERE"))
        {
            using (var command = new SqlCommand("SELECT TOP 1 Id," + 
                   "Name,Country,Age FROM Person", connection))
            {
                connection.Open();

                var x = new DynamicSqlDataReader(command.ExecuteReader(
                            System.Data.CommandBehavior.CloseConnection));
                while (x.Read())
                {
                    assertion(x);
                }
            }
        }
    }

    [TestMethod]
    [ExpectedException(typeof(ColumnIsNotInResultSetException))]
    public void Test_Read_Person_Table_Failure()
    {
        Theory_Read_Person_Table((x) =>
        {
            string s = x.NonExistingColumn;
        });
    }

    [TestMethod]
    public void Test_Read_Person_Table_Success()
    {
        Theory_Read_Person_Table((x) =>
        {
            Guid id = x.Id;
            string name = x.Name;
            string country = x.Country;
            int? age = x.Age;

            Assert.AreNotEqual(Guid.Empty, id);
            Assert.AreEqual("Tim", name);
            Assert.AreEqual("Belgium", country);
            Assert.AreEqual(null, age);
        });
    }
}

The actual code:

public class DynamicSqlDataReader : DynamicObject
{
    private SqlDataReader reader;

    public DynamicSqlDataReader(SqlDataReader reader)
    {
        this.reader = reader;
    }

    public bool Read()
    {
        return reader != null && (!reader.IsClosed) && reader.Read();
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        try
        {
            var rawResult = reader.GetValue(reader.GetOrdinal(binder.Name));
            result = rawResult == DBNull.Value ? null : rawResult;
            return true;
        }
        catch (IndexOutOfRangeException)
        {
            throw new ColumnIsNotInResultSetException(binder.Name);
        }
    }
}

Points of interest

Please provide some feedback and perhaps a discussion on the use of the new dynamic feature. I also know that it would be cleaner to wrap the DbDataReader instead of the concrete SqlDataReader, but hey it's just to show the use of the new dynamic feature :)