Programming ASP.NET 3.5, 4th Edition

Stored Procedures

The examples in this book have thus far interacted with the database only through SQL statements. Many real-world applications interacting with SQL Server or other large databases use stored procedures, which are compiled by the database in advance of execution and, thus, offer better performance over SQL statements, which are compiled on the fly. They are also much more secure as the only requests made to the database are to run the stored procedures rather than the plain SQL, which could have been altered by malicious users and, for example, could result in the deletion of data.

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, you’ll adapt DataRelations.aspx to fill its two main GridView controls using stored procedures rather than SQL statements.

When creating the DataSet to contain the information for the page, the CreateAdapterForOrders method created a SELECT statement for the first DataAdapter.

private static SqlDataAdapter CreateAdapterForOrders( SqlConnection connection) { //Build the SQL command StringBuilder s = new StringBuilder( "select o.SalesOrderID, o.OrderDate, c.CompanyName, "; s.Append("c.FirstName + ' ' + c.LastName as 'Contact', o.TotalDue"); s.Append(" from SalesLT.SalesOrderHeader o "); s.Append("inner join SalesLT.Customer c on c.CustomerID = o.CustomerID"); SqlDataAdapter OrdersAdapter = new SqlDataAdapter(); OrdersAdapter.SelectCommand ...