Performing SQL Server Transaction Using ADO.NET

Introduction

A transaction allows you to execute two or more SQL commands in such a way that if any one of the statements fail, then no other statement will change the database. Either all operations in the transaction are performed, or none.

Now let us consider a situation. First the customer is going to transfer $10 from your account to a second customer account. Now how it can be done? There are two steps that can be involved in it.

The first customer will deduct $10 from the account
The second customer will add $100 to the account

Now think, after completion of step1, something bad happens or some error happens for which step 2 didn't complete. What will occur? $10 will be deduced from the first customer's account but it will not be added to the second customer account. $10 will be missing. Transactions help to avoid these types of problems by ensuring that if any one of the statements fail, then no other statement will be able to change. If step 2 fails, then the changes made by step 1 will not be committed to the database.

ACID properties

A transaction is a way to group SQL statements together so that, when executed, the transaction obeys the ACID principles. In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:

Atomicity

The Atomicity property of the transaction defines that a transaction is an atomic unit of work or collection of separate operations. In electronic transactions, the transaction needs to be done either by 100% or 0%. This property ensures that the transaction is successful and if by chance the transaction can not be done fully, it brings the system in previous state or rollback.

Consistency

A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.

Isolation

One transaction does not interfere with another running at the same time. In case of only one transaction, it does not matter.

Durability

A committed transaction will not be lost. If the system fails later on, the data can be retrieved.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns UserId and UserName. The table looks like as below.

Now create a new web application project in Visual Studio 2010. Add the namespace declaration to enable the use of non-fully qualified references to other namespace types.

Now add the following namespace.

using System.Data.SqlClient;

using System.Data;

Now write the connection string to connect to the database.

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";

Creating Transactions

Call the BeginTransaction method of the Connection object to mark the start of the transaction. The BeginTransaction method returns a reference to the Transaction. This reference is assigned to the Command objects that are enlisted in the transaction.

BeginTransaction() - To Begin a database transaction

You may use the following methods to work with the 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.

In the following code it is shown how we can use Begin Transaction, Commit Transaction and Rollback Transaction.

Let's take a closer look at the use of transactions within the .NET Framework. Creating a new web application project in Visual Studio 2010. Drag and Drop a Button and Label control to the form. For a sample design your aspx page might be like this.

<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Save" />

<asp:Label ID="LabelMessage" runat="server" ForeColor ="Red" Text="ShowMessage"></asp:Label>

In Codebehind write the following code on the Button Event.

Codebehind

protected void Button1_Click(object sender, EventArgs e)

{

SqlConnection connection = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");

{

SqlCommand command = connection.CreateCommand();

SqlTransaction transaction = null;

try

{

// BeginTransaction() Requires Open Connection

connection.Open();

transaction = connection.BeginTransaction();

command.Transaction = transaction;

command.CommandText = "Insert into UserDetail (UserId, UserName) VALUES (100, 'Rohatash')";

command.ExecuteNonQuery();

// Execute 2nd Command

//command.CommandText = "delete from UserDetail where UserId='100'";

command.CommandText = "Insert into UserDetail (UserId, UserName) VALUES (101, 'Manoj')";

command.ExecuteNonQuery();

transaction.Commit();

LabelMessage.Text = "Success";

}

catch

{

transaction.Rollback();

LabelMessage.Text ="Not Success";

}

finally

{

connection.Close();

}

In the above code we execute two insert statements to insert a value in the UserDetail table in the database. If both operations are true, the data will be committed to the database and a message will be generated saying success. If any one operation fails then the data will be rolled back (by a Rollback) and a message will be shown indicating no success. Now run the application.

Now click on the Button Control.

Now change the second insert statement to a delete statement which is incorrect. The data will be rolled back.

command.CommandText = "delete * from UserDetail where UserId='100'";

Now again run the application.