C# • Configure Transaction Settings Using TransactionOptions
Listing B. Use the TransactionOptions object to perform the transaction-related configurations. After setting the appropriate properties on the TransactionOptions object, supply it as an argument to the constructor of the TransactionScope object.
<%@ 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;
TransactionOptions transactionOption =
new
TransactionOptions();
transactionOption.IsolationLevel =
System.Transactions.IsolationLevel.ReadCommitted;
//Set the transaction timeout to 60
//seconds
transactionOption.Timeout = new
TimeSpan(0, 0, 60);
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.Required,
transactionOption))
{
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
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>Configuring Settings with 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>