To DbException or to SqlException / OdbcException / OleDbexception / OracleException
ADO.NET 2.0 offers some great new features in the Provider model which I've talked about previously, what someone asked me though sparked some interest.
stated that unless the providers allowed for generic Exception handling they were next to useless: -
"Every provider has its own exception for reporting errors, and they're not derived from a generic ADO.NET exception.This thus makes it hard to catch db specific exceptions in generic code and handle it. (i.e.: you then have to go back to db specific code, in other words: back to square 1)"
Looking at the callee graph of System.Data.Common.DbException in ADO.NET 2.0 I can see that it's referenced by the following other classes: -
- OdbcException : DbException
- OleDbException : DbException
- OracleException : DbException
- SqlException : DbException
This has changed a bit from ADO.NET 1.1 where the above classes inherited directly from SystemException: -
public sealed class SqlException : SystemException
A bit like this: -
Now in ADO.NET 2.0 they inherit from DbException: -
public sealed class SqlException : DbException
DbException inherits from ExternalException: -
public abstract class DbException : ExternalException
Which inherits from SystemException (the class ADO.NET 1.1 uses): -
public class ExternalException : SystemException
And that inherits from Exception: -
public class SystemException : Exception
So what I've done is knock together a quick sample which looks at the DbException class when it is thrown during the normal course of a Sql lookup: -
[TestMethod]
public void CatchAGenericExceptionInDetail()
{
System.Data.Common.DbConnection Conn = CodeSamples.GenericFactoryHelper.Connection;
Conn.ConnectionString = CodeSamples.GenericFactoryHelper.ConnectionString;
System.Data.Common.DbCommand Comm = Conn.CreateCommand();
Comm.CommandText = ("SELECT * FROM NORTHWIND.dbo.TableDoesntExist");
Comm.CommandType = System.Data.CommandType.Text;
Conn.Open();
System.Data.DataTable Dt = new System.Data.DataTable();
try
{
Dt.Load(Comm.ExecuteReader());
}
catch (System.Data.Common.DbException Ex)
{
Console.WriteLine(Ex.GetType().ToString());
Console.WriteLine(Ex.Message);
Console.WriteLine(Ex.StackTrace);
}
catch (System.Exception Ex)
{
Assert.Fail("An Exception was thrown with the message '" + Ex.Message + "'");
}
Conn.Close();
}
Now, the "generic" DbException exposes all the information a provider specific exception would (in fact it is a provider specific exception class under the hood) meaning you can handle each provider differently but from a central place in your code, one single catch(DbException Ex): -
System.Data.SqlClient.SqlException
Invalid object name 'NORTHWIND.dbo.TableDoesntExist'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at CodeSampleTests.Exceptions.CatchAGenericExceptionInDetail()
Is this good enough for a generic factory?