Creating a Formatted Column within a DataTable

Introduction

If you've worked with databases for any length of time in .Net, you've used a DataTable. And if you've ever used a DataTable, you know the pain of having to create a for-display-only formatted string. Sure, if you have control of the view code, you can just rig it up to display several data-bound fields in sequence. But when the view is generic or you otherwise don't have control over the view, how do you get multiple fields into a single column, formatted as they need to be?

The Old, Old Way

Assume you have a DataTable that has returned to you from whatever data-store you use, and it contains all of the name and address data of a list of contacts that need to be displayed inside a <div> tag on the page. This <div> is already wrapped in a <asp:Repeater> and can be data bound to your control.

<asp:Label ID="NameLabel" runat="server" Text='<%#Eval("Name")%>' /><br />
<asp:Label ID="AddressLabel" runat="server" Text='<%#Eval("StreetAddress")%>' /><br />
<asp:Label ID="CityLabel" runat="server" Text='<%#Eval("City")%>' />, 
<asp:Label ID="StateLabel" runat="server" Text='<%#Eval("State")%>' />
<asp:Label ID="ZIPCodeLabel" runat="server" Text='<%#Eval("ZIPCode")%>' />

And so, now that your fingers are tired just from looking at that, you understand why it might be time to look for a better way. We want this to work:

<asp:Label ID="HTMLAddressLabel" runat="server" Text='<%#Eval("HTMLFormattedAddress")%>' />

The New, Old Way

How might we go about making that work? Well, first we have to format every record in the datatable we retrieved from our data store.

// Get the data
DataTable dt = SomeMethodThatGetsADataTableFromOurDatabase();

// Add an empty column to it
dt.Columns.Add("HTMLFormattedAddress", typeof(string));

// Go through each row and create a "computed" column
for(int x = 0; x < dt.Rows.Count; x++)
{
    dt.Rows[x]["HTMLFormattedAddress"] = string.Format(
        "{0}<br />{1}<br />{2}, {3} {4}", dt.Rows[x]["Name"],
        dt.Rows[x]["StreetAddress"],
        dt.Rows[x]["City"],
        dt.Rows[x]["State"],
        dt.Rows[x]["ZIPCode"]
    );
}

Now you just bind the DataTable to your Repeater like you normally would.

It's only a few lines of code, and it's really not so bad when you only have one or two of these to do. I'm using a very simplistic example, so you might very well think that this isn't worth your time. But trust me, when you have this snippet of code all over your codebase and hindering your ability to maintain the code, you will beg for another way.

Now, the veterans out there among you are doubtless screaming at me for not using DataColumn.Expression to do the above without the for-loop. That's a valid point, but I showed String.Format because it uses a syntax that is much more friendly to our task. What we're aiming for is a hybrid of DataColumn.Expression and String.Format.

The Hero Arrives

Extension methods are made for this. They've been around for a while now, and they're very handy when you need to do something consistently in (or to) a framework class, but would like to keep your code clean and easy to read. We're going to use one to encapsulate our formatting functionality, and do things in a reusable way. Here's the magic:

// remember, extension methods must be in a static class!
public static class DataTableExtension
{
    // and the methods themselves must also be static!
    public static void CreateFormattedColumn(this DataTable dt, string columnName, string formatString)
    {
        // first, we need to create the empty column
        dt.Columns.Add(columnName, typeof(string));

        // then, alter the formatString to use standard String.Format syntax
        for(int x = 0; x < dt.Columns.Count; x++)
        {
            formatString = formatString.Replace(
                "{" + dt.Columns[x].ColumnName + "}",
                "{" + x.ToString() + "}"
            );
        }

        // finally, get the data into the correct format and put it in our new column
        for(int x = 0; x < dt.Rows.Count; x++)
        {
            dt.Rows[x][columnName] = string.Format(formatString, dt.Rows[x].ItemArray);
        }
    }
}

The first step is to create an empty column with the chosen name. This is limited to being a String type column in this implementation.

The next step is to convert the named columns in the format string to be their positional integer placeholders that String.Format will understand. For our example with "Name", "StreetAddress", "City", "State", and "ZIPCode", it converts those to "0", "1", "2", "3", and "4" respectively.

After we have a format string that String.Format can use, we set our new column's value to the output of the String.Format function. It takes a format string and an object array, so we have to convert our DataRow to an object[] by using the DataRow.ItemArray property. Magic, I tell you!

Using the code

So, how does it all come together? Here's the original usage snippet rewritten for this formatter:

// Get the data
DataTable dt = SomeMethodThatGetsADataTableFromOurDatabase();

// Add our formatted column to it
dt.CreateFormattedColumn(
    "HTMLFormattedAddress",
    "{Name}<br />{StreetAddress}<br />{City}, {State} {ZIPCode}"
);

Now just data bind the DataTable to the Repeater. You've reduced what used to be 5 to 7 lines of messy code into just 3 clean lines of code!

Limitations and Next Steps

There are a few limitations. First of all, the formatted columns must be of type String. There are ways around that, but they're more complicated. Second, this doesn't do conditional formatting. That can be coded as well, but it's even more complicated than handling multiple data types, and typically involves a temporary calculated column using an expression. That means there's a bit more memory and processing overhead. Make sure it's worth it before you head down that path.

Building these sorts of extension methods to do common operations with framework classes has huge maintenance benefits when done well.

Enjoy!

History

2014-07-02 - v1.0 - Just dipping my toes into writing articles on CodeProject... Don't expect much!