Example to explain Transaction in Sql server using Asp.net

Introduction: In this article i am going to explain the use of transaction in Sql server with example using asp.net web application. 
In previous related article i explained What is transaction and ACID (atomicity, consistency, isolation and durability) properties and What is cursor? Advantages and disadvantages,when to use and example to explain and Use of Cursor with example to print calculated records and SELF JOIN in SQL SERVER with example and  Remove duplicate records from table and 20 main differences between Stored procedures and Functions 

Description: To understand the use of transaction i am going to create a situation where it is required to store the Employee's personal details like Name, age and address in one table and his official details like his department Name, his designation and the salary in another table.  Employee will fill the details from the registration form as shown in the above image.

In this case we need to execute two different queries to store the details in two different tables. First query will store the employee's name, age and address in one table and second query will store the department Name, designation and the salary corresponding to that employee in another table.  
Note that second query is dependent on first query because the id of the employee from the first table will be required to store the corresponding details in another table.  So we can group these queries and put that in transaction so that either all committed or nothing at all which is the characteristics of transaction. If anything goes wrong while executing these queries then all the changes made will be aborted.

 So using transaction either the full details of the employee will be stored or none will be stored which was our requirement.  We can track the status of the transaction i.e. committed or rolled back so that we can display the appropriate message to the user whether the record has been successfully saved or not. Let's create an asp.net web application demonstrating this.

Implementation: First of all create a Sql Server Database e.g. "dbEmp" and create a table "EmpPersonalDetail" for storing the employee's personal details like Name, age and address using the script below:

CREATE TABLE [dbo].[EmpPersonalDetail]

(

                [EmpPersonalDetailIdPk] [int] IDENTITY(1,1) NOT NULL,

                [EmpName] [varchar](100) NULL,

                [Age] [int] NULL,

                [Address] [varchar](500) NULL

)

• Create another table "EmpOfficeDetail" for storing the employee's official details like his department Name, his designation and the salary using the script below.

CREATE TABLE [dbo].[EmpOfficeDetail]

(

                [EmpOfficeDetailId] [int] IDENTITY(1,1) NOT NULL,

                [EmpPersonalDetailIdFk] [int] NULL,

                [DeptName] [varchar](100) NULL,

                [Designation] [varchar](100) NULL,

                [Salary] [decimal](18, 2) NULL

)

• Also create a stored procedure as:

CREATE PROCEDURE [dbo].[InsertEmpDetails_Sp]

                @EmpName                     VARCHAR(100),

                @Age                                INT,

                @Address                        VARCHAR(100),

                @DeptName                    VARCHAR(100),

                @Designation                  VARCHAR(100),

                @Salary                            DECIMAL(18,2)              

AS

BEGIN

                BEGIN TRANSACTION                                

                                INSERT INTO EmpPersonalDetail(EmpName,Age,[Address]) VALUES(@EmpName,@Age,@Address)                               

                                IF (@@ERROR <> 0) GOTO ERR_HANDLER

                                 DECLARE @Id int

                                --get the latest inserted id from the EmpPersonalDetail table                  

                                SET @id= scope_identity()

                                 

                                INSERT INTO EmpOfficeDetail(DeptName,Designation,Salary,EmpPersonalDetailIdFk) VALUES(@DeptName,@Designation,@Salary,@id)

                                IF (@@ERROR <> 0) GOTO ERR_HANDLER                    

                COMMIT TRAN                                 

                                RETURN 1

    GOTO AfterErrBlock

                ERR_HANDLER:                             

                ROLLBACK TRAN

                                RETURN 0

                AfterErrBlock:

END

Explanation: In the above stored procedure first i am storing the employee's personal details like name, age and address in the "EmpPersonalDetail" table then i am checking the error status using @@ERROR. If it is 0 then next i need to get the Id of the last inserted record.

So using scope_identity() function id of the last inserted records is fetched from the "EmpPersonalDetail" table and then corresponding to that id, the official details are stored in the "EmpOfficeDetail" table and the changes are committed to the database using the COMMIT statement. If the @@ERROR status is not 0 then the control will be passed the Error handler where i have written the ROLLBACK statement that will abort the changes made to the database.

So if the transaction succeeded then this stored procedure will return 1 otherwise 0. I will check the return value of the stored procedure in the code behind and based on that display the success or failure message to the user.

Note: @@ERROR returns 0 if the last Transact-SQL statement executed successfully; if the statement generated an error, @@ERROR returns the error number. 

Note: scope_identity() returns the last identity value inserted into an identity column in the same scope.

Let's create the asp.net web application to see the transaction in actual use.

• In the <Body> tag of the design page (.aspx) design the page as shown in above Image using the following Html source:

<form id="form1" runat="server">

    <div>

    <fieldset style="width:270px">

    <legend>Transaction Example</legend>

     <table>

     <tr><td>Emp Name: </td><td>

         <asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox></td></tr>

     <tr><td>Age: </td><td>

         <asp:TextBox ID="txtAge" runat="server"></asp:TextBox></td></tr>

     <tr><td>Address: </td><td>

         <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td></tr>

     <tr><td>Department: </td><td>

         <asp:TextBox ID="txtDept" runat="server"></asp:TextBox></td></tr>

     <tr><td>Designation: </td><td>

         <asp:TextBox ID="txtDesignation" runat="server"></asp:TextBox></td></tr>

     <tr><td>Salary: </td><td>

         <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox></td></tr>

          <tr><td>&nbsp;</td><td>

          <asp:Button ID="btnSubmit" runat="server" Text="Submit"

            onclick="btnSubmit_Click" />

              <asp:Button ID="btnClear" runat="server" Text="Reset"

                  onclick="btnClear_Click" />

              </td></tr>

    </table>

    </fieldset>

    </div>

    </form>

Asp.Net C# code

• In the code behind file (.aspx.cs) write the code as:

First include the following namespaces:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

Then write the code as:

protected void btnSubmit_Click(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

        SqlCommand cmd = new SqlCommand("InsertEmpDetails_Sp", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@EmpName", txtEmpName.Text.Trim());

        cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text));

        cmd.Parameters.AddWithValue("@Address", txtAddress.Text.Trim());

        cmd.Parameters.AddWithValue("@DeptName", txtDept.Text.Trim());

        cmd.Parameters.AddWithValue("@Designation", txtDesignation.Text.Trim());

        cmd.Parameters.AddWithValue("@Salary", Convert.ToDecimal(txtSalary.Text));

        SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);

        returnParameter.Direction = ParameterDirection.ReturnValue;

        con.Open();

        cmd.ExecuteNonQuery();

        int statusVal = Convert.ToInt32(returnParameter.Value);

       if (statusVal == 1)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record saved successfully');", true);      

            ClearControls();

        }

        else

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record could not be saved');", true);      

        }

    }

    protected void btnClear_Click(object sender, EventArgs e)

    {

        ClearControls();

    }

    private void ClearControls()

    {

        txtEmpName.Text = string.Empty;

        txtAge.Text = string.Empty;

        txtAddress.Text = string.Empty;

        txtDept.Text = string.Empty;

        txtDesignation.Text = string.Empty;

        txtSalary.Text = string.Empty;

    }

ASP.NET VB code

• In the code behind file (.aspx.vb) write the code as:

First include the following three required namespaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Then write the code as:

Protected Sub btnSubmit_Click(sender As Object, e As System.EventArgs) Handles btnSubmit.Click

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

        Dim cmd As New SqlCommand("InsertEmpDetails_Sp", con)

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@EmpName", txtEmpName.Text.Trim())

        cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text))

        cmd.Parameters.AddWithValue("@Address", txtAddress.Text.Trim())

        cmd.Parameters.AddWithValue("@DeptName", txtDept.Text.Trim())

        cmd.Parameters.AddWithValue("@Designation", txtDesignation.Text.Trim())

        cmd.Parameters.AddWithValue("@Salary", Convert.ToDecimal(txtSalary.Text))

        Dim returnParameter As SqlParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int)

        returnParameter.Direction = ParameterDirection.ReturnValue

        con.Open()

        cmd.ExecuteNonQuery()

        Dim statusVal As Integer = Convert.ToInt32(returnParameter.Value)

     If statusVal = 1 Then

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Record saved successfully');", True)

            ClearControls()

        Else

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Record could not be saved');", True)

        End If

    End Sub

    Protected Sub btnClear_Click(sender As Object, e As System.EventArgs) Handles btnClear.Click

        ClearControls()

    End Sub

    Private Sub ClearControls()

        txtEmpName.Text = String.Empty

        txtAge.Text = String.Empty

        txtAddress.Text = String.Empty

        txtDept.Text = String.Empty

        txtDesignation.Text = String.Empty

        txtSalary.Text = String.Empty

    End Sub

Now over to you:

" I hope you have got what are the transactions using the example above and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post