Abstraction of SqlExceptions (using Entity Framework)

Question

I have been using Entity Framework 6 and trying to handle some common problems like Primary/Foreign/Unique Key constraints via EF. EF gives SqlException as an InnerException and it seems that - as far as I found upto now - the only way to understand the actual problem is using error codes in that SqlException object?

I would like to abstract these exceptions by catching EF exceptions and throwing my own exceptions by analyzing the error code in the InnerException. I have some considerations at that point.

1. SQL Server codes change by the server version or not? Should I handle different versions of SQL Server in a different way like creating different implementations for 2008, 2012 and etc?

2. Instead of SQL Server it is possible to use other SQL Servers like MySQL and its another reason I am trying to abstract these exceptions.

For example, as in the accepted answer of that question, I would like catch specific errors, but instead of rethrowing I would like to throw my own exception(s). If I do not want to do something special or I do not have any special exception for the error I can create and use a more generic exception in which I can store the original exception into that generic exceptions InnerException field.

I read this blog post and unfortunately the problematic case was my first way to go. I would like to do that without using any third party library as much as possible (of course this is not more important doing it right). I wonder if there is tested and accepted way of doing this, otherwise I am open to any suggestion.

Answer 1

SQL Server codes change by the server version or not?

Exception numbers cannot change once released. Deadlock is and will remain 1205, unique index duplicate key is and will remain 2601, unique constraint violation is and will remain 2627 and so on and so forth. But, you see, in the very examples I choose I show you the danger lurking in relying on these: what decides the difference between 2601 and 2627? Your friendly DBA can decide that it should drop an unique index and instead add an unique constraint (enforce by an index, but that is irrelevant) and all of the sudden your app sees new errors. By doing deep inspection of the exception you add coupling between the application code and the SQL storage, and risk breaking the app when the storage modifies in what would otherwise be a completely transparent change (eg. add an index). Be warned.

I think is feasible to add handling for several well known cases. But you must allow for generic exception you are not aware of ad development time, and handle those.

As for cross-platform, you would have to customize for each platform. The good news is that a 'primary key violation' is the same concept on SQL Server and on MySQL, so you could translate it to a PrimaryKeyViolationException for both providers. Is not trivial, but it is possible.

And finally, a heads up: I've seen folk trying to do similar before, to mix results. The benefits are not exactly overwhelming, and the effort put in is considerable.