Checking to see if a column exists in a data reade

Question

This question already has an answer here:

• Check for column name in a SqlDataReader object 22 answers

Is there a way to see if a field exists in an IDataReader-based object w/o just checking for an IndexOutOfRangeException?

In essence, I have a method that takes an IDataReader-based object and creates a strongly-typed list of the records. In 1 instance, one data reader has a field that others do not. I don't really want to rewrite all of the queries that feed this method to include some form of this field if I don't have to. The only way I have been able to figure out how to do it so far is to throw the 1 unique field into a try/catch block as shown below.

try
{
    tmp.OptionalField = reader["optionalfield"].ToString();
}
catch (IndexOutOfRangeException ex)
{
    //do nothing
}

Is there a cleaner way short of adding the "optional field" to the other queries or copying the loading method so 1 version uses the optional field and the other doesn't?

I'm in the 2.0 framework also.

Answer 1

Appears I stand corrected. I know your actual column names are in there, but I was going down the wrong path. This reference helped clear things up a bit, but I'm still not sure if there's an elegant way of doing it. Adapted from the above link, you could get a list of all of your columns with the following:

List<string> myCols = new List<string>();
DataTable schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
    myCols.Add(row[schema.Columns["ColumnName"]]);
}

Unfortunately it appears you can only access schema.Rows by index, so I'm not sure you can get around looping through the rows first before checking by name. In that case, your original solution seems far more elegant!

Note: my original answer suggested checking for presence of a column simply by: reader.GetSchemaTable().Columns["optionalfield"].

Answer 2

This should work, try this:

private static bool ColumnExists(SqlDataReader reader, string columnName)
        {
            using (var schemaTable = reader.GetSchemaTable())
            {
                if (schemaTable != null)
                    schemaTable.DefaultView.RowFilter = String.Format("ColumnName= '{0}'", columnName);

                return schemaTable != null && (schemaTable.DefaultView.Count > 0);
            }
        }

Answer 3

Enumerable.Range(0, reader.FieldCount).Any(i => reader.GetName(i) == "ColumnName")

Answer 4

I ended up finding a solution using the reader.GetName(int) method. I created the below method to encompass the logic.

public bool ColumnExists(IDataReader reader, string columnName)
{
    for (int i = 0; i < reader.FieldCount; i++)
    {
         if (reader.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
        {
            return true;
        }
    }

    return false;
}

Answer 5

The following will give you a list of the column name strings given a data reader. (Remember the results are based on the last read so it may not be the same depending on what the reader read).

var cols = reader.GetSchemaTable()
                 .Rows
                 .OfType<DataRow>()
                 .Select(row => row["ColumnName"]);

Or to check if a column exists:

public bool ColumnExists(IDataReader reader, string columnName)
{

  return reader.GetSchemaTable()
               .Rows
               .OfType<DataRow>()
               .Any(row => row["ColumnName"].ToString() == columnName);
}

Answer 6

Don't need so much complication, just this:

bool bFieldExists = datareader.GetSchemaTable().Columns.Contains(strFieldName);