C # Find out which column caused the Sql exception
I am getting an exception from SQL Data Reader (MS SQL as data store) and I would like to know what column name is causing this exception. But I can't find it in InnerException .. anywhere.
((System.InvalidOperationException)ex.InnerException).StackTrace:
System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
System.Data.SqlClient.SqlDataReader.IsDBNull(Int32 i)
...
Where is he hidden please?
+2
a source to share
1 answer
You can not. The data reader does not report this in its stack trace. What you can do is wrap your data reader usage in another class. On a project I'm working on, we used extension methods to do 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 like this:
using (var reader = SqlHelper.ExecuteReader(...))
{
while (reader.Read())
{
yield return new Order()
{
OrderId = reader.GetInt32("orderId"),
ItemId = reader.GetInt32("itemId")
};
}
}
by the way. Such a class also allows you to put objects Nullable<T>
back and get rid of any manual transformations DbNull
that you still need to do.
+2
a source to share