Binding to Database Key/Code Tables with Caching

• Download code table example/source project - 14.4 KB

Introduction

The problem

Large OnLine Transaction Processing applications primarily use near third normal form databases that can have many relatively small code tables that provide an ID key for storage in large tables and user-friendly descriptions for use in your application's presentation. .NET allows binding an ID to DropDownLists or RadioButtonLists, but how about displaying the description when data is being viewed as read-only? The GridView above has 3 of the 4 columns that get their value from code tables. If you were a user, which would you rather see?

or

When a row is retrieved for viewing, you will retrieve the IDs for many of the columns rather than the descriptions that you want to display to the user. You can join all those columns to their respective code tables to get the descriptions, but that makes for far more complicated statements and Stored Procedures. E.g.:

select name, state, mstatid, genderid from userdata where uid = @uid

or

select u.name s.statedesc, m.mstatdesc, g.genderdesc from userdata u join states 
  s on u.state = s.state join mstat m on u.mstatid = m.mstatid join 
  gender g on u.genderid = g.genderid where uid = @uid

Other problems that arise when working with code tables is that occasionally a code will no longer be wanted, but deleting the code causes problems for old entries that used it. All the statements that you created to handle the joins will probably no longer work as you originally intended. As an example, say, you buy a cell phone plan 'A'. Later, the cell phone company no longer offers plan 'A' and now has plan 'B'. Plan 'A' is still valid for the person that purchased it, but any new customer would not have plan 'A' as an option.

The requirements of the solution

• No joining of code tables to business data.
• Code tables will need to be cached for performance.
• Make it easy to bind to code tables, whether editing or using as read-only.
• Allow all code table statements to be managed in a single place.
• Allow for any code table row to be disabled so that:
• In DropDownLists and RadioButtonLists, the disabled codes will not be in the list, if desired.
• In read-only mode, if an existing record has a disabled code, it will get the correct description.
• In edit mode, if an existing record has a disabled code, only enabled descriptions and the currently bound disabled description will show up in the list.
• Each code table is accessed by a simple descriptive name.

Overview

This example has two primary classes and an interface that can be used in any ASP.NET 2.0 application.

1. CodeTableCache.cs - This retrieves the statements to create a DataSet for each code table when needed, and then stores them in the cache. When only enabled code tables are needed, DataViews are used on the cached DataSets. If an item is in the cache, it will return that value. If it is not in the cache, it will generate the DataSet from the table and put it in the cache.
2. CodeTableDataSource.cs - This is a control that extends the ObjectDataSource, and can easily bind to the correct code table data set for use by DropDownLists and RadioButtomLists.
3. ICodeTableList.cs - This is an interface that a custom class must implement so that the CodeTableCache can retrieve the simple code table names and their corresponding statements.

Also included in the download are the files necessary to see this capability in action:

1. CodeTableList.cs - Class that implements the ICodeTableList interface.
2. EditCodeTables.aspx - Web page to modify the code tables. This helps in testing the disabled code feature.
3. UseCodeTable.aspx - This is a working example of a table that has a text field and a lot of code table ID fields.
4. Default.aspx - Easy access to the other two pages.
5. web.config and global.asax.
6. BuildDatabase.sql and Inserts.sql - SQL Server Express scripts to create the database for this application.

To use this example, install in IIS or in a directory for use in Visual Studio. Create a database in SQL Server or Express, and execute the BuildDatabase.sql and Inserts.sql scripts to build the tables. The connection string in the web.config may need modification to access the database.

If you do not have SQL Server or Express, you can download it and the Management Studio from Microsoft for free: Microsoft SQL Server Express and Microsoft SQL Server Management Studio Express. I am not a SQL Server Express user, and I was unable to get a connection to the server to work properly on one of my systems. If you have the same problem, set up impersonation for the application in the web.config. The node to add is:

<identity impersonate="true" password="win_pwd" username="machinename\winlogin" />

Using the code

In an ASPX page, a data source can be added as simply as this. These are used for the list items of DropDownLists and RadioButtonLists. In the example, AgreementDS is used by two RadioButtonLists. So, if you were getting multiple addresses, only one state data source would need to be defined. The CodeType field must match the simple name given to the code table in the CodeTableList class. By default, "Disabled" codes will not show up in these lists. Set DisplayEnabledOnly to False to see all values.

<opp:CodeTableDataSource ID="StatesDS" runat="server" CodeType="States" />
<opp:CodeTableDataSource ID="GenderDS" runat="server" CodeType="Gender" />
<opp:CodeTableDataSource ID="MaritalStatusDS" runat="server" CodeType="MaritalStatus" />
<opp:CodeTableDataSource ID="SatisfactionVDS" runat="server" 
                         CodeType="Satisfaction" IdValue="SatisValue" />
<opp:CodeTableDataSource ID="SatisfactionQDS" runat="server" 
                         CodeType="Satisfaction" IdValue="SatisQuality" />
<opp:CodeTableDataSource ID="AgreementDS" runat="server" CodeType="Agreement" />

Notice that the "Satisfaction" CodeType has two entries with different IdValues. These are configured to add the code ID of the existing row to the list even if it is disabled. Remember the cell phone plan example above? Two additional things are needed to get this to work. First, in the GridView, DetailsView, or FormView, add the columns you need to the DataKey attribute. Second, in the code behind in the Page_Load, tell the CodeTableDataSource how to find the currently selected value.

SatisfactionVDS.DatakeyValues = TestTableDetail.DataKey.Values;
SatisfactionQDS.DatakeyValues = TestTableDetail.DataKey.Values;

This isn't limited to the new ASP.NET 2.0 GridView, DetailsView, and FormView although it was made for them. What is required is to set the DatakeyValues attribute to an IOrderedDictionay with keys that match the IdValue attribute.

For either a DropDownList or a RadioButtonList, set the DataValueField to "ID", the DataTextField to "Description", the DataSourceID to the appropriate CodeTableDataSource, and the SelectedValue to the bound field. To get a description back for the read-only values, just pass the Eval() value to the GetCodeDesc method with the correct CodeType directly in your ASPX page.

<asp:TemplateField SortExpression="AgreeUseAgain" HeaderText="Would you use again?" >
    <EditItemTemplate>
        <asp:RadioButtonList ID="RadioList2" DataValueField="ID" 
            DataTextField="Description" DataSourceID="AgreementDS" 
            Runat="server" SelectedValue='<%# Bind("AgreeUseAgain") %>' />
    </EditItemTemplate>
    <ItemTemplate>
        <asp:Label Runat="server" id="Label6" 
        Text='<%# CodeTableCache.GetCodeDesc("Agreement", 
                         Eval("AgreeUseAgain").ToString()) %>' />
    </ItemTemplate>
</asp:TemplateField>

How to integrate the library in your application

1. When using this in an application, add the files from the App_Code/OppSol directory to the same directory in the new application.
2. Create a class that implements the ICodeTableList class. Only four methods are required.

string GetStatement(string CacheCode);
string GetConnectionString(string CacheCode);
System.DateTime GetExpiration(string CacheCode);
bool IsAvailable(string CacheCode);

CacheCode is the simple name that is given to each code table. I.e.: the States code table can just be recognized by "States". The GetStatement method is the most important and it should get all the rows of the table. The columns required in the statement are "ID", "Description", and "Disabled". If your code table uses different column names, they must be aliased to these names. If you don't have a "Disabled" column, just return the literal 'false' which will make all rows enabled. If you add a disabled column at a later date, just change the statement here and it will be functional. The states table statement would look like this - select state AS ID, name AS Description, 'false' AS Disabled from states order by name. In the example application, I created a single code table that can manage many code tables. It has a codetype column that must match the CacheCode used in the application.

The GetConnectionString method will just return the connection string. If your code tables are in different databases or schemas, different connection strings can be used. The GetExpiration returns when a cached item should be removed from the cache. Code tables have slowly changing values, so they are great candidates for caching. Since they can change, putting a reasonable expiration is good practice. In a high volume site, even a short expiration like 1 minute will save many trips to the database. IsAvailable will just return if the CacheCode is a valid CacheCode.

The CodeTableList class in the example inherits from the StringDictionary for tracking CacheCodes and statements. Your class can use any method for tracking that information including an XML file or a separate database table.

3. Once you have your CodeTableList class created, it just needs to be registered with the CodeTableCache.

CodeTableCache is a static class, so there is only one instance for your entire application. A static class never needs to be instantiated with new. When you need to call a method, just call it. The best time to register your class with the CodeTableCache is at application startup. The global.asax has just such an event. Add the following code to your global.asax:

void Application_Start(object sender, EventArgs e) 
{ 
    if (OppSol.Software.CodeTableHelpers.CodeTableCache.StatementList == null) 
        OppSol.Software.CodeTableHelpers.CodeTableCache.StatementList = 
                                         new CodeTableList();
}

4. The last change to get ready to use the new capability is to add your CodeTableDataSource control and make it accessible in your web pages. I added it to the web.config, but it can also be done on a per page basis in the ASPX file.

   <pages>
       <controls>
           <add namespace="OppSol.Software.CodeTableHelpers" tagPrefix="opp"/>
       </controls>
   </pages>

That's it. I hope you find it as useful as I do.

Points of interest

I have used a slightly earlier revision of this in several good-sized applications, and it has worked very well. This example uses a SQLDataSource for the main tables, which I do not recommend. I like to use custom business objects which may be the next place that I integrate code tables with.

Another future enhancement may be a custom business object to hold the code table data which would allow some additional functionality. For now, the DataSet/DataView combination is just too easy though, so we will have to see.

What I like the least about this solution is the requirement to set the DatakeyValues attribute. I don't know another way to get the values from the currently bound row from the control in a more elegant manner. Anybody else have thoughts on that?

If you have any thoughts or enhancements with regard to code tables, add a comment to the discussion below.

History

• 10/03/2006 - First revision posted on CodeProject.