asp.net/sql server try catch what is going on?

Question

I identified a bug in my code and I'm baffled as to how it could have occurred in the first place.

My question is, I found a skip in the database ID fields (it is an incremented identity field), indicating some records weren't inserted - which means my SQL sprocs (probably) threw an error. Thinking backwards from there, that means that my business object should have caught that error and thrown it, exiting immediately and returning back to the page's codebehind (instead it continued right on to the second stored procedure). I don't understand why or how this is possible. What's going on in respect to the code execution skipping my try catches?

Page code behind:

 protected void submitbutton_click(object sender, EventArgs e){
      try{
        mybusinessobject.savetodatabase()
      } catch( Exception ex) {
        Response.Redirect("Error.aspx");
      }
 }

business object code:

 public static void savetodatabase(){
        int ID1=-1;
        int ID2=-1;
        //store the billing contact
        SqlCommand cmd1 = new SqlCommand("SaveInfo1", con);
        cmd1.CommandType = CommandType.StoredProcedure;
        //...
        cmd1.Parameters.Add("@Ret", SqlDbType.Int);
        cmd1.Parameters["@Ret"].Direction = ParameterDirection.ReturnValue;

        try
        {
            con.Open();
            cmd1 .ExecuteNonQuery();
            ID1 = Convert.ToInt32(cmd1.Parameters["@Ret"].Value);
        }
        catch (Exception ex) { throw ex; }
        finally { con.Close(); }

        if (ID1  > 0)
        {
            SqlCommand cmd = new SqlCommand("SaveInfo2", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //...
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                ID2= Convert.ToInt32(cmd.Parameters["@Ret"].Value);
            }
            catch (Exception ex) { throw ex; }
            finally { con.Close(); }
        }
 }

SQL Code:

PROCEDURE [dbo].[SaveInfo1]
( 
-- ... parameters ...
)
AS
    INSERT INTO Table1 ( ... ) Values ( ... )
RETURN SCOPE_IDENTITY

PROCEDURE [dbo].[SaveInfo2]
( 
-- ... parameters ...
)
AS
    DECLARE @SpecialID INT
    INSERT INTO Table2 ( ... ) Values ( ... )
    SET @SpecialID = SCOPE_IDENTITY()
    INSERT INTO Table3 ( [ID],  ... ) Values ( @SpecialID, ... )
RETURN SCOPE_IDENTITY()

Answer 1

Isn't the more likely scenario that someone just deleted some records from the table?

Answer 2

If records are deleted, their unique identifiers will not be recycled, even when new records are later inserted. You can use RESEED in SQL to reset the identity seed to 0 if you desire, but I suggest against that unless you wipe the table. Otherwise you could end up with primary key violations.

Also, make sure your column's identity seed is set to increment 1 at a time.

Answer 3

Your code doesn't matter, just go to Web.config and play with appropriate node:

<customErrors mode="On|Off" />

P.S. Use the using clause to auto-close a connection, instead of manual in the finally clause

Answer 4

you can test the catch. just change the procedure:

PROCEDURE [dbo].[SaveInfo1]
( 
-- ... parameters ...
)
AS
    INSERT INTO Table1 ( ... ) Values ( ..., some_out_of_range_value_here, ....)
RETURN SCOPE_IDENTITY()

to have some hard coded out of range value (so the insert fails), and then run your application...

Answer 5

Your exception handling is horrible. Never do this:

catch (Exception ex) { throw ex; }

All that accomplishes is to screw up the stack trace in the exception. It makes it look like the exception originated at the point of the throw.

Never do this:

  try{
    mybusinessobject.savetodatabase()
  } catch( Exception ex) {
    Response.Redirect("Error.aspx");
  }

You don't know what exception happened. You have no idea whether or not it's safe to redirect, and on top of it all, you lose all information about what the exception was!

You should also get into the habit of implementing using blocks:

public static void savetodatabase()
{
    using (SqlConnection con = new SqlConnection("Connectionstring"))
    {
        int ID1;
        //store the billing contact
        using (SqlCommand cmd1 = new SqlCommand("SaveInfo1", con))
        {
            cmd1.CommandType = CommandType.StoredProcedure;
            //...
            cmd1.Parameters.Add("@Ret", SqlDbType.Int);
            cmd1.Parameters["@Ret"].Direction = ParameterDirection.ReturnValue;

            con.Open();
            cmd1.ExecuteNonQuery();
            ID1 = Convert.ToInt32(cmd1.Parameters["@Ret"].Value);
        }

        if (ID1 <= 0)
        {
            return;
        }

        int ID2 = -1;
        using (SqlCommand cmd = new SqlCommand("SaveInfo2", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            //...
            con.Open();
            cmd.ExecuteNonQuery();
            ID2 = Convert.ToInt32(cmd.Parameters["@Ret"].Value);
        }
    }
}

A using block will ensure that the resource will have its Dispose method called, whether or not an exception is thrown.