Transaction and Exception Handling in ASP.NET

With SQL-Transaction class and Exceptions Handling provided by .NET Framework, we can deal with database operations and catch system exceptions in a very reliable way. This little article will explain the basic concept and usage of Transactions and Exceptions Handling.

What is Transaction?
A transaction is a sequence of operations performed as a single logical unit of work, it can consist of a series of SQL statements, SELECT, INSERT, UPDATE, DELETE If no errors occur after this unit is processed, all changes made to the database will become permanent. But once errors occur, no changes and modifications will be made to database, nothing is gonna happen.

To define a Transaction, the command “Begin tran” is used, any statement after this command will be considered part of transactions. The command “Commit” is used to complete the transaction and make the changes permanent. And the command “Rollback” is used to cancel the transation and rollback the changes.

The following is an example:
[SQL SERVER7.0 or SQL SERVER2000]
BEGIN TRAN
INSERT INTO PRODUCT(PRODUCTID, PRODUCTNAME) VALUES("0001", "KEYBOARD")
UPDATE PRODUCT SET PRICE=12 WHERE PRODUCTID= "0002"
IF (@ERROR>0)
ROLLBACK
ELSE
COMMIT

What is Exceptions Handling?
One of the responsibilities of programmer is to develop a mechanism to deal with error messages and provide useful, clear, and meaningful messages for the users. Exceptions Handling is just the one which can provide this service. Once transactions fail, the server will throw a system exception which provides database error messages for troubleshooting purpose, we can use Exception Handling to catch this exception and do the rest of work. The usage of Exception Handling is:

[c#]
try
{
��//database operations go here
}
catch (Exception e)
{
�.//if Exceptions occur, statements in this block will be executed.
}
finally
{
�.//No matter if Exceptions occur or not, statements in this block will be executed
}

How to implement Transactions?
1.Write Transactions statements in a Store Procedure, and use flow control to determine if errors occur and return relative value, and then the web applications display the correct and friendly error message based on the returned value. Here is the example:

[Store Procedure]
CREATE PROCEDURE PRODUCT_SAVE( AS
DECLARE
(@USERID CHAR(5),
   @LOCATION VARCHAR(50),
 @RETURNS INT OUTPUT
)
BEGIN TRAN
UPDATE ADDRESS SET LOCATION=@LOCATION WHERE USERID=@USERID
IF (@@ERROR>0)
BEGIN
@RETURNS=-1  /* Fail to Update */
ROLLBACK
END
ELSE
@RETURNS=0  /* Succeed to update */
COMMIT

RETURN @RETURNS

[Web Application in C#]
int values;
DBClass dbc=new DBClass(); // New a database class
values=dbc.updatedb("0001", "23 Rain Street"); //And call its function member to update record
if (values==0)
Lable_message.text= "Update successfully";
else
Lable_message.text= "Sorry, can not update this record, please contact your DBA."

The above example is very suitable for those programmers who are very familiar with database programming, such as DBA, they prefer to do exception handling in store procedure. If you are not so familiar with database programming, you can take the following choice:2.In .NET Framework, you can define a transaction by using SqlTransaction class. After that, you can use its commit or rollback function to control transaction. And, of course, you can use Exception Handling provided by .NET Framework to catch system exceptions. Ok, Here is the example:

[Web Applicaion in C#]
SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();

  SqlTransaction myTrans = myConnection.BeginTransaction();  //New a transaction
SqlCommand myCommand = new SqlCommand();
myCommand.Transaction = myTrans;

try
{
  myCommand.CommandText = "Update Address set location='23 rain street' where userid='0001'";
  myCommand.ExecuteNonQuery();
  myTrans.Commit();
  Console.WriteLine("Record is udated.");
}
catch(Exception e)
{
  myTrans.Rollback();
  Console.WriteLine(e.ToString());
  Console.WriteLine("Sorry, Record can not be updated.");
}
finally
{
  myConnection.Close();
}

Note: If you use OleDb class to define SQL command and connection instead of Sqlclient class, then you have to define a transaction using OleTransation class.