Using Stored Procedures in Database Interaction
(Page 1 of 4 )
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.
Next: Calling a Stored Procedure >>
More ASP.NET Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter 10 of ASP.NET 2.0 DeMYSTiFieD, written by Jim Keogh (McGraw-Hill/Osborne; ISBN: 0072261412). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 6 
