Explanation:
In this
article I have explained the use of transaction using web application. I have
used transaction in SQL Stored procedure and used that procedure in my
application.
So if the transaction succeeded then this stored procedure will return 1 otherwise 0. So we can check in code if transaction is succeeded or not.
Web Application:
Private Sub ClearControls()
In this
article I have explained the use of transaction using web application. I have
used transaction in SQL Stored procedure and used that procedure in my
application.
In this
application I have entered Employee information in two database tables. i.e.
personal and official through one stored procedure. So if one of the queries
got some error transaction will not commit. It will be rolled back.
Now create
two tables:
CREATE TABLE [dbo].[Emp_Personal]
(
[EmpPer_Id] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](100) NULL,
[Age] [int] NULL,
[Address] [varchar](500) NULL
)
Create another table "Emp_Office" for storing the
employee's official details like his department Name, his designation and the
salary using the script below.
CREATE TABLE [dbo].[Emp_Office]
(
[EmpOffice_Id] [int] IDENTITY(1,1) NOT NULL,
[EmpPersonal_ID] [int] NULL,
[Department] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[Salary] [decimal](18, 2) NULL
)
Also create a stored procedure as:
Also create a stored procedure as:
CREATE PROCEDURE [dbo].[InsertEmp_Sp]
@EmpName VARCHAR(100),
@Age
INT,
@Address
VARCHAR(100),
@Dept VARCHAR(100),
@Designation
VARCHAR(100),
@Salary
DECIMAL(18,2)
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO Emp_Personal(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 Emp_Office(Department,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
In
the above stored procedure first i am storing the employee's personal details :
name, age and address in the "Emp_Personal" table.
Then check
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 "Emp_Personal" table and then corresponding to that id, the official details are stored in the "Emp_OfficeDetail" 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 using scope_identity() function id of the last inserted records is fetched from the "Emp_Personal" table and then corresponding to that id, the official details are stored in the "Emp_OfficeDetail" 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. So we can check in code if transaction is succeeded or not.
Key
Points to remember: @@ERROR returns 0 if the last Transact-SQL statement executed successfully; if the statement generated
an error, @@ERROR returns the error number.
scope_identity() returns the last identity value inserted into an identity column in the same scope.
scope_identity() returns the last identity value inserted into an identity column in the same scope.
Web Application:
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 In Stored Proc</legend>
<table>
<tr><td>Employee
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> </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:
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["Empcon"].ConnectionString);
SqlCommand
cmd = new SqlCommand("InsertEmp_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:
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("Empcon").ConnectionString)
Dim cmd As New SqlCommand("InsertEmp_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("@Dept",
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 savedsuccessfully');", 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
0 comments:
Post a Comment