How to catch DB errors and translate them into meaningful information for the business layer?

Question

Usually I have to insert some data in a DB and it can't be inserted because the table has constraints preventing me from doing that. With the app I'm developing, some business rules (like "there are not two persons with the same id type and number" or "the XXXX product is already registered") are enforced with UNIQUE or composite keys and other mechanisms. Although I know that DBMS throws an error message (like ORA-6346 or ) I do not know how to catch those errors in .net 4.0 and translate them to an error that can be meaningful for the business layer.

As an example: I've seen an insertion mechanism that asks the DB if the register already exists and then it proceeds to insert data if it isn't the case. I want to do this only using a query and catching the database constraint violation error because the first way seems to me as very inefficient (DB can alert you about duplication with an error).

How can I implement something like that?

Note: I think that it is possible to catch the exception from the database and use its ORA-xxxx code to try to figure out what has happened. I do not remember with precision if the error message shows which constraint (the name of...) has been broken, but business layer code can contain constants with the constraint names and, from them, know what has happened.

Quiteris

this sounds very much like wrapping a method call with try/catch instead of validating arguments before call – Brusque

if you don't want to first check whether register already exists, you can first try to create it, and then if exception is thrown from db you can check whether RegisterExists() returns true, to be sure that was the source of problem, and not that db wasn't working at all. If already exists case is pretty rare you won't get that performance hit so often. – Student

Answer 1

What you should do here depends really on the architecture of your system, and your attitude towards the placement of business logic.

Many systems architects prefer to use a database as a dumb data store, and implement the type of error handling and integrity checking you're talking about in the middle/application layer. This is a perfectly valid approach, and is particularly suited to systems requiring regular smaller releases, where the business logic is subject to regular change (its much easier to redistribute an executable midweek than to co-ordinate a database release), and where the data model is fairly simple.

The other approach is to put some well-defined semi-permanent business logic into the database layer. This is particularly powerful when the data model is more complex, and you have a good DBA! ;)

My personal opinion is, an enterprise database should be responsible for its own integrity, and so I prefer to have logic in the database layer to ensure this - removing any vulnerability to bugs being introduced in non-database code releases. So in your specific example, I would definitely catch the error and report it meaningfully to your application layer.

Oracle supports catching various types of error using names exceptions, allowing you to raise these exceptions to your applications in a meaningful way. For example:

PROCEDURE test() AS
  b VARCHAR2;
BEGIN

  -- if the following row exists, then DUP_VAL_ON_INDEX will be thrown 
  -- (assuming there is a primary key constraint)        

  INSERT INTO table(a,b,c)
  VALUES(1,2,3);  

  -- if there is no matching record, NO_DATA_FOUND will be thrown

  SELECT a
  INTO b
  FROM TABLE
  WHERE c = 'blah';  

EXCEPTION   -- both types of exception can be caught and embellished 
  WHEN DUP_VAL_ON_INDEX THEN
    raise_application_error(-20570, 'Attempted to insert a duplicate value', TRUE);
  WHEN NO_DATA_FOUND THEN
    raise_application_error(-20571, 'No matching row in table for value:' || 'blah', TRUE);
  WHEN OTHERS THEN
  rollback
END test;

You can find more information here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm

Hope this helps..

Duque

Your idea is more or less as @mootinator proposes?... I do not know to whom give the check...! both say sound solutions... :S – Quiteris

When in doubt, go with popular opinion ;) – Explicable

Answer 2

There's a couple of approaches, here's broadly what I would do:

1. Let the error bubble up from the DB call back into your managed code.
2. Use a component to examine the error message provided by SQL, and identify the corresponding "user/business layer friendly" message.

I agree with Mellamokb that error handling can be done within the stored proc but that doesn't exactly fit with your scenario as you specifically want to provide something that the business layer understands - which by definition the data layer should never know.

For #2, the MS Enterprise Libraries have an error handling block which (I think) allows you to that sort of thing through config; or if not it might get you close.

Shrapnel

Answer 3

I had been thinking about the same thing recently. I made an extension method which takes the Message from a SqlException and translates it to something more useful to an end-user using regular expressions to extract the useful information from the error message and String.Format to put that information into the new message.

I used a second dictionary to look up constraint names found by the regular expression and translate those to an English description of the constraint violated.

This SQL error message:

Violation of UNIQUE KEY constraint 'uniq_ticket'. Cannot insert duplicate key in object 'dbo.TicketHeader'. The statement has been terminated.

Returns this result:

Save to table dbo.TicketHeader failed: Ticket number must be unique.

I would imagine it could work very similarly for exceptions sent by Oracle.

    public static class SqlExceptionExtension
    {
        private static readonly Dictionary<string, string> Messages;
        private static readonly Dictionary<string, string> Constraints;
        static SqlExceptionExtension()
        {
            Messages = new Dictionary<string, string> {{@"Violation of UNIQUE KEY constraint '(?<Constraint>.*)'. Cannot insert duplicate key in object '(.*)'. The statement has been terminated.", "Save to table {2} failed: {0}"}};
            Constraints = new Dictionary<string, string> { { "uniq_ticket", "Ticket number must be unique." } };
        }
        public static string BusinessLayerMessage(this Exception e)
        {
            foreach(var reg in Messages.Keys)
            {
                var match = Regex.Match(e.Message, reg);
                if (match.Success)
                {
                    string friendlyConstraint = "";
                    if (match.Groups["Constraint"] != null)
                    {
                        friendlyConstraint = Constraints[match.Groups["Constraint"].Value] ??
                                             match.Groups["Constraint"].Value;
                    }
                    var groups = match.Groups.Cast<Group>().Select(x => x.Value);
                    var strings = new [] {friendlyConstraint};
                    return String.Format(Messages[reg], strings.Concat(groups).ToArray());
                }
            }
            return "Unexpected Database error.";
        }
    }
}

I'd imagine you could do this on the individual SqlError or OracleErrors included in your Exception for more reliable results, this was just a proof of concept.

Explicable

Answer 4

A method I know of and have used is to perform the same validations yourself and return useful error codes to your application. So for example, if you have a stored procedure that inserts a record to the database, it should also check if the constraints will all be satisfied, and if not, return an appropriate error code:

(pseudo-sql)
function create-user
    @username, @password, @name, @email
as

if @username already exists return 1  --duplicate username
if @email already exists return 2   --duplicate email

insert user values (@username, @password, @name, @email)
return 0   -- success

Famish

if we are talking about oracle we can first try insert and only if it fails perform additional queries to figure out source of actual errors, it will be more performant than always executing a couple of selects before insert – Student

Answer 5

If you're looking for inspiration, Have a look at how NHibernate handles this with its ISQLExceptionConverter interface. You can see a sample implementation here.

Plume