Programmatically Retrieving a Stored Procedure's Parameters
By Scott Mitchell
Introduction Stored procedures in SQL Server are similar to methods in C# and Visual Basic code.
They encapsulate one or more statements into a single, parameterized construct. Both stored procedures and methods are a form of code reuse and
their use help developers adhere to the DRY principle (Don't Repeat
Yourself). But the similarities don't end there. The .NET Framework has a feature called Reflection
that enables developers to programmatically retrieve a list of methods for a given class, along with their input parameters and return types. It's also
possible to programmatically determine what stored procedures exist in a database, along with each stored procedure's input and output parameters.
Being able to programmatically retrieve a database's stored procedures and determine their parameters are useful in a handful of scenarios. For example,
code generators like CodeSmith and the Typed
DataSet feature in Visual Studio use these techniques to determine the code to construct to call each stored procedure. These techniques are also
useful for allowing ad-hoc stored procedure execution from a page on your website, which can be a useful tool for administrators.
This article shows how to retrieve a list of stored procedures in a database and how to enumerate a selected stored procedure's input and output
parameters. We'll also look at how to let the user visiting the page pick a stored procedure, enter values for its parameters, and execute and view the
resulting output. Read on to learn more!
An Overview of the Demo Application
The download available at the end of this article includes a web page with a DropDownList Web control enumerating the stored procedures in the website's
database. Selecting a stored procedure form the DropDownList causes a postback. On postback the stored procedure's input and output parameters
are each displayed in a BulletedList control. In addition, the input parameters are listed in a two-column GridView: the first column lists each
parameter's name, while the second has a TextBox control. The user visiting the page can enter values for the input parameters into these TextBoxes
and then click the "Execute Stored Procedure" Button. Doing so displays the results in another GridView further down on the page.
The following screen shot shows this demo page in action.
The DropDownList at the top of the page lists the stored procedures in the database.
In the screen shot below I have selected the aspnet_Membership_GetAllUsers stored procedure, which is one of the stored procedures
used by the SqlMembershipProvider. (For more information on ASP.NET's Membership feature, read Examining
ASP.NET's Membership, Roles, and Profile.)
The selected stored procedure's input and output parameters are enumerated in bulleted lists. Note that there are three input parameters for this
stored procedure: @ApplicationName, @PageIndex, and @PageSize. These input parameters are listed alongside TextBoxes
in a GridView further down in the page. A user can enter values for these parameters, click the "Execute Stored Procedure" button, and see the results
in a GridView at the bottom of the page. Granted, the GridView contains rather raw-looking, unformatted data, but the data is there nevertheless.
The remainder of this article looks at how various portions of this demo were implemented.
Retrieving a List of Stored Procedures
Every Microsoft SQL Server database includes a number of system tables that catalog the various objects that comprise the database, including
the database's tables (and their columns), its triggers, its stored procedures, its UDFs, and so on. The "core" system table, and the one that contains
a list of the database's stored procedures, is named sysobjects. Each stored procedure is represented as a row in sysobjects.
Likewise, each table and constraint and view and so on are represented by rows in this table. The different database objects can be differentiated by the
value in the sysobjects.type column - stored procedures have a type value of P.
The following SQL query returns the name of every stored procedure in the database:
SELECT name
FROM sysobjects
WHERE type = 'P'
The DropDownList in the demo is populated by a SqlDataSource control that runs the above query. It's that simple.
Retrieving a Stored Procedure's Parameters
Once a stored procedure has been selected, how do we retrieve its parameters? While this information can be retrieved from SQL Server's system
tables, there's an easier way: by calling the SqlCommandBuilder
class's DeriveParameters
method. This method accepts a SqlCommand object as its input and populates the SqlCommand object's Parameters
collection with the specified stored procedure's parameters.
The following code illustrates the SqlCommandBuilder.DeriveParameters method in action. (Note: While this article shows Visual
Basic code, the demo available for download at the end of this article includes both a VB and C# version.)
' ... Create SqlConnection object
Dim myCommand As New SqlCommand
myCommand.Connection = myConnection myCommand.CommandText = stored procedure name
myCommand.CommandType = Data.CommandType.StoredProcedure
While I don't show the code in the above snippet, the first step is to create a valid SqlConnection object to the database (which I named
myConnection in the code above). Next create a SqlCommand object, and associate it with the SqlConnection object.
Set the CommandText property to the name of the stored procedure whose parameters you want and set the CommandType property to
StoredProcedure. All that remains is to open a connection to the database and call the SqlCommandBuilder.DeriveParameters method,
passing in the SqlCommand object. This populates the passed-in SqlCommand object's Parameters collection with
the specified stores procedure's parameter information.
The Parameters property is a collection of SqlParameter
objects. Each SqlParameter object represents a parameter in a parameterized query or stored procedure. The most germane properties of
the SqlParameter class are:
ParameterName - the name of the parameter.
SqlDbType - the parameter's data type.
Direction - indicates whether the parameter is an input parameter, is an output parameter, or can serve as both an
input and output parameter. As discussed in Retrieving Scalar Data from a
Stored Procedure, output parameters are useful for returning scalar values from a stored procedure.
Value - the parameter's value.
Once you have the parameters you can close the connection to the database and work with the parameters. The following code snippet continued the
code from above by enumerating the returned parameters, displaying each parameter (along with its data type) in the appropriate BulletedList control
depending on whether the parameter serves as an input or output parameter.
For Each param As SqlParameter In myCommand.Parameters
If param.Direction = Data.ParameterDirection.Input OrElse param.Direction = Data.ParameterDirection.InputOutput Then
blInputParameters.Items.Add(param.ParameterName & " - " & param.SqlDbType.ToString())
Else
blOutputParameters.Items.Add(param.ParameterName & " - " & param.SqlDbType.ToString())
End If
Next
Prompting the User for Input Parameter Values and Executing the Stored Procedure With Those Values
In addition to the two BulletedList controls the demo page also includes a GridView named gvParameters that lists the selected stored procedure's input parameters
along with a TextBox for each parameter. In particular, this GridView is programmatically bound to a list of SqlParameter objects (namely, the set of
input parameters returned by SqlCommandBuilder.DeriveParameters) and is composed of two fields:
A BoundField that displays each SqlParameter object's ParameterName property, and
A TemplateField that contains a TextBox control named ParameterValue.
Additionally, the gvParameters GridView's DataKeyFields property is set to ParameterName.
The page also includes an "Execute Stored Procedure" Button control and another GridView for the stored procedure results. This results GridView
is named gvResults. When the "Execute Stored Procedure" Button is clicked the following code runs:
' ... Create SqlConnection object
Dim myCommand As New SqlCommand
myCommand.Connection = myConnection myCommand.CommandText = stored procedure name
myCommand.CommandType = Data.CommandType.StoredProcedure
'Get the parameter values from the grid
For Each gvRow As GridViewRow In gvParameters.Rows
'Determine the parameter name
Dim paramName As String = gvParameters.DataKeys(gvRow.RowIndex).Value.ToString()
Dim paramValue As Object = DBNull.Value
'Get the TextBox in the row
Dim paramValueTextBox As TextBox = CType(gvRow.FindControl("ParameterValue"), TextBox)
If Not String.IsNullOrEmpty(paramValueTextBox.Text) Then
paramValue = paramValueTextBox.Text
End If
'Add the parameter name/value pair to the SqlCommand
myCommand.Parameters.AddWithValue(paramName, paramvalue)
Next
The code starts by configuring a SqlCommand object that will execute the specified stored procedure. In this case we don't need to
go off and programmatically determine the stored procedure's parameters because they are already encompassed by the gvParameters GridView.
Each row in the gvParameters GridView is enumerated and the parameter's name and user-entered value are grabbed and added to the
SqlCommand object's Parameters property via the AddWithValue(name, value) method.
After the parameters have been added to the SqlCommand object the results are bound to the gvResults GridView.
That's all there is to it!
Conclusion
Programmatically retrieving the list of stored procedures in a database and those stored procedures' parameters are quite easy tasks. The database
provides both the stored procedures and their parameters through system tables. Additionally, the parameters for a stored procedure can also be
retrieved via the .NET Framework's SqlCommandBuilder.DeriveParameters method.
The demo available at the end of this article implements a quick and dirty dynamic reporting system. In short, it allows the visitor to execute
any stored procedure on the database through a single page. Granted, the results are ugly and there's nothing stopping the visitor from executing any
stored procedure she chooses, but such a page, when limited to a section accessible only to administrators, might be useful. In fact, with a little more
effort it is possible to enhance this functionality to create a more user-friendly and secure general purpose reporting page. A future article will
explore such functionality in greater detail.
