Controlling SQL Transactions with SQL Server 2000 and ADO.NET

Hi

I have to work on a system that has a SQL Server 2000 database. I am using C# and ADO.NET to access the database. I now have an update to do with 4 tables. Now with SQL Server 2008 I can use a try.. catch block and rollback any failed updates. But given the technology I'm using can someone suggest the most sensible way to begin/rollback/commit a transaction with SQL Server 2000 ?

Many thanks..
jonatecAsked:
Who is Participating?
 
chandrasekar1Connect With a Mentor Commented:
we can catch the error through @@ERROR, while exceuting queries, if there was any error, @@ERROR will set not equal to “0”
below I given 2 approaches using with GOTO and without GOTO.  

Approach 1:

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    IF (@@ERROR <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    IF (@@ERROR <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@@ERROR <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Open in new window


In above approach, we need to write “GOTO” statement after each DML operation, if any error occurs, then it will Rollback the transaction.

Approach 2:

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

IF (@@ERROR = 0) 
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
IF (@@ERROR <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Open in new window

0
 
jonatecAuthor Commented:
Thanks for that.

Question I have is; will SQL Server 2000 control an error caused when a table or column has been accidentally renamed or a column deleted, or will it just crash out ?
0
 
jogosCommented:
It will give a severe error, not controlable just stops , rollback and gives error like 'unkown ...'  
From SQL2005 you have more control.  But 'accidentally' renamed or deleted column is something
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
chandrasekar1Commented:
it will crash, if you want to avoid this situation, before doing the DML operation, you can check in the “information_schema.columns” view and then you can proceed it.
0
 
jogosCommented:
The sql will crash, but the error returned is perfectly capturable in your C#-code to let it there have an elegant exit.
0
 
jonatecAuthor Commented:
Is it better to escalate the problem back to ADO.NET and create a rollback there ?
0
 
hspoulsenConnect With a Mentor Commented:
You should take a look at www.sommarskog.se, in particular http://www.sommarskog.se/error-handling-II.html
that describes what to do on SQL Server 2000.

The solution by chandrasekar1 is fine, but you will not be able to see the @@error number, because "After each statement, SQL Server sets @@error to 0 if the statement was successful" (quote from link above)

Best regards,
Henrik Staun Poulsen
Stovi Software
0
 
jonatecAuthor Commented:
Thanks. Very useful.
0
 
hspoulsenCommented:
you should not use Approach 2, as

SQL Server sets @@error to 0 if the statement was successful

0
 
jonatecAuthor Commented:
Agreed. I'll use approach 1.
0
All Courses

From novice to tech pro — start learning today.