Using Stored Procedures in Database Interaction - Passing Parameters to a Stored Procedure
(Page 3 of 4 )
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.
Next: Quiz >>
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.
|
|
/ 7 
