Introduction

A well-designed application that uses a relational database management system in the backend should make extensive use of stored procedures. A stored procedure is a named collection of SQL statements that are stored in the database. To the client a stored procedure acts similar to a function. The stored procedure is called by name, can accept parameter values passed in, and can return parameter values back to the client. There are many advantages to incorporating stored procedures into your application logic including:

The purpose of this article is to demonstrate how stored procedures are created in SQL Server 2000 and consumed by clients written in VB.NET.

Note: In order to complete the activities outlined in this article you must have Visual Studio .NET installed and access to SQL Server 2000 with the Pubs database installed.

Creating a Stored Procedure

Creating a stored procedure is a fairly straightforward process and can be completed inside the Visual Studio IDE. Open Visual Studio and navigate to the Pubs database node in the Server Explorer window and expand the node. You should see a stored procedure node. By right clicking on the stored procedure node a popup menu will give you the option to create a new stored procedure. When you choose to create a new stored procedure the following code template will be presented in the Code Editor window.

CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN

The create procedure statement is used to create a new stored procedure and is followed by the procedure name. After the procedure name is declared, the parameters used (if any) by the stored procedure are declared. The AS key word follows the parameter declarations and is followed by the SQL code that makes up the body of the stored procedure. The RETURN key word is used to exit from the stored procedure and can be used to send an integer status value back to the caller. The following code creates a simple stored procedure that takes no parameters and returns a result set back to the caller.

CREATE PROCEDURE dbo.up_AuthorNames
AS
SELECT au_fname + ' ' + au_lname AS auName FROM authors
RETURN

Once you have entered the code into the Code Editor window, save the stored procedure. After saving the stored procedure it should show up under the Stored Procedure node in the Server Explorer window. Notice that the CREATE key word has been changed to the ALTER key word in the code editor window. The ALTER key word is used to make any changes to existing stored procedures. To test the stored procedure right click the procedure's node in the Server Explorer window and choose "Run Stored Procedure". The output from the stored procedure is written to the Output window. It should contain a list of the authors' names and a return value of 0 as shown in figure 2.

Using the Command Object to Execute a Stored Procedure

In order to access the stored procedure from a .NET client application, you use the System.Data.SqlClient namespace. This namespace contains the objects used to interact with SQL Server 7.0 and above. A SqlConnection object is used to establish a connection into the database. Once the connection is established a SqlCommand object is used to execute SQL statements or Stored Procedures. The SqlCommand object contains four different methods for executing statements against the database. The ExecuteReader method is used to execute commands that return records. The ExecuteNonQuery is used to execute commands that do not return records such as update and insert statements. The ExecuteScalar method is used to execute a command that returns a single value rather than a result set. The ExecuteXmlReader is used to execute a command that returns the results in an XML formatted string.

The CommandType property of the SqlCommand object is used to indicate what type of command is being executed. The CommandType property is set to one of three possible CommandType enumeration values. The default Text value is used when a SQL string is passed in for execution. The StoredProcedure value is used when the name of a stored procedure is passed in to execute. The TableDirect value is used when a table name is being passed in. This setting will pass back all the records in the table. The CommandText property of the SqlCommand object is used in conjunction with the CommandType property. The CommandText property will contain a SQL string, stored procedure name, or table name depending on the setting of the CommandType property.

In order to demonstrate the process of executing a stored procedure from a C# client, create a new console application project in Visual Studio named SPClient. Add a class to the project and rename it Authors. Add using statements above the namespace declaration to enable the use of non-fully qualified references to other namespace types.

Imports System
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Data
'Create a method procedure called GetNames in the class that takes no input parameters and returns an ArrayList to the caller.
Namespace SPDemo
' <summary>
' Summary description for Authors.
' </summary>
Public Class Authors
Public Sub New()
End Sub 'New
' TODO: Add constructor logic here
Public Function getNames() As ArrayList
End Function 'getNames
End Class 'Authors
End Namespace 'SPDemo

In the body of the method, create an instance of the SqlConnection class and pass in connection information with the instantiation of the SqlConnection object.

Dim cnPubs As New SqlConnection("server=localhost;integrated security=true;" + "database=pubs")

Note: This assumes you have a local instance of SQL Server and are logged on with a trusted connection.

Next, create a SqlCommand object and set the appropriate properties needed to execute the up_AuthorNames stored procedure created earlier.

Dim cmdAuthors As New SqlCommand("up_AuthorNames", cnPubs)cmdAuthors.CommandType = CommandType.StoredProcedure

The next step is to use the SqlCommand object to create an instance of the SqlDataReader class. The SqlDataReader class is used to read a forward only stream of records returned from the database. The SqlDataReader object is not instantiated directly through a constructor (hence the lack of the New key word) but rather through the ExecuteReader method of the SqlCommand object. Before calling the ExecuteReader method the connection to the database is established using the Open method of the SqlConnection object.

Dim drAuthors As SqlDataReader
cnPubs.Open()
drAuthors = cmdAuthors.ExecuteReader()

Now that the stored procedure has been executed, the Read method of the SqlDataReader object is used to read the records and pass them into an ArrayList that will be returned to the caller. The Read method returns a value of False when it reaches the end of the records. Before exiting the method, the close methods of the SqlDataReader and the SqlConnection objects are called.

Dim alNames As New ArrayList
While drAuthors.Read()
alNames.Add(drAuthors.GetValue(0))
End While
drAuthors.Close()
cnPubs.Close()
Return alNames

To test the method, place the following code in the Main procedure of Class1. This code instantiates an instance of the Authors class and calls the GetNames method. The list of names returned is then written out to the console. The ReadLine method of the Console class pauses execution until a keystroke is entered in the console screen.

'Entry point which delegates to C-style main Private Function
Public Overloads Shared Sub Main()
Main(System.Environment.GetCommandLineArgs())
End Sub
Overloads Shared Sub Main(ByVal args() As String)
Dim objAuthors As New Authors
Dim alNames As System.Collections.ArrayList = objAuthors.getNames()
Dim item As [String]
For Each item In alNames
Console.WriteLine(item)
Next item
Console.ReadLine()
End Sub 'MainCreating a Stored Procedure with Parameters

Now that you know how to create a basic stored procedure and call it from a VB.NET client, let's take a look at creating a more advanced stored procedure that includes parameters. Navigate to and expand the Pubs database node in the Server Explorer window of Visual Studio. Right click on the stored procedure node and select "Create a New Stored Procedure" from the popup menu. Change the name of the stored procedure to up_AuthorBookCount and add the following code to the body of the stored procedure.

CREATE PROCEDURE dbo.up_AuthorBookCount
(
@au_id varchar(11),
@Count int OUTPUT
)
AS
SET NOCOUNT ON
Select @Count = count(title_id) from titleauthor where au_id = @au_id
RETURN

The difference between this stored procedure and the previous one is the use of the parameter values. The parameters of the stored procedure are declared as local variables by preceding the name with an @ sign. The data type of the parameter is then declared along with the direction. Input parameters are passed in by the caller of the stored procedure and are the default type. Output parameters are returned back to the caller and are designated by the OUTPUT keyword. This stored procedure uses the author id passed in by the caller and returns the corresponding number of title ids in the titleauthor table. Once the stored procedure has been created, save it to the database.

Using the Parameters Collection to Pass Parameters To and From Stored Procedures

Calling a stored procedure that contains parameters from a VB.NET client is very similar to the previous process of executing stored procedure without parameters. A SqlConnection object is used to establish a connection to the database and a SqlCommand object is used to execute the stored procedure. The difference when calling a parameterized stored procedure is the use of the Parameters collection of the SqlCommand object. When the parameter is added to the collection, the appropriate properties such as ParameterName, DbType, Size, and Value are set.

In order to demonstrate the process of executing a parameterized stored procedure from a VB.NET client, open the previous console application project in Visual Studio. Create a method in the Authors class called GetBookCount that takes an input parameter of type string and returns an integer type to the caller.

Public Function getBookCount(ByVal AuthorID As String) As Integer
Return 0
End Function 'getBookCount

In the body of the method, create an instance of the SqlConnection class and pass in connection information with the instantiation of the SqlConnection object.
Dim cnPubs As New SqlConnection("server=localhost;integrated security=true;" + "database=pubs")

Next, create a SqlCommand object and set the appropriate properties needed to execute the up_AuthorBookCount stored procedure created earlier.

Dim cmdAuthors As New SqlCommand("up_AuthorBookCount", cnPubs)cmdAuthors.CommandType = CommandType.StoredProcedure

Using the Add method of the SqlCommand's Parameter collection add an input parameter that takes the AuthorId value passed in by the caller. Also add an output parameter that will store the value passed back by the stored procedure. The names and data types of the parameters must mach those defined in the stored procedure.

cmdAuthors.Parameters.Add("@au_id", SqlDbType.NVarChar, 11)cmdAuthors.Parameters("@au_id").Value = AuthorID '
cmdAuthors.Parameters.Add("@Count", SqlDbType.Int)
cmdAuthors.Parameters("@Count").Direction = ParameterDirection.Output
'method of the command object

Once the stored procedure is executed, the value of the output parameter is held in a local variable, which is in turn passed back to the client. Don't forget to close the connection after executing the stored procedure.

cnPubs.Open()
Dim iCount As Integer
cmdAuthors.ExecuteNonQuery()
iCount = CInt(cmdAuthors.Parameters("@Count").Value)
cnPubs.Close()
Return iCount

To test the method, comment out the previous code in the Main procedure of Class1. Add the following code to the Main procedure. This code instantiates an instance of the Authors class and calls the GetBookCount method. The book count returned is then written out to the console. The final ReadLine method call of the Console class is used to pause execution until a keystroke is entered in the console screen.

Dim objAuthors As New Authors
Console.WriteLine("Enter an author id.")
Dim authorID As String = Console.ReadLine()

Console.WriteLine(objAuthors.getBookCount(authorID))
Console.ReadLine()

Run the application and enter a value of "213-46-8915" for the author id. This should return a book count of 2.

Summary

This article introduced you to creating stored procedures for SQL Server 2000 and executing the stored procedure from C# code. While not all of the business logic of an application should be developed within stored procedures, there are many benefits to encapsulating the data access logic and the business logic within stored procedures. This generally allows for enhanced scalability, extensibility, security and efficient use of the network resources. Using Visual Studio you can easily develop and test the stored procedures from within the same IDE that you use to develop your applications. The SqlCommand object is used to execute stored procedures from C# code.  The Parameters collection of the SqlCommand object is used to pass parameter values to and from the stored procedure.

This article focused on a connected scenario in which the data was returned through a SqlDataReader object or an output parameter. A future article will concentrate on a disconnected scenario using the SqlDataAdapter class and its use of parameters when updating data back to the database.

Note: The code presented in this article is for demonstration purposes only. Proper error handling has been omitted for clarity.