What is the proper way to check errors from a cascading save?

Question

On creation of a user, a row must be inserted into both the User and Email table. It can fail in either of them(unique constraints). How can I find out which is the reason for failure? My thoughts have been using a lock and querying the database prior to the inserts or parsing the SqlException that comes back(which I'd prefer not to do).

Edit: I should have mentioned this will be running on several machines simultaneously, and I would like it to support different databases.

Edit 2: My solution ended up being using a lock around checking for duplicates. Stored procedures was an option, but I didn't want to place business logic into the database. I commented for others that I was aware of the race conditions in the web farm, but the rarity of the circumstances didn't warrant further work.

Answer 1

You should be catching that kind of case in your business logic somewhere rather than relying solely on the database to give you the error you're looking for.

Answer 2

Exception handling should be used to capture non-prime scenarios such as the database is down or a command overran the timeout. If you have constraints around User being unique and the Email being unique you should really test for them before you do your submission of data. Relying on check/index constraints as a way to handle these scenarios is going to create confusion in the longrun. Besides, a key best practice in error handling is to never let the end user know the particulars of why an error had occurred.

Answer 3

Use a stored procedure, and check for known conditions that will make the transaction fail inside the transaction, e.g.:

BEGIN TRANSACTION
IF EXISTS (SELECT UserID FROM User WHERE UserID = @UserID)
   BEGIN
      ROLLBACK
      SELECT 'User already exists in the User table.'
      RETURN 1
   END

IF EXISTS (SELECT UserID FROM Email WHERE UserID = @UserID)
   BEGIN
      ROLLBACK
      SELECT 'User already exists in the Email table.'
      RETURN 2
   END

INSERT INTO User ...
INSERT INTO Email ...
COMMIT
RETURN 0

This actually is using two mechanisms for returning an error (return code and result set); it usually only makes sense to use one.

Answer 4

I would not rely on the table constraints for data validation. Validate the data with a query before the insert. Exceptions are expensive objects to create. Also, I prefer having constraints in place to prevent invalid data but not to validate. I think of a constraint as a safety-belt for the table. It should only be invoked if something wrong happens. The business logic should validate all data before inserting. Don't rely on stored procedures if you might be targeting databases that do not support them.

Here's the general way I would handle it.

        private void Form1_Load(object sender, EventArgs e)
    {
        OleDbConnection conn = null;
        OleDbTransaction t = null;
        try
        {
            conn = new OleDbConnection("a database");

            conn.Open();

            //query both tables to prevent insert fail, 
            //obviously UserID should be parameter.
            var cmd = new OleDbCommand("select count(*) from User where UserID = 1", conn);
            var count = (double)cmd.ExecuteScalar();

            cmd.CommandText = "select count(*) from Email where UserID = 1";
            count += (double)cmd.ExecuteScalar();

            if (count != 0)
            {
                MessageBox.Show("Record exists");
                return;
            }

            t = conn.BeginTransaction();

            //insert logic goes here


            t.Commit();
        }
        catch (Exception x)
        {
            //we still need catch block, someone else may have updated the 
            //data after you checked but before you insert or db open may 
            //fail

            MessageBox.Show(x.Message);
            if (t != null)
                t.Rollback();
        }
        finally
        {                
            if (conn != null)
                conn.Close();
        }
    }