Better the devil you know - Part IV.

Roger Torres - December 19th, 2008

In Part III of this series I described the execution methods supported by the DAL. In this post I will show how to materialize data into .NET objects with generic type readers.

GENERIC READERS

One of the most common operations performed by any .NET enterprise applications is reading data records from the backing store, and passing the results back to the users in some kind of structured form. I wanted to rationalize this pattern with a simple mechanism that “materializes records into .NET type instances” via callback delegates. Again, all the boilerplate code is abstracted away from the user, a practice that not only saves development time and minimizes coding errors, but makes the framework more flexible to accept future modifications.

Let’s review the steps to read a list of employees hired after a given date as you would normally write it with classic ADO.NET:

Define the Employee class

public class Employee {
	public int EmployeeId { get; set; }
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public DateTime HireDate { get; set; }
 
	public Employee() {}
	public Employee(int id, string firstName, 
                string lastName, DateTime hireDate) {
		EmployeeId = id;
		FirstName = firstName;
		LastName = lastName;
		HireDate = hireDate;
	}
 
	public override string ToString() {
		return string.Format("{0}-{1} {2}", 
                      EmployeeId, FirstName, LastName);
	}
}

Define the data adapter to read employees using a text command.

public class EmployeeAdapter {
      public static IEnumerable<Employee> ReadEmployees(DateTime fromDate) {
            IList<Employee> result = new List<Employee>();
            SqlConnection cnn;
            try {
                cnn = new SqlConnection(ConnectionString); 
                SqlCommand cmd = new SqlCommand(
                  @"SELECT EmployeeId, FirstName, LastName, HireDate FROM Employee
                   WHERE HireDate BETWEEN @FromDate AND GETDATE()", cnn);
                cmd.Parameters.Add(new SqlParameter("FromDate", fromDate));
		cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                      result.Add(new Employee(
			record.GetInt32(0),
			record.GetString(1),
			record.GetString(2),
			record.GetDateTime(3)));
                }
                return result;
            }
            catch(Exception ex) {
                if(cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
                throw ex;
            }    
      }
}

Invoke the adapter to read the employees

1 2	IEnumerable<Employee> result = EmployeeAdapter.ReadEmployees(new DateTime(2000, 1, 1));

Lets review the same example but this time using the generic reader pattern implemented by our DAL. The framework will perform exactly the same steps, although most of the boilerplate code is now encapsulated and transparent to the developers:

Define the Employee class (The Model).
Same as above

Define the Command class to read employees.

1
2
3

--  ReadEmployees.sql - Command Embedded Resource
SELECT EmployeeId, FirstName, LastName, HireDate FROM Employee
WHERE HireDate BETWEEN @FromDate AND GETDATE()

// ReadEmployees.cs - Command Code-Behind
public class ReadEmployees : Command {
	public DateTime FromDate { get; set; }
 
	public Employee MaterializeEmployee(
                System.Data.IDataRecord record, int resultIndex) {
		return new Employee(
			record.GetInt32(0),
			record.GetString(1),
			record.GetString(2),
			record.GetDateTime(3));
	}
}

Invoke the command to read the employees

ReadEmployees cmd = new ReadEmployees(); 
cmd.FromDate = new DateTime(2000, 1, 1);
IEnumerable<Employee> result = 
      cn.ExecuteReader<Employee>(cmd, cmd.MaterializeEmployee);

As expected, the command class declares a FromDate property matching the SQL parameter to feed the script before the command is executed. What’s new here is the command class implementing the core of the data adapter in a local function that accepts a data record and the index of the resultset in the internal data reader (used when multiple resultsets are returned). This is the only application specific part that developers need to be worried about, the rest is taken care by the framework. When the command is executed inside a generic reader, the framework invokes “the materializer” delegate for each returned record, storing the results in a collection or container structure that the caller can manipulate after the reading operation is completed.

In this example the materializer function has been implemented inside the command class, but this is not a requirement (in case the user decides to create a library of materializers in some other place).

AUTO MATERIALIZER

But in case you only need a simple one-to-one mapping of data records, make sure the property names of the entity class (only the ones you need to materialize) coincide with the column names coming from the script. You don’t need to provide a callback delegate in this case since the framework will create one for you automatically. Look how easy is to read the same employees, but this time we will only materialize the first and last names:

1
2
3

-- Command Script
SELECT FirstName, LastName FROM Employee
WHERE HireDate BETWEEN @FromDate AND GETDATE()

// Command Code-Behind
public class ReadEmployees : CoNatural.Data.Command {
	public DateTime FromDate { get; set; }
}
 
// The Controller Invoking the Command
ReadEmployees cmd = new ReadEmployees(); 
cmd.FromDate = new DateTime(2000, 1, 1);
IEnumerable<Employee> result = cn.ExecuteReader<Employee>(cmd);

THE OTHER TWO GENERIC READERS

Sometimes the developer needs to cache a keyed dictionary of entities to perform faster lookups in the application. The generic reader method ExecuteReader<K, T> follows the same pattern described above, but this time accepting an additional parameter to specify the column index in the data record where the key value of the dictionary will be found. The user must guarantee that this column returns valid (unique and not null) values of type <K>.

Finally, there is another ExecuteScalar<T> method that accepts a callback delegate to materialize only the first record in the result set returned by a data reader. This method is used as an alternative to the classic ADO.NET ExecuteScalar where all values must be returned as output parameters.

I will provide some more realistic examples later in this series (using the AdventureWorks database), but before that I will discuss the Command Binders and Batch execution modes.

Stay tuned

CoNatural Blogging