CoNatural Blogging

Better the devil you know - Part VI.

Roger Torres - December 26th, 2008
2 Comments

This is the last part of a series about building a simple .NET data access layer based on ADO.NET. You can find all the previous posts here:

• Part I: Motivation.
• Part II: Design.
• Part III: Execution Methods.
• Part IV: Reading Data with Type Binders.
• Part V: Writing Data with Command Binders.

BATCH EXECUTION

Sometimes developers need to invoke multiple commands in a single database call, mainly for performance reasons, but ADO.NET doesn’t provide a native mechanism to solve this type of pattern. I decided to include a couple of methods to execute multiple commands in a batch, again taking care of parameter mappings before and after the execution, because this is another common requirement I’ve found in many projects.

Let’s see how we can insert a list of new employees in a single batch call:

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

List<Command> commands = new List<Command>();
for (int i = 0; i < 10; i++) {
	InsertEmployee cmd = new InsertEmployee();
	cmd.FirstName = "FirstName" + i;
	cmd.LastName = "LastName" + i;
	cmd.HireDate = new DateTime(2006, 1, 1);
	commands.Add(cmd);
}
 
using (TransactionScope scope = new TransactionScope()) {
	cn.Execute(commands);
	scope.Complete();
}
 
// show the new ids
commands.ForEach(e => Console.WriteLine(((InsertEmployee)e).Id));

It’s that simple, and you can verify that after the transaction is completed, each individual command holds the corresponding newly created employee id.

There is one small limitation though, the batch execution methods always use the stored procedure execution mode (for performance reasons), thus a deployment of the commands is required beforehand. In case you want to inspect the generated batch execution script, try the following line of code:

string script = cn.GetBatchExecuteScript(commands);

This is a great way to understand what’s happening inside the framework when one or multiple commands are executed.

BATCH EXECUTION WITH COMMAND BINDERS

A second batch execution method is available to “apply” the same command to multiple entity instances via the command binder interface. This is a common scenario found in web applications for example, when the user wants to apply multiple updates to a list of entities in a single transaction.

Going back to our list of employees, let’s try the same batch insert operation but now using a list that can be created outside the data access layer:

1
2
3
4
5
6
7
8
9
10

List<Employee> employees = new List<Employee>();
for (int i = 0; i < 10; i++) {
	employees.Add(
		new Employee() { 
			FirstName = "FN" + i, 
			LastName = "LN" + i, 
			HireDate = new DateTime(2005, 1, 1) 
		}
	);
}

The application controller just needs to accept the list of employee instances and invoke the DAL to apply the InsertEmployee command:

1
2
3
4
5
6
7

using (TransactionScope scope = new TransactionScope()) {
	cn.ExecuteBinder<Employee>(new InsertEmployee(), employees);
	scope.Complete();
}
 
// show the new employees
employees.ForEach(e => Console.WriteLine(e));

The instances are automatically updated with the newly created employee ids.

LINKED PARAMETERS INSIDE A BATCH

There is one final aspect of batch execution I want to cover. Sometimes your application will require to execute a list of data operations in order to complete a use case, and the results of the first group of operations will be used as input parameters to the second group of operations. What developers usually do is make two or more database calls to resolve this issue, but if the list of operations is too big and there are too many dependencies, the amount of calls can kill your performance.

With our DAL you can execute all these operations in a single batch call by linking command parameters. Let’s add a ManagerID column to our Employee table and insert a hierarchy of employees in a batch, where each employee is going to be the direct manager of the next employee in the list.

First, let’s make some modifications to the table and command class to incorporate the ManagerID field:

-- create table with new ManagerID column
CREATE TABLE [dbo].[Employee](
   [EmployeeID] [INT] IDENTITY(1,1) NOT NULL,
   [ManagerID] [INT],
   [FirstName] [NVARCHAR](50) NOT NULL,
   [LastName] [NVARCHAR](50) NOT NULL,
   [HireDate] [DATETIME] NOT NULL,
   CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
   ([EmployeeID] ASC)
) ON [PRIMARY]
 
-- set the FK constraint
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([ManagerID])
REFERENCES [dbo].[Employee] ([EmployeeID])

Update the command’s script to insert the ManagerID

INSERT INTO dbo.Employee(ManagerID, FirstName, LastName, HireDate)
VALUES(@ManagerId, @FirstName, @LastName, @HireDate)
SET @Id = SCOPE_IDENTITY()

And update the code-behind. Note that the new ManagerID property is nullable and annotated with Input/Ouput direction since we don’t now the new Ids until the batch execution is completed, and we want the DAL to update these properties for us.

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

public class InsertEmployee : CoNatural.Data.Command {
	[CoNatural.Data.Parameter(System.Data.ParameterDirection.Output)]
	public int Id { get; set; }
 
	[CoNatural.Data.Parameter(System.Data.ParameterDirection.InputOutput)]
	public int? ManagerId { 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; }
 
	public override string ToString() {
		return string.Format("Id={0}, ManagerId={1}, Name={2} {3}, HireDate={4}",
			Id, ManagerId, FirstName, LastName, HireDate);
	}
}

Finally, let’s deploy the command and execute the batch inside a transaction:

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

conn.Deploy(new InsertEmployee());
 
// prepare the commands for batch execution
List<Command> commands = new List<Command>();
for (int i = 0; i < 10; i++) {
	InsertEmployee cmd = new InsertEmployee();
	cmd.FirstName = "FirstName" + i;
	cmd.LastName = "LastName" + i;
	cmd.HireDate = new DateTime(2006, 1, 1);
       // Set Manager.
       // NOTE THE USE OF LINKED PARAMETERS
	if (i > 0)
		cmd["ManagerId"].Value = commands[i - 1]["Id"];
	commands.Add(cmd);
}
 
using (TransactionScope scope = new TransactionScope()) {
	conn.Execute(commands);
	scope.Complete();
}
 
// show the script
System.Console.WriteLine(conn.GetBatchExecuteScript(commands));
 
// show the commands after batch execution
commands.ForEach(e => System.Console.WriteLine(e));

Note how the ManagerId parameter is linked to the previous employee Id command by using the indexer property defined in the command class. You can inspect the console output to understand how the DAL prepares the batch script and verify that the newly created employee ids and linked manager ids properties are properly updated.

Batch Script

-- declare table to record output parameter values
DECLARE @OUTPUT TABLE(commandIndex INT, parameterName VARCHAR(100), parameterVal
ue SQL_VARIANT)
 
DECLARE @Id0 INT
DECLARE @ManagerId0 INT
SET @ManagerId0 = null
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id0 OUTPUT, @ManagerId0 outp
ut, 'FirstName0', 'LastName0', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(0, 'Id', @Id0)
INSERT INTO @OUTPUT VALUES(0, 'ManagerId', @ManagerId0)
 
DECLARE @Id1 INT
DECLARE @ManagerId1 INT
SET @ManagerId1 = @Id0
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id1 OUTPUT, @ManagerId1 outp
ut, 'FirstName1', 'LastName1', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(1, 'Id', @Id1)
INSERT INTO @OUTPUT VALUES(1, 'ManagerId', @ManagerId1)
 
DECLARE @Id2 INT
DECLARE @ManagerId2 INT
SET @ManagerId2 = @Id1
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id2 OUTPUT, @ManagerId2 outp
ut, 'FirstName2', 'LastName2', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(2, 'Id', @Id2)
INSERT INTO @OUTPUT VALUES(2, 'ManagerId', @ManagerId2)
 
DECLARE @Id3 INT
DECLARE @ManagerId3 INT
SET @ManagerId3 = @Id2
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id3 OUTPUT, @ManagerId3 outp
ut, 'FirstName3', 'LastName3', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(3, 'Id', @Id3)
INSERT INTO @OUTPUT VALUES(3, 'ManagerId', @ManagerId3)
 
DECLARE @Id4 INT
DECLARE @ManagerId4 INT
SET @ManagerId4 = @Id3
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id4 OUTPUT, @ManagerId4 outp
ut, 'FirstName4', 'LastName4', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(4, 'Id', @Id4)
INSERT INTO @OUTPUT VALUES(4, 'ManagerId', @ManagerId4)
 
DECLARE @Id5 INT
DECLARE @ManagerId5 INT
SET @ManagerId5 = @Id4
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id5 OUTPUT, @ManagerId5 outp
ut, 'FirstName5', 'LastName5', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(5, 'Id', @Id5)
INSERT INTO @OUTPUT VALUES(5, 'ManagerId', @ManagerId5)
 
DECLARE @Id6 INT
DECLARE @ManagerId6 INT
SET @ManagerId6 = @Id5
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id6 OUTPUT, @ManagerId6 outp
ut, 'FirstName6', 'LastName6', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(6, 'Id', @Id6)
INSERT INTO @OUTPUT VALUES(6, 'ManagerId', @ManagerId6)
 
DECLARE @Id7 INT
DECLARE @ManagerId7 INT
SET @ManagerId7 = @Id6
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id7 OUTPUT, @ManagerId7 outp
ut, 'FirstName7', 'LastName7', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(7, 'Id', @Id7)
INSERT INTO @OUTPUT VALUES(7, 'ManagerId', @ManagerId7)
 
DECLARE @Id8 INT
DECLARE @ManagerId8 INT
SET @ManagerId8 = @Id7
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id8 OUTPUT, @ManagerId8 outp
ut, 'FirstName8', 'LastName8', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(8, 'Id', @Id8)
INSERT INTO @OUTPUT VALUES(8, 'ManagerId', @ManagerId8)
 
DECLARE @Id9 INT
DECLARE @ManagerId9 INT
SET @ManagerId9 = @Id8
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id9 OUTPUT, @ManagerId9 outp
ut, 'FirstName9', 'LastName9', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(9, 'Id', @Id9)
INSERT INTO @OUTPUT VALUES(9, 'ManagerId', @ManagerId9)
 
-- return output parameters
SELECT commandIndex, parameterName, parameterValue FROM @OUTPUT

Console Output

Id=51, ManagerId=, Name=FirstName0 LastName0, HireDate=1/1/2006 12:00:00 AM
Id=52, ManagerId=51, Name=FirstName1 LastName1, HireDate=1/1/2006 12:00:00 AM
Id=53, ManagerId=52, Name=FirstName2 LastName2, HireDate=1/1/2006 12:00:00 AM
Id=54, ManagerId=53, Name=FirstName3 LastName3, HireDate=1/1/2006 12:00:00 AM
Id=55, ManagerId=54, Name=FirstName4 LastName4, HireDate=1/1/2006 12:00:00 AM
Id=56, ManagerId=55, Name=FirstName5 LastName5, HireDate=1/1/2006 12:00:00 AM
Id=57, ManagerId=56, Name=FirstName6 LastName6, HireDate=1/1/2006 12:00:00 AM
Id=58, ManagerId=57, Name=FirstName7 LastName7, HireDate=1/1/2006 12:00:00 AM
Id=59, ManagerId=58, Name=FirstName8 LastName8, HireDate=1/1/2006 12:00:00 AM
Id=60, ManagerId=59, Name=FirstName9 LastName9, HireDate=1/1/2006 12:00:00 AM

CONCLUSION

ADO.NET has been around for many years, and we all know it’s stable, fast, simple, widely adopted, and one of the most important pillars of the .NET framework. Today Microsoft is pushing for newer data access technologies like LINQ for SQL and LINQ for Entities, but it will take a while for the community to learn and adopt these technologies (I don’t even know what’s going to happen now that they are more focused on cloud computing and distributed data services).

In the mean time, we need to continue building robust and maintainable software … and to me, ADO.NET still looks like the best option for the DAL (when used properly). This article presented a simple DAL based on ADO.NET that was designed with two goals in mind: simplicity and efficiency. Now I want to invest my limited spare time learning other technologies that will make a bigger impact on my projects, and there are plenty of them (WCF, WWF, Silverlight, ASP.NET MVC).

I’m planning to continue covering this framework in the future, presenting more complex examples using the AdventureWorks database.

Any comments or suggestions are appreciated.

I hope you enjoyed it.

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.

Better the devil you know - Part II.

Roger Torres - December 12th, 2008
7 Comments

In Part I of this series I presented the case for building simple in-house solutions when the development team didn’t have the time or desire to try more complex alternatives, listing a desired set of common requirements for a .NET data access layer.

In this post I’m going to describe the data access layer that was developed to meet those requirements, but:

1. Did NOT impose a big learning curve.
2. Simplified the life of developers and architects by “enforcing” a system of coding best practices.

THE DESIGN

As expected, the solution is based on the well known ADO.NET Connection – Command – Parameter abstractions, although most of the regular ADO.NET boilerplate code is transparent to the developers. Figure 1 depicts a layered view of the framework and its place within the architecture.

Figure 1

The entire framework is packaged in a single .NET assembly that can be downloaded here – [download#2].

I’m going to start with a very simple scenario to show the basic components of the framework and their role in the system. In future posts I will introduce more complex scenarios, covering all the supported execution modes and other advanced features.

THE CONNECTION CLASS

Just like in ADO.NET, the connection class is the entry point to the framework. It represents a connection to a single database instance, encapsulating all the execution methods and modes as specified by the requirements. Connections delegate most of the work to a specific database provider implementing a database agnostic interface.

The framework includes a fully functional SQL Server provider, although module designers can customize the routines in charge of generating stored procedures and applying naming conventions to meet their coding standards.

Listing 1 shows how to get a connection string from a configuration file, and instantiate a connection object with the default SQL Server provider:

1
2
3
4

string cStr = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
CoNatural.Data.IDbProvider dbProvider = new CoNatural.Data.SqlClient.SqlDbProvider();
CoNatural.Data.Connection cn = new Connection(cStr, dbProvider);
cn.TimeOut = 10000;

THE COMMAND CLASS

Commands are “very basic” .NET wrappers encapsulating SQL scripts. Their main role is to provide a strong typed interface to the script’s input/output parameters through its properties.

The association between commands and their corresponding script is similar to the code-behind pattern found in Windows/Web Forms; each command class implements the code-behind of a SQL script. The framework automatically performs the association by matching the “full” type name of the command class with the “full” file name of the script.

Figure 2 shows a Visual Studio project where multiple commands are defined. It’s required that both files share the same .NET assembly and namespace. Developers can add new commands to any type of project, although it’s a good practice to dedicate specific assemblies to the data access layer.

Figure 2

I’m currently working on a Visual Studio Wizard to streamline the creation of new commands following a predefined template as shown in Figure 3. Solution designers can modify the default template or create others according to their needs. Note that the wizard takes care of configuration details like setting the SQL script “Build Action” to “Embedded Resource” and creating a file dependency in the project to nest the code-behind file within the SQL script file.

Figure 3

The following code snippets show the files generated by the default template when adding a new command named “Command1.

Command1.sql

1
2
3
4
5
6
7
8
9

-------------------------------------------------------
-- Command1
--
-- Created by Roger
-- Created on 11/13/2008 7:51:43 PM
-------------------------------------------------------
 
-- TODO Write your command's sql statement here
-- e.g.  SELECT a, b, c FROM tableName WHERE id = @id

Command1.cs

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

//-------------------------------------------------------
//-- Command1
//--
//-- Created by Roger
//-- Created on 11/13/2008 7:51:43 PM
//-------------------------------------------------------
using System;
 
namespace AdventureWorks.DAL.HumanResources {
  public class Command1 : CoNatural.Data.Command {
    // TODO Write your parameters and other helper methods here
  }
}

Using the Visual Studio Wizard is not required. Developers can create new commands by adding both files manually, making sure they are under the same namespace, the file extensions are correct, and the build action of the file holding the SQL script is set to “Embedded Resource”.

THE PARAMETER CLASS

Parameters are the vehicles responsible for holding and applying the value mapping rules between command properties and SQL input/output parameters. Developers must code commands carefully, making sure that each property name and type coincides with the corresponding parameter in the SQL script.

All properties are mapped as input parameters by default, but the user can override this behavior by annotating properties with the special ParameterAttribute to explicitly set the direction, size, precision, and scale of the underlying ADO.NET parameters. The framework will use this information to generate the stored procedures at deployment time.

The runtime is guided by the following rules when preparing the collection of parameter mappings associated with a command:

1. Creates a Parameter object for each property defined by the specialized command class, overriding defaults when the property is annotated with ParameterAttribute.
2. If the property is Nullable, the parameter will allow and handle null values.
3. The type of the underlying ADO.NET parameter is determined as follows:

Int16 → DbType.Int16
Int32 → DbType.Int32
Int64 → DbType.Int64
Boolean → DbType.Boolean
Byte → DbType.Byte
DateTime → DbType.DateTime
Decimal → DbType.Decimal  (Uses Precision and Scale attributes)
Double → DbType.Double
Guid → DbType.Guid
Sbyte → DbType.SByte
Single → DbType.Single
String → DbType.String  (Requires Size attribute between 1 - 8000)
Uint16 → DbType.UInt16
Uint32 → DbType.UInt32
Uint64 → DbType.UInt64

Before commands are invoked, the runtime automatically assigns property values to the corresponding input parameters. Likewise, the command’s properties are updated with the corresponding output parameter values after the results are back.

To illustrate this concept, let’s explore a simple scenario to insert a new “Employee” record into a fictitious “Employees” table:

1. Create the Employee table

1 2 3 4 5 6 7 8

CREATE TABLE [dbo].[Employee]( [EmployeeID] [INT] IDENTITY(1,1) NOT NULL, [FirstName] [NVARCHAR](50) NOT NULL, [LastName] [NVARCHAR](50) NOT NULL, [HireDate] [DATETIME] NOT NULL, CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED ([EmployeeID] ASC) ) ON [PRIMARY]

2. Create a new file named InsertEmployee.sql with the following SQL script, and set the build action to “Embedded Resource”:

1 2 3

INSERT INTO Employee(FirstName, LastName, HireDate) VALUES(@FirstName, @LastName, @HireDate) SET @Id = SCOPE_IDENTITY()

3. Create a .NET class named InsertEmployee.cs under the same folder with the following C# code:

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

public class InsertEmployee : CoNatural.Data.Command { [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; } }

4. Invoke the command somewhere in the application. Let’s insert a new employee named “John Doe” hired on January 1, 2008.

1 2 3 4 5 6 7 8

InsertEmployee cmd = new InsertEmployee(); cmd.FirstName = "John"; cmd.LastName = "Doe"; cmd.HireDate = new DateTime(2008, 1, 1); cn.Execute(cmd);   // get new employee id int newId = cmd.Id;

Note how all the properties are automatically mapped “by name” to the SQL parameters defined in the script. The “Id” property is annotated to identify the output parameter, and both “Name” properties are annotated to enforce the size of the nvarchar columns in the underlying employee table.

This example shows how easy is to create and execute commands with this framework, but what’s most important, how the data layer is constrained behind a strong typed interface, systematically keeping all the parts together inside a portable and source-controlled location while moving most of the deployment issues to the end of the development process.

In the next couple of posts I will describe the different “command execution modes” supported by this framework, and how they cover the most common CRUD requirements with the help of Type Readers and Command Binders. Finally, I’m going to introduce the command batch execution methods with linked parameters, and how to automatically compile and deploy commands as stored procedures.

Stay tuned for part III.

[download#2]

Better the devil you know - Part I.

Roger Torres - December 1st, 2008
5 Comments

INTRODUCTION

A common situation found in software development projects is “technical” managers trying to introduce, and sometimes enforce, “the latest” technologies without considering if developers have the time or willingness to learn new stuff… and yet expect they will quickly adapt to finish the job within schedule and budget.

I have seen entire projects go down the drain because the proposed architecture imposed a steep learning curve; furthermore it was almost impossible to find new developers with enough experience to fill the gaps. Today, when presented with such scenarios, I basically know what to expect: moving deadlines, frustration, and poorly written code that’s hard to follow and maintain.

Don’t get me wrong, I believe it’s very important to learn and apply new and “proven” technologies in order to succeed and sometimes survive in our careers; we just need to be very careful with the timing, particularly when others are involved and the completion of a project is on the line.

Software architects who understand their team’s abilities and motivations will eventually recognize that in this business sometimes it’s “better the devil you know than the devil you don’t”, and while pushing for the adoption of best practices and proven design principles, we should also strive for simple and pragmatic solutions.

THE CASE FOR IN-HOUSE SOLUTIONS

Mastering a sophisticated technology like LINQ for Entities or NHibernate requires the time and dedication that when extrapolated to an entire development team represents an investment that many companies are not ready to accept, particularly when there are simpler tools like ADO.NET that most developers understand and “can do the job”.

Some would argue that “the more sophisticated the technology you use, the more productive you are”… and as convincing as it sounds in theory, this shouldn’t be the only reason to embark ourselves into the tricky waters of third party solutions.

Let’s consider the following questions for a moment:

1. Are we really going to use all the features the solution is offering?
2. Does the solution support all the features we need?
3. How much time is it going to take for the “entire” development team to learn and adopt this technology without compromising the project?
4. How easy is to find developers with enough experience using this solution?

There will be other questions to ponder for sure, but when we don’t know or like the answers, it’s time to explore other options.

Many designers will love the idea of developing in-house, and while this is fine when we know exactly what’s needed, the most important precondition is having a team with enough “commitment and proficiency” to make it happen. Ultimately, the right decision will be influenced by each specific situation, and the one who makes it will definitely find a fair share of supporters and detractors.

GATHERING REQUIREMENTS

I’m going to present a .NET data access layer that was built with a few conflicting goals in mind:

1. Serve the most common data scenarios found in modern .NET applications following software best practices.
2. Simple enough so that it could be adopted with just a few hours of training.
3. Powerful enough so that users would feel compelled to adopt it.

The motivation to assemble something like this was not the shortage of solutions, because there are many excellent frameworks out there that can perform miracles. What I’ve found in my consulting career is that the majority of .NET developers are still using plain ADO.NET, and they are not really interested in learning other ways to handle their data. It’s not hard to understand their reasons, especially when you see the managers breathing behind their necks all the time, but I’m not going to get there. The problem is that when ADO.NET is used at will, it can lead to messy and sometimes unmaintainable code that will eventually call for a complete rewrite. I though that the best way to get them back on track was to develop a “simple layer” on top of ADO.NET that would basically “enforce” a systematic methodology without adding “unwanted noise” to the process.

I started by presenting the following list of functional and non-functional requirements to satisfy:

1. Use simple abstractions and technologies that are familiar to most .NET developers. Because ADO.NET was already in use, the design should follow ADO.NET as much as possible.
2. Follow best practices. Encapsulate using the open close principle, design in layers, and build a testable system.
3. Use a provider pattern to support multiple database management systems. Leverage the providers found in ADO.NET.
4. All the source code (including SQL scripts) should be under the same source control system.
5. SQL scripts should be organized in namespaces.
6. Enforce strong typing and systematic handling of NULL values.
7. Avoid ADO.NET DataSets. Encourage the use of “plain” object models.
8. Limit the proliferation of stored procedures during the development phase.
9. Provide a mechanism to automatically deploy and invoke stored procedures in a production environment where direct access to tables and views is disabled. This is a commonly used security pattern. Any syntax or dependency errors in the SQL scripts should be found at deployment time.
10. The system (not developers) should enforce a consistent naming convention for stored procedures.
11. Module designers should have the ability to wrap the SQL scripts with custom code to streamline security and business rule validations.
12. Keep the business objects unaware of the framework. All CRUD operations should be invoked by a higher layer in the system (Controller Layer).
13. Rationalize the coding required to materialize data records into business objects. Likewise, develop a simple mechanism to bind object property values to SQL scripts to support CRUD operations.
14. Provide the ability to execute multiple commands in batch (with a single call to the database).
15. Favor the use of transaction scopes. Share ADO.NET connections when possible to avoid the promotion of lightweight transactions to distributed transactions.
16. Develop with Visual Studio 2008, SQL Server 2005, and the .NET framework 3.5.
17. Deliver a prototype based on the AdventureWorks database shipped with SQL Server 2005 within 2 months.

Stay tuned for part II of this article where I’m going to start describing the solution and how it’s applied to real world scenarios.