Handling exception while trying to read column missing in DataReader

when we need to share the same mapping methods for different SQL queries which may have a little differences in their selection list (Which is a very common scenario) and usually we try to do either of the followings:

Include all columns in the selection list of all stored procedures even if not necessary

Or,

Use a try…catch block as follows:

try
{
          string FirstName = DataReaderHelper.GetString(reader["FirstName"]);
}
catch(Exception ex)
{
         //The reader doesn’t have this column in selection
}

Or,

Create different versions of the Mapping methods and create different entities for different queries

Neither of those are not good approaches.

Here is a solution


Add a following private method which does the trick to check the existence of the column value in the DataReader

private static object GetDataForColumn(string columnName, IDataReader reader)
{
     reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
     return reader.GetSchemaTable().DefaultView.Count > 0? reader[columnName]:null; 
}

Basically, it filters the Schema information using the ColumnName as the filter and reads count to determine whether the field exists in the DaraReader.

Now, you can use the method as follows:

public static string GetString(IDataReader reader, string columnName)
{
     object data = GetDataForColumn(columnName, reader);
     return ((data == null) || (data == DBNull.Value)) ? string.Empty : data.ToString();
}

So, you can use the method as follows now:

string FirstName = DataReaderHelper.GetString(reader,"FirstName");

This would handle the situation gracefully :)