C# Find out what column caused the Sql Exception

Question

Hi. I'm getting an exception from SQL Data Reader (MS SQL as datastore) and I'd like to know which column name causes this Exception to be thrown. But I cannot find it in the InnerException.. nowhere.

((System.InvalidOperationException)ex.InnerException).StackTrace:

System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
System.Data.SqlClient.SqlDataReader.IsDBNull(Int32 i)
...

Where is it hidden please ?

Answer 1

You can't. The data reader does not communicate that in its stack trace. What you can do is wrapping the use of the data reader in another class. In a project I'm working on we used extension methods for this. The class looks like this:

public static class DataRecordExtensions
{
    public static byte GetByte(this IDataRecord record, string name)
    {
        return Get<byte>(record, name);
    }

    public static short GetInt16(this IDataRecord record, string name)
    {
        return Get<short>(record, name);
    }

    public static int GetInt32(this IDataRecord record, string name)
    {
        return Get<int>(record, name);
    }

    private static T Get<T>(IDataRecord record, string name)
    {
        // When the column was not found, an IndexOutOfRangeException will be 
        // thrown. The message will contain the name argument.
        object value = record[name];

        try
        {
            return (T)value;
        }
        catch (InvalidCastException ex)
        {
            throw BuildMoreExpressiveException<T>(record, name, value, ex);
        }
    }

    private static InvalidCastException BuildMoreExpressiveException<T>(
        IDataRecord record, string name, 
        object value, InvalidCastException ex)
    {
        string exceptionMessage = string.Format(CultureInfo.InvariantCulture,
            "Could not cast from {0} to {1}. Column name '{2}' of {3} " + 
            "could not be cast. {4}",
            value == null ? "<null>" : value.GetType().Name, 
            typeof(T).Name, name, record.GetType().FullName, ex.Message);

        return new InvalidCastException(exceptionMessage, ex);
    }
}

You can use it as follows:

using (var reader = SqlHelper.ExecuteReader(...))
{
    while (reader.Read())
    {
        yield return new Order()
        {
            OrderId = reader.GetInt32("orderId"),
            ItemId = reader.GetInt32("itemId")
        };
    }
}

btw. Such an class also allows you to get Nullable<T> objects back and get rid of those manual DbNull conversions you else need to do.