An easy way to find whether a SqlDataReader contains a field

[Andrei Badea]

Hi all,

it seems there's no easy way to find out whether a SqlDataReader contains a
field (given the field name). It has the GetOrdinal() method, but it throws
an exception if the readed doesn't contain the field. I could probably use
the GetSchemaTable method, but it seems it's a little too complicated for
what I want to do. Deep in the System.Data namespace there's a class names
FieldNameLookup which is used by SqlDataReader.GetOrdinal() to lookup the
field names, and it also contains an IndexOfName() method, which returns -1
if the field name is unknown. I can't understand why SqlDataReader doesn't
also expose a method like this (since it's easy and possible to do it). Or
am I missing something?

Regards,

Andrei

 

[Pete Wright]

I found the class you speak of, looking in WinCV, but it's not in the MSDN
documentation that comes with VS.NET 2003. To my mind that would put
FieldNameLookup out of bounds as an internal, unsupported and very much
subject to change class. So, that leaves you with, as you said,
GetSchemaTable, or GetOrdinal. GetSchemaTable would be the best bet, since
your code intends to examine the schema of a set of results, it makes sense
to use a class that is designed to let you do just that.

Using GetSchemaTable to do what satisfy your particular problem is actually
trivial. It's worth explaining how GetSchemaTable works though.
GetSchemaTable returns a table holding the schema of the reader. There is
one row in the table for each column returned in your reader, and the
columns of the schema table define properties of the reader's result set,
such as the column name, size, data type and so on.

Now, since GetSchemaTable returns a table, all we really need to do is ask
ADO.NET to filter the rows in that table to just the row matching the column
we want (remember, the schema table holds 1 row per column). The easiest way
to do this is with the default view.

For example, if I were looking for a row called "FOO" in a reader's results,
I could do this
DataView myView = myReader.GetSchemaTable().DefaultView;

myView.RowFilter = "ColumnName = 'Foo' ";

All i now need to do is check myView.Count to see if it is zero or not. If
myView.Count is zero, then the column in question is not in the result set.
If on the other hand I get anything other than zero (probably a 1), then the
column I'm looking for exists.


Of course, dumping all that code into your app everytime you want to check
the existence of a column is not a particularly elegant idea. It would be
better to go and invent a function to do the work for you easily. I've
written one for you and included it below. Using this function, you can
just write
if ( columnExists(myReader, "ColumnName") ) { } else { }

So, here's the function
private bool columnExists( SqlDataReader reader, string columnName )

{

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

columnName + "'";

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

}

Hope that helps,

 

[Andrei Badea]

Pete,

thanks for your (pretty extensive) answer. I agree I can't use
FieldNameLookup, I was just trying to point out that SqlDataReader could
very easily export its IndexOf() method. But of course GetSchemaTable() has
a better design. However, I'm not sure it will work in my case. I am trying
to write a class to encapsulate data. This class has several fields and
should be able to read itself from a data reader (by passing an IDataRecord
interface).

class Person
{
public static void FromDataRecord(IDataRecord dataRecord)
{
....
}
}

The problem is the IDataRecord could contain just some of the needed fields,
not all of them. So the Person class has to find out which fields are in the
IDataRecord. Because the data reader could contain lots (e.g. 100) of
persons, the FromDataRecord() method will be called 100 times. And looking
up the existent fields in the DataTable returned by GetSchemaTable() during
each call will probably by pretty slow. I guess I will have to find another
way to do this.

Regards,

Andrei