Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

Creating a Dynamic DataRow for easier DataRow Syntax


:P
On this page:

I've been thrown back into an older project that uses DataSets and DataRows as their entity storage model. I have several applications internally that I still maintain that run just fine (and I sometimes wonder if this wasn't easier than all this ORM crap we deal with with 'newer' improved technology today - but I disgress) but use this older code. For the most part DataSets/DataTables/DataRows are abstracted away in a pseudo entity model, but in some situations like queries DataTables and DataRows are still surfaced to the business layer.

Here's an example. Here's a business object method that runs dynamic query and the code ends up looping over the result set using the ugly DataRow Array syntax:

public int UpdateAllSafeTitles()
{
    int result = this.Execute("select pk, title, safetitle from " + Tablename + " where EntryType=1", "TPks");
    if (result < 0)            
        return result;

    result = 0;

    foreach (DataRow row in this.DataSet.Tables["TPks"].Rows)
    {
        string title = row["title"] as string;
        string safeTitle = row["safeTitle"] as string;
        int pk = (int)row["pk"];

        string newSafeTitle = this.GetSafeTitle(title);
        if (newSafeTitle != safeTitle)
        {
            this.ExecuteNonQuery("update " + this.Tablename + " set safeTitle=@safeTitle where pk=@pk",
                                 this.CreateParameter("@safeTitle",newSafeTitle),
                                 this.CreateParameter("@pk",pk) );
            result++;
        }
    }

    return result;
}

The problem with looping over DataRow objecs is two fold: The array syntax is tedious to type and not real clear to look at, and explicit casting is required in order to do anything useful with the values. I've highlighted the place where this matters.

Using the DynamicDataRow class I'll show in a minute this code can be changed to look like this:

public int UpdateAllSafeTitles()
{
    int result = this.Execute("select pk, title, safetitle from " + Tablename + " where EntryType=1", "TPks");
    if (result < 0)            
        return result;

    result = 0;

    foreach (DataRow row in this.DataSet.Tables["TPks"].Rows)
    {
        dynamic entry = new DynamicDataRow(row);

        string newSafeTitle = this.GetSafeTitle(entry.title);
        if (newSafeTitle != entry.safeTitle)
        {
            this.ExecuteNonQuery("update " + this.Tablename + " set safeTitle=@safeTitle where pk=@pk",
                                 this.CreateParameter("@safeTitle",newSafeTitle),
                                 this.CreateParameter("@pk",entry.pk) );
            result++;
        }
    }

    return result;
}

The code looks much a bit more natural and describes what's happening a little nicer as well.

Well, using the new dynamic features in .NET it's actually quite easy to implement the DynamicDataRow class.

Creating your own custom Dynamic Objects

.NET 4.0 introduced the Dynamic Language Runtime (DLR) and opened up a whole bunch of new capabilities for .NET applications. The dynamic type is an easy way to avoid Reflection and directly access members of 'dynamic' or 'late bound' objects at runtime. There's a lot of very subtle but extremely useful stuff that dynamic does (especially for COM Interop scenearios) but in its simplest form it often allows you to do away with manual Reflection at runtime.

In addition you can create DynamicObject implementations that can perform  custom interception of member accesses and so allow you to provide more natural access to more complex or awkward data structures like the DataRow that I use as an example here.

Bascially you can subclass DynamicObject and then implement a few methods (TryGetMember, TrySetMember, TryInvokeMember) to provide the ability to return dynamic results from just about any data structure using simple property/method access.

In the code above, I created a custom DynamicDataRow class which inherits from DynamicObject and implements only TryGetMember and TrySetMember. Here's what simple class looks like:

/// <summary>
/// This class provides an easy way to turn a DataRow 
/// into a Dynamic object that supports direct property
/// access to the DataRow fields.
/// 
/// The class also automatically fixes up DbNull values
/// (null into .NET and DbNUll to DataRow)
/// </summary>
public class DynamicDataRow : DynamicObject
{
    /// <summary>
    /// Instance of object passed in
    /// </summary>
    DataRow DataRow;
    
    /// <summary>
    /// Pass in a DataRow to work off
    /// </summary>
    /// <param name="instance"></param>
    public DynamicDataRow(DataRow dataRow)
    {
        DataRow = dataRow;
    }

   /// <summary>
   /// Returns a value from a DataRow items array.
   /// If the field doesn't exist null is returned.
   /// DbNull values are turned into .NET nulls.
   /// 
   /// </summary>
   /// <param name="binder"></param>
   /// <param name="result"></param>
   /// <returns></returns>
    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        result = null;

        try
        {
            result = DataRow[binder.Name];

            if (result == DBNull.Value)
                result = null;
            
            return true;
        }
        catch { }

        result = null;
        return false;
    }


    /// <summary>
    /// Property setter implementation tries to retrieve value from instance 
    /// first then into this object
    /// </summary>
    /// <param name="binder"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        try
        {
            if (value == null)
                value = DBNull.Value;

            DataRow[binder.Name] = value;
            return true;
        }
        catch {}

        return false;
    }
}

To demonstrate the basic features here's a short test:

[TestMethod]
[ExpectedException(typeof(RuntimeBinderException))]
public void BasicDataRowTests()
{
    DataTable table = new DataTable("table");
    table.Columns.Add( new DataColumn() { ColumnName = "Name", DataType=typeof(string) });
    table.Columns.Add( new DataColumn() { ColumnName = "Entered", DataType=typeof(DateTime) });
    table.Columns.Add(new DataColumn() { ColumnName = "NullValue", DataType = typeof(string) });

    DataRow row = table.NewRow();

    DateTime now = DateTime.Now;

    row["Name"] = "Rick";
    row["Entered"] = now;
    row["NullValue"] = null; // converted in DbNull

    dynamic drow = new DynamicDataRow(row);

    string name = drow.Name;
    DateTime entered = drow.Entered;
    string nulled = drow.NullValue;

    Assert.AreEqual(name, "Rick");
    Assert.AreEqual(entered,now);
    Assert.IsNull(nulled);
    
    // this should throw a RuntimeBinderException
    Assert.AreEqual(entered,drow.enteredd);
                
}

The DynamicDataRow requires a custom constructor that accepts a single parameter that sets the DataRow. Once that's done you can access property values that match the field names. Note that types are automatically converted - no type casting is needed in the code you write. The class also automatically converts DbNulls to regular nulls and vice versa which is something that makes it much easier to deal with data returned from a database.

What's cool here isn't so much the functionality - even if I'd prefer to leave DataRow behind ASAP -  but the fact that we can create a dynamic type that uses a DataRow as it's 'DataSource' to serve member values. It's pretty useful feature if you think about it, especially given how little code it takes to implement.

By implementing these two simple methods we get to provide two features I was complaining about at the beginning that are missing from the DataRow:

  • Direct Property Syntax
  • Automatic Type Casting so no explicit casts are required

Caveats

As cool and easy as this functionality is, it's important to understand that it doesn't come for free. The dynamic features in .NET are - well - dynamic. Which means they are essentially evaluated at runtime (late bound). Rather than static typing where everything is compiled and linked by the compiler/linker, member invokations are looked up at runtime and essentially call into your custom code. There's some overhead in this. Direct invocations - the original code I showed - is going to be faster than the equivalent dynamic code.

However, in the above code the difference of running the dynamic code and the original data access code was very minor. The loop running over 1500 result records took on average 13ms with the original code and 14ms with the dynamic code. Not exactly a serious performance bottleneck. One thing to remember is that Microsoft optimized the DLR code significantly so that repeated calls to the same operations are routed very efficiently which actually makes for very fast evaluation.

The bottom line for performance with dynamic code is: Make sure you test and profile your code if you think that there might be a performance issue. However, in my experience with dynamic types so far performance is pretty good for repeated operations (ie. in loops). While usually a little slower the perf hit is a lot less typically than equivalent Reflection work.

Although the code in the second example looks like standard object syntax, dynamic is not static code. It's evaluated at runtime and so there's no type recognition until runtime. This means no Intellisense at development time, and any invalid references that call into 'properties' (ie. fields in the DataRow) that don't exist still cause runtime errors. So in the case of the data row you still get a runtime error if you mistype a column name:

// this should throw a RuntimeBinderException
Assert.AreEqual(entered,drow.enteredd);

A little more tweaking: Creating some DataTable extensions to use DynamicDataRow

If you want this functionality even more integrated, you can extend the DataTable class with a couple of extension methods to automatically provide you with DynamicDataRow instances. The DynamicRow(index) and DynamicRows() method retrieve these new dynamic methods in a way that is more natural:

foreach (dynamic drow in table.DynamicRows())
{
    Console.WriteLine(drow.Name + " " + drow.Entered.ToString("d") + " " + drow.NullValue);
}

Or you can ask for specific rows:

dynamic drow2 = table.DynamicRow(0);
Console.WriteLine(drow2.Name + " " + drow2.Entered.ToString("d") + " " + drow2.NullValue);

The DataTable extension methods are pretty simple to implement:

public static class DataTableDynamicExtensions
{
    /// <summary>
    /// Returns a dynamic DataRow instance that can be accessed
    /// with the field name as a property
    /// </summary>
    /// <param name="index"></param>
    /// <returns></returns>taTab
    public static dynamic DynamicRow(this DataTable dt, int index)
    {
        var row = dt.Rows[index];            
        return new DynamicDataRow(row);
    }

    /// <summary>
    /// Returns a dynamic list of rows so you can reference them with
    /// row.fieldName
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static List<dynamic> DynamicRows(this DataTable dt)
    {
        List<dynamic> drows = new List<dynamic>();

        foreach (DataRow row in dt.Rows)
            drows.Add(new DynamicDataRow(row));

        return drows;
    }

}

Note that DynamicRows() incurs the overhead of creating all of the dynamic objects before you actually return the data and creates yet another structure - the List<dynamic>. If you're looping through the table rows to find a match it might be more efficient to call DynamicRow() with an index in a plain For loop for improved performance.

Dynamic - Lots of uses

The arrival of Dynamic types in .NET has been met with mixed emotions. Die hard .NET developers decry dynamic types as an abomination to the language. After all what dynamic accomplishes goes against all that a static language is supposed to provide. On the other hand there are clearly scenarios when dynamic can make life much easier (COM Interop being one place).

Think of the possibilities. What other data structures would you like to expose to a simple property interface rather than some sort of collection or dictionary? And beyond what I showed here you can also implement 'Method missing' behavior on objects with InvokeMember which essentially allows you to create dynamic methods. It's all very flexible and maybe just as important: It's easy to do.

There's a lot of power hidden in this seemingly simple interface. Your moveā€¦

Posted in CSharp  .NET  

The Voices of Reason


 

Dennis Gorelik
November 24, 2011

# re: Creating a Dynamic DataRow for easier DataRow Syntax

Sounds a little bit tempting, but introduce more problems that it solves.

The most important goal for us is improve code maintainability, right? (As opposing to making it easier to write new code).

So let's consider maintenance scenario and compare direct DataRow use with DynamicDataRow.

row["title"] as string // DataRow
vs
entry.title // DynamicDataRow

Because it's dynamic, I cannot find all references where title property was used. So that very important advantage of strong typing is lost anyway.

In order to find such reference I have to search for "title" in my code.
However when dealing with DataRow I only need to know what DataRow is and how it was formed.
With DynamicDataRow I have to know what DataRow is AND what DynamicDataRow is.
That add extra complexity to my mental model, which is not good, considering that no real improvement was made.

Rick Strahl
November 24, 2011

# re: Creating a Dynamic DataRow for easier DataRow Syntax

@Dennis - not sure I understand some of your points. How is it different to search for .title than it is to "title"? You can still search this out. The compiler doesn't flag invalid values with DynamicDataRow any more than it does with a DataRow - true, but again there's nothing lost. What you get is: Cleaner syntax and no typecasting required which are definitely two worthwhile things in the work I do.

Ncage
November 24, 2011

# re: Creating a Dynamic DataRow for easier DataRow Syntax

Rick can you please explain how the automatic type casting work? I'm reading the code and it seems like everything in your custom class is still an object. How is it that type casting is automatic?

Rick Strahl
November 25, 2011

# re: Creating a Dynamic DataRow for easier DataRow Syntax

@Ncage - the DLR takes care of that automatically. The compiler knows the type of target properties so it automatically injects the type casts for you at compilation time. IOW dynamic relies on a combination of dynamic invocation PLUS some compiler magic that infers types at compile time.

It's similar to 'var' definitions that infer the type of the target at compile time and replace it with a concrete type. It works as long as the compiler can determine the target type based on a type definition or member signature (method parameters, property type or variable definition).

Hector Correa
November 27, 2011

# re: Creating a Dynamic DataRow for easier DataRow Syntax

It's great to to see other people taking advantages of new features of the language to make the coding experience more pleasant.

Michael Montelone did something similar for XML that shows (1) the power of dynamic and (2) how coding can be less cumbersome.

http://michaelmonteleone.net/2010/06/21/suspiciously-pleasant-xml-with-csharp-4/

Dennis Gorelik
November 30, 2011

# re: Creating a Dynamic DataRow for easier DataRow Syntax

@Rick

My point is that in comparison with direct use of DataRow:
1) Dynamic typing does not add much value (the syntax is only marginally cleaner).
2) Dynamic typing adds complexity.

Overall result: does not worth the effort.

Liam
December 30, 2011

# re: Creating a Dynamic DataRow for easier DataRow Syntax

Here's a lazy evaluation version of DynamicRows:

public static IEnumerable<dynamic> DynamicRows(this DataTable dt)
{
    return dt.Rows.Cast<DataRow>().Select(d => new DynamicDataRow(d));
}

Just add a .ToList() to fully evaluate it and turn it into a list again.

Rick Strahl
February 05, 2012

# re: Creating a Dynamic DataRow for easier DataRow Syntax

@Dennis, I don't agree. Here are what I think are the main benefits:

* Cleaner, more natural looking code and quicker typing
* Automatic type casting - no need to cast in most cases
* Automatic support for null conversion

The latter is actually quite a pain if you're dealing with nullable types because you have to explicitly convert.

Pete Wilson
October 05, 2016

# re: Creating a Dynamic DataRow for easier DataRow Syntax

Is there a reason to prefer Rows.Cast<DataRow>() over .AsEnumerable() ?

public static IEnumerable<dynamic> DynamicRows(this DataTable dt)
{
    return from r in dt.AsEnumerable() select new DynamicDataRow(r);
}

Pete Wilson
February 21, 2017

# re: Creating a Dynamic DataRow for easier DataRow Syntax

After using this for a while in LINQPad, I think a few extensions are useful. I added indexers for column names that aren't proper C# identifier syntax, and implicit conversions to/from DataRow as well as a member implementation of the DataRowExtensions Field<>() function.


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024