Working with Transactions with an SQL Server Database

Transactions provide a way for you to group together database executions as a group so that they succeed or fail together. For example, if you had an e-commerce site you may have code that allows visitors to add a quantity of an item to their shopping cart. When you do that you also want to remove the number of items ordered from your inventory.


Therefore, you have two actions that you need to execute. You want to add items to a shopping cart and you want to remove items from inventory.

These executions need to happen as a group. You don't want to add items to the shopping cart if something goes wrong with removing them from inventory. And the opposite is also true.

Therefore, the database executions need to be grouped in a Transaction. This technique shows you

how to use a Transaction object with an SQL Server database.

This ASP.NET page contains SQL Delete statements that delete records from the Employees table. But the records are not deleted because they are in a transaction and the transaction is not committed to the database.

The code that performs this task fires when the ASP.NET page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) Within that procedure you will need a Connection object and a Command object:

Dim DBConn as SQLConnection Dim DBDelete As New SQLCommand

You will also need a Transaction object:

Dim DBTrans As SQLTransaction

You start by connecting to the SQL Server database:

DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")
and opening that connection:
DBConn.Open()

You then start a transaction by calling the BeginTransaction method of the Connection object. That method returns an open transaction, which is placed into the local Transaction object:

DBTrans = DBConn.BeginTransaction()

The Command object will connect to the database through the Connection object:

DBDelete.Connection = DBConn

It will also use the Transaction object:

DBDelete.Transaction = DBTrans

Next, two records are deleted and executed from the Employees table:

DBDelete.CommandText = "Delete From Employees
& "Where EmpID = 1" DBDelete.ExecuteNonQuery() DBDelete.CommandText = "Delete From Employees
& "Where EmpID = 2"DBDelete.ExecuteNonQuery()

But the records are not actually deleted from the database since the RollBack method of the Transaction object is called:

DBTrans.RollBack()
lblMessage.Text = "No action was taken."

The RollBack method causes the queries that were executed within the Transaction object to be cancelled.

You could instead call the Commit method:

'DBTrans.Commit()

['his method causes the pending execute statements to be executed as a group so that they fail or succeed together.

Adding SQL Server Data, ASP.net

When your ASP.NET application includes SQL Server database connectivity, you often will need to add records to the database through your ASP.NET pages. For example, you may track visitors as they visit your site. With each page they visit, you may want to add a hit record to your database. Or you may allow visitors to sign themselves up for a mailing list. In that case you would need to add their information to a database. This technique shows you how to add a record to an SQL Server database table.

The ASP.NET page presented with this technique allows visitors to add an employee record to the SQL Server database. Visitors enter information into the provided TextBox controls and click the Button control to add the new record to the database.

Defined on the page are TextBox controls like this one used to enter the new information through:

<asp:textbox
id="txtLastName" runat="Server"

This Button control allows visitors to submit their request:

<asp:button
id="butOK"
text=" OK "
onclick="SubmitEtn_Click" runat="server"
/>

When that button is clicked, this procedure fires:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs) To add a record, you will need a Connection object:

Dim DBConn as SQLConnection

and a Command object:

Dim DBAdd As New SQLCommand

The Connection object connects to the SQL Server database:

DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")

Next, you place into the CommandText property of the Command object the SQL Insert statement that adds the new record:

DBAdd.CommandText = "Insert Into Employees (" _
& "LastName, FirstName, BirthDate, Salary, " _
& "EmailAddress) values (" _
& "'" & Replace(txtLastName.Text, "'", """) _
& "', "
* "'" Replace(txtFirstName.Text, "'", """)
&
* "'" Replace(txtBirthDate.Text, "'", """)
&
Replace- (txtSalary.Text, "'", """) & ", " _
& "'" & Replace(txtEmailAddress.Text, "'", """) _
& “.).

The Command object will connect to the database through the Connection object: DBAdd.Connection = DBConn

That connection is opened:

DBAdd.Connection.Open

and the record is added to the database:

DBAdd.ExecuteNonQuery()