Stored Procedures

   

One of the great advantages of Microsoft SQL Server and Oracle databases are stored procedures. Unfortunately, Microsoft Access doesn't support them. The simple explanation is that they are just Transact SQL statements that are stored in the database. This makes compartmentalizing function that is associated with the database, in the database a bit more real. But there is more to it than that.

Stored procedures allow true logical function before data is ever passed across the connection to ASP.NET pages. You can perform logical functions such as branching right within SQL Server, so you are really only delivering data through ADO.NET that you need.

Stored procedures also allow multi-part interaction with your database, as well. For instance, imagine if you wanted to insert data into two separate tables and then retrieve the unique identity or primary key of the newly inserted row in one of the tables. Using the standard methods would involve some very complicated code within your ASP.NET pages.

Stored procedures enable you to write all the code encapsulated in a single procedure. You pass in the information you want to insert as parameters to the stored procedure, and it handles it from there. You can also have one of the functions of the procedure be retrieving the primary key of the newly inserted information from the table you want and passing it back to you as an output parameter.

So essentially, you pass parameters in, the stored procedure does the work, and it passes the data back out. 1, 2, 3. But the advantages do not end there.

Stored procedures are also compiled pieces of code that reside in the SQL Server's memory. They don't have to be "processed" every time they are called. They are just sitting there in memory waiting to be called and BANG!!! They do their work. In other words, in most cases there are significant performance advantages to using stored procedures.

Querying with Stored Procedures

The following examples show two simple stored procedures that perform select statements. The first one simply returns the CategoryID and CategoryName of every row in the Categories table into an SqlDataReader. I will use this to populate a DropDownList server control so I can pick what products I want to see on the page.

Stored Procedure ProductTypes
CREATE PROCEDURE ProductTypes  AS  Select CategoryID, CategoryName From Categories  GO 

The second procedure has two parameters in it: one that supplies a value to the stored procedure, and one that the stored procedure returns. These are called input parameters and output parameters, respectively. The input parameter passing in is the CategoryID that was selected from the DropDownList server control. The stored procedure uses that CategoryID to filter the Products table by that value against the CategoryID column. The procedure returns the proper products according to the value of the @CategoryID input parameter provided. The @AveragePrice output parameter is set to return the average unit price of the selected category of products. SQL Server's Avg() function performs that calculation.

Stored Procedure ProductsByType
CREATE PROCEDURE ProductsByType      @CategoryID int,      @AveragePrice    Money output  AS  Select  ProductName,UnitPrice From Products where CategoryID = @CategoryID  Select @AveragePrice = Avg(UnitPrice) From Products where CategoryID = @CategoryID  GO 

Now, as you look at the following code, notice that the DropDownList server control is populated with the contents of the SqlDataReader if it's the first time the Page_Load event has fired. If the page is posting back, the selected product category is sent to the stored procedure and a datagrid is populated with the returned contents of the first statement. The average price of the selected products is returned in the @AveragePrice Output parameter.

Visual Basic .NET ado_sproc_select_vb.aspx
<%@ page language="vb" runat="server" %>  <%@ Import Namespace="System.Data" %>  <%@ Import Namespace="System.Data.SqlClient" %>  <script runat=server>  Sub Page_Load()      if (Not IsPostBack) then          dim OurConnection as SqlConnection          dim OurCommand as SqlCommand          dim OurDataReader as SqlDataReader          OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind")          OurCommand = New SqlCommand("ProductTypes" ,OurConnection)          OurCommand.CommandType = CommandType.StoredProcedure          OurConnection.Open()          OurDataReader = OurCommand.ExecuteReader()          OurDropDown.DataSource = OurDataReader          OurDropDown.DataTextField="CategoryName"          OurDropDown.DataValueField="CategoryID"          OurDropDown.DataBind()          OurDataReader.Close()          OurConnection.Close()      else          dim OurConnection as SqlConnection          dim OurCommand as SqlCommand          dim OurDataAdapter as SqlDataAdapter          dim OurDataSet as new DataSet          OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind")          OurCommand = New SqlCommand("ProductsByType" ,OurConnection)          OurCommand.CommandType = CommandType.StoredProcedure          OurCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = OurDropDown. graphics/ccc.gifSelectedItem.Value          OurCommand.Parameters.Add("@AveragePrice", sqlDBType.Float).Direction =  graphics/ccc.gifParameterDirection.Output          OurDataAdapter = new SqlDataAdapter(OurCommand)          OurDataAdapter.Fill(OurDataSet, "Products")          OurDataGrid.DataSource = OurDataSet.Tables("Products")          DataBind()            dim UnitAve as Double = OurCommand.Parameters("@AveragePrice").Value          OurLabel.Text = "Average Unit Price of All Products: " + UnitAve.ToString("C")          end if  End Sub  </script>  <html>  <head>  <title>ADO Stored Procedures - Select</title>  </head>  <body bgcolor="#FFFFFF" text="#000000">  <form runat="server">  <asp:DropDownList  runat="server" />  <asp:Button  Text="Get Products" runat="server" /><br><br>  <ASP:DataGrid            EnableViewState="false"      BorderWidth="1"      BorderColor="#000000"      CellPadding="3"      CellSpacing="0"      Font-Name="Verdana"      HeaderStyle-BackColor="#AAAAAA"      ItemStyle-BackColor="#EEEEEE"      AutoGenerateColumns="False"      runat="server">      <Columns>          <asp:BoundColumn              HeaderText="Product Name"              DataField="ProductName" />          <asp:BoundColumn              HeaderText="Unit Price"              DataField="UnitPrice"              DataFormatString="{0:c}"/>      </Columns>  </asp:DataGrid>  <asp:Label   Font-Name="Verdana" runat="server" />  </form>  </body>  </html> 
C# ado_sproc_select_cs.aspx
<%@ page language="c#" runat="server" %>  <%@ Import Namespace="System.Data" %>  <%@ Import Namespace="System.Data.SqlClient" %>  <script runat=server>  void Page_Load() {     if (!IsPostBack) {         SqlConnection OurConnection;          SqlCommand OurCommand;          SqlDataReader OurDataReader;          OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind");          OurCommand = new SqlCommand("ProductTypes" ,OurConnection);          OurCommand.CommandType = CommandType.StoredProcedure;          OurConnection.Open();          OurDataReader = OurCommand.ExecuteReader();          OurDropDown.DataSource = OurDataReader;          OurDropDown.DataTextField="CategoryName";          OurDropDown.DataValueField="CategoryID";          OurDropDown.DataBind();          OurDataReader.Close();          OurConnection.Close();      }      else {         SqlConnection OurConnection;          SqlCommand OurCommand;          SqlDataAdapter OurDataAdapter;          DataSet OurDataSet = new DataSet();          OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind");          OurCommand = new SqlCommand("ProductsByType" ,OurConnection);          OurCommand.CommandType = CommandType.StoredProcedure;          OurCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = OurDropDown. graphics/ccc.gifSelectedItem.Value;          OurCommand.Parameters.Add("@AveragePrice", SqlDbType.Float).Direction =  graphics/ccc.gifParameterDirection.Output;          OurDataAdapter = new SqlDataAdapter(OurCommand);          OurDataAdapter.Fill(OurDataSet, "Products");          OurDataGrid.DataSource = OurDataSet.Tables["Products"];          DataBind();          Double UnitAve = (Double)OurCommand.Parameters["@AveragePrice"].Value;          OurLabel.Text = "Average Unit Price: " + UnitAve.ToString("C");          }  }  </script>  <html>  <head>  <title>ADO Stored Procedure - Select</title>  </head>  <body bgcolor="#FFFFFF" text="#000000">  <form runat="server">  <asp:DropDownList  runat="server" />  <asp:Button  Text="Get Products" runat="server" /><br><br>  <ASP:DataGrid              EnableViewState="false"      BorderWidth="1"      BorderColor="#000000"      CellPadding="3"      CellSpacing="0"      Font-Name="Verdana"      HeaderStyle-BackColor="#AAAAAA"      ItemStyle-BackColor="#EEEEEE"      AutoGenerateColumns="False"      runat="server">      <Columns>          <asp:BoundColumn              HeaderText="Product Name"              DataField="ProductName" />          <asp:BoundColumn              HeaderText="Unit Price"              DataField="UnitPrice"              DataFormatString="{0:c}"/>      </Columns>  </asp:DataGrid><br>  <asp:Label   Font-Name="Verdana" runat="server" />  </form>  </body>  </html> 

As you can see in Figure 10.8, the DropDownList was properly populated, the input parameter from the DropDownList has both filtered the Products table and caused the @AveragePrice output parameter to perform only the Avg() function on the selected products. The @AveragePrice is displayed as the Text parameter of OurLabel.

Figure 10.8. Stored procedures let you perform multiple functions on the database server, as opposed to performing the logic on the web server.
graphics/10fig08.gif

But querying data isn't the only thing that you can do with stored procedures. As I said, you can perform all the functions that you can with T-SQL statements, including inserting, updating, and deleting.

Inserting, Updating, and Deleting with Stored Procedures

Stored procedures add a lot of flexibility to processing data. When using standard T-SQL statements to perform insert, update, and delete functions, you are kind of pigeonholed into doing things in a certain way. Stored procedures not only provide enhanced performance in most cases, but they also give you a much broader way to approach creating functions in your ASP.NET applications.

Here you can look back at the example that I used for the command version of inserting, updating, and deleting, but now all the functions are performed within the confines of a stored procedure. Each function within this ASP.NET page calls the same stored procedure and sends the Action parameter so I can branch within the stored procedure to execute the insert, update, or delete function.

Stored Procedure Products_IUD
CREATE PROCEDURE [Products_IUD]      @Action varchar(10),      @EmployeeID Int,      @FirstName varchar(10),      @LastName varchar(20)  AS      if @Action = 'Insert'          Insert Into Employees (FirstName, LastName)                  Values (@FirstName, @LastName)      if @Action = 'Update'          Update Employees Set Firstname = @FirstName,                  LastName = @LastName                  Where EmployeeID = @EmployeeID      if @Action = 'Delete'          Delete Employees Where EmployeeID = @EmployeeID  GO 

As you can see, there are three branches in the stored procedure. You can do a whole host of things within a stored procedure much more than I can cover here but this shows a simple branch that executes an insert, update, or delete function, depending on which branch shows true.

Stored procedures also make cleaning up the code a bit easier, because the stored procedure expects uniform input parameters no matter what branch is executing. What I've done is utilize the CommandName property of the Button server control to pass the action that's being performed and to have all three types of action be performed in the same function, instead of having a function each for the insert, update, and delete.

I could have achieved this the same way with the SqlCommand, but it would make things a bit messy. I would have needed to build the T-SQL statements through concatenation of strings and the server controls that are delivering the data. I would be restricted from using the Parameters.Add() method because all three statements require different parameters.

The following is the more consolidated code that parallels the function of the SqlCommand insert, update, and delete functions covered earlier in this chapter.

Visual Basic .NET ado_sproc_iud_vb.aspx
<%@ page language="vb" runat="server" %>  <%@ Import Namespace="System.Data" %>  <%@ Import Namespace="System.Data.SqlClient" %>  <script runat=server>  Sub Page_Load()      If (Not IsPostBack) then          LoadData()      End If  End Sub  Sub LoadData()      dim OurConnection as SqlConnection      dim OurCommand as SqlCommand      dim OurDataAdapter as SQLDataAdapter      dim OurDataSet as New DataSet()      OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind")      OurConnection.Open()      OurCommand = New SqlCommand("Select Top 15 EmployeeID,FirstName,LastName from  graphics/ccc.gifEmployees" ,OurConnection)      OurDataAdapter = New SQLDataAdapter(OurCommand)      OurDataAdapter.Fill(OurDataSet, "Employees")      OurDataGrid.DataSource=OurDataSet.Tables("Employees")      UpdateEmployeeID.DataSource=OurDataSet.Tables("Employees")      DeleteEmployeeID.DataSource=OurDataSet.Tables("Employees")      DataBind()  End Sub  Sub UpdateSetup(sender As Object, e As System.EventArgs)      dim SelectedID as String = UpdateEmployeeID.SelectedItem.Value      dim OurConnection as SqlConnection      dim OurCommand as SqlCommand      dim OurDataReader2 as SqlDataReader      UpdateSelect.Visible="False"      UpdateTextBoxes.Visible="True"      UpdateButton.CommandArgument = SelectedID      OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind")      OurConnection.Open()        OurCommand = New SqlCommand("Select FirstName, LastName From Employees Where  graphics/ccc.gifEmployeeID = " + SelectedID ,OurConnection)      OurDataReader2 = OurCommand.ExecuteReader()      OurDataReader2.Read()      UpdateFirstName.Text = OurDataReader2("FirstName")      UpdateLastName.Text = OurDataReader2("LastName")      OurConnection.Close()      OurDataReader2.Close()      LoadData()  End Sub  Sub UpdateReset(sender As Object, e As System.EventArgs)      UpdateSelect.Visible="True"      UpdateTextBoxes.Visible="False"      LoadData()  End Sub  Sub Products_Function(sender As Object, e As System.Web.UI.WebControls.CommandEventArgs)      dim Action as String = e.CommandName      dim FirstName as String      dim LastName as String      dim EmployeeID as Integer      FirstName = ""      LastName = ""      Select Case Action      Case "Insert"          FirstName = InsertFirstName.Text          LastName = InsertLastName.Text      Case "Update"          FirstName = UpdateFirstName.Text          LastName = UpdateLastName.Text          EmployeeID = e.CommandArgument      Case "Delete"          EmployeeID = DeleteEmployeeID.SelectedItem.Value      End Select      dim OurConnection as SqlConnection      dim OurCommand as SqlCommand      OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind")      OurCommand = New SqlCommand("Products_IUD",OurConnection)      OurCommand.CommandType = CommandType.StoredProcedure      OurCommand.Parameters.Add("@Action", SqlDbType.Varchar, 10).Value = Action      OurCommand.Parameters.Add("@FirstName", SqlDbType.Varchar, 10).Value = FirstName      OurCommand.Parameters.Add("@LastName", SqlDbType.Varchar, 20).Value = LastName      OurCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4).Value = EmployeeID      OurConnection.Open()      OurCommand.ExecuteNonQuery()      OurConnection.Close()      LoadData()  End Sub  </script>  <html>  <head>  <title>ADO SQLCommand -Insert,Update,Delete</title>  </head>  <body bgcolor="#FFFFFF" text="#000000">  <form runat="server">  <table border="0" cellpadding="0" cellspacing="20">  <tr><td>  <ASP:DataGrid            EnableViewState="false"      BorderWidth="1"      BorderColor="#000000"      CellPadding="3"      CellSpacing="0"      Font-Name="Verdana"      HeaderStyle-BackColor="#AAAAAA"      ItemStyle-BackColor="#EEEEEE"      runat="server" />  </td><td>  <h4>Insert</h4>  First Name: <asp:TextBox  runat="server" /><br>  Last Name: <asp:TextBox  runat="server" />  <asp:button      OnCommand="Products_Function"      CommandName="Insert"      text="Submit"      runat="server" /><br><br>  <hr style="height:1px">  <h4>Update</h4>  <asp:Panel  runat="server" >  EmployeeID:  <asp:DropDownList            DataTextField="EmployeeID"      DataValueField="EmployeeID"      runat="server" />  <asp:button runat="server" text="Select" OnClick="UpdateSetup" />  </asp:Panel>  <asp:Panel  Visible="false" runat="server" >  First Name: <asp:TextBox  runat="server" />  <asp:button            onCommand="Products_Function"      CommandName="Update"        runat="server"      text="Update" /><br>  Last Name: <asp:TextBox   runat="server" />  <asp:button  runat="server" onClick="UpdateReset" text="Select  graphics/ccc.gifOther" />  </asp:Panel><br>  <hr style="height:1px">  <h4>Delete</h4>  EmployeeID:  <asp:DropDownList            DataTextField="EmployeeID"      DataValueField="EmployeeID"      runat="server" />  <asp:button      onCommand="Products_Function"      CommandName="Delete"      text="Delete"      runat="server" />  </td></tr>  </table>  </form>  </body>  </html> 
C# ado_sproc_iud_cs.aspx
<%@ page language="c#" runat="server" %>    <%@ Import Namespace="System.Data" %>    <%@ Import Namespace="System.Data.SqlClient" %>    <script runat=server>    void Page_Load() {       if (!IsPostBack) {           LoadData();        }    }    void LoadData() {       SqlConnection OurConnection;        SqlCommand OurCommand;        SqlDataAdapter OurDataAdapter;        DataSet OurDataSet = new DataSet();        OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind");        OurConnection.Open();        OurCommand = new SqlCommand("Select Top 15 EmployeeID,FirstName,LastName from  graphics/ccc.gifEmployees" ,OurConnection);        OurDataAdapter = new SqlDataAdapter(OurCommand);        OurDataAdapter.Fill(OurDataSet, "Employees");        OurDataGrid.DataSource=OurDataSet.Tables["Employees"];        UpdateEmployeeID.DataSource=OurDataSet.Tables["Employees"];        DeleteEmployeeID.DataSource=OurDataSet.Tables["Employees"];        DataBind();    }    void UpdateSetup(Object sender, EventArgs e) {       String SelectedID = UpdateEmployeeID.SelectedItem.Value;        SqlConnection OurConnection;        SqlCommand OurCommand;        SqlDataReader OurDataReader2;        UpdateSelect.Visible=false;        UpdateTextBoxes.Visible=true;        UpdateButton.CommandArgument = SelectedID;        OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind");        OurConnection.Open();        OurCommand = new SqlCommand("Select FirstName, LastName From Employees Where  graphics/ccc.gifEmployeeID = " + SelectedID ,OurConnection);        OurDataReader2 = OurCommand.ExecuteReader();        OurDataReader2.Read();        UpdateFirstName.Text = OurDataReader2["FirstName"].ToString();        UpdateLastName.Text = OurDataReader2["LastName"].ToString();        OurConnection.Close();        OurDataReader2.Close();        LoadData();    }    void UpdateReset(Object sender, EventArgs e) {       UpdateSelect.Visible=true;        UpdateTextBoxes.Visible=false;        LoadData();    }    void Products_Function(Object sender, System.Web.UI.WebControls.CommandEventArgs e) {        String Action = e.CommandName;        String FirstName;        String LastName;        int EmployeeID = 0;        FirstName = "";        LastName = "";        switch (Action.ToLower()) {        case "insert":              FirstName = InsertFirstName.Text;            LastName = InsertLastName.Text;            break;        case "update":            FirstName = UpdateFirstName.Text;            LastName = UpdateLastName.Text;            EmployeeID = Int32.Parse(e.CommandArgument.ToString());            break;        case "delete":            EmployeeID = Int32.Parse(DeleteEmployeeID.SelectedItem.Value);            break;        default:            break;        }        SqlConnection OurConnection;        SqlCommand OurCommand;        OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; graphics/ccc.gifdatabase=Northwind");        OurCommand = new SqlCommand("Products_IUD",OurConnection);        OurCommand.CommandType = CommandType.StoredProcedure;        OurCommand.Parameters.Add("@Action", SqlDbType.VarChar, 10).Value = Action;        OurCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;        OurCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = LastName;        OurCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4).Value = EmployeeID;        OurConnection.Open();        OurCommand.ExecuteNonQuery();        OurConnection.Close();        LoadData();    }    </script>    <html>    <head>    <title>ADO SqlCommand -Insert,Update,Delete</title>    </head>    <body bgcolor="#FFFFFF" text="#000000">    <form runat="server">    <table border="0" cellpadding="0" cellspacing="20">    <tr><td>    <ASP:DataGrid                EnableViewState="false"        BorderWidth="1"        BorderColor="#000000"        CellPadding="3"        CellSpacing="0"        Font-Name="Verdana"        HeaderStyle-BackColor="#AAAAAA"        ItemStyle-BackColor="#EEEEEE"        runat="server" />    </td><td>    <h4>Insert</h4>    First Name: <asp:TextBox  runat="server" /><br>    Last Name: <asp:TextBox  runat="server" />    <asp:button        OnCommand="Products_Function"        CommandName="Insert"        text="voidmit"        runat="server" /><br><br>    <hr style="height:1px">    <h4>Update</h4>    <asp:Panel  runat="server" >    EmployeeID:    <asp:DropDownList                DataTextField="EmployeeID"        DataValueField="EmployeeID"        runat="server" />    <asp:button runat="server" text="Select" OnClick="UpdateSetup" />    </asp:Panel>    <asp:Panel  Visible="false" runat="server" >    First Name: <asp:TextBox  runat="server" />    <asp:button                onCommand="Products_Function"        CommandName="Update"        runat="server"        text="Update" /><br>    Last Name: <asp:TextBox   runat="server" />    <asp:button  runat="server" onClick="UpdateReset" text="Select  graphics/ccc.gifOther" />    </asp:Panel><br>    <hr style="height:1px">    <h4>Delete</h4>    EmployeeID:    <asp:DropDownList                DataTextField="EmployeeID"        DataValueField="EmployeeID"        runat="server" />    <asp:button        onCommand="Products_Function"        CommandName="Delete"        text="Delete"        runat="server" />    </td></tr>    </table>    </form>    </body>    </html> 

Notice that the single Products_Function provides all the functionality of the InsertCommand, UpdateCommand, and DeleteCommand of the SqlCommand example given earlier in this chapter.

Use a Select statement to evaluate which action is being performed, which is established via the CommandName variable. This is just the tip of the iceberg when it comes to what stored procedures can do for you when manipulating data in your databases and how they can help consolidate code in your ASP.NET pages.

Experiment and be creative. You will be surprised at the power of stored procedures, and after you start using them, you will begin to wonder how many unnecessary lines of code you've written doing things the conventional way.


   
Top


ASP. NET for Web Designers
ASP.NET for Web Designers
ISBN: 073571262X
EAN: 2147483647
Year: 2005
Pages: 94
Authors: Peter Ladka

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net