Generic ListHelper Class - .NET 4.5

• Download source - 491.2 KB

Introduction

This is an update to my previous article, Generically Populate List of Objects from SqlDataReader. The information posted in this article is intended primarily to update the generic list populator to version 4.5 of the .NET Framework and to add the ability to sort the list of objects based on a given property within the class. For the most part, the text of this article will be the same as the other, but the code has been modified and added to. With that said, let's get started.

While creating web applications, it is a very common practice to have a ‘list’ page that will display a list of objects to the user. The user will click on a link within one of the items inside that list to open a ‘detail’ page. This is common practice that most all of us have coded on a regular basis. The struggle we have is dynamically creating that list of objects to populate our tables with.

We have all written the following type of code on more than one occasion:

List<Models.Employee> result = new List<Models.Employee>();
while (reader.Read())
{
	Models.Employee item = new Models.Employee()
	{
		EmployeeID = (int)reader["EmployeeID"],
		FirstName = reader["FirstName"].ToString(),
		LastName = reader["LastName"].ToString(),
		Title = reader["Title"].ToString(),
		BirthDate = (reader["BirthDate"] == DBNull.Value ? (DateTime?)null : (DateTime?)reader["BirthDate"]),
		HireDate = (reader["HireDate"] == DBNull.Value ? (DateTime?)null : (DateTime?)reader["HireDate"]),
		City = reader["City"].ToString()
	};
	result.Add(item);
}

Background

While there is nothing wrong with the above code and the results, I must admit that I'm somewhat lazy and would like to not have to write that code for every class inside my project. That is where the desire for a 'generic' list populator came from. I wanted to create a class that could populate a list of any type of data without costing me performance and scalability. So the requirements for this class became:

• Use the models within C# to allow generic ('<T>') functionality
• Performance should be similar to populating a DataTable object with the DataTable.Load function
• Population of an item in the list should be type safe
• Population of an item in the list should allow for default values
• Population of an item in the list should allow for 'nullable' data elements

During the course of my research, I found some assistance with this option through an article on CodeProject titled Reflection Optimization Techniques that pointed me in the right direction. Unfortunately, it didn't provide everything I needed and was targeted at the .NET Framework 4.0 (my company is currently targeting version 3.5). So I took the information and continued to experiment and dig into MSDN until I had the result: GenericPopulator<T>

For the purposes of this article, I'm using the Employee table within the Northwind SQL Server sample database. Apart from creating the database, all of the code is available in the download package.

Using the GenericPopulator Class

The dynamic list population is quite simple to make use of. There are basically three steps necessary:

1. Create a data model that represents an individual item in the list
2. Execute a query, the results of which will populate a List<T> of your data model
3. Instantiate the GenericPopulator class and call the CreateList() function

Here is my data model for the Employee. As you can see, there is nothing special about the data model. It does allow for various data types to be tested, as well as nullable data types.

[Serializable]
public class Employee
{
	public int EmployeeID { get; set; }
	public string LastName { get; set; }
	public string FirstName { get; set; }
	public string Title { get; set; }
	public DateTime? BirthDate { get; set; }
	public DateTime? HireDate { get; set; }
	public string City { get; set; }
}

Executing the query and populating a list based on this data model is really quite simple.

var query = "select * from Employees order by LastName, FirstName";
var cmd = new SqlCommand(query, conn);
using (var reader = cmd.ExecuteReader())
{
	var employeeList = new GenericPopulator.ListHelper<Employee>().CreateList(reader);
	rptEmployees.DataSource = employeeList;
	rptEmployees.DataBind();

	reader.Close();
}

Explanation of the GenericPopulator Class

In order to dynamically populate a class, you must use Reflection to determine the various properties on the class. A simple function to populate a list using Reflection could look similar to the following code snippet:

public class ReflectionPopulator<T>
{
	public virtual List<T> CreateList(SqlDataReader reader)
	{
		var results = new List<T>();
		var properties = typeof(T).GetProperties();
	
		while (reader.Read())
		{
			var item = Activator.CreateInstance<T>();
			foreach (var property in typeof(T).GetProperties())
			{
				if (!reader.IsDBNull(reader.GetOrdinal(property.Name)))
				{
					Type convertTo = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
					property.SetValue(item, Convert.ChangeType(reader[property.Name], convertTo), null);
				}
			}
			results.Add(item);
		}
		return results;
	}
}

Unfortunately, using Reflection to dynamically populate a class with data can be time-consuming – particularly if you are populating a ‘wide’ class (one with many properties). We can significantly improve performance by using Reflection to dynamically build a Lambda function that will be used to efficiently populate our data model with the data from the database. The resulting CreateList() function appears in the following code snippet.

public virtual List<T> CreateList(SqlDataReader reader)
{
	Func<SqlDataReader, T> readRow = this.GetReader(reader);

	var results = new List<T>();

	while (reader.Read())
		results.Add(readRow(reader));

	return results;
}

As you can see, we call a GetReader function that will return a Lambda function that accepts a SqlDataReader as an input parameter and returns an instance of Type <T>. Reflection will be used within the GetReader function to build the resulting function. The Lambda that gets generated will be able to populate even a very wide class in a very efficient manner, significantly improving performance over the strict Reflection method shown above.

GetReader and Dynamic Lambda Expressions

Ultimately, the GetReader function needs to create a lambda function that will perform the task shown in the first snippet of code in this article. To do that, we will use Reflection, the SqlDataReader itself, and dynamic lambda expressions (System.Linq.Expressions) to build a lambda that will look like:

reader => new Employee() {
	EmployeeID = IIF((System.DBNull.Value != reader.GetValue(0)), Convert(reader.GetValue(0)), Convert(0)), 
	LastName = IIF((System.DBNull.Value != reader.GetValue(1)), Convert(reader.GetValue(1)), Convert("")), 
	FirstName = IIF((System.DBNull.Value != reader.GetValue(2)), Convert(reader.GetValue(2)), Convert("")), 
	Title = IIF((System.DBNull.Value != reader.GetValue(3)), Convert(reader.GetValue(3)), Convert("")), 
	BirthDate = IIF((System.DBNull.Value != reader.GetValue(5)), Convert(reader.GetValue(5)), Convert(null)), 
	HireDate = IIF((System.DBNull.Value != reader.GetValue(6)), Convert(reader.GetValue(6)), Convert(null)), 
	City = IIF((System.DBNull.Value != reader.GetValue(8)), Convert(reader.GetValue(8)), Convert(""))
}

The signature of our GetReader function looks like:

private Func<SqlDataReader, T> GetReader(SqlDataReader reader)

Step 1 - Get Column List

The first step in our function is to get the list of columns that are in our SqlDataReader input parameter. We may have properties of the class that are not data elements in the query. Getting the list of columns in the dataset will allow us to quickly skip any properties in the class that are not in the reader.

List<string> readerColumns = new List<string>();
for (int index = 0; index < reader.FieldCount; index++)
	readerColumns.Add(reader.GetName(index));

Step 2 - Create Storage for a List of Lambda Expressions

We will be creating several Lambda expressions that we want to put together at the end. Creating a List will allow a single place for having those expressions.

var statements = new List<Expression>();

Step 3 - Get Indexer Property of SqlDataReader

We will need to know what the "Item" property of the SqlDataReader is called. We will store that in a variable that we will be using later.

var indexerProperty = typeof(SqlDataReader).GetProperty("Item", new[] { typeof(string) });

Step 4 - Expressions to Create Instance of Object

Our lambda function creates an instance of the specified class and accepts a SqlDataReader input parameter. We need to create a ParameterExpression for each of those objects -- one for the output and one for the input.

var instanceParam = Expression.Variable(typeof(T));
var readerParam = Expression.Parameter(typeof(SqlDataReader));

Step 5 - Create Instance of Type

We are now ready to create an expression that will actually generate the instance of our specified data type.

BinaryExpression createInstance = Expression.Assign(instanceParam, Expression.New(typeof(T)));
statements.Add(createInstance);

Step 6 - Get the List of Properties and Their Database Column Names

We need to get the list of properties on the data model class. Using the DatabaseProperty attribute (found in the Attributes folder), you can specify which column in the database to use when populating the property. For instance, this allows populating a property called EmployeeID with the data from the Employee_ID column. This is one of the additions that I made after the initial generation of this class in the previous version. It was also something that a few people asked about, so I'm including that information in this version of the article.

var properties = typeof(T).GetProperties();
var columnNames = this.GetColumnNames(properties);

Step 7 - Create a List of ConditionalExpressions for each Property

We are using ConditionalExpressions because we need to determine how we will be setting the actual column value. The actual value for the property will depend on a couple factors.

First, we are looking at the datatype of the property. In our environment, we chose to have 'string' properties initially populated with an empty string if the data was null. Also, if the data type of the property is a nullable value, we will set the value to NULL if appropriate.

We will loop through the properties of the class (defined by T) to determine how many ConditionalExpression elements are necessary and what they will look like. The processing of the loop is:

• Loop through each of the Properties in the Class<T>
• If the property is found as a column in the dataset
  • Create a MemberExpression to set the property
  • Create an IndexExpression to get the appropriate data from the SqlDataReader
  • Create a ConstantExpression that will represent a DBNull.Value
  • Create a BinaryExpression to determine whether the data from the table is equal to DBNull.Value
  • Create the appropriate ConditionalExpression to assign either the default value or the value from the database
  • Add the ConditionalExpression to the list of expressions

foreach (var property in properties)
{
	string columnName = columnNames[property.Name];
	//string columnName = property.Name;
	if (readerColumns.Contains(columnName))
	{
	   // get the instance.Property
	   MemberExpression setProperty = Expression.Property(instanceParam, property);

	   // the database column name will be what is in the columnNames list -- defaults to the property name
	   IndexExpression readValue = Expression.MakeIndex(readerParam, indexerProperty, new[] { Expression.Constant(columnName) });
	   ConstantExpression nullValue = Expression.Constant(DBNull.Value, typeof(System.DBNull));
	   BinaryExpression valueNotNull = Expression.NotEqual(readValue, nullValue);
	   if (property.PropertyType.Name.ToLower().Equals("string"))
	   {
		  ConditionalExpression assignProperty = Expression.IfThenElse(valueNotNull, Expression.Assign(setProperty, Expression.Convert(readValue, property.PropertyType)), Expression.Assign(setProperty, Expression.Constant("", typeof(System.String))));
		  statements.Add(assignProperty);
	   }
	   else
	   {
		  ConditionalExpression assignProperty = Expression.IfThen(valueNotNull, Expression.Assign(setProperty, Expression.Convert(readValue, property.PropertyType)));
		  statements.Add(assignProperty);
	   }
	}
}
var returnStatement = instanceParam;
statements.Add(returnStatement);

Step 5 - Create and Compile the Lambda Function

This final section of the function will perform the following steps:

• Create the body of the lambda expression
• Compile the lambda expression
• Return the function delegate to the calling function

var body = Expression.Block(instanceParam.Type, new[] { instanceParam }, statements.ToArray());
var lambda = Expression.Lambda<Func<SqlDataReader, T>>(body, readerParam);
resDelegate = lambda.Compile();

return (Func<SqlDataReader, T>)resDelegate;

Points of Interest

Performance Metrics Not Included

While I have not included any performance metrics here, I have performed testing on a variety of datasets both large and small. The results of my performance testing were better than expected in most cases -- in fact, sometimes beating the DataTable.Load() option. Small datasets are where the 'hit' in performance will be seen (from about 2 milliseconds to 4-6 milliseconds) because of the Reflection. Medium to large datasets are typically very comparable with other more 'defined' methods of populating the list.

Eat My Own Dog Food

Please understand that, while there was some research and experimentation for this project, the result was useful. This method of generically populating lists is used within my company on a regular basis now.

Download Package

Inside the download package, you will find the complete, usable code for the functionality described here.

Custom Attributes

This version of the allows for custom attributes on the properties of the data model. These custom attributes are basically present to allow you to have different property names and column names (EmployeeID property populated from Employee_ID column).