Chapter 6: Data Manipulation with ADO.NET
4. Using Stored Procedures with ADO.NET
There are two ways of executing a SQL command from within an ASP.NET page. You can execute the command directly from code in the page, or you can package the SQL command as a stored procedure and execute the stored procedure from the page.
Building stored procedures takes a little more work than executing commands directly on a page. However, you can significantly increase the performance of a database-driven Web site by using stored procedures. A SQL statement must be parsed, compiled, and optimized by SQL Server whenever it is executed from an ASP.NET page. A stored procedure, on the other hand, needs to be parsed, compiled, and optimized only once.
Another advantage of using stored procedures is that they enable you to reuse the same SQL statements in multiple ASP.NET pages. If, at some later date, you need to make changes to your code, you can change it in only one place rather than in every page.
In any case, after you have set up the parameters for a SQL command (as you did in the previous section), executing the command as a stored procedure requires a trivial amount of work. For example, Listing 6.4.1 illustrates how you can convert the code in Listing 6.3.1 to use a stored procedure (this page is included on the CD as SQLStoredProcedure.aspx).
Listing 6.4.1 Using Stored Procedures
1: <%@ Import Namespace="System.Data" %> 2: <%@ Import NameSpace="System.Data.SqlClient" %> 3: 4: <% 5: Dim myConnection As SqlConnection 6: Dim myCommand As SqlCommand 7: Dim FirstName As String = "Robert" 8: Dim LastName As String = "Johnson" 9: 10: myConnection = New SqlConnection(
"server=localhost;uid=sa;pwd=secret;database=myData" ) 11: myConnection.Open() 12: myCommand = New SqlCommand( "InsertAuthors", myConnection ) 13: myCommand.CommandType = CommandType.StoredProcedure 14: 15: myCommand.Parameters.Add( New SqlParameter( "@FirstName", SqlDbType.Varchar, 30 )) 16: myCommand.Parameters( "@FirstName" ).Value = FirstName 17: 18: myCommand.Parameters.Add( New SqlParameter( "@LastName", SqlDbType.Varchar, 30 )) 19: myCommand.Parameters( "@LastName" ).Value = LastName 20: 21: myCommand.ExecuteNonQuery() 22: myConnection.Close 23: %> 24: Record Inserted! 25: 26:
When you execute the page contained in Listing 6.4.1, a new record is added to the Authors table with the following stored procedure named InsertAuthors:
create procedure InsertAuthors ( @FirstName Varchar( 50 ), @LastName Varchar( 50 ) ) AS Insert Authors ( FirstName, LastName ) VALUES ( @FirstName, @LastName )
To create this stored procedure in Microsoft SQL Server, launch the SQL Query Analyzer by going to Start, Programs, Microsoft SQL Server, Query Analyzer. Enter the preceding stored procedure and click Execute (the green VCR play button).
This stored procedure accepts two input parameters: @FirstName and @LastName. The input parameters are used in the SQL INSERT command to add a new record to the Authors table.
Two modifications were made to the ASP.NET page in Listing 6.2.17 to enable the page to execute the InsertAuthors stored procedure. First, instead of a SQL statement being passed to the SqlCommand object, the name of the stored procedure (InsertAuthors) is passed instead:
myCommand = New SqlCommand( "InsertAuthors", myConnection )
An additional line of code was also added to Listing 6.2.17. The following statement was added to set the CommandType property of the SQLCommand class to the value StoredProcedure:
myCommand.CommandType = CommandType.StoredProcedure
Using Output Parameters
The code in Listing 6.4.1 demonstrates how you can pass input parameters to a stored procedure. However, stored procedures can also have output parameters and return values. You can use both output parameters and return values to return information from a stored procedure.
Output parameters and return values return different types of data. A return value can only return an integer value. Whenever you execute a stored procedure, the stored procedure has a return value. By default, the return value is 0.
Output parameters can return values of most data types. For example, you can use an output parameter to return a Varchar, Money, or Integer value. However, all versions of SQL Server, including the current version (SQL Server 2000), do not support returning TEXT values as output parameters.
For example, the following stored procedure has an input parameter, an output parameter, and Listing 6.4.1 demonstrates a return value:
create procedure getLastName ( @FirstName Varchar( 50 ), @LastName Varchar( 50 ) Output ) As Select @LastName = LastName From Authors WHERE FirstName = @FirstName IF @LastName is Null Return( 0 ) ELSE Return( 1 )
The getLastName stored procedure accepts an input parameter named @FirstName and returns an output parameter named @LastName. If an author with the first name exists in the Authors table, a return value of 1 is retrieved. Otherwise, the stored procedure sends back a return value of 0.
The ASP.NET page in Listing 6.4.2 illustrates how you can use the getLastName stored procedure from within an ASP.NET page (this page is included on the CD as SQLInputOutput.aspx).
Listing 6.4.2 Retrieving Output Parameters and Return Values
1: <%@ Import Namespace="System.Data" %> 2: <%@ Import NameSpace="System.Data.SqlClient" %> 3:Listing 6.4.1 demonstrates 4: <% 5: Dim myConnection As SqlConnection 6: Dim myCommand As SqlCommand 7: Dim myParam As SqlParameter 8: 9: myConnection = New SqlConnection(
"server=localhost;uid=sa; pwd=secret;database=myDataPubs" ) 10: myConnection.Open() 11: 12: myCommand = New SqlCommand( "getLastName", myConnection ) 13: myCommand.CommandType = CommandType.StoredProcedure 14: 15: myParam = myCommand.Parameters.Add( New SqlParameter( "RETURN VALUE", SqlDbType.INT )) 16: myParam.Direction = ParameterDirection.ReturnValue 17: 18: myParam = myCommand.Parameters.Add( New SqlParameter( "@FirstName", SqlDbType.Varchar, 50 )) 19: myParam.Direction = ParameterDirection.Input 20: myParam.Value = "Robert" 21: 22: myParam = myCommand.Parameters.Add( New SqlParameter( "@LastName", SqlDbType.Varchar, 50 )) 23: myParam.Direction = ParameterDirection.Output 24: 25: myCommand.ExecuteNonQuery() 26: If myCommand.Parameters( "RETURN VALUE" ).Value Then 27: Response.Write( "The last name is " & MyCommand.Parameters( "@LastName" ).Value ) 28: Else 29: Response.Write( "No author found!" ) 30: END If 31: myConnection.Close() 32: %> 33:
In Listing 6.4.2, a return value parameter, input parameter, and output parameter are added to the Parameters collection of the SqlCommand class. The direction of the parameter is set with the Direction property of the SqlParameter class. For example, the following two statements are used to create the output parameter that Listing 6.4.1 demonstrates:
myParam = myCommand.Parameters.Add( New SqlParameter( "@LastName", SqlDbType.Varchar, 50 )) myParam.Direction = ParameterDirection.Output
After the command has been executed, the value of the parameters can be retrieved. The value of the output parameter is displayed in the following Response.Write() statement:
Response.Write( "The last name is " & Listing 6.4.1 demonstrates MyCommand.Parameters( "@LastName" ).Value )