With all the advances made in .NET, many developers are beginning to rely more and more on the front end of an application to provide functionality that makes data conform to the specific business rules of an application. Although ASP.NET pages are compiled and execute faster than regular ASP pages, keeping the business logic in the page is still not a manageable or very maintainable process for most organizations. In most cases, the database server ends up being the most durable box from a hardware point of view. Most developers will take no chances on whether the database server has an adequate number of processors, memory, disk space, or built-in backup devices. Yet, for most operations, the database server, when properly configured, is one of the least resource-intensive machines. Consider the following machine configurations for a fictitious Web application.
This is where the use of stored procedures can really boost the performance of your application. Consider the basic chain of events that happens in a Web request.
The goal is to limit the number of times that the user must send information to the Web server, yet at the same time keep the application from being drawn down by too much code in the pages. Consider the following steps from the computer's point of view.
Obviously, the Web server has plenty to do without having to deal with a lot of added code in the pages. And the preceding steps don't even take into consideration "dangling" objects that may have been created and not destroyed. Next, we change the code from Listings 11.1 and 11.2 to reflect the use of a stored procedure to return data to a page. From there, it's merely a matter of creating stored procedures that perform the tasks that we need them to perform. This model creates an efficient two-tier application. Warning As with any ASP.NET application, when collecting data from form, you should use Server.HTMLEncode(Request.Form("objectName") to prevent cross-site scripting attacks. This method encodes the information as a string literal that SQL Server understands, eliminating risks such as malicious users inserting "; TRUNCATE TABLE MASTER" into a textbox named "txtFirstName" on a Web page and having SQL Server execute it. Some developers seem to be uneasy about using stored procedures from Web-based applications. This unease most often comes from the confusion regarding the Command/SQLCommand object, ADO or ADO.NET, and the use of building parameters with the stored procedure. As covered in Chapter 4, the setting of parameters via code is done to "prequalify" the data by setting their type. That keeps SQL Server and .NET from having to spend time trying to figure out the type of the data (integer, string, and so on) being sent. In our case, we are executing a stored procedure with no parameters. The code in Listing 11.4 shows the procedure created for this example. Note Using the steps outlined in the Adding the ASPNET User to the SQL Server Logins section earlier in the chapter, you must configure the public role to have EXEC permissions on the stored procedure created and used in Listings 11.4 and 11.5. Listing 11.4 sp_GetCustomersOrdersCREATE PROCEDURE sp_GetCustomersOrders AS SELECT tblCustomer.LastName, tblCustomer.FirstName, tblOrder.OrderDate FROM tblOrder INNER JOIN tblCustomer ON tblCustomer.ID = tblOrder.CustomerID ORDER BY tblCustomer.LastName GO Note that the query is asking only for what we need. In Listing 11.3, we used SELECT * and only certain columns were shown on the page. We did so to emphasize that if you don't need it, don't ask for it. In the interest of application scalability, being a minimalist must apply. Now, let's take a look at the modified ASP.NET code in Listing 11.5. Note that we had to add a line to handle the date being returned by the stored procedure. Listing 11.5 ShowCustomers SubRoutine Private Sub ShowCustomers() 'This is just a simple function to get 'things started, showing the collection 'and displaying tblCustomer. 'Initialize the connection object with 'the connection string Dim conn As New SqlConnection(connString) 'Also, initialize the command object with 'the SQL to be executed Dim cmd As New SqlCommand("exec sp_GetCustomersOrders", conn) conn.Open() Dim dReader As SqlDataReader = cmd.ExecuteReader (CommandBehavior.CloseConnection) While dReader.Read Response.Write(dReader.GetString(0)) Response.Write(" " & dReader.GetString(1)) Response.Write(" " & dReader.GetDateTime(2)) Response.Write("BR") End While dReader.Close() conn.Close() End Sub By using a stored procedure, we have increased efficiency. Specifically, stored procedures are compiled on the database server and execute rapidly compared to dynamic queries. In Listing 11.1, the SELECT statement had to be interpreted each time by SQL Server before any data was returned. At this point, both the page and the query have been compiled; from a performance perspective, this is a very nice feature of the .NET Framework. |