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()

I don't use asp.net, so I could be missing the boat here. It wouldn't cause the issue you are asking about, but is there a transaction around the two stored procedure calls? — KM.
– KM., Commented Jul 29, 2009 at 18:25
No, which was part of the problem. But that still doesn't explain why it skipped my try catches. — rlb.usa
– rlb.usa, Commented Jul 29, 2009 at 18:59
See my answer. I'm sure I'm right. I've been beating my brains out trying to solve the same problem for few weeks before found the solution — abatishchev
– abatishchev, Commented Jul 29, 2009 at 19:55

John Saunders · Accepted Answer · 2009-07-30 16:36:00Z

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.

MyItchyChin · Accepted Answer · 2009-07-29 18:12:37Z

0

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

answered Jul 29, 2009 at 18:12

MyItchyChin

14.1k1 gold badge27 silver badges45 bronze badges

1 Comment

MyItchyChin Over a year ago

Does it matter that in your first proc you're returning RETURN SCOPE_IDENTITY and not RETURN SCOPE_IDENTITY()?

tsilb · Accepted Answer · 2009-07-29 18:16:29Z

0

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.

answered Jul 29, 2009 at 18:16

tsilb

8,03514 gold badges74 silver badges100 bronze badges

Comments

abatishchev · Accepted Answer · 2009-07-29 18:19:05Z

0

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

answered Jul 29, 2009 at 18:19

abatishchev

101k88 gold badges304 silver badges443 bronze badges

2 Comments

Pavel Minaev Over a year ago

This has absolutely no effect on whether exceptions are caught by catch blocks or not (they're always caught).

abatishchev Over a year ago

no, they don't! Make a test for each possible value for customErrors and you will see. I got the same issue with VS2008TS/SQL2008/Win7RC. blind me if not!

KM. · Accepted Answer · 2009-07-29 18:21:25Z

0

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...

answered Jul 29, 2009 at 18:21

KM.

104k34 gold badges183 silver badges214 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related