Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to perform SQL Server Transaction in ASP.NET

Author: Mike Green
Download Source Code : 1326_SQLTransactionUsingCode.zip

SQL transactions can be executed in ASP.NET or more specifically in ADO.NET code. I will explain it in this article.

 

What is SQL Server Transaction?
 
A Transaction is the group of two or more SQL commands that will execute as a single unit. It means that if any of the SQL command from a transaction will fail; all the changes made to database will be rolled back. A transaction will complete only if all the SQL commands successfully completed. The transaction will fail if one of the commands fails.
 
In other words, transaction includes multiple SQL statements that are executed together in a unit. A transaction is executed as single component of work. It gives only two results, either all the statements succeed or all of them fail. No other result is possible in a transaction.
 
ACID Principle
 
Transaction follows ACID principle. ACID consists of four properties; Atomicity, Consistency, Isolation, Durability.
 
Atomicity means two or more pieces of information in a transaction will successful together or fail together.
 
Consistency in transaction means that a transaction after completion leaves data into a consistent state. This state can be new because of the successful completion of transaction or it can be previous state of data before the transaction started.
 
Isolation means a transaction will not obstructed by another transaction. It should execute independently.
 
Durability means that a committed transaction data should not be lost because of any system failure.
 
Commit and Rollback Transaction methods
 
Commit transaction method means that a transaction has successfully completed. On the other hand when any of the SQL commands fails, we will call Rollback method which rollback all executed commands in transaction.
 
Transaction in ASP.NET
 
I have create a new database “EmployeeDatabase” and a table “Employees” in database for this article. Employees table has one Integer field “EmpID” and four variable character fields “FName”, “LName”, “City” and “State”. 
  1. Open MS Visual Studio 2010
  2. File > New > Website > Visual C# or Visual Basic > ASP.NET Empty Web Site
  3. Select Web Location as File System and Click OK
  4. From Menu, Website > Add New Item > Select Web Form and Click Add. Name of the Web Form will be Default.aspx
  5. Drag and Drop a Button Control and a Label Control in Default.aspx page

    <asp:Button ID="btnTransaction" runat="server" Text="Start Transaction"
                onclick="btnTransaction_Click" />
    <br />
    <asp:Label ID="lblMessage" runat="server" ></asp:Label>

     
  6. Write code below in web.cofig file for connection. You have to specify your own server and connection string here.

    <system.web>
           <compilation debug="true" targetFramework="4.0"/>
    </system.web>
    <connectionStrings>
           <add name="EmployeeDatabase" connectionString="Data Source=(Local);Initial Catalog=EmployeeDatabase;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

     
  7. Open code behind file and write code below in button click event

    C#
     
    protected void btnTransaction_Click(object sender, EventArgs e)
    {
        string connString = ConfigurationManager.ConnectionStrings["EmployeeDatabase"].ConnectionString;
        SqlConnection connection = new SqlConnection(connString);
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction = null;
     
        try
        {
            
            transaction = connection.BeginTransaction();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO Employees VALUES(1, 'Richard', 'Thomas', 'Washington', 'DC')";
            command.ExecuteNonQuery();
            command.CommandText = "INSERT INTO Employees VALUES(2, 'Gary', 'Smith', 'Boston', 'MA')";
            command.ExecuteNonQuery();
            command.CommandText = "INSERT INTO Employee VALUES(3, 'John', 'Steel', 'Seattle', 'WA')";
            command.ExecuteNonQuery();
            transaction.Commit();
            lblMessage.Text = "Transaction is completed successfully";
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            lblMessage.Text = "Transaction is not completed";
        }
        finally
        {
            connection.Close();
        }
    }
     
    VB.NET
     
    Protected Sub btnTransaction_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnTransaction.Click
        Dim connString As String = ConfigurationManager.ConnectionStrings("EmployeeDatabase").ConnectionString
        Dim connection As New SqlConnection(connString)
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()
        Dim transaction As SqlTransaction = Nothing
     
        Try
            transaction = connection.BeginTransaction()
            command.Transaction = transaction
            command.CommandText = "INSERT INTO Employees VALUES(1, 'Richard', 'Thomas', 'Washington', 'DC')"
            command.ExecuteNonQuery()
            command.CommandText = "INSERT INTO Employees VALUES(2, 'Gary', 'Smith', 'Boston', 'MA')"
            command.ExecuteNonQuery()
            command.CommandText = "INSERT INTO Employee VALUES(3, 'John', 'Steel', 'Seattle', 'WA')"
            command.ExecuteNonQuery()
            transaction.Commit()
            lblMessage.Text = "Transaction is completed successfully"
        Catch ex As Exception
            transaction.Rollback()
            lblMessage.Text = "Transaction is not completed"
        Finally
            connection.Close()
        End Try
    End Sub
     
    Get the SQL Server connection and open transaction. To execute transaction, you need an open connection to the SQL Server. CreateCommand() method will create a new SqlCommand object. Create a SqlTransaction object and initialize it to null or nothing.  Call BeginTransaction() method of SqlConnection object and set the Transaction property of SqlCommand object. Now write command text. Here I have written INSERT statement to insert data in “Employees” table. Call the ExecuteNonQuery() method after envery statement. In third INSERT statement, I have intentionally written “Emplyee” instead of “Employees” as table name. First two INSERT statements will be executed successfully but third statement will throw exception. Then call the Commit() method. This method is called when all commands execute successfully. In catch block, Rollback method is called which rollback all executed commands in transaction when last statement fails.
     
  8. Now browse the website and click on button. The transaction will not complete successfully because of an error in third INSERT statement. You can see in your database table that there will no changes in the table because transaction is not completed successfully
     
  9. Comment out the following lines of code and browse the website again.

    C#
     
    command.CommandText = "INSERT INTO Employee VALUES(3, 'John', 'Steel', 'Seattle', 'WA')";
    command.ExecuteNonQuery();
     
    VB.NET

    command.CommandText = "INSERT INTO Employee VALUES(3, 'John', 'Steel', 'Seattle', 'WA')"
    command.ExecuteNonQuery()
     
  10. You will see the transaction has completed successfully. It means that this time commit() method is called. 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to export DataTable to Excel in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net