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.