Example to explain Transaction in Sql server using Asp.net

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(thisthis.GetType(), "Message""alert('Record saved successfully');"true);      
            ClearControls();
        }
        else
        {
            ScriptManager.RegisterStartupScript(thisthis.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.EventArgsHandles 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(MeMe.[GetType](), "Message""alert('Record saved successfully');"True)
            ClearControls()
        Else
            ScriptManager.RegisterStartupScript(MeMe.[GetType](), "Message""alert('Record could not be saved');"True)
        End If
    End Sub

    Protected Sub btnClear_Click(sender As Object, e As System.EventArgsHandles 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."
Previous
Next Post »

5 comments

Click here for comments
Unknown
admin
December 13, 2013 ×

Hello Lalit!!..can we perform unit testing on asp.net snippets that you have explained in your site..if possible then please explain how,when and where to do unit testing considering various approaches like NUnit testing,MoQ testing,Moles on Asp.net website and Winforms applications..

Reply
avatar
Anonymous
admin
December 13, 2013 ×

running your storedprocedure it shown as "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION". what is the mistake??

Reply
avatar
December 14, 2013 ×

Hi, this stored procedure is working properly. please check whether you have copied the whole stored procedure or not? i suggest you to again copy and paste the stored procedure and try once more..if still you face any problem then let me know..i will help you in resolving your error..:)

Reply
avatar
Anonymous
admin
December 14, 2013 ×

Hi, pls see the below code and let me know how to fix.
--CREATE PROCEDURE [dbo].[InsertEmpDetails_Sp]
Declare @EmpName VARCHAR(100)
set @EmpName = 'Hari'
Declare @Age INT
set @Age = 25
Declare @Address VARCHAR(100)
set @Address = 'VSP'
Declare @DeptName VARCHAR(100)
set @DeptName = 'IT'
Declare @Designation VARCHAR(100)
Set @Designation='Jr App Developer'
Declare @Salary DECIMAL(18,2)
set @Salary = '18000'
--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
Select 1

ERR_HANDLER:
ROLLBACK TRAN
Select 0

END

Reply
avatar
December 15, 2013 ×

Hi, change your stored procedure to :
CREATE PROCEDURE [dbo].[InsertEmpDetails_Sp]
AS
BEGIN
BEGIN TRAN
Declare @EmpName VARCHAR(100)
set @EmpName = 'Hari'
Declare @Age INT
set @Age = 25
Declare @Address VARCHAR(100)
set @Address = 'VSP'
Declare @DeptName VARCHAR(100)
set @DeptName = 'IT'
Declare @Designation VARCHAR(100)
Set @Designation='Jr App Developer'
Declare @Salary DECIMAL(18,2)
set @Salary = '18000'


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
Select 1
Goto AfterErrBlock


ERR_HANDLER:
ROLLBACK TRAN
Select 0
AfterErrBlock:

END

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..