In Part 2 we looked at the Cache.Insert method
extensively. In this third and final part we'll examine a real-world application that utilizes data caching
to provide increased performance for a pagable DataGrid.
A Real-World Example
Over a year ago I had written another article here on 4Guys discussing a method of
paging a .NET datagrid with exact count - Custom ASP.NET Datagrid Paging With Exact Count.
I went on to show how you can cleverly page a datagrid and always show how many
rows are coming up, like "Next 5 >" and right before the last page you'll see "Next 1 >", for
instance, assuming there would be just one record to be displayed on the last page.
Now this was cool, and the emails received attested this fact that people enjoyed it and
found it useful. Nevertheless, as Scott
Mitchell pointed out, the DataGrid by default has one
particular flaw. This flaw in question is when displaying your DataGrid result set with say 5,000
records, upon each consecutive paging action, the database get hit again, and pulls in all 5,000
records in again! This is an obvious performance issue that does affect the
application and can greatly diminish scalability.
There are other means to remedy this. One way is creating a stored procedure that returns only
the pertinent records, as discussed at: Paging through Records using a Stored
Procedure. I have used this method on occasion prior to utilizing .NET caching - it works well
but the amount of code you need to write is quite substansive. Additionally, even with the stored procedure
approach the database must be hit each time the user pages through the data.
By caching the result set in the data cache you can allow the user to page through the cached
data, thereby not enduring any database hits except for the first time the item is loaded into
the cache (and any other time it gets evicted and needs to be reinserted into the cache).
The only potential downside is that the cached data may become stale over time as the underlying
database data changes. With some clever programming, though, you can set up your database inserts,
updates, and deletes such that when new data is added to the underlying database the cached data is
invalidated. See Invalidating an ASP.NET Web Application Cache Item from SQL Server
for more details.
The caching for my example resides solely in the BindMyDataGrid() method, which is responsible
for binding the data to the DataGrid. The first line of code in this subroutine grabs the cached
DataSet from the data cache, as can be seen below:
Sub BindMyDataGrid()
'Programmatic Caching Setup
Dim DataGridCache As DataSet = CType(Cache.Get("DataGridCache"),DataSet)
...
Of course the DataSet might not exist in the cache. We might not have added it, or it may have
expired or been evicted. Hence, before doing anything else we must check to see if
DataGridCache is equal to Nothing. If it is, then we must populate our
DataSet from the database and store it back in the cache. If it is not, then we can just
proceed to the code that binds the DataSet to the DataGrid.
'Continued from previous code block...
...
If DataGridCache is Nothing Then
'Populate the DataSet with data from the database
Const CommandText As String = _
"SELECT FAQID, Description FROM tblFAQ ORDER BY FAQID"
'The connection to our database
Dim myConnection as New _
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim DS As New DataSet()
myCommand.Fill(DS)
'Specify the DataSource for the DataGrid is the DataSet
'we just populated
MyDataGrid.DataSource = DS
'Now insert dataset into cache, specifying that it should
'expire in 10 minutes
Cache.Insert ("DataGridCache", DS, Nothing, _
DateTime.Now.AddMinutes(10), TimeSpan.Zero)
lblCacheInfo.text = "DataGrid was populated from the database..."
'Specify what time the cache was updated
Application("TimeCachedDataSetAdded") = DateTime.Now
'Determine how many total records we have
RcdCount = CInt(DS.Tables(0).Rows.Count.ToString())
Else
'The DataSet is in the cache.
lblCacheInfo.text = "DataGrid was used from cache. The cache " & _
was populated at " & _
Application("TimeCachedDataSetAdded").ToString()
'Populate datagrid from cache.
MyDataGrid.DataSource = DataGridCache
'Calculate the total # of records
RcdCount = CInt(DataGridCache.Tables(0).Rows.Count.ToString())
End If
'Bind the datagrid from either source
MyDataGrid.DataBind()
ShowStats() 'Displays what page we're on and such
End Sub
Other than the BindMyDataGrid() method shown above the only other important part of the
ASP.NET Web page is the HTML section, which sets certain DataGrid properties to allow for paging.
The more important properties of the DataGrid Web control have been bolded. Be sure to
check out the live demo to see how the pieces fit together.
For more information on the DataGrid be sure to read:
An Extensive Examination of the DataGrid Web Control.
Summary
In this article we examined the awesome power and useful features of the .NET caching API.
Specifically, in this article we looked at a real-world example where the data for a pagable DataGrid
was cached in the data cache, saving round trips to the database each time the user steps through
a page of data.
