Calling Stored Procedures | MCAD/MCSD: Visual Basic .NET XML Web Services and Server Components Study Guide

A stored procedure is any Structured Query Language (SQL) statement or set of statements that are saved on the database server along with the database definition. The Microsoft SQL Server database uses its own programming language, called Transact-SQL (or T-SQL for short), to write these queries. Transact-SQL is based on the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) standard SQL language published in 1992 (Microsoft SQL Server 2000 supports the Entry Level of SQL-92). T-SQL also includes programming features beyond just standard SQL instructions, such as conditional logic, standard operators, variables, built-in functions, and system variables, so stored procedures can be quite complex.

There are a lot of advantages to using stored procedures as an alternative to generating all SQL statements in your application code:

When you send a SQL string from your application code, the database server must check the syntax of the SQL statement, verify that table and field names are correct, and then create a plan before each execution of the query. Stored procedures are compiled the first time they are run, and this information is saved, so subsequent calls to them run quickly.
Stored procedures can be a security improvement as well. The database administrator (DBA) grants permission to execute the stored procedures, rather than granting full access to the underlying database tables. Users of your application can run the stored procedures, but cannot access the data in any other way.
Maintenance can be improved too. Because stored procedures are all located in one place, any changes that need to be made can be done once, and applications that call the stored procedures can continue to use the revised procedures without having to recompile or redeploy the application.

Although you can use the tools that come with Microsoft SQL Server to create and maintain stored procedures, Visual Studio .NET gives you the ability to do this as well. The Server Explorer window enables you to access any SQL Server installation on your network (assuming you have the appropriate permissions to do so) or your development workstation. Figure 5.1 shows the Server Explorer, the pubs sample database, and the listing of stored procedures in pubs.

Note

We will be working with the stored procedure called byroyalty in the upcoming examples.

click to expand
Figure 5.1: Viewing stored procedures with the Server Explorer

When you are using the Server Explorer, just expand the Servers node, expand the computer name that you are interested in, and then continue drilling down through SQL Servers. You should see the database names, and by expanding those you can see the database tables and columns. If you right-click one of the table names, the menu offers choices such as Retrieve Data from Table and Design Table. When you expand the Stored Procedures node, you will see a list of all procedures. When you expand one of the procedure names, you see a list of the parameters that the procedure accepts and the list of data fields that it will return.

You can edit the stored procedure directly from the Server Explorer. Using the pubs sample database, right-click the byroyalty stored procedure name and choose Edit Stored Procedure from the menu. This is a simple procedure that returns the Author ID (au_id) column from the TitleAuthor table. It accepts one input parameter that is used in the SQL WHERE clause. The WHERE clause selects only those authors who have a value matching the input parameter, in their Royalty Percentage (royaltyper) column. Notice that Transact-SQL uses the single @ character in front of the names of local variables and parameters. Listing 5.7 shows the complete code of this procedure.

Listing 5.7: The byroyalty Stored Procedure from the pubs Sample Database

ALTER PROCEDURE byroyalty @percentage int AS SELECT au_id from titleauthor WHERE titleauthor.royaltyper = @percentage

You can also test the stored procedure. Right-click the procedure name and choose Run Stored Procedure from the menu. Because the byroyalty stored procedure requires an input parameter in order to run, a dialog box pops up requesting you to fill in the value for the percentage parameter. Figure 5.2 shows the Run Stored Procedure dialog box.

click to expand
Figure 5.2: The Run Stored Procedure dialog box

Type in a value (some valid values are 100, 50, 25) and click OK. The results of the stored procedure can be viewed in the Output window. If this window doesn’t display automatically after the procedure runs, choose View Ø Other Windows Ø Output from the menu to display it. Figure 5.3 shows the results of the query displayed in the Output window.

click to expand
Figure 5.3: Query results in the Output window

When you want to call a stored procedure from your code, you can create a Command object. In the following example, we set the CommandText property to the name of the stored procedure and set the CommandType property to StoredProcedure. Then we add parameters to the Command object’s Parameters collection, setting the properties for each parameter as we add it. Table 5.7 lists the properties of the Parameter object. Most properties are supported by both the SqlParameter object and OleDbParameter object; those that are not are noted in the table.

Listing 5.8 shows how to create the parameter and then continues to set two additional properties separately: Direction and the Value that we are assigning.

Table 5.7: Properties of *SqlParameter* and *OleDbParameter*
Property	Description
DbType	Gets or sets the data type of the parameter
Direction	Gets or sets a value indicating whether the parameter is Input, Output, InputOutput, or a stored procedure ReturnValue
IsNullable	Gets or sets a value indicating whether the parameter accepts null values
Offset	Gets or sets the offset to the Value property—SqlParameter only
OleDbType	Gets or sets the OleDbType of the parameter—OleDbParameter only
ParameterName	Gets or sets the name of the Parameter object
Precision	Gets or sets the maximum number of digits used to represent the Value property
Scale	Gets or sets the number of decimal places to which Value is resolved
Size	Gets or sets the maximum size, in bytes, of the data within the column
SourceColumn	Gets or sets the name of the source column
SourceVersion	Gets or sets the DataRowVersion to use when loading Value
SqlDbType	Gets or sets the SqlDbType of the parameter—SqlParameter only
Value	Gets or sets the value of the parameter

Listing 5.8: Calling a Stored Procedure with an Input Parameter

Private Function GetAuthorsByRoyalty(ByVal percentRoyalty _    As Integer) As SqlDataReader    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = _        "Data Source=localhost; Initial " & _        "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()    Dim myProc As SqlCommand = _       New SqlCommand("byroyalty", myConn)    myProc.CommandType = CommandType.StoredProcedure    myProc.Parameters.Add("@percentage", _       SqlDbType.Int).Value= percentRoyalty    Dim myProcReader As SqlDataReader    myProcReader = myProc.ExecuteReader( _       CommandBehavior.CloseConnection)    Return myProcReader End Function

In this case, the Value property is passed into the function when it is called. The final step is to add the SqlParameter to the SqlCommand.Parameters collection. Then we are ready to execute the command.

Exercise 5.3 gives you an opportunity to create a new stored procedure in the pubs sample database and then write code to call that procedure.

Exercise 5.3: Creating and Calling Stored Procedures

Setting Up the Project:

Start a new Visual Studio .NET ASP.NET Web Application project. Set the location to http://localhost/StoredProcedureExamples. Use your own web server name in place of localhost if appropriate.
Change the name of WebForm1.aspx to default.aspx.
Use the Properties window to change the pageLayout property of the document to FlowLayout.
Using the Visual Studio .NET Toolbox, drag a Web Forms Label, DataGrid, and HyperLink controls to the design surface of default.aspx. Your page should look like the following screen.
Right-click default.aspx in the Solution Explorer and choose View Code. Add the Imports statement at the top of the code module.
```
Imports System.Data.SqlClient
```

Create a function procedure called GetStoreList. Add code to connect to the pubs sample database and issue a SQL command to retrieve all the data in the Stores table. This function will return a SqlDataReader. Here is the code to do this:

Private Function GetStoreList() As SqlDataReader    Dim myConn As SqlConnection = New SqlConnection()    Dim myQuery As SqlCommand = _       New SqlCommand("SELECT * FROM stores", myConn)    myQuery.CommandType = CommandType.Text    myConn.ConnectionString = _      "Data Source=localhost; Initial " & _      "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()    Dim myReader As SqlDataReader    myReader = myQuery.ExecuteReader( _       CommandBehavior.CloseConnection)    Return myReader End Function

Call the GetStoreList function from the Page_Load event procedure and bind the returned SqlDataReader to the DataGrid:

Private Sub Page_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    Dim localReader As SqlDataReader    localReader = GetStoreList()    DataGrid1.DataSource = localReader    DataGrid1.DataBind()    LocalReader.Close() End Sub

Save and test your work. You project should look something like the following.

Creating a New Stored Procedure:
Open the Server Explorer window and expand nodes until you can see the pubs database stored procedures. Right-click Stored Procedures and choose New Stored Procedure from the menu.

You will see a basic format for Transact-SQL stored procedures in the Code Editor window. You will create a stored procedure to insert a new entry into the Stores table. Write the code as shown:
```
CREATE PROCEDURE dbo.InsertNewStore    (       @storeid char(4),       @storename varchar(40),       @storeaddress varchar(40),       @city varchar(20),       @state char(2),       @zip char(5)    ) AS INSERT stores (stor_id, stor_name, stor_address, city, state, zip) VALUES (@storeid, @storename, @storeaddress, @city, @state, @zip); GRANT EXECUTE ON InsertNewStore TO public
```
Notice that after you have saved the procedure for the first time, the statement on the first line changes from CREATE PROCEDURE to ALTER PROCEDURE. The statement on the last line is necessary so that your sample application will have permission to run the stored procedure:
```
GRANT EXECUTE ON InsertNewStore TO public 
```
Right-click your new procedure and choose Run Stored Procedure to test it. Fill in appropriate values in the Run Stored Procedure dialog box.
Right-click the Stores table and choose Retrieve Data From Table to view the data and verify that your new item has been added.

Note: After the first time you test the stored procedure, remove the GRANT statement and save the procedure.

Creating a Web Page for User Input and Calling the Stored Procedure:
In the Solution Explorer window, right-click your project name and choose Add Web Form. Name the new form AddStore.aspx.
Use the Properties window to change the pageLayout property of the document to FlowLayout.
Using the Visual Studio .NET Toolbox, drag six Web Forms TextBox controls and an HTML Submit button to the design surface of default.aspx. Use the following names for the TextBox controls:
- txtID
- txtName
- txtAddress
- txtCity
- txtState
- txtZip
Add descriptive Label controls. Your page should look like the following.
Right-click AddStore.aspx in the Solution Explorer and choose View Code. Add the Imports statement at the top of the code module.
```
Imports System.Data.SqlClient 
```

Add code to the Page_Load event procedure for AddStore.aspx:

Private Sub Page_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    If Page.IsPostBack Then        Dim recsAdded As Integer        Dim myConn As SqlConnection = New SqlConnection()        Dim myProc As SqlCommand = _           New SqlCommand("InsertNewStore", myConn)        myProc.CommandType = CommandType.StoredProcedure        myConn.ConnectionString = _          "Data Source=localhost; Initial " & _          "Catalog=pubs; Integrated Security=SSPI; "        myConn.Open()        myProc.Parameters.Add("@storeid", _           SqlDbType.Char, 4).Value= txtID.Text        myProc.Parameters.Add("@storename", _           SqlDbType.VarChar, 40).Value = txtName.Text        myProc.Parameters.Add("@storeaddress", _           SqlDbType.VarChar, 40).Value = txtAddress.Text        myProc.Parameters.Add("@city", _           SqlDbType.VarChar, 20).Value = txtCity.Text        myProc.Parameters.Add("@state", _           SqlDbType.Char, 2).Value = txtState.Text        myProc.Parameters.Add("@zip", _           SqlDbType.Char, 5).Value = txtZip.Text        recsAdded = myProc.ExecuteNonQuery()        If recsAdded = 1 Then            Response.Redirect("default.aspx")        Else            Response.Write("Record could not be added.")        End If        MyConn.Close()    End If End Sub

Your code will:

Connect to the database.
Create a SqlCommand.
Create the six parameters that are required to send the value from the text boxes to the stored procedure.
Call the stored procedure.
Check the return value of the SqlCommand.ExecuteNonQuery method.
If the return value is something other than 1, you give an error message.
If the return value is 1, you redisplay the default.aspx page.

Back on the design surface of default.aspx, set the Text property of the Hyperlink control to Add New Store and the NavigateURL property to AddStore.aspx.
Save and test your work. You will be adding to this project in Exercise 5.4.

In Exercise 5.4, you will call a stored procedure that returns multiple results and use the DataReader.NextResult method to access all of the data.

Exercise 5.4: Accessing Multiple Resultsets

Start a new Visual Studio .NET ASP.NET Web Application project. Set the location to http://localhost/MutilpleResultExamples. Use your own web server name in place of localhost if appropriate.
Change the name of WebForm1.aspx to default.aspx.
Use the Properties window to change the pageLayout property of the document to FlowLayout.
Use the Server Explorer to locate the stored procedure called reptq1 in the pubs sample database. Remove the two COMPUTE statements at the end of the procedure and replace them with ELECT statements. Your stored procedure should look like this:
```
ALTER PROCEDURE reptq1 AS SELECT pub_id, title_id, price, pubdate from titles where price is NOT NULL order by pub_id SELECT avg(price) from titles
```
Run the stored procedure and view the results in the Output window.
Right-click AddStore.aspx in the Solution Explorer and choose View Code. Add the Imports statement at the top of the code module.
```
Imports System.Data.SqlClient
```

Add code to the Page_Load event procedure of default.aspx to call the stored procedure and display the results. After you loop through the first resultset and display the rows of data that were returned for the first SELECT statement, you can call the NextResult method and move to the average price value that is returned from the second SELECT statement in the stored procedure. Here is the code:

Private Sub Page_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = _        "Data Source=localhost; Initial " & _        "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()    Dim myProc As SqlCommand = _       New SqlCommand("reptq1", myConn)    myProc.CommandType = CommandType.StoredProcedure    Dim myProcReader As SqlDataReader    myProcReader = myProc.ExecuteReader()    Do While myProcReader.Read()        Response.Write(myProcReader.GetString(0) & ", " & _           myProcReader.GetString(1) & ", " & _           myProcReader.GetDecimal(2).ToString & ", " & _           myProcReader.GetDateTime(3) & "<BR>")    Loop    myProcReader.NextResult()    myProcReader.Read()    Response.Write("The Average price of a book is: " & "<BR>")    Response.Write(myProcReader.GetDecimal(0).ToString)    myProcReader.Close()    myConn.Close() End Sub

Save and test your work. Your results should look like the following.