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-5, you will
find two SQL Select
statements. The first is in the
CreateBugDataSet method:
StringBuilder s = new StringBuilder( "Select b.BugID, h.BugHistoryID, b.Description,h.Response, "); s.Append("o.FullName as owner, "); s.Append("p.ProductDescription, "); s.Append("r.FullName as reporter, "); s.Append("s.StatusDescription, "); s.Append("sev.SeverityDescription, "); s.Append("h.DateStamp "); s.Append("from "); s.Append( "(select bugID, max(bugHistoryID) as maxHistoryID "); s.Append("from BugHistory group by bugID) t "); s.Append("join bugs b on b.bugid = t.bugid "); s.Append( "join BugHistory h on h.bugHistoryID = t.maxHistoryID "); s.Append("join lkProduct p on b.Product = p.ProductID "); s.Append("join People r on b.Reporter = r.PersonID "); s.Append("join People o on h.Owner = o.PersonID "); s.Append("join lkStatus s on s.statusid = h.status "); s.Append( "join lkSeverity sev on sev.SeverityID = h.severity ");
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.
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.
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.
The second SQL select statement in Example 11-5 is slightly more complicated:
Select BugID, StatusDescription, SeverityDescription, Response, FullName as Owner, DateStamp from BugHistory h join People o on h.Owner = o.PersonID join lkStatus s on s.statusid = h.status join lkSeverity sev on sev.SeverityID = h.severity where BugID = + bugID
The problem here is that each time you run this procedure, you must
supply the bugID. To make this work, your new sproc (spBugHistory)
will need a parameter: @BugID
.
Here’s the sproc:
CREATE PROCEDURE spBugHistory @BugID integer AS Select BugID, StatusDescription, SeverityDescription, Response, FullName as Owner, DateStamp from BugHistory h join People o on h.Owner = o.PersonID join lkStatus s on s.statusid = h.status join lkSeverity sev on sev.SeverityID = h.severity where BugID = @BugID
You might invoke this sproc from within the Query Analyzer like this:
spBugID 2
A value of 2
would be passed
in as the
@BugID
argument.
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.
The rewrite to CreateBugDataSet is very straightforward. You’ll remember from Example 11-5 that your steps were as follows:
First you created the connection string:
string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs";
Then you created the new connection object and opened it:
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( );
You hand-built the SQL statement and you set the CommandText to the string you built:
StringBuilder s = new StringBuilder( "Select b.BugID, h.BugHistoryID, b.Description,h.Response, "); s.Append("o.FullName as owner, "); s.Append("p.ProductDescription, "); s.Append("r.FullName as reporter, "); s.Append("s.StatusDescription, "); s.Append("sev.SeverityDescription, "); s.Append("h.DateStamp "); s.Append("from "); s.Append( "(select bugID, max(bugHistoryID) as maxHistoryID "); s.Append("from BugHistory group by bugID) t "); s.Append("join bugs b on b.bugid = t.bugid "); s.Append( "join BugHistory h on h.bugHistoryID = t.maxHistoryID "); s.Append("join lkProduct p on b.Product = p.ProductID "); s.Append("join People r on b.Reporter = r.PersonID "); s.Append("join People o on h.Owner = o.PersonID "); s.Append("join lkStatus s on s.statusid = h.status "); s.Append( "join lkSeverity sev on sev.SeverityID = h.severity "); // set the command text to the select statement command.CommandText=s.ToString( );
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
:
command.CommandText="spBugs"; command.CommandType=CommandType.StoredProcedure;
When you set the CommandType property to
StoredProcedure
, the sproc can be run more
efficiently then when you use the default value of
Text
.
That’s it; the method is otherwise unchanged. The complete replacement for CreateBugDataSet is shown in Example 11-9.
Example 11-9. Replacement CreateBugDataSet using a stored procedure
private DataSet CreateBugDataSet( ) { // connection string to connect to the Bugs Database string connectionString = "server=Neptune; uid=sa; pwd=oWenmEany; database=ProgASPDotNetBugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // Create a SqlCommand object and assign the connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection=connection;command.CommandText="spBugs";
command.CommandType=CommandType.StoredProcedure;
// create a data adapter and assign the command object // and add the table mapping for bugs SqlDataAdapter dataAdapter = new SqlDataAdapter( ); dataAdapter.SelectCommand=command; dataAdapter.TableMappings.Add("Table","Bugs"); // Create the dataset and use the data adapter to fill it DataSet dataSet = new DataSet( ); dataAdapter.Fill(dataSet); return dataSet; }
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.
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 replacement for CreateBugHistoryDataSet is shown in Example 11-10.
Example 11-10. Replacement CreateBugHistoryDataSet using a stored procedure
private DataSet CreateBugHistoryDataSet(int bugID) { // connection string to connect to the Bugs Database string connectionString = "server=Neptune; uid=sa; pwd=oWenmEany; database=ProgASPDotNetBugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // create a second command object for the bugs hisotry table System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection;command.CommandText= "spBugHistory " + bugID;
command.CommandType = CommandType.Text;
// create a second data adapter and add the command // and map the table // then fill the dataset from this second adapter SqlDataAdapter dataAdapter = new SqlDataAdapter( ); dataAdapter.SelectCommand = command; dataAdapter.TableMappings.Add("Table", "BugHistory"); DataSet dataSet = new DataSet( ); dataAdapter.Fill(dataSet); return dataSet; }
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:
System.Data.SqlClient.SqlParameter param; param = command.Parameters.Add("@BugID",SqlDbType.Int); param.Direction = ParameterDirection..Input; param.Value = bugID;
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
replacement for CreateBugHistoryDataSet is shown in Example 11-11.
Example 11-11. Replacement CreateBugHistoryDataSetusing explicit parameters to a stored procedure
private DataSet CreateBugHistoryDataSet(int bugID) { // connection string to connect to the Bugs Database string connectionString = "server=Neptune; uid=sa; pwd=oWenmEany; database=ProgASPDotNetBugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // create a second command object for the bugs hisotry table System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection;command.CommandText= "spBugHistory";
command.CommandType = CommandType.StoredProcedure;
// declare the parameter object
System.Data.SqlClient.SqlParameter param;
// Add a new parameter, get back a reference to it
param = command.Parameters.Add("@BugID",SqlDbType.Int);
// set the parameter's direction and value
param.Direction = ParameterDirection.Input;
param.Value = bugID;
// create a second data adapter and add the command // and map the table // then fill the dataset from this second adapter SqlDataAdapter dataAdapter = new SqlDataAdapter( ); dataAdapter.SelectCommand = command; dataAdapter.TableMappings.Add("Table", "BugHistory"); DataSet dataSet = new DataSet( ); dataAdapter.Fill(dataSet); return dataSet; }
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:
CREATE PROCEDURE spBugHistoryCount
@BugID integer,
@TotalFound integer output
AS
select @totalFound = count(bugHistoryID)
from BugHistory where BugID = @BugID
Note that the second parameter is marked as an output parameter. To display the output value returned by this sproc, you’ll add a new label to the Panel control in the aspx file:
<asp:Label ID="lblTotalFound" Runat="server"/>
Remember to declare this label in the .cs
file
so that you can refer to it programatically:
protected System.Web.UI.WebControls.Label lblTotalFound;
You now add a new method, TotalRecordsFound, which will invoke the sproc and return the value the sproc returns as a string. You’ll then insert the string into the label you just created.
To start, modify UpdateBugHistory and add the following line as the
last line in the existing if
statement:
lblTotalFound.Text = "<b>Total History Records Found:</b> " + TotalRecordsFound(bugID);
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 source code for the TotalRecordsFound
method is shown in Example 11-12.
Example 11-12. Retrieving an output value
private string TotalRecordsFound(int bugID) { // connection string to connect to the Bugs Database string connectionString = "server=YourServer; uid=sa; pwd=YourPW; database=ProgASPDotNetBugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // create a command object for the sproc System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection; command.CommandText= "spBugHistoryCount"; command.CommandType = CommandType.StoredProcedure; // declare the parameter object System.Data.SqlClient.SqlParameter param; // Add a new parameter, get back a reference to it param = command.Parameters.Add("@BugID",SqlDbType.Int); // set the parameter's direction and value param.Direction = ParameterDirection.Input; param.Value = bugID; // Add a new parameter, get back a reference to it param = command.Parameters.Add("@TotalFound",SqlDbType.Int); // set the parameter's direction param.Direction = ParameterDirection.Output; // call ExecuteNonQuery because no dataset // will be returned command.ExecuteNonQuery( ); // get the param from the collection param = command.Parameters["@TotalFound"]; // extract the value int val = (int) param.Value; // cast to a string string output = val.ToString( ); // return the value as a string return output; }
3.138.105.215