11.7 Stored Procedures | Programming ASP.NET 3.5

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:

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

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.

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