LANGUAGES: VB .NET
TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server
Cache data for your dropdown lists.
By Wayne S. Freeze
Whenever possible, I like to use dropdown boxes in my Web applications. Dropdown lists allow the user to select from a set of valid values for a particular field, which eliminates typing errors. Although this helps the user, it makes more work for you because you must enter the choices into the dropdown list control.
You can populate a list of choices in a dropdown list by entering them directly in the form designer, but I prefer to store the data in the database. This makes it easier to update the list of values while ensuring all dropdown lists using these values are identical.
Get Cached Data
Although storing the data for a dropdown list in the database is convenient, it requires an extra call to the database each time you want to load the dropdown list. This isn't efficient. The caching mechanism is an ideal way to improve performance.
Because I'm the Lazy Programmer, I created a generic function that hides the interaction with the cache and returns a DataView object containing the data (see Figure 1). The GetCachedData takes a single parameter, which is the name of the table that should be retrieved. (The sample code in this article is available; see end of article for download details.)
Public Function GetCachedData(ByVal table As String) _
Dim ds As DataSet
Dim adpt As SqlDataAdapter
Dim conn As SqlConnection
Dim cmd As SqlCommand
Status.Text = "Cache"
CurrentTime.Text = DateTime.Now.ToString
If Cache("DataDriven." & table) Is Nothing Then
ds = New DataSet()
conn = New SqlConnection _
cmd = New SqlCommand("Get" & table, conn)
cmd.CommandType = CommandType.StoredProcedure
adpt = New SqlDataAdapter(cmd)
Cache.Insert("DataDriven." & table, ds.Tables(table), _
Nothing, DateTime.MaxValue, TimeSpan.FromMinutes(1))
Status.Text = "Database"
CacheTime.Text = DateTime.Now.ToString
Return CType(Cache("DataDriven." & table), _
Figure 1. The GetCachedData function will load a table into cache if it isn't present, then return the data as a DataView to the calling program.
The function begins by declaring some variables that will be used to access the database and initializing the Status TextBox to "Cache." Then it checks to see if the table is in the cache. Because the cache is global for the entire server, I use the name of the application as a prefix to the table name to ensure I don't have a conflict with another application.
Next, a new SqlConnection object is created using the connection string from the Web.Config file. The SqlConnection object is used to create a new SqlCommand object. The name of the stored procedure is constructed by concatenating the string "Get" with the table name. The data is retrieved from the database by creating the SqlDataAdapter object and using it to fill a DataSet object.
A call to Cache.Insert stores the table in the cache. Cache.Insert allows you to specify either an absolute expiration date when the information from the cache will be removed or a sliding time span that specifies the time that must elapse since the information was last accessed before it can be removed from cache. The benefit of a sliding time span is that the data is there while the application is actively being used, while releasing the memory when the application is idle.
For this application, I specified a time span of one minute, which is long enough to see the effect of using the cache and short enough so you aren't frustrated while waiting to verify that the information in the cache expired. In a production application, I would specify a larger value such 15 minutes to minimize the number of times the application needs to get the information from the database.
After storing the table in cache, the Status TextBox is updated to reflect that the data was retrieved from the database, and the value CacheTime TextBox is set to reflect the time the cache was updated.
The routine ends by retrieving the table from cache and using the CType function to return a properly typed object reference to the table's DefaultView.
Load a Dropdown List
To demonstrate this technique, I've created a simple program that allows you to test the GetCachedData function (see Figure 2). To run this program, click on the Load DropDown button. This will populate the dropdown list with the data from the database. Pressing the Load DropDown button a second time within 60 seconds will retrieve the data for the dropdown list from cache. If you wait more than 60 seconds, the data will expire from cache and the program will retrieve the data from the database again.
Figure 2. Press the Load DropDown button to populate the dropdown list from the database. Pressing the button again within 60 seconds will load the data from cache.
This code contains the code used to load the dropdown list:
States.DataSource = GetCachedData("States")
States.DataTextField = "StateName"
States.DataValueField = "State"
New ListItem("** Please select a state", "0"))
States.SelectedIndex = 0
This code uses the GetCachedData function to populate a dropdown list from the database and adds a default value for the dropdown box that will be displayed initially on the Web page.
The easiest way to load the information is to use data binding, so the dropdown list's DataSource property is set to the value returned from the GetCachedData function. Then the DataTextField and DataValueField properties are set to the appropriate column names. DataBind is called to copy the data from the table to the dropdown list. Next, the prompt for the dropdown list is added as the first position, and the SelectedIndex property is set to zero to display the prompt value in the dropdown list.
The sample code referenced in this article is available for download.
Wayne S. Freeze is a full-time author with more than a dozen books to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of development experience and a master's degree in management information systems, as well as degrees in computer science and engineering. Visit his Web site at http://www.JustPC.com or e-mail him at mailto:[email protected].