I am currently working or a project that, like many projects has many sets of code values. Some examples of code values in the project that I am working on would be Call Results, Degree Types, or Genders. On the UI of this project these are (generally) represented as drop down lists. In order to create a more consistent development environment, I decided to create a CodeDropDownList. This is a custom control, extending a System.Web.UI.WebControls.DropDownList that has one extra parameter:
CodeName. The goal is to be able to do something like <mike:CodeDropDownList runat=”server” id=”ddlGender” CodeName=”Gender” /> and have the drop down list auto populated with (“Male”, “Female”).
There are a few issues that you need to be aware of:
- Where to put your data loading code.
- How to cause the “DataBind” method to be called when it should be called.
- Performance improvements.
Where to load the data
The ideal time to load the data, is right before the list is DataBound. I accomplish this by overriding the DataBind method of the DropDownList with something like:
public override void DataBind() {
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=TestingAjax;Integrated Security=True;"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "code_gender_select";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
this.DataSource = dtr;
this.DataTextField = "GenderName";
this.DataValueField = "GenderId";
base.DataBind();
}
}
}
}
I have changed this to use generic ADO.NET database access rather than any framework so that his will be as easy to understand (and dependant free) as possible.
The problem with this is that it does not work. When a page loads with this custom control on it the control is not databound, unless it is inside another databound control.
How to get this to work correctl
To resolve this issue I used the Reflector and looked at what was causing this problem. The method at fault ended up being the EnsureDataBound() method in the BaseDataList class.
protected void EnsureDataBound() {
try
{
this._throwOnDataPropertyChange = true;
if (this.RequiresDataBinding && (this.DataSourceID.Length > 0))
{
this.DataBind();
}
}
finally
{
this._throwOnDataPropertyChange = false;
}
}
According to Microsoft, the EnsureDataBound method does the following: “Verifies that the data listing control requires data binding and that a valid data source control is specified before calling the DataBind method.” We can change this method to the following:
protected override void EnsureDataBound() {
try
{
if (this.RequiresDataBinding)
{
this.DataBind();
}
}
catch { }
}
This small change makes everything work great.
Performance Improvements
These code tables represent relatively static data, so a huge performance boost can be gained by caching the data from the database. The following methods are realtivley self explanatory.
I use the following class to help cache the data:
public class DropDownListItm {
private string _Text;
public string Text
{
get {return _Text;}
}
private string _Value;
public string Value
{
get {return _Value;}
}
public DropDownListItm(string text, string value)
{
_Text=text;
_Value=value;
}
}
A new method that encapsulates the database access:
public IEnumerable GetDataFromDB() {
List<DropDownListItm> lItems = new List<DropDownListItm>();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=TestingAjax;Integrated Security=True;"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "code_gender_select";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while(dtr.Read())
{
lItems.Add(new DropDownListItm(dtr["GenderName"].ToString(), dtr["GenderId"].ToString()));
}
}
}
}
return lItems; }
A new method for managing the caching:
public IEnumerable GetDataSource() {
string key = "CodeDrowDownListTest_" + CodeName;
object item = Page.Cache.Get(key);
if (item == null)
{
item = GetDataFromDB();
Page.Cache.Insert(key, item, null, System.DateTime.UtcNow.AddHours(1), System.Web.Caching.Cache.NoSlidingExpiration);
}
return (IEnumerable)item; }
And finally, our DataBind() method:
public override void DataBind() {
this.DataSource = GetDataSource();
this.DataTextField = "Text";
this.DataValueField = "Value";
base.DataBind();
}
All these methods and classes exist in the our CodeDropDownList class.
Full class listing:
using System; using System.Data; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Diagnostics;
namespace MikeThomas.CustomControls {
public class CodeDropDownList : DropDownList
{
public string CodeName
{
get
{
if (ViewState["CodeName"] != null)
{
return ViewState["CodeName"].ToString();
}
else
{
return String.Empty;
}
}
set { ViewState["CodeName"] = value; }
}
protected override void EnsureDataBound()
{
try
{
if (this.RequiresDataBinding)
{
this.DataBind();
}
}
catch { }
}
public override void DataBind()
{
this.DataSource = GetDataSource();
this.DataTextField = "Text";
this.DataValueField = "Value";
base.DataBind();
}
public IEnumerable GetDataSource()
{
string key = "CodeDrowDownListTest_" + CodeName;
object item = Page.Cache.Get(key);
if (item == null)
{
item = GetDataFromDB();
Page.Cache.Insert(key, item, null, System.DateTime.UtcNow.AddHours(1), System.Web.Caching.Cache.NoSlidingExpiration);
}
return (IEnumerable)item;
}
public IEnumerable GetDataFromDB()
{
List<DropDownListItm> lItems = new List<DropDownListItm>();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=TestingAjax;Integrated Security=True;"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "code_gender_select";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while(dtr.Read())
{
lItems.Add(new DropDownListItm(dtr["GenderName"].ToString(), dtr["GenderId"].ToString()));
}
}
}
}
return lItems;
}
public class DropDownListItm
{
private string _Text;
public string Text
{
get {return _Text;}
}
private string _Value;
public string Value
{
get {return _Value;}
}
public DropDownListItm(string text, string value)
{
_Text=text;
_Value=value;
}
}
}
}