CoNatural Blogging

Better the devil you know - Part II.

Roger Torres - December 12th, 2008

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]