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 VB.NET in the sample.
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.
Looking for the C#.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!
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.
| Imports System.Data.SqlClient |
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 Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myConnection As New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;") myConnection.Open() Dim myTrans=myConnection.BeginTransaction() Dim myCommand As 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 ep As Exception
myTrans.Rollback()
Response.Write(ep.ToString())
Response.Write("Neither record was written to database.") Finally
myConnection.Close() End Try |
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
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> |
The flow for the code behind page is as follows.
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myConnection As New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;") myConnection.Open() Dim myTrans=myConnection.BeginTransaction() Dim myCommand As 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 ep As Exception
myTrans.Rollback()
Response.Write(ep.ToString())
Response.Write("Neither record was written to database.") Finally
myConnection.Close() End Try End Sub |
Looking for the C#.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.