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.

Sub Category and Product Detail Pages of Six Functional Parts

Online stores use the following six basic component webpages in their store design:

• Home page
• Category pages
• Sub-category pages
• Product detail pages
• Informational content pages
• Checkout page

Sub-category Pages—"The Shelf"

Because most online stores carry hundreds or thousands of products, it is important to break up the information into manageable chunks. Products are displayed on a shelf—whether it is physical shelving in a brick-and-mortar store, a catalog page, a TV infomercial, or an e-commerce website product page. The product shelf is any web page on which a product is merchandised and sold. It can be a category page featuring many products, or it can be a specific product page with a single product.

In this example, there are many varieties of all-in-one printers. The subcategory page helps to narrow down the offering. A thorough understanding of customer needs is necessary to structure these pages. Depending on product complexities, informational content can provide customers with "why to buy" information that helps them select the right product to meet their needs. Emerging online tools—such as the shopping wizard—can help narrow choices.

Product Detail Pages—"The Package or SKU"

Because customers can't pick up virtual merchandise and read labels, the product detail page represents the product or behaves like the product package. It must clearly communicate what the product is and what the customer will receive as a result of its purchase. Anytime a page offers a product for sale, it must provide the following information for each of the products offered:

• What it is—the description, picture, uses
• Relevant and complete compatibility, sizing, color, or other information
• What's contained in the package—what the customer will receive
• Other items needed for immediate operation (batteries, cables, assembly, UL specifications)
• Spare or complementary items (extra batteries, film, or a carrying case)
• "Care and feeding" of the product (special polish, cleaning instructions)
• The price and any hidden charges (extra shipping or handling)
• The manufacturer's or designer's name
• Sample content (for example, sample book pages)

The customer must know clearly what the product is and what it looks like. And for customers who know exactly what they are looking for, accurate descriptions and specific product numbers or models must be included so they may easily recognize the correct product. Our research across more than 25 major elcommerce websites identified incomplete or inadequate information. In many cases, the web stores did not provide complete compatibility information.

Shoppers will not purchase from a site that cannot confirm their choice or be specific about what the product is that they are purchasing.

Product detail pages let customers know what they're getting and what else they may need or want. In this example, product features—the "speeds and feeds"—are listed. This includes products they may need to purchase in order to use the product. It also features other products the customer may want. Good clothing stores recommend coordinated accessories to give customers ideas to complete ensembles for a variety of social occasions.

The product detail page also needs to let people know how to buy the product. As simple as that sounds, customers who participated in website evaluation research had difficulty adding a product to the shopping cart. This function varies from website to website. Button labels can also vary. Some say "add to cart," "buy," or "add." The buttons also have different locations on these sites. Some are next to the products and some are far enough away to disassociate them from the product.

Also, some websites force customers to go to the shopping cart or checkout page every time they add an item to the shopping cart. This then forces them to start over or go back and forth if they are shopping for multiple items. It adds an unnecessary extra step.

Make it easy for the customer to know what they're getting and how to get it. Customers will leave tedious websites.

Page Design Tips and Considerations - Shopping for Multiple Items

Many websites have been designed for customers to purchase one item at a time. After a product is dropped into the shopping cart, the customer is brought to the checkout counter. Many websites use a "return to shopping" feature from their shopping cart after an item is placed into the cart. This feature often returned the customer to the home page, which is not typically what he expected. Return to shopping should go back to where the customer was last and not make him start over at the beginning.

The following comments are representative of customers' experiences:

And when I get to the screen that shows me my shopping cart, 1 prefer to be able to click on items and not go directly to the shopping cart after each item after clicking the "continue shopping" button]. I don't like that... Where did I go? I now have to go back through all the layers again to get back where 1 was. I have to select everything all over again. If I were doing this for real, I would have definitely given up. This website is cumbersome, very cumbersome. I didn't like having to go back to the main page every time I added something to my cart."

I don't like that I could have bone back and found another product instead of it taking me back to the home page. That doesn't help me any. What if I wanted to purchase more of the stuff?" People made it clear that they preferred to add multiple items to their cart and not have to start over from the beginning each time.