Programming ASP.NET, 3rd Edition

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.

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 ...