Check for column name in a SqlDataReader object

Question

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#.

Answer 1

I think your best bet is to call GetOrdinal("columnName") on your DataReader up front, and catch an IndexOutOfRangeException in case the column isn't present.

In fact, let's make an extension method:

public static bool HasColumn(this IDataRecord r, string columnName)
{
    try
    {
        return r.GetOrdinal(columnName) >= 0;
    }
    catch (IndexOutOfRangeException)
    {
        return false;
    }
}

Edit

Ok, this post is starting to garner a few down-votes lately, and I can't delete it because it's the accepted answer, so I'm going to update it and (I hope) try to justify the use of exception handling as control flow.

The other way of achieving this, as posted by Chad Grant, is to loop through each field in the DataReader and do a case-insensitive comparison for the field name you're looking for. This will work really well, and truthfully will probably perform better than my method above. Certainly I would never use the method above inside a loop where performace was an issue.

I can think of one situation in which the try/GetOrdinal/catch method will work where the loop doesn't. It is, however, a completely hypothetical situation right now so it's a very flimsy justification. Regardless, bear with me and see what you think.

Imagine a database that allowed you to "alias" columns within a table. Imagine that I could define a table with a column called "EmployeeName" but also give it an alias of "EmpName", and doing a select for either name would return the data in that column. With me so far?

Now imagine that there's an ADO.NET provider for that database, and they've coded up an IDataReader implementation for it which takes column aliases into account.

Now, dr.GetName(i) (as used in Chad's answer) can only return a single string, so it has to return only one of the "aliases" on a column. However, GetOrdinal("EmpName") could use the internal implementation of this provider's fields to check each column's alias for the name you're looking for.

In this hypothetical "aliased columns" situation, the try/GetOrdinal/catch method would be the only way to be sure that you're checking for every variation of a column's name in the resultset.

Flimsy? Sure. But worth a thought. Honestly I'd much rather an "official" HasColumn method on IDataRecord.

Answer 2

You can also call GetSchemaTable() on your DataReader if you want the list of columns and you don't want to have to get an exception...

Answer 3

bool exists = false;
int count = dr.FieldCount;
for(int i=0; i < count; i++)
{
   // do a case insensitive comparison here.
   if (dr.GetFieldName(i) == MYFIELDNAME)
   {
       exists = true;
       break;
    }
}

MYFIELDNAME could be the field name, you wish to look for. It might require case-insensitive comparison.

Answer 4

Try this:

Type x = (Type?)(returnData.GetSchemaTable().Columns.Contains("column_name_here")?(Type?)returnData["column_name_here"]:null)

where returnData is your sqlDataReader and Type is your nullable data type for tested column (eg. DateTime? or int? ).

Answer 5

It's much better to use this boolean function:

r.GetSchemaTable().Columns.Contains(field)

One call - no exceptions. It might throw exceptions internally, but I don't think so.

Answer 6

In the accepted answer, using Exceptions for control logic is considered bad practice and has performance costs.

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

The more appropriate way to do this is:

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;
    }
}

Answer 7

I wrote for VB Users

Protected Function HasColumnAndValue(ByRef reader As IDataReader, ByVal columnName As String) As Boolean
    For i As Integer = 0 To reader.FieldCount - 1
     If reader.GetName(i).Equals(columnName) Then
      Return Not IsDBNull(reader(columnName))
     End If
    Next

    Return False
End Function

I tihnk this is more powerful and usage is

If HasColumnAndValue(reader, "ID_USER") Then
    Me.UserID = reader.GetDecimal(reader.GetOrdinal("ID_USER")).ToString()
End If

Answer 8

Neither did I get GetSchemaTable to work, until I found this way.

Basically I do this:

Dim myView As DataView = dr.GetSchemaTable().DefaultView
myView.RowFilter = "ColumnName = 'ColumnToBeChecked'"

If myView.Count > 0 AndAlso dr.GetOrdinal("ColumnToBeChecked") <> -1 Then
  obj.ColumnToBeChecked = ColumnFromDb(dr, "ColumnToBeChecked")
End If

Answer 9

This works great

public static class DataRecordExtensions { public static bool HasColumn(this IDataRecord dr, string columnName) { for (int i=0; i