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:

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.

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:

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:

spBugHistory 2

A value of 2 would be passed in as the @BugIDargument.

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:

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 C# replacement for CreateBugDataSet is shown in Example 11-12.

Example 11-12. C# Replacement CreateBugDataSet using a stored procedure

private DataSet CreateBugDataSet(  )
{
   // connection string to connect to the Bugs Database
   string connectionString = 
      "server=YourServer; uid=sa; pwd=YourPassword; " +
      "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;
}

The complete VB.NET replacement for CreateBugDataSet is shown in Example 11-13.

Example 11-13. VB.NET Replacement CreateBugDataSet using a stored procedure

  Private Function CreateBugDataSet(  ) As DataSet

    ' connection string to connect to the Bugs Database
    Dim connectionString As String = _
        "server=YourServer; uid=sa; pwd=YourPassword; " + _
        "database=ProgASPDotNetBugs"

    ' Create connection object, initialize with connection string. 
    ' Open it.
    Dim connection As New _
       System.Data.SqlClient.SqlConnection(connectionString)
    connection.Open(  )

    ' Create a SqlCommand object and assign the connection
    Dim command As 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
    Dim dataAdapter As New SqlDataAdapter
    dataAdapter.SelectCommand = command
    dataAdapter.TableMappings.Add("Table", "Bugs")

    ' Create the dataset and use the data adapter to fill it
    Dim DataSet As New DataSet
    dataAdapter.Fill(DataSet)
    Return DataSet

  End Function

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.

Example 11-14. C# Replacement CreateBugHistoryDataSet using a stored procedure

private DataSet CreateBugHistoryDataSet(int bugID)
{
   // connection string to connect to the Bugs Database
   string connectionString = 
      "server=YourServer; uid=sa; pwd=YourPassword; " +
      "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 history 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;
}

The complete VB.NET replacement for CreateBugHistoryDataSet is shown in Example 11-15.

Example 11-15. VB.NET Replacement CreateBugHistoryDataSet using a stored procedure

  Private Function CreateBugHistoryDataSet(ByVal bugID As Integer) As DataSet

    Dim connectionString As String = _
        "server=YourServer; uid=sa; pwd=YourPassword; " + _
        "database=ProgASPDotNetBugs"
    Dim connection As New _
        System.Data.SqlClient.SqlConnection(connectionString)
    connection.Open(  )

    Dim command As New System.Data.SqlClient.SqlCommand
    command.Connection = connection

    command.CommandText = "spBugHistory " & bugID
    command.CommandType = CommandType.Text

    Dim myDataAdapter As New SqlClient.SqlDataAdapter
    myDataAdapter.SelectCommand = command
    myDataAdapter.TableMappings.Add("Table", "BugHistory")
    Dim ds As New DataSet
    myDataAdapter.Fill(ds)
    Return ds

  End Function

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:

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 C# replacement for CreateBugHistoryDataSet is shown in Example 11-16.

Example 11-16. C# Replacement CreateBugHistoryDataSetusing explicit parameters to a stored procedure

private DataSet CreateBugHistoryDataSet(int bugID)
{
   // connection string to connect to the Bugs Database
   string connectionString = 
      "server=YourServer; uid=sa; pwd=YourPassword; " +
      "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 history 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;
}

The complete VB.NET replacement for CreateBugHistoryDataSet is shown in Example 11-17.

Example 11-17. VB.NET Replacement CreateBugHistoryDataSetusing explicit parameters to a stored procedure

  Private Function CreateBugHistoryDataSet(ByVal bugID As Integer) As DataSet

    Dim connectionString As String = _
        "server=YourServer; uid=sa; pwd=YourPassword; " + _
        "database=ProgASPDotNetBugs"
    Dim connection As New _
        System.Data.SqlClient.SqlConnection(connectionString)
    connection.Open(  )

    Dim command As New System.Data.SqlClient.SqlCommand
    command.Connection = connection

    command.CommandText = "spBugHistory"
    command.CommandType = CommandType.StoredProcedure

    ' declare the parameter object
    Dim param As System.Data.SqlClient.SqlParameter

    ' 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

    Dim myDataAdapter As New SqlClient.SqlDataAdapter
    myDataAdapter.SelectCommand = command
    myDataAdapter.TableMappings.Add("Table", "BugHistory")
    Dim ds As New DataSet
    myDataAdapter.Fill(ds)
    Return ds

  End Function

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:

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 programmatically:

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:

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.

Example 11-18. Retrieving an output value with C#

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;
}

The complete VB.NET source code for the TotalRecordsFound function is shown in Example 11-19.

Example 11-19. Retrieving an output value with VB.NET

  Private Function TotalRecordsFound(ByVal bugID As Integer) As String

    ' connection string to connect to the Bugs Database
    Dim connectionString As String = _
        "server=YourServer; uid=sa; pwd=YourPassword; " + _
        "database=ProgASPDotNetBugs"

    ' Create connection object, initialize with 
    ' connection string. Open it.
    Dim connection As New _
       System.Data.SqlClient.SqlConnection(connectionString)
    connection.Open(  )

    ' create a  command object for the sproc
    Dim Command As New System.Data.SqlClient.SqlCommand
    Command.Connection = connection

    Command.CommandText = "spBugHistoryCount"
    Command.CommandType = CommandType.StoredProcedure

    ' declare the parameter object
    Dim param As System.Data.SqlClient.SqlParameter

    ' 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
    Dim val As Integer = CType(param.Value, Integer)

    ' cast to a string
    Dim output As String = val.ToString(  )

    ' return the value as a string
    Return output

  End Function