Generically Populate List of Objects from SqlDataReader

• Download source - 162.9 KB

Introduction

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 Classes.GenericPopulator<employee>().CreateList(reader);
	rptEmployees.DataSource = employeeList;
	rptEmployees.DataBind();

	reader.Close();
}</employee>

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)
{
	var results = new List<T>();
	Func<SqlDataReader, T> readRow = this.GetReader(reader);

	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 - Setup Input ParameterExpression

Our lambda function accepts a SqlDataReader input parameter. We need to create a ParameterExpression that will reference our SqlDataReader. Using Reflection, we also get the MethodInfo for the GetVaule method of the SqlDataReader class. This will be used later when creating the bindings between the properties of our class and the values in our dataset.

var readerParam = Expression.Parameter(typeof(SqlDataReader), "reader");
var readerGetValue = typeof(SqlDataReader).GetMethod("GetValue");

Step 3 - Setup the DBNull Check

This section of code assists our requirement of setting a property to it's default value. When creating the bindings, we set the property value to either the data from the dataset or the default value of the property, depending on whether the dataset value is null. This also helps us make sure that a null value in the dataset will not cause an error.

We use Reflection to get the FieldInfo for the DBNull.Value field. We then create a FieldExpression that will reference that field on our input SqlDataReader parameter.

var dbNullValue = typeof(System.DBNull).GetField("Value");
var dbNullExp = Expression.Field(Expression.Parameter(typeof(System.DBNull), "System.DBNull"), dbNullValue);

Step 4 - Create a List of MemberBinding Expressions for each Property

The MemberBinding is a type of Linq.Expression that refers to setting a member property to a value. We will loop through the properties of the class (defined by T) to determine how many MemberBinding 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
  • Determine the index of the column in the dataset
  • Create a Call expression that refers to the appropriate column in the dataset
  • Create a NotEqual expression to wrap setting the property to the column value
  • Create an expression for the True portion of the test to set the property to the value of the column in the dataset
  • Create an expression for the False portion of the test to set the property to the default value
  • Create the actual MemberBinding expression and add it to the collection

List<MemberBinding> memberBindings = new List<MemberBinding>();
foreach (var prop in typeof(T).GetProperties())
{
	if (readerColumns.Contains(prop.Name))
	{
		// determine the default value of the property
		object defaultValue = null;
		if (prop.PropertyType.IsValueType)
			defaultValue = Activator.CreateInstance(prop.PropertyType);
		else if (prop.PropertyType.Name.ToLower().Equals("string"))
			defaultValue = string.Empty;

		// build the Call expression to retrieve the data value from the reader
		var indexExpression = Expression.Constant(reader.GetOrdinal(prop.Name));
		var getValueExp = Expression.Call(readerParam, readerGetValue, new Expression[] { indexExpression });

		// create the conditional expression to make sure the reader value != DBNull.Value
		var testExp = Expression.NotEqual(dbNullExp, getValueExp);
		var ifTrue = Expression.Convert(getValueExp, prop.PropertyType);
		var ifFalse = Expression.Convert(Expression.Constant(defaultValue), prop.PropertyType);

		// create the actual Bind expression to bind the value from the reader to the property value
		MemberInfo mi = typeof(T).GetMember(prop.Name)[0];
		MemberBinding mb = Expression.Bind(mi, Expression.Condition(testExp, ifTrue, ifFalse));
		memberBindings.Add(mb);
	}
}

Step 5 - Create and Compile the Lambda Function

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

• Create a New expression that is used to create the instance of the class
• Create a MemberInit expression – combining the new instance creation with the MemberBindings collection that we created in the loop of properties. These two lines of code are what generate the actual desired lambda expression
• Create and compile the desired Lambda function
• Return the function delegate to the calling function

var newItem = Expression.New(typeof(T));
var memberInit = Expression.MemberInit(newItem, memberBindings);
var lambda = Expression.Lambda<Func<SqlDataReader, T>>(memberInit, new ParameterExpression[] { 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. In the Classes folder, you will find three different classes, each of which can be used to populate a List<Employee>. Each class represents one of the methods shown in this article (direct access, Reflection, Dynamic Lambda). As a result, you can see the various methods and do some performance testing on your own if you like.

Custom Attributes

While this code does not use this option, the version of the functionality that we use allows for custom attributes on the properties of the data model. These custom attributes are basically present to allow us to have different property names and column names (EmployeeID property populated from Employee_ID column). There are articles on CodeProject (click here for one example) and other areas that will assist in this type of 'upgrade' to the functionality and it is definitely one worth having. I didn't include that functionality here because I wanted to focus strictly on the Dynamic Lambda operation.