We can use two type of basic transaction type in an ASP.NET Application.

Stored Procedure Transaction

It is the best practice to use transaction in stored procedure because all actions can be executed in the database side, which ensure security, quick execution and overall performance boost. The following three T-SQL statements control transaction in the SQL server.

Begin Transaction: This ensures the start of a transaction.

Commit Transaction: This ensures the successful end of a transaction. It passes signals to the database to save the work.

Rollback Transaction: This denotes that a transaction has not been successful and passes signals to the database to roll back to the state it was in before starting of the transaction.

It should be noted that there is no End Transaction statement. Transactions end on (explicit or implicit) commits and rollbacks. We can end transaction with the commit or rollback. If we do not do so, the transaction will be automatically rolled back. In the following Listing it is shown how we can use Begin Transaction, Commit Transaction and Rollback Transaction.

Listing 1

CREATE Procedure SP_TransferAmount
(
@Amount money,
@B_CodeA int,
@B_CodeB int
)
AS
@Amount < 1 
GOTO PROBLEM
Declare  @rows int
SET @rows = (SELECT COUNT(*) FROM Accounts Where A_ID=@B_CodeA)
if(@rows<1)
GOTO PROBLEM
SET @rows = (SELECT COUNT(*) FROM Accounts Where A_ID=@B_CodeB)
if(@rows<1)
GOTO PROBLEM
BEGIN TRANSACTION
UPDATE Accounts Set Balance=Balance+@Amount Where A_ID=@B_CodeA
IF(@@ERROR>0)
GOTO PROBLEM
UPDATE Accounts SET Balance = Balance - @Amount Where A_ID=@B_CodeB
IF(@@ERROR>0)
GOTO PROBLEM
Commit
Return
PROBLEM:
ROLLBACK
RAISERROR ('COULD NOT UPDATE', 16,1)
GO

It should be noted that, when using the @@ERROR value we must be careful to check it immediately after each operation. That is because @@ERROR is reset to 0 when a successful SQL statement is completed. As a result, if the 1st update fails and 2nd update is successful, @@ERROR returns to 0. It is, therefore, too late to check at this point. In case of SQL Server 2005, we can use the try catch structure like in our C# coding (See Listing II). The benefit of this approach is that execution passes to subsequent error handling block whenever any error occurs.

Listing 2

@CREATE Procedure SPTransferAmount
(@Amount Money,
@B_CodeA int,
@B_CodeB int
)                              
AS
BEGIN TRY
BEGIN TRANSACTION
UPDATE Accounts Set Balance=Balance+@Amount Where A_ID=@B_CodeA
UPDATE Accounts SET Balance = Balance - @Amount Where A_ID=@B_CodeB
Commit
END TRY
BEGIN CATCH
IF ((@@ TRANCOUNT>0)
ROLLBACK
DECLARE @Errmsg nvarchar (4000), @ErrSeverity int
SELECT @Errmsg=ERROR_MESSAGE (), @Err Severity=ERROR_SEVERITY ()
RAISEERROR (@Errmsg, @ErrSeverity, 1)
END CATCH

Coding Transaction in ADO.NET

Most ADO.NET data providers include support for database transactions. Transactions are started through the connection object by calling the BeginTransaction () method. This method returns a provider specific transaction objects that is used to manage the transaction. All transaction classes implement the IdbTransaction interface. Examples include SQLTransaction, OLEDBTransaction, OracleTransaction, etc. Commands are associated with a specific transaction for a specific connection. The following are the steps to implement transaction processing in ADO.NET (See Code Listing 3).

Step 1: Create an instance of connection object passing connecting string of the database.

Step 2: Create an instance of SqlCommand object with the necessary parameters.

Step 3: Open the database connection using the connection instance.

Step 4: Call the BeginTransaction method of the Connection object to make the beginning of the transaction.

Step 5: Execute the SQL statements using the instance of Command object.

Step 6: Call the Commit method of the Transaction object to complete the
transaction or call the Rollback method to cancel the transaction.

Step 7: Close the connection to the database by closing connection object instance.

Listing 3

string cString =
  WebConfigurationManager.ConnectionString[testDB].ConnectionString;
SqlConnection con = new sqlConnection(cString)SqlCommand cmd1 = new sqlCommand
  (Insert into Emp(E_Code, E_Name)values(1, Employee1));
SqlCommand cmd2 = new sqlCommand(Insert into Emp_Details(E_Code, Sal)values(1,
  10000));
SqlTransaction tran = null;
try
{
  con.Open()tran = con.BeginTransaction()cmd1.Transaction = tran;
  cmd2.Transaction = tran;
  cmd1.ExecuteNonQuery();
  cmd2.ExecuteNonQuery();
  tran.Commit();
  catch (Exception ex)
  {
    tran.Rollback();
    throw ex;
  }
  finally
  {
    con.Close();
  }

In the above example code, instead of taking two separate SqlCommand objects, we can take a single command object and perform the same operation by just setting CommandText property, which is specified in the following code Listing.

Listing 4

SqlCommand cmd = new SqlCommand (); 
 cmd.Transaction = tran; 
  cmd.CommandText =(Insert into Emp(E_Code, E_Name)values(1,Employee1));
  cmd.ExecuteNonQuery(); 
  cmd.CommandText =(Insert into Emp_Details (E_Code, Sal) values (1, 10000));
  cmd.ExecuteNonQuery();

SavePoints

Whenever we Rollback a transaction, it rollbacks all the operations performed from the starting of transaction. But sometimes we need to rollback any part of an ongoing transaction and using Savepoint we can do the same. Savepoints are just like bookmarks within a transaction. The statements given after a Savepoint can be committed or rolled back. A Savepoint has to be given a name. We can set the Savepoint using the Transaction.Save () method. There can be more than one Savepoint within a transaction.

Listing 5

string cString =
  WebConfigurationManager.ConnectionString[testDB].ConnectionString;
SqlConnection con = new sqlConnection(cString)SqlCommand cmd = new SqlCommand();
SqlTransaction tran = null;
try
{
  con.Open()cmd.Transaction = tran;
  cmd.CommandText = (Insert into Emp(E_Code, E_Name)values(1, Employee1));
  cmd.ExecuteNonQuery();
  tran.Save("1stTransaction");
  cmd.CommandText = (Insert into Emp_Details(E_Code, Sal)values(1, 10000));
  cmd.ExecuteNonQuery();
  tran.Rollback("1stTransaction ");
  catch (Exception ex)
  {
    tran.Rollback();
    throw ex;
  }
  finally
  {
    tran.Commit();
    con.Close();
  }

Once we rollback to a SavePoint, all the transactions defined after the Savepoint are lost. In the above example Insert into Emp_Details will be lost.