Using Output Parameters with a Stored Procedure

One of the ways that you can use a stored procedure is to have it return data through output parameters. Then after the call to the stored procedure you can display the values returned to you. For example, you might use a stored procedure to return summary information about a table. This could be done through output parameters. The technique presented in this section of the chapter shows you how to use output parameters.


USE IT A Command object contains a collection of Parameter objects. These parameters are values that the stored procedure passes back to you after the call is complete. This ASP.NET page calls a stored procedure that returns values. The values returned are the total number of records in the Employees table and the name of the employee who is paid the most.

The stored procedure has this definition:

CREATE PROCEDURE Tablelnfo
@RecordCount integer OUTPUT,
@TopSalaryName varchar(100) OUTPUT
AS
Select @RecordCount = Count(EmpID) From Employees Select @TopSalaryName = LastName + ' + FirstName
From Employees Where Salary = (Select Max(Salary) From Employees)
Totice that the two parameters are declared as output parameters. In the first query, one of the put parameters is set to the record count. In the second query the other parameter is set to the name he employee who is paid the most.

That stored procedure is called when the ASP.NET page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
[n that procedure you will need a Connection object:
Dim DBConn as SQLConnection that points to the SQL Server database:
DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;"
& "User Id=sa;"
& "Password=yourpassword;")
You will need a Command object: Dim DBSP As New SQLCommand

You also will need two Parameter objects. The first has its name match the first parameter in the stored procedure and is of the same data type:

Dim parRecordCount as New _ SqlParameter("@RecordCount", SqlDbT e.Int)

The second parameter is set to the name and type of the second output parameter in the stored procedure:

Dim parTopSalaryName as New _ SqlParameter("@TopSalaryName", SqlDbType.VarChar, 100)

Both parameters are set to output parameters using the Direction property:

parRecordCount.Direction = ParameterDirection.Output parTopSalaryName.Direction = ParameterDirection.Output

Next, those parameters are added to the Parameters collection of the Command object:

DBSP.Parameters.Add(parRecordCount) DBSP.Parameters.Add(parTopSalaryName)

The CommandText property of the Command object is set to the name of the stored procedure: DBSP.CommandText = "TableInfo" and the command type is set to stored procedure: DBSP.CommandType = CommandType.StoredProcedure

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

DBSP.Connection = DBConn DBSP.Connection.Open
The stored procedure can then be executed: DBSP.ExecuteNonQuery()

After the call to the stored procedure you can use the values in the output parameters through the Value property:

lblMessage.Text = "Total Employees: "
& parRecordCount.Value _
& "

Highest Paid: "
& parTopSalaryName.Value
Visitors would then see through the Label control the total number of employees and the highest salary amount.

Sending Data to a Stored Procedure Through Input Parameters

If you are working towards making your ASP.NET application more efficient and that application connects to an SQL Server database, you will want to create stored procedures that do basic things like adding records, deleting records, and other such tasks. With these types of stored procedures, you need to pass data into them. This technique shows you how to call a stored procedure that needs to have parameters passed into it.


USE IT The ASP.NET page created for this technique allows visitors to add a record to the Employees table. Instead of using an Insert statement in the ASP.NET code, a stored procedure is called to add the record.

That stored procedure has this definition:

CREATE PROCE @LastName va
DURE AddEmployee rchar(50),
@FirstName varchar(50), @BirthDate datetime, @Salary money,
@EmailAddress varchar(50) AS
Insert Into Employees (LastName, FirstName, BirthDate,
Salary, EmailAddress) values
@LastName, @FirstName, @BirthDate,
@Salary, @EmailAddress) GO
Notice that the stored procedure has five input parameters. Those parameters are used in the Insert statement in the stored procedure.

On the ASP.NET page, visitors enter the values for the new employee record into TextBox control. When they click the Button control, this procedure fires, adding the new record by calling the stored procedure:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs)
Within the procedure, you need these data objects:
Dim DBConn as SQLConnection Dim DBAdd As New SQLCommand
You need to start by connecting to the SQL Server database:
DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")
Next, you place the SQL syntax into the Command object that calls the stored procedure:

DBAdd.CommandText = "Exec AddEmployee " _
& "'" & Replace(txtLastName.Text, "'", """) _
&
& ''" & Replace(txtFirstName.Text, "'", """)
&
& "'" & Replace(txtBirthDate.Text, "'", """)
& HI,
& Replace(txtSalary.Text, "'", """) & ", "
& "'" & Replace(txtEmailAddress.Text, "'", """)
Notice that the call passes in five parameters, the values for the new record. A comma separate each parameter.

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

kdd.Connection = DBConn kdd.Connection.Open

and the stored procedure can be executed:

kdd.ExecuteNonQuery()

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.

Retrieving Data from a Stored Procedure

Stored procedures frequently return data from a database. They can return single values, tables joined together, or all the records from a single value, and much more. You can call stored procedures that return data from your ASP.NET pages. This technique shows you how to do that..

USE IT The page defined for this technique displays in a DataGrid control all the employees who have a birthday in the current month. The DataGrid has this definition:

asp: datagrid
id="dgEmps"
runat="server" autogeneratecolumns="True"
/TD>TR>" ext etTipTable = GetTipTable & "Table >" function

ice that two parameters need to be passed into this procedure:

Method()> Public Function GetTipTable _ RedirectPage as String, _ ueryStringVaraibleName as String) as String

The first parameter is the path to the page that visitors should be taken to when they click on one of the title links. The second parameter is the name of the variable that the developer would like passed to that page when visitors click on the link. That variable is passed in the form of a query string.

In this procedure you will need these data variables:

Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim D SData as New DataSet You will also need this variable for a For code block: Dim I as Long You start by connecting to the Access database: DBConn = New OleDbConnection("PROVIDER=" _ & "Microsoft.Jet.OLEDB.4.0;" _ & DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" _ & "service/TipsDB.mdb;")

and you retrieve all the IDs and titles of the tips:

DBCommand = New OleDbDataAdapter _ ("Select TipID, TipTitle From Tips " _ & "Order by TipTitle", DBConn) DBCommand.Fill(DSData, _ "AllTips")

You return a value from the Function by assigning a value to the Function name. Here, it is initially set to the opening HTML Table tag:

GetTipTable = "< Table >"

You then start a loop, so that you can process each of the records that have been returned:

For I = 0 To DSData.Tables("AllTips").Rows.Count - 1

Within the loop, you append to the return value the title of the tip through an HTML Anchor tag:

GetTipTable = GetTipTable _ & "< TR >< TD >& RedirectPage & "?" _ & QueryStringVaraibleName & "=" & DSData.Tables("AllTips"). _ Rows(I).Item("TipID") _ & """>" & DSData.Tables("AllTips"). _ Rows(I).Item("TipTitle") _ & "<>< TR >"

Notice that the Anchor tag links to the page name passed in through the RedirectPage parameter and that the ID of the tip is passed into that page through the link's query string.

You then move on to process the next record:

Next

After the loop, you append the closing Table tag to the return value:

GetTipTable = GetTipTable & "< / Table >"

The second Function defined in this Web service would be used on a page that displays the contents of a tip. It returns the text of the tip's title:

< WebMethod() > Public Function GetTipTitle _ (TipID as Long) as String Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim DSData as New DataSet DBConn = New OleDbConnection("PROVIDER=" _ & "Microsoft.Jet.OLEDB.4.0;" _ & DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" & "service/TipsDB.mdb;") DBCommand = New OleDbDataAdapter ("Select TipTitle From Tips Where " & "TipID = " & TipID, DBConn) DBCommand.Fill(DSData, "CurrentTip") If DSData.Tables("CurrentTip").Rows.Count = 0 Then GetTipTitle = "" Else GetTipTitle = DSData.Tables("CurrentTip"). _ Rows(0).Item("TipTitle") End If End Function

Passed into the method through a parameter is the ID of the tip, whose title is to be returned:

(WebMethod()> Public Function GetTipTitle (TipID as Long) as String

You will need these data objects:

Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim DSData as New DataSet

You start by connecting to the database:

DBConn = New OleDbConnection("PROVIDER=" & "Microsoft.Jet.OLEDB.4.0;" "DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" _ & "service/TipsDB.mdb;")

and you retrieve from the database the title of the tip:

DBCommand = New OleDbDataAdapter _ ("Select TipTitle From Tips Where " & "TipID = " & TipID, DBConn) DBCommand.Fill(DSData, _ "CurrentTip")

You then check to see if a record was found based on the ID that was passed in: If DSData.Tables("CurrentTip").Rows.Count = 0 Then

If a record wasn't found, you return an empty string:

GetTipTitle = ""

Otherwise, the title of the tip is returned:

GetTipTitle = DSData.Tables("CurrentTip"). _ Rows(0).Item("TipTitle")

The other Function in this Web service returns the text of a tip, based on the ID of the tip passed That tip has this definition:

< WebMethod() > Public Function GetTipText _ (TipID as Long) as String Dim DBConn as OleDbConnection Dim DBCommand As OleDbDataAdapter Dim DSData as New DataSet DBConn = New OleDbConnection("PROVIDER=" & "Microsoft.Jet.OLEDB.4.0;" & "DATA SOURCE=" & "c:/inetpub/wwwroot/TT/C14/" _ & "service/TipsDB.mdb;") DBCommand = New OleDbDataAdapter ("Select TipText From Tips Where " & "TipID = " & TipID, DBConn) DBCommand.Fill(DSData, _ "CurrentTip") If DSData.Tables("CurrentTip").Rows.Count = 0 Then GetTipText = "" Else GetTipText = DSData.Tables("CurrentTip"). Rows(0).Item("TipText") End If End Function

Within this procedure, you connect to the Access database and attempt to retrieve the text of the tip. If a record with the ID passed in is not found, an empty string is returned. Otherwise, the text of the tip is returned.

Including Namespace References and Other Directives in a Web Service

As your Web service becomes more complex, you will find that you need to import other namespaces into it. For example, if your Web service provided database functionality, you would need to import the necessary data namespaces. This tip shows you how to do that and how to use options within a Web service.

At the top of a Web service, you need to include these opening lines:

WebService Language="VB" Class="Tips" % > Imports System.Web.Services

After those lines of code but before you define your class, you can include Option directives like these:

Option Strict Off
Option Explicit On

The first directive tells the compiler that you do not want strict data-type conversion to be enforced. T urning this directive off means that you can easily place a number variable into a string variable.

The second option indicates that you will declare all your variables. Having this option on makes it easier for you to debug your code, since an error will be thrown if you try to reference a variable that you haven't defined.

After defining your option directives, you can import other namespaces into your Web service:

Imports System.Data Imports System.Data.OLEDB

Here, two data namespaces are added to the Web service. These allow you to connect to and retrieve data from an Access database. Notice that this is very different from the namespace directive used on an ASP.NET page.

Calling a Stored Procedure at ASP.net

In SQL Server you create stored procedures that take some action or manipulate data in some way in your database. Once you create a stored procedure it becomes a compiled efficient set of code. You can call stored procedures from your ASP.NET pages. This tip shows you how to call a stored procedure that does not have any parameters nor does it return any value.

USE IT This ASP.NET page calls a stored procedure that adds a record to the Employees table. The stored procedure has this syntax:

CREATE PROCEDURE AddGarbageRecord AS

Insert Into Employees (LastName, FirstName, BirthDate, Salary, EmailAddress) values ('Smith', 'Jane', '12/17/1947', 55000, 'janegna.com')

GO and place the SQL text that calls the stored procedure into the Command object. Notice that the call consists of the name of the stored procedure preceded by the Exec keyword:

DBSP.CommandText = "Exec AddGarbageRecord"

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

DBSP.Connection = DBConn DBSP.Connection.Open

And the stored procedure is executed:

DBSP.ExecuteNonQuery()
lblMessage.Text = "Stored procedure completed."

Text, Color Schemes, Images, Copy and Virtual Bundles

Text, Color Schemes, Images, and Copy

If people feel good, they typically purchase more. With the convenience of having multiple stores at a shopper's fingertips, an online store must be inviting and must ensure that the customer feels comfortable to stay and purchase. The web also has the potential of becoming more like catalogs and having a more recreational aspect versus a pure product hunt.

Effective online merchandising considers advertisement and newspaper techniques. They start with the most important attributes and put them in the headlines and sub-headlines. These same techniques must be applied online—with the most important customer benefits up top as the headline.

Some products are complex to describe, such as those with textures and other sensory attributes that would make the sale in retail. A silky, luxurious blouse in rich colors can be described with words and images, but subtle variations in the difference between sueded-silk and satin may not be easily described. Magnifying glass "close up" techniques can give a deeper view. This technique, coupled with appropriate copy writing and other imagery, can enhance product details and simulate the retail environment. For expensive items, such as a large line of name-brand quality clothing, you may consider providing a swatch sample for your shoppers to order prior to purchasing.

Virtual Bundles—E-kits

Special product bundles have been a retail strategy to stimulate sales. Bundles consist of two or more products or a product with a special offer. At retail, physical bundles are created by putting two or more products into the same box or by strapping the products together to form a "hard" bundle. A new SKU number is created and assigned to the new "package" so it can be stocked, inventoried, and merchandised.

Online stores have an advantage over retail because they can create "virtual" bundles. Virtual bundles put the products or special offers together on the product detail page—the online "package." The virtual bundle is also assigned a new SKU number. Fulfillment can be notified to "pick and pack" those items that make up the bundle. Virtual bundles save on production costs and labor, because a physical hard-bundled product is not required. A hard bundle is created virtually through putting the separate items into the same shipping box.

These elkits require special merchandising treatments, however, because they are mot physically bundled items in one package. New photos may be required to show the bundle contents. All product categories are eligible for bundling. Dresses with handbags, crafts with tools, printers with cartridges, and dog food with dog toys are a few combinations. Bundles are limited only by the imagination and distribution capabilities. Bundles provide you a competitive advantage by providing "new" exclusive items for your target customers.