In this tutorial, we will show you how to make a Transact-SQL transaction in a SQL Server database. We will use ASP.NET 2.0 and C# in the sample.
First, import the namespace of System.Data.SqlClient. The System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server. The.NET Framework Data Provider for SQL Server describes a collection of classes used to access a SQL Server database in the managed space.
| using System.Data.SqlClient; |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
We instantiate a Connections object to connect the sample database of Northwind. Then instantiate a SqlTransaction object, and associate it to Connections object. The next step is to instantiate a SqlCommand object, set the Transaction property of SqlCommand to SqlTransaction. After then, use SqlCommand to commit two Sql statements. As one of the statements is incorrect, the transaction will be rolled back on the error.
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
myConnection.Open();
// Start a local transaction
SqlTransaction myTrans = myConnection.BeginTransaction();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "delete * from Region where RegionID=101";
// Attempt to commit the transaction.
myCommand.ExecuteNonQuery();
myTrans.Commit();
Response.Write("Both records are written to database.");
}
catch (Exception ep)
{
// Attempt to roll back the transaction.
myTrans.Rollback();
Response.Write(ep.ToString());
Response.Write("Neither record was written to database.");
}
finally
{
myConnection.Close();
} |
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
The front end Default.aspx page looks something like this:
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Transaction </title>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset style="height: 383px">
<legend><strong>Transaction</strong></legend> <br />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Insert" /></fieldset>
</div>
</form>
</body>
</html>
|
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
The flow for the code behind page is as follows.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
myConnection.Open();
SqlTransaction myTrans = myConnection.BeginTransaction();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "delete * from Region where RegionID=101";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Response.Write("Both records are written to database.");
}
catch (Exception ep)
{
myTrans.Rollback();
Response.Write(ep.ToString());
Response.Write("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
} |
Looking for the VB.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!