Section 10.4. Stored Procedures | Programming ASP.NET 3.5

10.4. Stored Procedures

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

10.4.1. Creating a Simple Stored Procedure

The easiest way to create a stored procedure (often referred to as a sproc ) is to begin with a working SQL statement. To see this at work, copy the web site you worked on previously in this chapter, DataRelations, to a new web site called StoredProcedures.

In the method CreateDataSet , you created a select statement for your first command object:

 StringBuilder s = new StringBuilder(         "select OrderID, c.CompanyName, c.ContactName, ");     s.Append(" c.ContactTitle, c.Phone, orderDate");     s.Append(" from orders o ");     s.Append("join customers c on c.CustomerID = o.CustomerID");     command.CommandText = s.ToString(  );

Put a break point on the final line and run the program. Step one instruction, and in the Autos window, you should see command.CommandText . Double-click on the value to extract the complete select statement:

 "select OrderID, c.CompanyName, c.ContactName,  c.ContactTitle, c.Phone, orderDate     from orders o join customers c on c.CustomerID = o.CustomerID"

If you have a copy of SQL Server Client, open its Query Analyzer and you should find you can drop this statement in (without the opening and closing double quotes) and have it run against the Northwind database, as shown in Figure 10-12.

You can now drop this query into a new stored procedure, which you will name spOrders . In SQL Server, the easiest way to do this is to right-click on the Stored Procedures listing in SQL Enterprise Manager and click on New Stored Procedure.

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

Having created the stored procedure, you may now call it instead of using the SQL command. Let's start clean:

Delete the third GridView ( OrderRelationsGridView ) from default.aspx .

Replace Page_Load with the following code:

 protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)

Figure 10-12. SQL QueryAnalyzer

Figure 10-13. Creating the new stored procedure

 {             DataSet ds = CreateDataSet(  );             GridView1.DataSource = ds.Tables[0];             GridView1.DataBind(  );         }     }

Replace the CreateDataSet method with the code shown in Example 10-7.

Example 10-7. Invoking a stored procedure

 private DataSet CreateDataSet(  ) {     // connection string to connect to the Orders Database     string connectionString =     "Data Source=Brahams;Initial Catalog=Northwind;Integrated Security=True";     // Create connection object, initialize with     // connection string and open the connection     System.Data.SqlClient.SqlConnection connection =        new System.Data.SqlClient.SqlConnection(connectionString);     DataSet dataSet;     try     {         connection.Open(  );         System.Data.SqlClient.SqlCommand command =            new System.Data.SqlClient.SqlCommand(  );         command.Connection = connection;  command.CommandText = "spOrders";         command.CommandType = CommandType.StoredProcedure;  SqlDataAdapter dataAdapter = new SqlDataAdapter(  );         dataAdapter.SelectCommand = command;         dataAdapter.TableMappings.Add("Table", "Orders");         // Create the dataset and use the data adapter to fill it         dataSet = new DataSet(  );         dataAdapter.Fill(dataSet);     }     finally     {         connection.Close(  );     }     return dataSet; }     // end createDataSet

The significant change in this code (beyond simplification) is that you are calling the stored procedure spOrders rather than passing in the entire select statement. To do this, you remove the string builder, and you set the commandText to the name of the stored procedure and the command type to the enumerated constant CommandType.StoredProcedure :

 command.CommandText = "spOrders";     command.CommandType = CommandType.StoredProcedure;

(The default CommandType is Text , and that is what you've been using until now.)

Running the application will demonstrate that the call to the stored procedure has the same effect as calling the query directly, with the advantage that, in larger applications, the stored procedure will be more efficient, because your database will optimize its execution.

10.4.2. Stored Procedures with Parameters

If the user clicks on a Details button, the current version of the program will throw an exception. Let's fix that. In the DataRelations program, you downloaded all the details and then selected which details to show in the grid in the panel by using a filtered view. This time, you'll create a parameterized stored procedure to retrieve the details for the specific orderID selected.

Which approach is more efficient will depend on the volume of data, the frequency with which you want to go back to the database, how much data you can hold in memory, and so forth. Typically, you'll use the filtered view whenever possible to reduce the number of connections to the database.

Create a new stored procedure named spOrderDetails as follows :

 CREATE PROCEDURE spOrderDetails     @OrderID int     AS     select od.orderid, orderDate, p.productid, productname, od.unitprice, quantity from     orders o     join [order details] od on o.orderid = od.orderid     join products p on p.productid = od.productid     where o.orderID = @OrderID

When you invoke the stored procedure, you must replace the parameter @OrderID with the OrderID you want to match, as shown in Example 10-8.

Example 10-8. Calling stored procedure with parameter

 private void UpdateDetailsGrid(  ) {      int index = GridView1.SelectedIndex;      if (index != -1)      {         DataKey key = GridView1.DataKeys[index];         int orderID = (int) key.Value;         string connectionString =            "Data Source=Brahams;Initial Catalog=Northwind;Integrated Security=True";          System.Data.SqlClient.SqlConnection connection =             new System.Data.SqlClient.SqlConnection(connectionString);          try          {              connection.Open(  );              System.Data.SqlClient.SqlCommand command =                 new System.Data.SqlClient.SqlCommand(  );              command.Connection = connection;  command.CommandText = "spOrderDetails";              command.CommandType = CommandType.StoredProcedure;              SqlParameter param =                  command.Parameters.AddWithValue("@OrderID", orderID);              param.Direction = ParameterDirection.Input;              param.DbType = DbType.Int32;  SqlDataAdapter dataAdapter = new SqlDataAdapter(  );              dataAdapter.SelectCommand = command;              dataAdapter.TableMappings.Add("Table", "OrderDetails");              DataSet dataSet = new DataSet(  );              dataAdapter.Fill(dataSet);              DetailsGridView.DataSource = dataSet.Tables["OrderDetails"];              DetailsGridView.DataBind(  );              OrderDetailsPanel.Visible = true;          }          finally          {              connection.Close(  );          }      }      else      {          OrderDetailsPanel.Visible = false;      } }

In a production-quality program, the shared code for creating the connection to the database would be factored out of these methods . It is left intact so the examples stand on their own.

What's new in this code is that you have added an SqlParameter object to the command object's Parameters collection by invoking the method AddWithValue and passing in the name of the parameter and its value:

  SqlParameter param =          command.Parameters.AddWithValue("@OrderID", orderID);      param.Direction = ParameterDirection.Input;      param.DbType = DbType.Int32;

Setting the Direction to input and the type to Int32 are optional as these are the default values, but it makes for good programming practice to set them explicitly. If you need to get a value out of a stored procedure, set the Direction property to ParameterDirection.Output . You can then pick up the value after calling the stored procedure:

 string retVal = command.Parameters["@MyReturnValue"].Value.ToString(  );