CoNatural Blogging

Better the devil you know - Part V.

Roger Torres - December 20th, 2008
Comments

In Part IV of this series I presented a type reader pattern that’s used by our DAL to read entity instances from the backing store.

While reading data is a big part of any enterprise application, it’s just about half of the story. To complete the CRUD circle, developers Create, Update, and Delete objects, persisting the changes back to the data store.

COMMAND BINDERS

We already discussed how to insert records invoking simple commands, but we didn’t see any domain model in the action. Our example was completely based on the command class and its properties feeding SQL parameter values. What many developers would expect is an interface to “dematerialize” and persist entity instances directly from the domain model to the backing store, abstracting away the extra mapping layer. This is exactly what the command binder interface does for us.

The following code revisits the example to insert employees, but this time using command binders:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

public class InsertEmployee : Command, IBinder<Employee> {
	[CoNatural.Data.Parameter(System.Data.ParameterDirection.Output)]
	public int Id { get; set; }
 
	[CoNatural.Data.Parameter(50)]
	public string FirstName { get; set; }
 
	[CoNatural.Data.Parameter(50)]
	public string LastName { get; set; }
 
	public DateTime HireDate { get; set; }
 
#region IBinder<Employee> Members
 
	public void SetInputValues(Employee instance) {
		FirstName = instance.FirstName;
		LastName = instance.LastName;
		HireDate = instance.HireDate;
	}
 
	public void GetOutputValues(Employee instance) {
		instance.EmployeeId = Id;
	}
 
#endregion
}

1
2
3
4
5
6
7
8

Employee e = new Employee() { 
	FirstName = "Johnny", 
	LastName = "Does", 
	HireDate = new DateTime(2007, 1, 1) 
};
 
cn.ExecuteBinder<Employee>(new InsertEmployee(), e);
int newId = e.EmployeeId;

The command binder interface only requires two operations:

1. Set the command’s property values from the entity instance before the command is executed. These values will feed the SQL input parameters.
2. Get the command’s property values (the ones updated after the execution) to populate the entity instance with the results. These map to SQL output parameters.

The example is trivial since the mappings are very linear, but in real applications we will find more complex scenarios where different business rules and validations are part of the binding logic, or when a single command must be bound to different types of entities (for example to persist a .NET class hierarchy). The main goal here is to provide a very simple layer to compensate for a well known problem - the object-relational impedance mismatch.

We will see more examples and the application of command binders in the next post when we discuss the last component of this framework: Batch Execution.

Stay tuned.

Better the devil you know - Part IV.

Roger Torres - December 19th, 2008
Comments

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:

1. Define the Employee class

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   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); } }

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

   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; } } }

3. 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:

1. Define the Employee class (The Model).
   Same as above
2. 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()

   1 2 3 4 5 6 7 8 9 10 11 12 13

   // 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)); } }

3. Invoke the command to read the employees

   1 2 3 4

   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()

1
2
3
4
5
6
7
8
9

// 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

Better the devil you know - Part III.

Roger Torres - December 18th, 2008
7 Comments

In Part II of this series I described the architecture and main components of the data access layer (DAL). While most of the design is based on ADO.NET, there are a couple of important differences:

1. All the SQL code is defined inside a Visual Studio project as “code-behind” associated to the command classes. This is important to keep the SQL code close to the .NET data adapters (not only in the VS project but in the same source control system).
2. Developers don’t need to deal with stored procedures directly since the framework will take care of all the deployment issues automatically. This is the desired behavior during the development phase when most security restrictions are normally removed. Developers are not forced to deal with the proliferation of stored procedures, naming guidelines, source code synchronization, and other headaches.

By default, all commands are executed in “Text” mode, which means the internal ADO.NET commands are created with the CommandType property set to CommandType.Text. Once the system is deployed to a production environment, and stored procedures are established as the only way to access the data, the execution mode of the connection object should be changed as follows:

cn.ExecutionMode = System.Data.CommandType.StoredProcedure;

At this juncture, all commands should be deployed as stored procedures to the underlying database. The connection class implements a deployment mechanism to assemble the stored procedures, taking care of the standard naming guidelines defined by the connection provider. Developers can choose to deploy (a) one command at a time, or (b) all commands defined inside an assembly dedicated to the DAL. Let’s see how to deploy the AdventureWorks data access layer using the second option:

cn.Deploy(typeof(AdventureWorks.DAL.AdventureWorksDbProvider).Assembly);

In general, the entire DAL should be deployed to a production or testing environment once the application is ready. Note that the current principal must have enough rights to create stored procedures.

EXECUTION METHODS

When developing a DAL using plain ADO.NET, developers must deal with repetitive patterns that are tedious and error prone. A regular pattern when executing a command would perform the following steps:

STEPS

1. Declare input and/or output parameters.
2. Set input parameter values.
3. Execute command (using one of several execution methods implemented by ADO.NET - NonQuery, Scalar, DataReader, etc)
4. Process results if the command returns a resultset.
5. Get output parameter values.

Developers must also take care of other issues like handling exceptions, null values, transactions, and multiple commands executed in a single batch.

The main goal of the DAL execution methods is to encapsulate all this boilerplate code, but provide a flexible interface to cover the most common data operations. In general, all commands are executed by a connection instance following the same STEPS enumerated above. We can group the execution methods in four functional categories:

1. Basic ADO.NET execution methods: These are the most basic command execution methods implemented by the framework. The only difference with ADO.NET is the generic interface to return strong typed results.
2. Type readers: Methods used to read and materialize data into .NET objects or collections.
3. Command binders: Methods used to bind .NET objects to commands before and after execution.
4. Batch execution methods: Methods used to invoke multiple commands in a single database call, taking care of input/output parameter passing automatically.

The following table summarizes all the command execution methods, and how the framework invokes ADO.NET internally:

In Part II we described how to execute a basic command to insert employee records. Most of the data operations can be implemented by the basic methods, but we can exploit some common patterns and find a few shortcuts that can improve our coding experience while enforcing consistency and a more robust product.

In the next post I will explore the generic reader pattern.

The Consulting Alternative

Roger Torres - September 1st, 2008
Comments

We live in a technologically challenging world, where individual and corporate fortunes are directly affected by the technical abilities of the people around them. Companies are having a hard time coping with the short supply of highly trained software engineers, and in general they settle by hiring “whatever” is available to cover their needs … hoping for the best.

They must face a tough reality:

1. “Good” software engineers require many years of preparation, tons of dedication and hard work.
2. There is no near term solution to the supply and demand issue.

Let’s explore a few options for a moment:

1. Increasing the number of H-1B visas: This doesn’t seem to work for many reasons. A few big corporations with access to the best foreign universities will benefit the most, but the rest will be exposed to the bigger problem of importing less skilled professionals and company hoppers. The public opinion is also against importing labor and exporting jobs. You can find more information about the H-1B program here.
2. Offering more incentives: This is a great solution, but again, not all companies can afford it. In fact, the best software engineers in this country are working for companies offering stock options, bonuses, long paid vacations, time for personal projects, a full stack of insurance plans, and most importantly a very fat salary. If you know a good engineer that’s not in this group, he is probably an entrepreneur, he is already rich, or he’s out of luck.
3. Hiring professional software consultants: This is probably the only viable short term alternative for the majority of companies. A good consultant will not only find the shortest path to solve problems, but he will provide a consistent approach to produce and maintain systems, serving as a mentor to the staff and transferring many tricks he must have learned over the years of exposure to the fire. The good news is that consulting services are temporary, and don’t require the extra compensation packages mentioned before.

Technology giants like IBM and HP have realized that “consulting services” make a lot of sense for everyone, that’s why they have been playing the game for many years. We offer an alternative to small and medium size companies who cannot afford these 800 pound gorillas, focusing our practice on personalized and affordable services, experience, and dedication. If you have software needs in our area, please let us know how we can help.

Roger Torres
President and Consultant