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();
        }
    }
}
Advertisements
Posted in C#

8 thoughts on “Using C# “dynamic” with SqlDataReader

  1. Terry Slack

    Great Article…but can we expand on it…that is, what if T was a class that contained objects of other classes. How would we get the properties for the objects…in the main class (T) a constructor is setup to instantiate the objects…so how do we find the names of the properties within the inner objects of class T? I’ve tried doing a foreach over element after new T() is called. I then loop over the GetFields FileInfo [] that is returned and I am unable to get the inner properties. Not sure how to format the code exactly to display correctly, so I’m cutting and pasting. Any help is appreciated..I also apologize in advance for the formatting…I’m not sure how to format it the same way the article was formatted.

    public class IClass
    {
    public A AClass;
    public B BClass;
    public IClass()
    {
    AClass = new A();
    BClass = new B();
    }
    }

    public class A
    {
    public String aString;
    }
    public class B
    {
    public String bString;
    }

    foreach (var EnumerableObj in getSqlData())
    {
    //….Do someting with the IEnumerable
    }

    public static IEnumerable GetSqlData(String SP_TO_USE, IList ParamterList) where T : class, new()
    {
    return getSqlData(SP_TO_USE, ParamterList);
    }
    private static IEnumerable getSqlData(String SP_TO_USE, IList ParamterList) where T : class, new()
    {
    const Int32 DeadLocked = 1205;
    using (var cmd = AddParameters(getDBCommand(), ParamterList))
    {
    var properties = typeof (T).GetProperties();
    cmd.CommandTimeout = 500;
    cmd.CommandText = SP_TO_USE.Trim();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection.Open();
    var nextResult = true;
    using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
    while (nextResult)
    {
    while (reader.Read())
    {
    var element = new T();
    /*
    Need code here to get the inner obj properties from objects created in T

    My attempt

    foreach (var objs in element.GetType().GetFields())
    {
    Type b = objs.GetType();
    PropertyInfo[] props = b.GetProperties();

    Type[] innerTypes = objs.GetType().GetNestedTypes();
    foreach (Type t in innerTypes)
    {
    //Need the name of the innerTypes to use in place of f.Name below
    }
    }
    */

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

    yield return element; // as IDataRecord;
    }
    nextResult = reader.NextResult();
    }
    }
    }
    }

  2. phejndorf

    As I see your question, what you want to do is to create a hierarchy of nested classes from one stored procedure. You might want to solve this by making IClass an interface that inherits from an interface of IClassA and IClassB instead of instantiating them inside IClass. Otherwise you need to “flatten” the object hierarchy – which can lead to other problems such as uniqueness of field- (and property-) names. Though not recommended I think you might be able to do pull it off recursively like this (beware that this dosn’t take properties into account):

    static List<Tuple> FlattenObject(object element, List<Tuple> list = null)
    {
    if (list == null) list = new List<Tuple>();

    foreach (var field in element.GetType().GetFields())
    {
    var t = field.FieldType;
    if (t.IsPrimitive || t == typeof(string) || t == typeof(Decimal) || t == typeof(DateTime))
    {
    list.Add(new Tuple(field, element));
    }
    else
    {
    object o = field.GetValue(element);
    FlattenObject(o, list);
    }
    }

    return list;
    }

    Called like so:

    var element = new T();
    var l = FlattenObject(element);

    The List of Tuples then contains the information to fill the elements from the SQL reply,

    • Terry Slack

      I was thinking along the same lines. I currently just return datasets, but have been trying to figure a way to harness the speed of the datareader, and dealing with multiple selects returned from a stored procedure. At first I was yielding the rows as IDataRecords, but ran into the problem with column names being the same…I”ll try your solution

  3. Terry

    In try to apply your solution, how would I send in an interface for Type T, as opposed to a class. In trying it in C# I have to remove the where T : new() from the end of the method, and when I do and try and do anything with T in the method, Ie var element = new T() I get an error.
    private static IEnumerable GetSqlData(string connectionstring, string sql) where T : new()

    Any thoughts?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s