Recipe 13.5. Using Stored Procedures

Problem

You need to use a stored procedure in your database, and you're not sure how to specify values for its input and output parameters.

Solution

Use the command object's Parameters collection to set and retrieve stored procedure argument values.

Discussion

Here's a simple SQL Server stored procedure that does nothing more than retrieve a field from a table given its ID value:

 CREATE PROCEDURE GetRecordName    @PriKey int,    @NameResult varchar(50) OUT AS BEGIN    -- Given an ID value, return the RecordName field.    SET @NameResult =       (SELECT RecordName FROM Table1 WHERE ID = @PriKey); END 

To use this stored procedure, create a command object that calls it, and add separate input and output parameters:

 ' ----- Connect to the database. Dim connectionString As String = _    "Data Source=MySystem\SQLEXPRESS;" & _    "Initial Catalog=MyDatabase;Integrated Security=true" Dim theDatabase As New SqlClient.SqlConnection(connectionString) theDatabase.Open() ' ----- Build the basic command. Dim theCommand As New SqlClient.SqlCommand() theCommand.CommandType = CommandType.StoredProcedure theCommand.Connection = theDatabase theCommand.CommandText = "GetRecordName" ' ----- Add the input parameter. In this case, '       use a test value of 25. theCommand.Parameters.AddWithValue("@PriKey", 25) ' ----- Add the output parameter. Dim outParam As SqlClient.SqlParameter = _    theCommand.Parameters.Add( _    "@NameResult", SqlDbType.VarChar, 50) outParam.Direction = ParameterDirection.Output ' ----- Run the stored procedure. theCommand.ExecuteNonQuery() ' ----- The parameter has been updated for us. MsgBox(outParam.Value) ' ----- Clean up. theCommand = Nothing theDatabase.Close() theDatabase.Dispose() 

Stored procedures come in a variety of styles. The most basic stored procedure simply returns a set of records, just like a SELECT statement does. Other stored procedures return their results either as return values or as output parameters. ADO.NET supports all these various kinds of stored procedures.

Stored procedures are called using a standard command object, but instead of supplying a SQL statement for the command text, you supply only the name of the stored procedure. Any input and output parameters are added through the command object's Parameters collection. The names given to the parameters in each Parameter object match those included in the stored procedure definition.

Basic input parameters can be added simply with the AddWithValue() method:

 theCommand.Parameters.AddWithValue("@PriKey", 25) 

Output parameters require a little more configuration:

 Dim outParam As SqlClient.SqlParameter = _    theCommand.Parameters.Add( _    "@NameResult", SqlDbType.VarChar, 50) outParam.Direction = ParameterDirection.Output 

The Direction property indicates how this parameter is used by the stored procedure. It can be set to one of the following enumerated values:

• ParameterDirection.Input

• ParameterDirection.Output

• ParameterDirection.InputOutput

• ParameterDirection.ReturnValue

Once the parameters have been added, execute the stored procedure as you would any other command object:

 theCommand.ExecuteNonQuery() 

If the stored procedure returns a set of records, use ExecuteReader() instead of ExecuteNonQuery() to access those records.

Once processed, ADO.NET automatically updates any output Parameter objects for you. Access the Value properties of these objects to retrieve the stored procedure results.