Introduction
A lots of developers use DataReader
or DataSet
and bind to a data control directly. Although there isn't anything wrong with that, there are alternatives that can just come in handy. One such alternative is using strongly typed collection objects for data retrieval. In this article, I demonstrate how you can retrieve data of a database table, populate a strongly typed collection, and bind it to a DataGrid
control. This method requires more coding than using DataTable
. In this example, I used MS SQL Server 2000 Pubs database, Jobs entity.
First, we create an interface
and Job
class, which only holds private members and public properties, specified according to Jobs entity of Pubs database.
public interface IJob
{
short JobId{ get; set; }
string JobDesc{ get; set; }
byte MinLvl{ get; set; }
byte MaxLvl{ get; set; }
}
public class Job
{
private short job_id;
private string job_desc;
private byte min_lvl;
private byte max_lvl;
public Job()
{
}
public short JobId
{
get
{
return this.job_id;
}
set
{
this.job_id = value;
}
}
public string JobDesc
{
get
{
return this.job_desc;
}
set
{
this.job_desc = value;
}
}
public byte MinLvl
{
get
{
return this.min_lvl;
}
set
{
this.min_lvl = value;
}
}
public byte MaxLvl
{
get
{
return this.max_lvl;
}
set
{
this.max_lvl = value;
}
}
public override ToString()
{
return String.Format(@"
<Job>
<JobId>{0}</JobId>
<JobDesc>{1}</JobDesc>
<MinLvl>{2}</MinLvl>
<MaxLvl>{3}</MaxLvl>
</Job>",
this.JobId, this.JobDesc, this.MinLvl, this.MaxLvl);
}
}
Next, I want to create JobComparer
class that sorts items of my collection. This class has two properties, SortField
and SordOrder
. SortField
basically is a property of the Job
class. SortOrder
is a description of how the collection must be sorted, ascending or descending. Also, this class has one public method that does our comparison job.
public class JobComparer : IComparer
{
public JobComparer(){}
private string sortField;
private string sortOrder;
public string SortField
{
get
{
return sortField;
}
set
{
sortField = value;
}
}
public string SortOrder
{
get
{
return sortOrder;
}
set
{
sortOrder = value;
}
}
public int Compare(object x, object y)
{
Job xjob = (Job)x;
Job yjob = (Job)y;
switch (SortField)
{
case ("JobDesc"):
if (xjob.JobDesc.CompareTo(yjob.JobDesc) == -1)
return (SortOrder == "ASC") ? -1 : 1;
else if (xjob.JobDesc.CompareTo(yjob.JobDesc) == 1)
return (SortOrder == "ASC") ? 1 : -1;
else
return 0;
case ("MinLvl"):
if (xjob.MinLvl < yjob.MinLvl)
return (SortOrder == "ASC") ? -1 : 1;
else if (xjob.MinLvl > yjob.MinLvl)
return (SortOrder == "ASC") ? 1 : -1;
else
return 0;
case ("MaxLvl"):
if (xjob.MaxLvl < yjob.MaxLvl)
return (SortOrder == "ASC") ? -1 : 1;
else if (xjob.MaxLvl > yjob.MaxLvl)
return (SortOrder == "ASC") ? 1 : -1;
else
return 0;
default:
if (xjob.JobId < yjob.JobId)
return (SortOrder == "ASC") ? -1 : 1;
else if (xjob.JobId > yjob.JobId)
return (SortOrder == "ASC") ? 1 : -1;
else
return 0;
}
}
}
Now, I'm ready to build the initial collection class. Obviously, this process will become much simpler when Microsoft introduces generics, but for now, this process is required each time you need a strongly typed list.
public class JobCollection : CollectionBase
{
public JobCollection()
{
}
public void Add(Job job)
{
List.Add(job);
}
public void Remove(Job job)
{
List.Remove(job);
}
public void Sort(string sortField, string sortOrder)
{
JobComparer comparer = new JobComparer();
comparer.SortField = sortField;
comparer.SortOrder = sortOrder;
this.InnerList.Sort(comparer);
}
public Job this[int index]
{
get
{
return (Job)List[index];
}
set
{
base.List[index] = value;
}
}
}
The JobCollection
class provides basic collection functionality such as add, remove, and sort. This class is inherited from the CollectionBase
class. Next step: retrieving the job data and populating the JobCollection
class with Job
classes. Basically, this class is our DAL (data access layer). I am not going to write actual code of how to get data from database, I just included methods that should be at this level. You can always modify this the way you want it, there are lots of ways to do this. One of the solutions I wrote in my previous article, you can view it here: "Building robust middle tier".
public class JobDB
{
public JobDB()
{
}
public short AddJob(Job job)
{
}
public void UpdateJob(Job job)
{
}
public void DeleteJob(short jobId)
{
}
public Job GetJob(short jobId)
{
}
public JobCollection GetJobs()
{
}
}
There is one more thing we need to do before we bind JobCollection
to our DataGrid
. For example, if you try to save JobCollection
in a ViewState at this moment, you will get an error "... must be marked as Serializable or have a TypeConverter other than ReferenceConverter to be put in viewstate". Type converters are used in the .NET framework to convert objects to different types of objects. One of the most common types of converters is one which converts an object
to a string
, or a string
to an object
. Type converters descend from the common base class “TypeConverter
” and are associated with a class by use of the TypeConverterAttribute
. So what we need to write is our JobConverter
class. I created a generic converter because I do not implement any specific logic that describes Job
class.
public class GenericTypeConverter : TypeConverter
{
public GenericTypeConverter(){}
public override bool
CanConvertTo(ITypeDescriptorContext context, Type destType)
{
if (destType == typeof(InstanceDescriptor))
return true;
return
base.CanConvertTo(context, destType);
}
public override object ConvertTo(ITypeDescriptorContext context,
System.Globalization.CultureInfo culture,
object val, Type destType)
{
if (destType == typeof(InstanceDescriptor))
{
Type valueType = val.GetType();
ConstructorInfo ci =
valueType.GetConstructor(System.Type.EmptyTypes);
return new InstanceDescriptor(ci, null, false);
}
return base.ConvertTo(context, culture, val, destType);
}
}
To associate the TypeConverter
with the JobCollection
class, a TypeConverterAttribute
is required. All you need to do is just add the following attribute above the Job
class. Also, you need to mark your collection class as [Serializable]
.
[TypeConverter(typeof(GenericTypeConverter))]
public interface IJob
{
...
}
[TypeConverter(typeof(GenericTypeConverter))]
[Serializable]
public class Job
{
...
}
[Serializable]
public class JobCollection
{
...
}
[Serializable]
public class JobComparer
{
...
}
Now we are ready to bind our collection to a DataGrid
. Again, I am not going to write the complete web form, only some specific methods.
public class JobForm : System.Web.UI.Page
{
protected DataGrid TblJobs;
public string SortOrder
{
get
{
return (string)ViewState["SortOrder"];
}
set
{
ViewState["SortOrder"] = value;
}
}
public string SortField
{
get
{
return (string)ViewState["SortField"];
}
set
{
ViewState["SortField"] = value;
}
}
private JobCollection Jobs
{
get
{
if (ViewState["Jobs"] == null)
{
JobDB db = new JobDB();
ViewState["Jobs"] = db.GetJobs();
}
return (JobCollection)ViewState["Jobs"];
}
}
private void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}
private void BindData()
{
TblJobs.DataSource = Jobs;
TblJobs.DataBind();
}
private void TblJobs_SortCommand(object sender,
DataGridSortCommandEventArgs e)
{
SortField = e.SortExpression.ToString();
SortOrder = (SortOrder == "ASC") ? "DESC" : "ASC";
Jobs.Sort(SortField, SortOrder);
BindData();
}
...
this.TblJobs.SortCommand += new
System.Web.UI.WebControls.DataGridSortCommandEventHandler
(this.TblJobs_SortCommand);
...
}
Conclusion
In a real world project, you'll very likely end up dealing with much more complex situations. The techniques demonstrated in the sample pages show how you can enhance performance in the application, by caching the collection in the ViewState. However, be careful when combining it with data manipulation. In this article, I tried to make a simple example of how you can use the strongly typed collection to bind to a DataGrid
.