C# • Implicit Transactions Using TransactionScope
Listing 1. Use the TransactionScope object to execute the SQL statement within the scope of a transaction. After executing the statement, invoke the Complete() method of the TransactionScope object to signal that the transaction can be committed. The Complete() method is already invoked at the end of the using block of the TransactionScope object, so the transaction will be completed automatically.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Transactions" %>
<script runat="server">
protected void btnSave_Click(
object sender, EventArgs e)
{
try
{
string connectionString =
ConfigurationManager.ConnectionStrings
["pubsConnectionString"].ConnectionString;
using (TransactionScope scope = new
TransactionScope())
{
using (SqlConnection connection =
new
SqlConnection(connectionString))
{
string sql =
"Insert into Dept(Name," +
"Description)
Values(@Name,
@Description)";
//Opening the connection will
//automatically enlist the
//connection in the
//transaction scope
connection.Open();
SqlCommand command = new
SqlCommand(sql,
connection);
command.CommandType =
CommandType.Text;
SqlParameter nameParam = new
SqlParameter("@Name",
SqlDbType.VarChar, 20);
nameParam.Value =
txtDeptName.Text;
command.Parameters.Add(nameParam);
SqlParameter descParam = new
SqlParameter("@Description",
SqlDbType.VarChar, 50);
descParam.Value =
txtDescription.Text;
command.Parameters.Add(descParam);
command.ExecuteNonQuery();
}
//Commit the transaction
scope.Complete();
}
Response.Write(
"Dept is written successfully");
}
catch (Exception ex)
{
Response.Write(
"Exception is : " + ex.Message);
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
title>Simple Implicit Transactions using
TransactionScope</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblDeptName"
runat="server"
Text="Dept Name:"
Width="179px"></asp:Label>
<asp:TextBox ID="txtDeptName"
runat="server">
</asp:TextBox>
<br/><br/><br/>
<asp:Label ID="lblDescription"
runat="server"
Text="Description"
Width="177px"></asp:Label>
<asp:TextBox ID="txtDescription"
runat="server">
</asp:TextBox>
<br/><br/>
<asp:Button ID="btnSave" runat="server"
Text="Save" Width="92px"
OnClick="btnSave_Click"/>
</div>
</form>
</body>
</html>