Detecting if an IDataReader contains a certain field before iteration

Question

So I'm using an IDataReader to hydrate some business objects, but I don't know at runtime exactly what fields will be in the reader. Any fields that aren't in the reader would be left null on the resulting object. How do you test if a reader contains a specific field without just wrapping it in a try/catch?

Answer 1

This should do the trick:

    Public Shared Function ReaderContainsColumn(ByVal reader As IDataReader, ByVal name As String) As Boolean
        For i As Integer = 0 To reader.FieldCount - 1
            If reader.GetName(i).Equals(name, StringComparison.CurrentCultureIgnoreCase) Then Return True
        Next
        Return False
    End Function

or (in C#)

public static bool ReaderContainsColumn(IDataReader reader, string name)
{
    for (int i = 0; i < reader.FieldCount; i++) {
        if (reader.GetName(i).Equals(name, StringComparison.CurrentCultureIgnoreCase)) return true; 
    }
    return false;
}

:o)

Answer 2

You can also use IDataReader.GetSchemaTable to get a list of all the columns in the reader.

http://support.microsoft.com/kb/310107

Answer 3

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

Answer 4

The best solution I've used is doing it like this:

DataTable dataTable = new DataTable();
dataTable.Load(reader);
foreach (var item in dataTable.Rows) 
{
    bool columnExists = item.Table.Columns.Contains("ColumnName");
}

Trying to access it through reader["ColumnName"] and checking for null or DBNull will throw an exception.

Answer 5

While I disagree with this approach (I think when accessing data, you should know the shape before hand), I understand that there are exceptions.

You could always load up a datatable with the reader and then iterate through it. You can then check to see if the column exists. This will be less performant, but you won't need try/catch blocks (so maybe it is more performant for your needs).

Answer 6

You can't just test reader["field"] for null or DBNull because a IndexOutOfRangeException is thrown if the column isn't in the reader.

The code I use in my mapping layer for creating domain objects and the stored procedures that use the mapping layer might have different column names is below; you could modify it to not throw an exception if the column isn't found and return default(t) or null.

I understand this isn't the most elegant or optimal solution (and really, if you can avoid it then you should), however, legacy stored procedures or Sql queries might warrant a work-around.

    /// <summary>
    /// Grabs the value from a specific datareader for a list of column names.
    /// </summary>
    /// <typeparam name="T">Type of the value.</typeparam>
    /// <param name="reader">Reader to grab data off of.</param>
    /// <param name="columnNames">Column names that should be interrogated.</param>
    /// <returns>Value from the first correct column name or an exception if none of the columns exist.</returns>
    public static T GetColumnValue<T>(IDataReader reader, params string[] columnNames)
    {
        bool foundValue = false;
        T value = default(T);
        IndexOutOfRangeException lastException = null;

        foreach (string columnName in columnNames)
        {
            try
            {
                int ordinal = reader.GetOrdinal(columnName);
                value = (T)reader.GetValue(ordinal);
                foundValue = true;
            }
            catch (IndexOutOfRangeException ex)
            {
                lastException = ex;
            }
        }

        if (!foundValue)
        {
            string message = string.Format("Column(s) {0} could not be not found.",
                string.Join(", ", columnNames));

            throw new IndexOutOfRangeException(message, lastException);
        }

        return value;
    }