what to check for when a datareader might not have a particular column?

Question

I am doing a null check but I am getting a index of out range exception when doing:

if(myReader["column1"] != null)
{

}

Under certain conditions that column will not be present, why isn't a null check working?

Answer 1

Why is your query changing the columns that it is returning? Referencing the column using the name of the column throws the exception because the column index that corresponds to the name of the column is not found. If I were you I'd capture the exception and handle the error accordingly.

Answer 2

Check to see if the column exists first

for(int i = 0; i < myReader.FieldCount; i++) 
    if (myReader.GetName(i) == "column1")
        return true;

Answer 3

you might try GetSchemaTable()

Answer 4

You want something like:

if (myReader.GetSchemaTable().Columns.Contains("ColumnName"))
{

}

Answer 5

If the same query returns different columns depending on stuff, doesn't look like a very good design to me. Anyway, you can use myReader.GetName(i) on the column that might be different to check whether you got the right column or not.

Answer 6

Well, the DataReader will expose a GetSchemaTable() which will give you the schema/columns within the DataReader.

Another alternative is to just attempt to grab the value from a column and catch the resulting exception, should that column not exist:

try
{
     object value = dataReader["ColumnName"];
     // Do something with the value...
}
catch(IndexOutOfRangeException e)
{
     // Column not there!
}

However, I wouldn't advise this as throwing/catching exception is expensive business and should be truly reserved for something exceptional happening within your code.

I think the best thing is to use the GetSchemaTable() function but "roll-your-own" DoesColumnExist style function, something like:

public bool DoesColumnExist(IDataReader reader, string columnName)
{
     reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '"  + columnName +  "'";  
     return (reader.GetSchemaTable().DefaultView.Count > 0);
}