determine if a field is returned from a datareader

Question

I'm dynamically picking a table to select from and there's one field that doesn't exist on all of the tables. Inside my reader, how can I check to see if that field exists in the collection?

I'm using this, but it only determines if it's null...not whether it exists or not:

if (myReader.GetValue(myReader.GetOrdinal("PrePay")) != DBNull.Value)
    myModel.PrePay = myReader.GetBoolean(myReader.GetOrdinal("PrePay"));

possible duplicate of [Check for column name in a SqlDataReader object](http://stackoverflow.com/questions/373230/check-for-column-name-in-a-sqldatareader-object). Appears to be the big answer to this question — Preet Sangha, Sep 01 '11 at 21:04

score 0 · Accepted Answer · answered Sep 01 '11 at 21:04

0

Have a look at SqlDataReader.GetSchemaTable (assuming myReader is a SqlDataReader).

answered Sep 01 '11 at 21:04

Philipp Schmid

5,778
5
44
66

score 0 · Answer 2 · answered Sep 01 '11 at 21:05

0

while(reader.Read())
{
    if(reader.GetOrdinal("FieldName")>=0) //field exists
    {
 ....
    }
}

answered Sep 01 '11 at 21:05

Icarus

63,293
14
100
115

And if the fieldname doesn't exist then you'll get an `IndexOutOfRangeException`, which is presumably what the OP is trying to avoid. – LukeH Sep 01 '11 at 21:09
You are right, @LukeH I thought it would return a -1 but reading this: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getordinal.aspx I realized that it actually throws an Exception. Christoper Johnson: ignore my answer, I don't know what I'm talking about. – Icarus Sep 01 '11 at 21:12

LukeH · Answer 3 · 2011-09-01T21:22:53.057

for (int i = 0; i < myReader.FieldCount; i++)
{
    string name = myReader.GetName(i);
    if (string.Equals(name , "PrePay", StringComparison.OrdinalIgnoreCase))
    {
        object value = myReader.GetValue(i);
        if (!Convert.IsDBNull(value))
        {
            myModel.PrePay = (bool)value;
        }
        break;
    }
}

If your reader returns more than one row then you should perform the lookup just once at the beginning and then cache the ordinal to use when iterating through the resultset.

score 0 · Answer 4 · answered Sep 02 '11 at 04:39

0

While(myReader.Read())
{ 
   MessageBox.Show(myReader.Item("PrePay").toString());
}

Basically, if the value of "PrePay" is null, it's doesn't exist otherwise it's exist.

answered Sep 02 '11 at 04:39

Sam Casil

938
1
11
16

determine if a field is returned from a datareader

4 Answers4