O'Reilly logo

Programming ASP.NET, Second Edition by Dan Hurwitz, Jesse Liberty

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Stored Procedures

Until now, you’ve interacted with the database using nothing but SQL statements. Many real-world applications interacting with SQL Server or other large databases will use stored procedures. Stored procedures can be compiled by the database, and, thus, offer better performance.

The easiest way to create a stored procedure (often referred to as a sproc ) is to begin with a working SQL statement. If you return to Example 11-6, you will find two SQL Select statements. The first is in the CreateBugDataSet method:

StringBuilder s = new StringBuilder(
   "Select b.BugID, h.BugHistoryID, b.Description,h.Response, ");
s.Append("o.FullName as owner, ");
s.Append("p.ProductDescription, ");
s.Append("r.FullName as reporter, ");
s.Append("s.StatusDescription, ");
s.Append("sev.SeverityDescription, ");
s.Append("h.DateStamp ");
s.Append("from  ");
s.Append("(select bugID, max(bugHistoryID) as maxHistoryID ");
s.Append("from BugHistory group by bugID) t ");
s.Append("join bugs b on b.bugid = t.bugid ");
s.Append("join BugHistory h on h.bugHistoryID = t.maxHistoryID ");
s.Append("join lkProduct p on b.Product = p.ProductID  ");
s.Append("join People r on b.Reporter = r.PersonID  ");
s.Append("join People o on h.Owner = o.PersonID ");
s.Append("join lkStatus s on s.statusid = h.status ");
s.Append("join lkSeverity sev on sev.SeverityID = h.severity ");

If you extract the SQL from this statement and insert it into the SQL Server Query analyzer, you can run that statement and get back ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required