SyncLock(Me)

Determine what's wrong with the .NET 1.x code snippet below :

public void Foo(){
try{
IDbConnection conn = ConnectionFactory.CreateConnection();
IDbCommand cmd = conn.CreateCommand();
...
}
catch(System.Data.DbException ex)
{
...
}
}


1. Foo is a reserved C# word
2. The CreateCommand method does not exist
3. DbException does not exist

The answer is 3) : in .NET 1.*, ADO.NET provider-specific exception classes (OleDbException, SqlException, ...) have nothing in common, in that they do not inherit from a common exception class that would be dedicated to all database access errors.
This makes it difficult if not impossible to code truly database-independant data access code : event if you stick to IDbXXX interfaces to access data or use a toolkit such as MS Data Access App Block you'll run into a problem when catching exceptions.

Indeed, imagine that at some point you want to catch "all database exceptions" ?

public void SomeMethod(){
try{
//Manipulate IdbXXX implementors
}
catch(?????){ //I want to catch database-related exceptions only
}
}


Well the only receipe that works is to have the knowledge of the underlying ADO.NET provider type, for instance SqlClient, and catch SqlException. Your code is no longer DB-agnostic.

There are many workarounds to this situation, some of them around the Template Method design pattern (which is basically what the Data Access App Block is all about), some others using Reflection (based on the assumption that OleDbException, SqlException, ... all have the same properties even though then do not inherit from a common base class)

But none of them is as clean as what Microsoft introduced in .NET 2.0 : the System.Data.Common.DbException class !

But I'm not quite sure I'm satisfied yet. OK I'm now able to catch all data access exceptions, but I'm not able to do anything intelligent with them, such as :


//some DAO
void InsertCustomer(Customer c)
{
try{
//the SQL INSERT statement execution
}catch(DbException e){
//what I want : if the DB tells me "DUPLICATE KEY",
//then throw a DuplicateCustomer exception, so that
//the controller up in the call stack will proceed to
//a meaningful error page

//But how the F*** do I determine that it is a duplicate key
//error (other than parsing the e.Message property or downcasting it
//to a SqlException ?)
}
}


So I would expect DbException to have common properties, such as a collection of errors, each one having a SqlState property to detect this primary key violation. After all SQL statuses values are (correct me if I'm wrong) part of the SQL Ansi specs, so it would make sense to expose them in the base class for all provider-specific exception classes.
BTW, isn't it the case with JDBC's SQLException class ?

What does the community think ?