Using C# “dynamic” with SqlDataReader

Type safety is, in my opinion, a lot better than sliced bread. But though the dynamic keyword has to be used very carefully, I still think there are places where it can be an advantage to bring it into play.

For instance when accessing data via SqlDataReader in a dynamic fashion. To that end I suggest wrapping the data reader in a construct that will allow you to return a dynamic class where the columns are accessible by name and not a string reference. This can be done via an ExpandoObject that makes it possible to work with the dynamic class as if it was a dictionary:

private dynamic SqlDataReaderToExpando(SqlDataReader reader)
{
    var expandoObject = new ExpandoObject() as IDictionary<string, object>;

    for (var i = 0; i < reader.FieldCount; i++)
        expandoObject.Add(reader.GetName(i), reader[i]);

    return expandoObject;
}

To create a method for returning the elements of a Sql query in a dynamic fashion can then be implemented like so:

private IEnumerable<dynamic> GetDynamicSqlData(string connectionstring, string sql)
{
    using (var conn = new SqlConnection(connectionstring))
    {
        using (var comm = new SqlCommand(sql, conn))
        {
            conn.Open();
            using (var reader = comm.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return SqlDataReaderToExpando(reader);
                }
            }
            conn.Close();
        }
    }
}

 

There are of course all the usual problems with late binding that misspelling and erroneous datatypes won’t be caught until you run the code, but still I find it more elegant than accessing the SqlDataReader’s columns with column-name strings as indexers. And in case you later on decide to make the IEnumerable use an actual class, it will not break the code. This can be done as shown here, where you have a class of T that maps to your database-request:

private static IEnumerable<T> GetSqlData<T>(string connectionstring, string sql) 
{
    var properties = typeof (T).GetProperties();

    using (var conn = new SqlConnection(connectionstring))
    {
        using (var comm = new SqlCommand(sql, conn))
        {
            conn.Open();
            using (var reader = comm.ExecuteReader())
            {
                while (reader.Read())
                {
                    // http://blog.benhall.me.uk/2006/08/creating-objects-dynamically-with-c-20.html
                    var element = Activator.CreateInstance<T>();

                    foreach (var f in properties)
                    {
                        var o = reader[f.Name];
                        if (o.GetType() != typeof(DBNull)) f.SetValue(element, o, null);
                    }
                    yield return element;
                }
            }
            conn.Close();
        }
    }
}

 

Actually the little Activator.CreateInstance<T> trick can nowadays also be coded with a new T() if you add a type-constraint on the method (where T : new()), but internally it’s still Activator.CreateInstance<T> so this version is basically just sugar-coating:

private static IEnumerable<T> GetSqlData<T>(string connectionstring, string sql) where T : new()
{
    var properties = typeof (T).GetProperties();

    using (var conn = new SqlConnection(connectionstring))
    {
        using (var comm = new SqlCommand(sql, conn))
        {
            conn.Open();
            using (var reader = comm.ExecuteReader())
            {
                while (reader.Read())
                {
                    var element = new T();

                    foreach (var f in properties)
                    {
                        var o = reader[f.Name];
                        if (o.GetType() != typeof(DBNull)) f.SetValue(element, o, null);
                    }
                    yield return element;
                }
            }
            conn.Close();
        }
    }
}