HomeASP.NET Using Stored Procedures in Database Intera...
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).
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値l 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値l find that some stored procedures, such as those used to insert a new row into the database, don稚 require a return value, while others, such as procedures for counting the number of customers, do require one.
Let痴 take a look at a simple stored procedure that will count the number of customers there are in the custContact table. We値l 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値l 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値l see in the next chapter, you can replace the asterisk with a column name. As you値l 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.
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値l 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痴 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%>")
Many times you値l 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痴 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値l 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.
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稚 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値l focus on how to create more complex queries in the next chapter.
ASP.NET 
/ 7 
ASP.NET Export to Excel and Word using VB.NET and C#
