When writing dynamic database queries in C#, you might encounter a common issue: if your SQL query doesn't select a specific column, accessing it via an ADO.NET DataReader (like OleDbDataReader or SqlDataReader) throws an IndexOutOfRangeException.

By default, the DataReader indexer expects the column to exist. If it is missing, it won't return null; instead, it throws an exception. In this article, we'll explore how to handle missing columns gracefully, ranging from simple extension methods to modern micro-ORMs like Dapper.

Why Does This Happen?

The DataReader["column_name"] indexer is designed to fetch values from the active result set. If the column was omitted from the SELECT statement, the schema doesn't contain that field, resulting in an IndexOutOfRangeException.

Solution 1: Create a Safe Extension Method (Pure ADO.NET)

If you want to stick with pure ADO.NET, the cleanest approach is to write an extension method for IDataRecord (which OleDbDataReader implements). This method checks if the column exists before attempting to read its value.

public static class DataReaderExtensions
{
    public static bool HasColumn(this IDataRecord reader, string columnName)
    {
        for (int i = 0; i < reader.FieldCount; i++)
        { 
            if (reader.GetName(i).Equals(columnName, StringComparison.OrdinalIgnoreCase))
            {
                return true;
            }
        }
        return false;
    }

    public static T? GetValueOrDefault<T>(this IDataRecord reader, string columnName, T? defaultValue = default)
    {
        if (!reader.HasColumn(columnName))
        {
            return defaultValue;
        }

        var value = reader[columnName];
        if (value == DBNull.Value || value == null)
        {
            return defaultValue;
        }

        var targetType = Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T);
        return (T)Convert.ChangeType(value, targetType);
    }
}

How to use it in your code:

Now, you can simplify your mapping logic. If the column doesn't exist in the query, it will safely fall back to null (or your specified default value) without throwing an exception:

while (reader.Read())
{
    Part p = new Part
    {
        id = reader.GetValueOrDefault<string>("part_id"),
        name = reader.GetValueOrDefault<string>("name"),
        deliveryTime = reader.GetValueOrDefault<int?>("delivery_time"),
        price = reader.GetValueOrDefault<decimal?>("price")
    };
    parts.Add(p);
}

Solution 2: The Modern Approach (Use Dapper)

If you're open to adding a lightweight library, Dapper (a micro-ORM developed by Stack Overflow) is the industry-standard solution for this exact scenario. Dapper automatically maps query results to strongly typed C# objects. If a column is missing from the query, Dapper simply leaves the corresponding object property at its default value (e.g., null for nullable types).

First, install the Dapper NuGet package:

dotnet add package Dapper

Then, rewrite your retrieval method in just a few lines of code:

using Dapper;
using System.Data.OleDb;

public List<Part> RetrieveParts(string query)
{
    using (var connection = new OleDbConnection(connectionstring))
    {
        // Dapper handles opening the connection, executing, mapping, and closing!
        return connection.Query<Part>(query).ToList();
    }
}

With Dapper, if your query is SELECT part_id, name FROM Parts, the price and deliveryTime properties of your Part objects will automatically remain null. It completely eliminates boilerplate mapping code and prevents IndexOutOfRangeException entirely.

Conclusion

  • For legacy codebases: Use the HasColumn or GetValueOrDefault extension methods to safely check for column existence.
  • For new or refactored projects: Adopt Dapper. It is faster, cleaner, and handles missing database columns out of the box with zero boilerplate.