11.7 Stored Procedures

Until now, you've
interacted with the database using nothing but SQL statements.
Many real-world applications interacting with SQL Server or other
large databases will use stored procedures. Stored procedures can be
compiled by the database, and, thus, offer better performance.

The easiest way to create a stored
procedure (often referred to as a
sproc) is to begin with a working SQL
statement. If you return to Example 11-6, you will
find two SQL Select
statements. The first is in the CreateBugDataSet method:

If you extract the SQL from this statement and insert it into
the SQL Server Query analyzer,
you can run that statement and get back the Bug records, as shown in
Figure 11-18.

Figure 11-18. Executing the query in Query Analyzer

You are now ready to drop this into a new stored procedure, which you
will name spBugs. In SQL Server the easiest way to do this is to
right-click on the Stored Procedures listing in SQL Enterprise
Manager, as shown in Figure 11-19.

Figure 11-19. Creating a new stored procedure

This opens the New Stored Procedure window. Preface the
select statement with the string "CREATE
PROCEDURE spBugs AS" to create a new sproc named
spBugs, as shown in Figure 11-20.

Figure 11-20. Saving the new sproc

The second SQL select statement in Example 11-6 is
slightly more complicated:

11.7.1 Invoking the Stored Procedure Programmatically

To use stored
procedures rather than a simple SQL select
statement, you need modify only the CreateBugDataSet and
CreateBugHistoryDataSet methods. CreateBugDataSet will invoke
spBugs with no parameters. CreateBugHistoryDataSet
will invoke spBugHistory, passing in the chosen
BugID as a parameter.

11.7.1.1 Invoking a sproc with no parameters

The rewrite to CreateBugDataSet is very straightforward.
You'll remember from Example 11-6
that your steps were as follows:

Finally, you created a data adapter and you set its Command object to
the Command object you just built. You added the table mappings,
created a dataset, filled the dataset, and returned the dataset.

The steps with a stored procedure are identical
except for step 3. Rather than building an SQL statement,
you'll instead set the command text to the name of
the sproc, and you'll set the Command
object's CommandType property to
CommandType.StoredProcedure:

11.7.2 Invoking a Stored Procedure with Parameters

To invoke the sproc
spBugHistory, you will need to pass in the BugID.
There are two ways to do this. The first option is simply to invoke
the sproc name and its argument in the CommandText property:

command.CommandText= "spBugHistory " + bugID;

The second option is to create explicit Parameter objects.
You'll explore each of these options in turn.

11.7.2.1 Inline arguments

To see the first
option at work, modify the CreateBugHistoryDataSet method, changing
only step 3 as described above for CreateDataSet. Rather than
building the SQL Select statement,
you'll invoke the sproc directly:

command.CommandText= "spBugHistory " + bugID;

When the user clicks on the bug whose ID is 2, this will set the
command text equal to spBugHistory 2. You would
like to set the CommandType property to
CommandType.StoredProcedure but you may not do so
with an "in line" parameter. If you
do, the compiler will look for a sproc named spBugHistory
2, and since no such sproc exists, an error will be
generated. You must instead set the CommandType property to
Command.CommandText, which is somewhat less
efficient.

The complete C# replacement for CreateBugHistoryDataSet is shown in
Example 11-14.

11.7.2.2 Invoking a sproc with explicit parameters

Implicit parameters are straightforward and
easy to use. Unfortunately, if you need a return (out) parameter to
get a result back, you will need to use explicit Parameter objects.
Many programmers also use explicit parameters when they have a large
number of parameters. In any case, explicit parameter invocation is
more efficient.

The SqlCommand object and its cousin OleDbCommand both expose a
Parameters collection that can contain any number of Parameter
objects.

To use an explicit parameter, you add it to the Parameters collection
by calling the Add method. The return value is a reference to an
object of type Parameter. You may then modify that
object's properties, setting its direction (e.g.,
Input, Output, or InputOutput)
as well as its value, as the following code fragment shows:

Now that you are using an explicit Parameter object, you can modify
the command text to be just the name of the stored procedure, and you
may modify the CommandType property to be the more efficient
CommandType.StoredProcedure. The complete C#
replacement for CreateBugHistoryDataSet is shown in Example 11-16.

11.7.2.3 Return values from a sproc

You can imagine that your stored
procedure might return the total number of history items found when
you pass in a BugID. To capture this return value, you will need an
output parameter. To experiment with output parameters you will add a new
sproc, SpBugHistoryCount, which will take two
parameters: @BugID, and a new parameter,
@TotalFound. The stored procedure is written as
follows:

Thus, if the user selects a bug, you'll run the
sproc and display the total number of bugs found. The implementation
of TotalRecordsFound is fairly straightforward:

Create the connection and command objects.

Set the command text to the name of the sproc and set the command
type to StoredProcedure.

Set up the two parameters, remembering to set their direction.

Invoke the sproc.

Extract the values.

What is new this time, however, is that rather than using the sproc
to fill a dataset or even a data adapter, you need only run the sproc
and get back the output value in the Parameters collection of the
command object. To make this most efficient, the command object
offers a ExecuteNonQuery method. This highly efficient method simply
executes the SQL statement (in this case the sproc) but does not
return a dataset. You can use ExecuteNonQuery when you need to poke
the database but do not need to get back records. For
Update, Insert, and
Delete statements, ExecuteNonQuery returns the
number of rows affected; otherwise it returns -1.

To extract the value from the output parameter, you must first
extract it from the Parameters collection. You may use the name of
the parameter as an index into the
collection:

param = command.Parameters["@TotalFound"];

The Parameter object has a Value property which is an object. You
must cast that object to the appropriate type, in this case int:

int val = (int) param.Value;

The TotalRecordsFound method returns a string. You can easily turn
the int into a string because int, like all
objects, implements ToString:

string output = val.ToString( );

You can, of course, combine all these steps in your
return statement:

return command.Parameters["@TotalFound"].Value.ToString( );

The complete C# source code for the
TotalRecordsFound method is shown in Example 11-18.