C# – Check for column name in a SqlDataReader object

cnetsqldatareader

How do I check to see if a column exists in a SqlDataReader object? In my data access layer, I have create a method that builds the same object for multiple stored procedures calls. One of the stored procedures has an additional column that is not used by the other stored procedures. I want to modified the method to accommodate for every scenario.

My application is written in C#.

Best Answer

public static class DataRecordExtensions
{
    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i=0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;
        }
        return false;
    }
}

Using Exceptions for control logic like in some other answers is considered bad practice and has performance costs. It also sends false positives to the profiler of # exceptions thrown and god help anyone setting their debugger to break on exceptions thrown.

GetSchemaTable() is also another suggestion in many answers. This would not be a preffered way of checking for a field's existance as it is not implemented in all versions (it's abstract and throws NotSupportedException in some versions of dotnetcore). GetSchemaTable is also overkill performance wise as it's a pretty heavy duty function if you check out the source.

Looping through the fields can have a small performance hit if you use it a lot and you may want to consider caching the results.