Exception Handling in the .NET Framework Data Provider for ODBC, OLE DB, and for SQL Server
Developers using the .NET Framework Data Provider for ODBC, the .NET Framework Data Provider for OLE DB, or the NET Framework Data Provider for SQL Server often send feedback to Microsoft, asking for a complete list of the exceptions that a given method can throw.
Until such time as the documentation team updates the reference topics for all members in all types of the (above) mentioned providers, this post can provide some guidance on what exceptions your application may need to catch.
Your code should catch exceptions to prevent the application from crashing and to allow displaying a relevant error message to the user. You can use database transactions to ensure that the data is consistent regardless of what happens in the client application (including a crash). Features like System.Transaction.TransactionScope or the BeginTransaction method (in System.Data.OleDb.OleDbConnection, System.Data.ODBC.ODBCConnection, and System.Data.SqlClient.SqlConnection) ensure consistent data regardless of exceptions raised by a provider. Transactions can fail, so catch failures and retry the transaction.
Each data provider has an exception class that reports provider-specific errors. For example System.Data.Odbc has OdbcException, System.Data.OleDb has OleDbException, and System.Data.SqlClient has SqlException. For the best level of error detail, catch these exceptions and use the members of these exception classes to get details of the error.
In addition to the provider-specific errors reported by the .NET Framework data provider’s exception class, .NET Framework data provider types can raise .NET Framework exceptions such as System.OutOfMemoryException and System.Threading.ThreadAbortException. Recovery from these exceptions may not be possible.
Bad input can cause a .NET Framework data provider type to raise an exception such as System.ArgumentException or System.IndexOutOfRangeException. Calling a method at the wrong time can raise System.InvalidOperationException.
So, in general, write an exception handler that catches the provider specific exception as well as exceptions from the common language runtime. These can be layered as follows:
try
{
// Odbc code here.
}
catch (OdbcException odbcEx)
{
// Handle more specific OdbcException exception here.
}
catch (Exception ex)
{
// Handle generic ones here.
}
Or:
try
{
// Odbc code here.
}
catch (Exception ex)
{
if (ex is OdbcException)
{
// Handle more specific OdbcException exception here.
}
else
{
// Handle generic ones here.
}
}
It is also possible for a .NET Framework data provider method call to fail on a thread pool thread with no user code on the stack. In this case, and when using asynchronous method calls, you must register the System.AppDomain.UnhandledException event to handle those exceptions and avoid application crash.