IDataReader and "HasColumn", Best approach?

Question

I've seen two common approaches for checking if a column exists in an IDataReader:

public bool HasColumn(IDataReader reader, string columnName)
{
  try
  {
      reader.getOrdinal(columnName)
      return true;
  }
  catch 
  {
       return false;
  }
}

Or:

public bool HasColumn(IDataReader reader, string columnName)
{

    reader.GetSchemaTable()
         .DefaultView.RowFilter = "ColumnName='" + columnName + "'";

    return (reader.GetSchemaTable().DefaultView.Count > 0);
}

Personally, I've used the second one, as I hate using exceptions for this reason.

However, on a large dataset, I believe RowFilter might have to do a table scan per column, and this may be incredibly slow.

Thoughts?

Answer 1

A lot depends on how you're using HasColumn. Are you calling it just once or twice, or repeatedly in a loop? Is the column likely to be there or is that completely unknown in advance?

Setting a row filter probably would do a table scan each time. (Also, in theory, GetSchemaTable() could generate an entirely new table with every call, which would be even more expensive -- I don't believe SqlDataReader does this, but at the IDataReader level, who knows?) But if you only call it once or twice I can't imagine this being that much of an issue (unless you have thousands of columns or something).

(I would, however, at least store the result of GetSchemaTable() in a local var within the method to avoid calling it twice in quick succession, if not cache it somewhere on the off chance that your particular IDataReader DOES regenerate it.)

If you know in advance that under normal circumstances the column you ask for will be present, the exception method is a bit more palatable (because the column not being there is, in fact, an exceptional case). Even if not, it might perform slightly better, but again unless you're calling it repeatedly you should ask yourself if performance is really that much of a concern.

And if you ARE calling it repeatedly, you probably should consider a different approach anyway, such as: call GetSchemaTable() once up front, loop through the table, and load the field names into a Dictionary or some other structure that is designed for fast lookups.

Answer 2

I wouldn't worry about the performance impact. Even if you had a table with 1000 columns (which would be an enormous table), you are still only doing a "table scan" of 1000 rows. That is likely to be trivial.

Premature optimization will just lead you toward an unnecessarily complex implementation. Implement the version that seems best to you, and then measure the performance impact. If it is unacceptable compared to your performance requirements, then consider alternatives.

Answer 3

I think I have a reasonable answer for this old gem.

I would go with the first approach cause its much simpler. If you want to avoid the exception you can cache the field names and do a TryGet on the cache.

public Dictionary<string,int> CacheFields(IDataReader reader)
{

    var cache = new Dictionary<string,int>();
    for (int i = 0; i < reader.FieldCount; i++)
    {
        cache[reader.GetName(i)] = i;
    }
    return cache;
}

The upside of this approach is that it is simpler and gives you better control. Also, note, you may want to look into case insensitive or kana insensitive compares, which would make stuff a little trickier.