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.
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.
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.
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.
<%@ 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; database=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; database=Northwind") OurCommand = New SqlCommand("ProductsByType" ,OurConnection) OurCommand.CommandType = CommandType.StoredProcedure OurCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = OurDropDown. SelectedItem.Value OurCommand.Parameters.Add("@AveragePrice", sqlDBType.Float).Direction = ParameterDirection.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>
<%@ 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; database=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; database=Northwind"); OurCommand = new SqlCommand("ProductsByType" ,OurConnection); OurCommand.CommandType = CommandType.StoredProcedure; OurCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = OurDropDown. SelectedItem.Value; OurCommand.Parameters.Add("@AveragePrice", SqlDbType.Float).Direction = ParameterDirection.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.
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.
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.
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.
<%@ 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; database=Northwind") OurConnection.Open() OurCommand = New SqlCommand("Select Top 15 EmployeeID,FirstName,LastName from Employees" ,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; database=Northwind") OurConnection.Open() OurCommand = New SqlCommand("Select FirstName, LastName From Employees Where EmployeeID = " + 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; database=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 Other" /> </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>
<%@ 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; database=Northwind"); OurConnection.Open(); OurCommand = new SqlCommand("Select Top 15 EmployeeID,FirstName,LastName from Employees" ,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; database=Northwind"); OurConnection.Open(); OurCommand = new SqlCommand("Select FirstName, LastName From Employees Where EmployeeID = " + 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; database=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 Other" /> </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 |