Transaction Sql Server Using Asp.net
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 Stored Procedure
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
Above stored
procedure first storing the employee's personal details like name, age and
address in the "EmpPersonalDetail" table then checking the error
status using @@ERROR. If it is 0 then next 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.
Create an
application for Transaction
.ascx
<form id="form1" runat="server">
<div>
<fieldset style="width:270px; color:greenyellow;">
<legend class="auto-style1"><strong>Transaction Example</strong></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> </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>
Code Behind file in
C#
.aspx.cs
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=jp-pc;Initial
Catalog=jaya;Persist Security Info=True;User ID=sa;Password=learner");
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;
}
The two Different Tables shown below
