Using Stored Procedures in Database Interaction

This article, the third of three parts, explains how to link your ASP.NET web pages with your own database by using the ADO.NET connection, as well as how to write SQL statements in a query that direct the DBMS to perform common tasks. It is excerpted from chapter 10 of ASP.NET 2.0 DeMYSTiFieD, written by Jim Keogh (McGraw-Hill/Osborne; ISBN: 0072261412).

Stored Procedures

Throughout this chapter you learned how to create simple queries to perform operations that are common to commercial web sites. These queries are created in the event handler in your web page and are sent to the DBMS for processing.

Commercial web sites typically use complex queries that perform multiple operations such as updating two or more tables whenever a new customer is added to the database.

Complex queries can become rather long, and sending them from the web page to the DBMS is time-consuming. Although the time it takes to send the query from the web page to the DBMS might seem fast to us, it can actually slow down processing if you consider that a commercial web site might need to process many requests each second.

A common way to increase speed is by using a stored procedure. A stored procedure is a query that resides in the DBMS and can be called from your web page. Think of a stored procedure as a function or procedure (see Chapter 7) that is stored in the DBMS.

Creating a Stored Procedure

A stored procedure is defined in a query using the Create Procedure statement. You can enter the query directly into an interactive software tool provided by the DBMS such as the Microsoft SQL Server Enterprise Manager or Query Analyzer. Alternatively, you can execute the query from your application by using the ExecuteNonQuery() function, which is illustrated in examples throughout this chapter. It is important to understand that not all DBMSs support stored procedures, and therefore, you’ll need to check with the DBMS manufacturer before incorporating stored procedures in your application.

The Create Procedure statement requires a unique name, SQL statements that are to execute when the stored procedure is called, and a return value if required by your application. You’ll find that some stored procedures, such as those used to insert a new row into the database, don’t require a return value, while others, such as procedures for counting the number of customers, do require one.

Let’s take a look at a simple stored procedure that will count the number of customers there are in the custContact table. We’ll call this HowManyCustomers:

Create Procedure HowManyCustomers
As
Dim intNumCustomers As Integer
intNumCustomers = Select Count (*) From custContact
Return (iIntNumCustomers)

Statements below the As keyword form the query that you would otherwise run from your application. In this example, we declared a variable called intNumCustomers, which then receives the results from the query.

The Select statement in the query uses the SQL Count() function, which you’ll learn more about in the next chapter. An asterisk is placed within the parentheses. This is a wildcard character that tells the DMBS to use any column to count the number of rows in the table. As you’ll see in the next chapter, you can replace the asterisk with a column name. As you’ll remember from other examples, the From clause specifies the name of the table. Return is used to specify the value that is returned to the SQL statement in the web page that called the stored procedure.

{mospagebreak title=Calling a Stored Procedure}

You call a stored procedure from your web page by using the name of the stored procedure. This is illustrated in the next example, which calls the HowManyCustomers stored procedure that is defined in the preceding section of this chapter.

This example is similar to others you wrote in this chapter. However, there are two new twists that you’ll need to learn. The first is calling the stored procedure, and the other is using the value that the stored procedure returns to your web page.

Here’s how to call the stored procedure. Notice that the query is simply the name of the stored procedure:

Dim cmdTotalCustomers As SqlCommand
cmdTotalCustomers = New SqlCommand(“HowManyCustomers”, custDb)

Accessing the return value requires a few steps as shown here. First you need to declare a variable as parameter. We call this parmTotalCustomers. Next you must add a parameter using the Add function, which you learned previously in this chapter. We call the parameter ReturnValue and declare it as an Int data type.

Dim parmTotalCustomers As SqlParameter
parmTotalCustomers = cmdTotalCustomers.Parameters.Add(“ReturnValue”, SqlDbType.Int )

Next we need to retrieve the return value. The initial step is to declare a variable that will hold the return value. We called this intTotalCustomers. Next, we associate the return value with the parameter that we previously created. The value returned from the stored procedures is assigned to the parameter. We then need to assign the value of the parameter to the variable. The variable is then used within the web page.

Dim intTotalCustomers As Integer
parmTotalCustomers.Direction = ParameterDirection.ReturnValue
intTotalCustomers = cmdTotalCustomers.Parameters( “ReturnValue” ).Value
Response.Write(“Total Number of Customers: <%=intTotalCustomers%>”)

Here is the complete code:

<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SqlClient” %>
<%
Dim custDb As SqlConnection
Dim cmdTotalCustomers As SqlCommand
Dim parmTotalCustomers As SqlParameter
Dim intTotalCustomers As Integer
custDb = New SqlConnection(“Server=localhost;uid=myID;pwd=mypassword; database=customer”)
cmdTotalCustomers = New SqlCommand( “HowManyCustomers”, custDb)
cmdTotalCustomers.CommandType = CommandType.StoredProcedure
parmTotalCustomers = cmdTotalCustomers.Parameters.Add( “ReturnValue”, SqlDbType.Int )
parmTotalCustomers.Direction = ParameterDirection.ReturnValue
custDb.Open()
cmdTotalCustomers.ExecuteNonQuery()

intTotalCustomers = cmdTotalCustomers.Parameters( “ReturnValue” ).Value
custDb.Close()
%>
Response.Write(“Total Number of Customers: <%=intTotalCustomers%>”)

{mospagebreak title=Passing Parameters to a Stored Procedure}

Many times you’ll need to provide a stored procedure information so that the DBMS can process the stored procedure; for instance, you must provide customer information to a stored procedure that inserts a new customer into the customer contact table.

Information is passed to a stored procedure by way of a parameter. Each parameter must have a unique name and a data type. A parameter is then used within the stored procedure.

Let’s create a stored procedure called AddCustomer and declare three parameters to hold a customer number and a customer name. Parameters are declared within the French braces between the name of the stored procedure and the As keyword, as shown in the following example. You’ll notice that the Insert statement in this example is nearly identical to the Insert example that you previously created in this chapter, except that the parameter names are used as values. That is, values that are passed to the stored procedure are assigned to the parameters, and the parameters are used as the values that are inserted into the table.

Create Procedure AddCustomer
{
  
@CustNumber varchar(5), @CustFirstName varchar(30), @CustLastName varchar(30)
}
As
Insert custContact (custNumber, custFirstName, custLastName) Values (@CustNumber,
@CustFirstName, @CustLastName)

The next example shows how to pass parameters to the AddCustomer stored procedure. Notice that the stored procedure is called the same way you call a stored procedure that doesn’t have parameters. However, we use the Add() function to create the three parameters required by the stored procedure. Here is where we insert the actual values that will be inserted into the table.

<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SqlClient” %>
<%
Dim custDb As SqlConnection
Dim strInsert As String
Dim cmdInsertCustomers As SqlCommand
custDb = New SqlConnection(“Server=localhost;uid=myID;pwd=mypassword; database=customer”)
cmdInsertCustomers = New SqlCommand( “AddCustomer”, custDb)
cmdInsertCustomers.CommandType = CommandType.StoredProcedure
cmdInsertCustomers.Parameters.AddWithValue( “@CustNumber”, “45678”)
cmdInsertCustomers.Parameters.AddWithValue( “@CustFirstName”, “Mary” )

cmdInsertCustomers.Parameters.AddWithValue( “@CustLastName “, “Roberts”)
custDb.Open()
   cmdInsertCustomers.ExecuteNonQuery()

custDb.Close()
%>

Looking Ahead

You can link your web page to a DBMS by using ADO.NET. ADO.NET is a component of .NET. ActiveX Data Objects have functions and properties that you can use in your application to interact with a DBMS.

There are many popular DBMSs on the market, each requiring a unique ActiveX Data Object. You let the ASP.NET engine know which ActiveX Data Object you want to use by importing the namespace that corresponds to the DBMS. A namespace organizes classes in a hierarchy of classes to prevent naming conflicts.

In order to interact with a DBMS, you import the namespace, create an instance of the connection class, and then open the connection to the DBMS. Once the connection is open, you can prepare and then send a query.

A query is a set of instructions written using SQL that direct the DBMS to do something. You use a query to request information from the database, insert new information into the database, modify existing information, and delete information. You can perform any of these tasks as long as the login used by your application has authorization to perform them.

You use a reader function to access information returned by the DBMS. The reader enables you to access the columns that you requested from the DBMS.

Each time you execute a query from your web page, the SQL statements that compose the query are sent to the DBMS for processing. This is inefficient, especially if many queries are being sent each second to the DBMS, as in the case of a popular e-commerce web site.

A more efficient method of executing queries is to store the query in the DBMS as a stored procedure, and then send the DBMS the name of that procedure each time you want the procedure executed.

In this chapter you learned how to interact with a DBMS using simple queries. However, real-world applications require more complex queries than those you learned about in this chapter. Therefore, we’ll focus on how to create more complex queries in the next chapter.

{mospagebreak title=Quiz}

  1. A namespace organizes classes in a hierarchy of classes to prevent naming conflicts.

    a. True

    b. False
  2. You open a connection to a database by using

    a. ConnectDB

    b. DBConnection 

    c. DBConnect

    d. None of the above
  3. The best place to open a connection to a database is

    a. The On_Click event handle

    b. The Page_Load subroutine

    c. In the SQL query

    d. In the query
  4. Which SQL statement specifies the information you want returned to your application?

    a. Return

    b. Where

    c. From

    d. Select
  5. Which SQL statement sets the search criteria for rows that contain information you want returned to your application?

    a. Return

    b. Where

    c. From

    d. Select
  6. What statement identifies the database that contains information you want returned to your application?

    a. Where

    b. From

    c. Select

    d. None of the above
  7. A stored procedure is sent from your web page to the DBMS each time you execute a query.

    a. True

    b. False
  8. You cannot pass information to a stored procedure.

    a. True

    b. False
  9. Your application cannot determine the number of rows that were returned by the DBMS.

    a. True

    b. False
  10. A query parameter is assigned values that visitors enter into a form on your web site.

    a. True

    b. False

Answers

  1. a. True
  2. d. None of the above. You use the Connection object available for your database type. For SQL Server, it is SqlConnect; for Access, OleDbConect; etc.
  3. b. The Page_Load subroutine
  4. d. Select
  5. b. Where
  6. d. None of the above
  7. b. False
  8. b. False
  9. b. False
  10. a. True

One thought on “Using Stored Procedures in Database Interaction

  1. This article is an excerpt from the book “ASP.NET 2.0 DeMYSTiFieD,” published by McGraw-Hill/Osborne. We hope you found it to be enjoyable and educational. Please let us know what you thought of it, and if you would like to see more content of this nature.

[gp-comments width="770" linklove="off" ]