4. Caching with the Data Source Controls
The SqlDataSource and ObjectDataSource
support built-in data caching. Using caching with these controls is
highly recommended, because they can be more inefficient than
handwritten data access code. For example, they query the data source
once for every bound control, so if you have three controls bound to the
same data source, three separate queries are executed against the
database just before the page is rendered. Even a little caching can
reduce this overhead dramatically.
To support caching, the SqlDataSource and ObjectDataSource controls use the same properties, which are listed in Table 2.
Table 2. Caching Properties of the Data Source Controls
| Property | Description |
|---|
| EnableCaching | If True, switches caching on. It's False by default. |
| CacheExpirationPolicy | Uses
a value from the DataSourceCacheExpiry enumeration—Absolute for
absolute expiration (which times out after a fixed interval of time), or
Sliding for sliding expiration (which resets the time window every time
the data object is retrieved from the cache). |
| CacheDuration | Determines
the number of seconds to cache the data object. If you are using
sliding expiration, the time limit is reset every time the object is
retrieved from the cache. The default value, 0, keeps cached items
perpetually. |
| CacheKeyDependency and SqlCacheDependency | Allow
you to make a cached item dependent on another item in the data cache
(CacheKeyDependency) or on a table in your database
(SqlCacheDependency).
|
4.1. Caching with SqlDataSource
When you enable caching for
the SqlDataSource control, you cache the results of the SelectCommand.
However, if you create a select query that takes parameters, the
SqlDataSource will cache a separate result for every set of parameter
values.
For example, imagine you create a
page that allows you to view employees by city. The user selects the
desired city from a list box, and you use a SqlDataSource control to
fill in the matching employee records in a grid (see Figure 3).
There are two
SqlDataSource controls at work in this example. The first SqlDataSource
gets the list of cities for the drop-down list. These results don't
change often, and so they are cached for one hour (3600 seconds):
<asp:SqlDataSource ID="sourceEmployeeCities" runat="server"
ProviderName="System.Data.SqlClient"
EnableCaching="True" CacheDuration="3600"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT DISTINCT City FROM Employees">
</asp:SqlDataSource>
<asp:DropDownList ID="lstCities" runat="server"
DataSourceID="sourceEmployeeCities"
DataTextField="City" AutoPostBack="True">
</asp:DropDownList>
The second SqlDataSource gets
the employees in the currently selected city. These results are cached
for 600 seconds and bound to a GridView:
<asp:SqlDataSource ID="sourceEmployees" runat="server"
ProviderName="System.Data.SqlClient"
EnableCaching="True" CacheDuration="600"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, City
FROM Employees WHERE City=@City">
<SelectParameters>
<asp:ControlParameter ControlID="lstCities" Name="City"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server"
DataSourceID="sourceEmployees" ... >
...
</asp:GridView>
This SqlDataSource is a bit more
sophisticated because it uses a parameter. Each time you select a city, a
separate query is performed to get just the matching employees in that
city. The query is used to fill a DataSet, which is then cached for up
to ten minutes (600 seconds). If you select a different city, the
process repeats, and the new DataSet is cached separately. However, if
you pick a city that you or another user has already requested, the
appropriate DataSet is fetched from the cache (provided it hasn't yet
expired).
Thus, this single
SqlDataSource can result in a surprisingly large number of cache
entries. If there are 20 different cities in your list (and therefore 20
different possible parameter values), you can end up with as many as 20
different DataSet objects in the cache at once.
NOTE
SqlDataSource caching
works only when the DataSourceMode property is set to DataSet (the
default). It doesn't work when the mode is set to DataReader, because
the DataReader object maintains a live connection to the database and
can't be efficiently cached. If you try to use caching with the
DataReader mode, you'll receive a NotSupportedException when you bind
the grid.
On the other hand, if the
parameter values are all used with similar frequency, this approach
isn't as suitable. One of the problems it imposes is that when the items
in the cache expire, you'll need multiple database queries to
repopulate the cache (one for each combination of parameter values),
which isn't as efficient as getting the combined results with a single
query.
If you fall into the second
situation, you can change the SqlDataSource so it retrieves a DataSet
with all the employee records and caches that. The SqlDataSource can
then extract just the records it needs to satisfy each request from the
DataSet. This way, a single DataSet with all the records is cached,
which can satisfy any parameter value.
To use this technique, you need to rewrite your SqlDataSource to use filtering. First, the select query should return all the rows and not use any SelectParameters:
<asp:SqlDataSource ID="sourceEmployees" runat="server"
SelectCommand=
"SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees"
...>
</asp:SqlDataSource>
Second, you need to define
the filter expression. This is the portion that goes in the WHERE clause
of a typical SQL query. However, this has a catch—if you're supplying
the filter value from another source (such as a control), you need to
define one or more placeholders, using the syntax {0} for the first
placeholder, {1} for the second, and so on. You then supply the filter
values using the <FilterParameters> section, in much the same way
you supplied the select parameters in the first version.
Here's the completed SqlDataSource tag:
<asp:SqlDataSource ID="sourceEmployees" runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand=
"SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees"
FilterExpression="City='{0}'" EnableCaching="True">
<FilterParameters>
<asp:ControlParameter ControlID="lstCities" Name="City"
PropertyName="SelectedValue" />
</FilterParameters>
</asp:SqlDataSource>
|
Don't use filtering
unless you are using caching. If you use filtering without caching, you
are essentially retrieving the full result set each time and then
extracting a portion of its records. This combines the worst of both
worlds—you have to repeat the query with each postback, and you fetch
far more data than you need each time.
|
|
4.2. Caching with ObjectDataSource
The ObjectDataSource
caching works on the data object returned from the SelectMethod. If you
are using a parameterized query, the ObjectDataSource distinguishes
between requests with different parameter values and caches them
separately. Unfortunately, the ObjectDataSource caching has a
significant limitation—it works only when the select method returns a
DataSet or a DataTable. If you return any other type of object, you'll
receive a NotSupportedException.
This limitation is
unfortunate, because there's no technical reason you can't cache custom
objects in the data cache. If you want this feature, you'll need to
implement data caching inside your method by manually inserting your
objects into the data cache and retrieving them later. In fact, caching
inside your method can be more effective, because you have the ability
to share the same cached object in multiple methods. For example, you
could cache a DataTable with a list of products and categories and use
that cached item in both the GetProductCategories() and
GetProductsByCategory() methods.
|
The only consideration you
should keep in mind is to make sure you use unique cache key names that
aren't likely to collide with the names of cached items that the page
might use. This isn't a problem when using the built-in data source
caching, because it always stores its information in a hidden slot in
the cache.
|
|
If your custom class
returns a DataSet or DataTable and you do decide to use the built-in
ObjectDataSource caching, you can also use filtering as discussed with
the SqlDataSource control. Just instruct your ObjectDataSource to call a
method that gets the full set of data, and set the FilterExpression to
retrieve just those items that match the current view.
