Checking for optional columns in a DataTable

Introduction

This article discusses ways to check for the presence of optional columns in a DataTable. Accessing optional columns may be tricky because attempting to access a non-present column (or a non- present DataTable in a DataSet) throws an exception.

Background

When dealing with external or dynamic data, you can never be sure which columns are going to be present in your DataRow, and for that matter, whether that DataTable is going to be present in the DataSet. I often use the DataSet.ReadXML() method to read XML files into a DataSet. The most common problem I encounter is that some XML elements and attributes are optional. Missing optional elements may result in absent tables (as ReadXml() maps each element to a table). Non-present optional attributes may result in missing columns in the table corresponding to the containing element.

Another source of ‘optional’ data columns is database schema versions and changes. The database schema used in the development environment might include columns which are not present in the production database schema. Robust code should survive small schema differences and cope with the missing data columns.

The Trivial Approach

Accessing a missing element throws an exception – no problem, I can just surround the code with an empty try-catch block and ignore the exception, right?

private void ReadingSomeData(DataTable dt)
{
    foreach (DataRow dr in dt.Rows)
    {
        …
        // Read optional data
        int myOptionalData = -1; // or whatever default makes sense
        try
        {
            myOptionalData = Convert.ToInt32(dr[“MyOptionalColumn”]);
        }
        catch {}
        …
        (General exception handling here)
    }
}

Looks good, right? There are two basic problems with the ‘try-catch’ method of testing:

• Throwing and catching exceptions is slow.
• Real exceptions (such as bad data format) are being masked.

For example: if our optional data was actually present, but contained a string instead of a int, the resulting exception thrown by Convert.ToInt32() will be ignored and you may never become aware of the problem.

Based on my experience, ignoring exceptions is bad unless done for very good (and very specific) reasons.

The Elegant Approach

Both DataSet and DataTable support the Contains() method. Use Contains() to check for the existence of optional elements. Using the Contains() approach, exceptions are not masked and if the data is problematic, the problem becomes apparent.

The example above becomes:

private void ReadingSomeData(DataTable dt)
{
    foreach (DataRow dr in dt.Rows)
    {
        …
        // Read optional data
        int myOptionalData = -1; // or whatever default makes sense
        if (dt.Columns.Contains(“MyOptionalColumn”)
        {
            myOptionalData = Convert.ToInt32(dr[“MyOptionalColumn”]);
        }
        …
        (General exception handling here)
    }
}

Much nicer, but what if you only have a DataRow to work with? It seems a shame to have to drag the entire DataTable down to the method! No need to worry, you can access the DataRow parent DataTable using the Table property of the DataRow.

private void ReadMyDataRow(DataRow dr)
{
    …
    // Read optional data
    int myOptionalData = -1; // or whatever default makes sense
    if (dr.Table.Columns.Contains(“MyOptionalColumn”)
    {
        myOptionalData = Convert.ToInt32(dr[“MyOptionalColumn”]);
    }
    …
    (General exception handling here)
}

What about optional tables? No problem…

private void ReadMyDS(DataSet ds)
{
    …
    // Check for an optional table
    if (ds.Contains(“MyNewTable”))
    {
        ReadingSomeData(ds.Tables[“MyNewTable”])
        …
    }
    …
    (General exception handling here)
}

Conclusion

External data is never 100% predictable. Use the Contains() method to elegantly and robustly handle optional data elements without masking real data problems.

History

V1.0 - Original publication.

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here