Editor's Note: The following is the first in a three part series consisting of an excerpt from the book ASP.NET 2.0 Illustrated, by Alex Homer and Dave Sussman (Addison-Wesley Professional, ISBN 0321418344, republished by permission).

Just as you are leaning back and admiring your handiwork, the phone rings again. This time, it is the senior developer at AdventureWorks Trading Inc.––and he is not a happy fellow. His team has spent months building an object-oriented business and data access layer, and they do not approve of people using SQL statements to access the database directly. This n-tier architecture approach is a common scenario, and you probably should have known better at the start. The SqlDataSource can use stored procedures instead of SQL statements, but to use a data layer based on business objects means more significant changes are required.

However, all you actually need do is change the controls that expose the data (the SqlDataSource controls) for controls that can communicate with business objects. AdventureWorks can provide a .NET managed code assembly that implements their data access layer, so all you have to do is switch to using this in place of direct database access.

However, first, you must install the business object. As it is a .NET assembly, there is no registration required. A compiled DLL can just be dropped into the bin folder of the application, and then referenced in the pages. What happens if the code is not compiled? In that case, you can run the compiler from the command line, or use Visual Studio to compile it, and then deploy.

Even better, in ASP.NET 2.0, you can deploy the source code and leave it to ASP.NET to compile it and register it with the application at runtime. Files placed in the App_Code subfolder of the application root folder are compiled automatically as soon as the application starts (when the first hit is received), and the compiled code is stored on disk for use in subsequent hits. If you edit or update the source code file, it is automatically recompiled and the application restarts.

The (extremely simplified) data access component provided by the AdventureWorks team is a single class file named DataLayer.cs containing public methods that return the data to be displayed in the page (it does not support updates to the data). The three methods it exposes are named GetProductsByCategory, GetProductByProductID, and GetCategoryList—as shown in Listing 2.2.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class DataLayer
{
 public DataSet GetProductsByCategory(Int32 category)
 {
  String connect = ConfigurationManager.ConnectionStrings[
      "AdventureWorksConnectionString1"].ConnectionString;
  String sql = "SELECT ProductID, Name, ProductNumber, Color, "
        + "StandardCost, ListPrice, ProductSubcategoryID "
        + "FROM AdventureWorks.Production.Product "
        + "WHERE ProductSubcategoryID = @Category";
  using (SqlConnection con = new SqlConnection(connect))
  {
   SqlDataAdapter da = new SqlDataAdapter(sql, con);
   da.SelectCommand.Parameters.AddWithValue("@Category", category);
   DataSet ds = new DataSet();
   da.Fill(ds, "Products");
   return ds;
  }
 }
 public DataSet GetProductByProductID(Int32 pid)
 {
  String connect = ConfigurationManager.ConnectionStrings[
      "AdventureWorksConnectionString1"].ConnectionString;
  String sql = "SELECT ProductID, Name, ProductNumber, "
        + "SafetyStockLevel, Color, FinishedGoodsFlag, "
        + "MakeFlag, ReorderPoint, StandardCost, ListPrice, "
        + "Size, DaysToManufacture, Weight, "
        + "WeightUnitMeasureCode, SizeUnitMeasureCode, "
        + "ProductSubcategoryID, Style, Class, ProductLine, "
        + "ProductModelID, SellStartDate, SellEndDate, "
        + "DiscontinuedDate "
        + "FROM AdventureWorks.Production.Product "
        + "WHERE ProductID = @ProductID";
  using (SqlConnection con = new SqlConnection(connect))
  {
   SqlDataAdapter da = new SqlDataAdapter(sql, con);
   da.SelectCommand.Parameters.AddWithValue("@ProductID", pid);
   DataSet ds = new DataSet();
   da.Fill(ds, "Products");
   return ds;
  }
 }
 public SqlDataReader GetCategoryList()
 {
  String connect = ConfigurationManager.ConnectionStrings[
      "AdventureWorksConnectionString1"].ConnectionString;
  String sql = "SELECT ProductSubcategoryID, Name "
        + "FROM AdventureWorks.Production.ProductSubcategory";
  SqlConnection con = new SqlConnection(connect);
  try
  {
   con.Open();
   SqlCommand cmd = new SqlCommand(sql, con);
   return cmd.ExecuteReader(CommandBehavior.CloseConnection);
  }
  catch
  {
   return null;
  }
 }
}

---

Note - The data access class listed here is designed to be only a basic demonstration of using the ObjectDataSource control. A "real-world" example would generally contain a great deal more code, incorporate update methods, and use stored procedures rather than declarative SQL statements.

---

The ASP.NET page calls these methods, via the data source control, to fetch rows from the database table. Therefore, the next step after deploying the data access class file is to remove the SqlDataSource controls from the page and replace them with instances of the ObjectDataSouce control. Using the Configure Data Source Wizard for each one, you connect these controls to the data access layer so that they can expose the same rowsets as the SqlDataSource controls did. No changes are required to the UI of the page, and––besides the data-access layer class––there is still no code required!

The figure to the left shows the only two steps required to connect the ObjectDataSource that populates the DropDownList of categories to the GetCategoryList method, because this method accepts no parameters. The ObjectDataSource for the GridView control requires a parameter, and this is specified in the third step of the Configure Data Source Wizard––it recognizes that a parameter is required, and you link it up to the SelectedValue property of the DropDownList just as you did when using a SqlDataSource control earlier.

Having added a third ObjectDataSource control to replace the SqlDataSource that populates the DetailsView control, you just connect the DropDownList, GridView, and DetailsView controls to the new data source controls using the tasks panes for each one. Because the data layer does not support editing, the tasks panes do not display the Enable Editing checkbox.

Now you can run the page, and see that—with the exception of editing features––the results are the same (see the figure to the right). This is just what you want and expect, because the UI has not changed. In addition, the work involved in changing to a data access/business object layer from declarative SQL statements is not difficult or time-consuming.

It has taken a couple of hours to build the new page for Margaret at AdventureWorks Trading Inc., and you are ready for a break. However, Lucy (the database administrator), has just been told about the new features in the application. It is her job to keep the database running smoothly and efficiently, and she is worried that you are going to slow things down. You are generating constant hits on her database server for every postback as users sort, page, filter, select, and edit rows.

Lucy is a great believer in caching data where it does not change very often, and wants you to implement this in the new page. No, not next week, but now. It looks very much like the game of golf you were planning is not going to happen today.

AdventureWorks Trading Inc. uses the new SQL Server 2005 database, and so you can take advantage of a feature called database cache invalidation to improve performance and reduce database server loading. This makes much more sense than the traditional technique of caching for a specific period, based on a best guess as to how often the data might change.

ASP.NET database cache invalidation supports both SQL Server 2000 and SQL Server 2005. In SQL Server 2000, you use the special tool named aspnet_regsql (in the %windir%\Microsoft.NET\Framework\[version] folder of your machine) to prepare the database and the table containing your source data. You also have to edit your Web.Config file (see Chapter 11 for more details).

In SQL Server 2005, database cache invalidation depends on the Broker Service feature of the database. This allows a SqlCacheDependency to be linked to a data source control so that the data is cached within ASP.NET and only refreshed from the database when it changes (or when another notifiable event such as a server restart occurs on the database server).

All that is required, when you use the data source controls, is to add an OutputCache directive to the page that includes a SqlDependency attribute.

<%@OutputCache SqlDependency="CommandNotification"
 Duration="60" VaryByParam="*" %>

---

Note - Note that you must enable the Broker Service for the database, and grant the relevant permissions, before using the Command Notification architecture. For more details, see http://msdn.microsoft.com/library/enus/dnvs05/html/querynotification.asp.

---

Now you can run the page and then refresh it without causing a database query to occur (you can monitor database activity using the SQL Profiler tool that comes with SQL Server). However, if you open the source table in Visual Studio or Visual Web Developer and change one of the column values, you will see that the next time you refresh the page there is a hit on the database.