CoNatural Blogging

Better the devil you know - Part VI.

Roger Torres - December 26th, 2008

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.