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.