Working on new features.

Roger Torres - May 14th, 2010

The last couple of weeks, I have been working on new CoNatural.Data features with the goal to simplify the persistence of complex object structures. I always try to keep my DAL as basic as possible, with the majority of operations being performed by direct table to object mappings (Table Adapter Pattern), but there are scenarios where more complex object structures should be persisted at once (inside a single transaction and ideally in just one trip to the database). In order to achieve this, I added two new features to the framework: (Note that with the current release we can persist complex structures and this is only to make things simpler)

1) The ability to map indirect references to scalar properties (via Type Mapper classes).
2) The implementation of strategies that navigate the complex object structure, binding instances to commands, and optionally executing the entire operation in a single batch.

Let me show some examples using AdventureWorks2008.

Indirect mappings

In this example I will create some new Stores using the model below:

public class Store {
	public Store() {}
 
	public BusinessEntity BusinessEntity { get; set; }
	public string Name { get; set; }
	public Int32? SalesPersonID { get; set; }
	public string Demographics { get; set; }
	public Guid rowguid { get; set; }
	public DateTime ModifiedDate { get; set; }
 
	public override string ToString() {
		return BusinessEntity.ToString() + ": " + Name;
	}
}
 
public class BusinessEntity {
	public BusinessEntity() {}
 
	public Int32 BusinessEntityID { get; set; }
	public Guid rowguid { get; set; }
	public DateTime ModifiedDate { get; set; }
 
	public override string ToString() {
		return BusinessEntityID.ToString();
	}
}

Now we create the command to insert stores:

public class StoreInsert : ICommand {
	public StoreInsert() {}
 
	public string GetScript() { 
		return 
@"
INSERT INTO [Sales].[Store] (
   [BusinessEntityID],
   [Name],
   [SalesPersonID],
   [rowguid],
   [ModifiedDate])
VALUES (
	@BusinessEntityID,
	@Name,
	@SalesPersonID,
	newid(),
	getdate())
"; 
	}
 
	public Int32 BusinessEntityID { get; set; }
	[Parameter(50)] public string Name { get; set; }
	public Int32? SalesPersonID { get; set; }
}

Note that we need to fill the BusinessEntityID parameter, but there is no “matching property” in class Store. The solution is to register with the type mapper factory an indirect property reference to Store.BusinessEntity.BusinessEntityID, so when the type mapper performs the bindings, it can find the property with the input values to feed the command parameters (or the properties that will store the results coming from output parameters). I’m using static reflection in this case to make sure the mapping expression is valid at compile time.

public void CreateStore(IConnection c, BusinessEntity be) {
	// set indirect mapping
	c.DefaultTypeMapperFactory.Map<Store>(r => r.BusinessEntity.BusinessEntityID);
 
	var store = new Store { BusinessEntity = be, Name = "Test Store", SalesPersonID = 288 };
	c.Execute(new StoreInsert(), store);
}

We can map multiple indirect references for one type, and we can specify the name of the parameter we want to map in case the referenced property doesn’t match the name. In the next example we will see these cases.

Strategies

Before I present this example, there is a new IConnection method (BeforeExecute) you can override to intercept commands before they are invoked. I will use the following connection to trace some information to the Console:

public class SqlConnectionWithTrace : CoNatural.Data.SqlClient.SqlClientConnection {
	public SqlConnectionWithTrace(string connectionString)
		: base(connectionString) {
		Trace.Listeners.Add(new ConsoleTraceListener());
	}
 
	public override void BeforeExecute(ICommand iCommand, IDataCommand dataCommand, System.Data.IDbCommand dbCommand) {
		if (iCommand != null)
			Trace.WriteLine("Executing " + iCommand.GetType().ToString());
		foreach (System.Data.IDbDataParameter p in dbCommand.Parameters)
			if (p.Direction != System.Data.ParameterDirection.Output)
				Trace.WriteLine(p.ParameterName + " = " + p.Value);
		Trace.WriteLine(dbCommand.CommandText);
	}
}

Now, going back to strategies, the idea here is to help the engine to traverse the object structure when persisting changes. There is a new StrategyBase class providing a default strategy that understands one-to-many relationships, and can bind to insert/update/delete commands if the objects implement the new IDataObject interface. Let’s take a look at the source code:

namespace CoNatural.Data {
	/// <summary>
	/// Implements object tree traversing strategy to persist changes
	/// </summary>
	public interface IStrategy {
		/// <summary>
		/// Adds one-to-many mappings as a guide to traverse the object tree.
		/// </summary>
		/// <typeparam name="T">The type with the many references.</typeparam>
		/// <param name="manyExps">The reference expressions in the form (T) => T.ManyProperty</param>
		void OneToMany<T>(params Expression<Func<T, IEnumerable>>[] manyExps);
 
		/// <summary>
		/// Traverses object tree and returns list of commands bound to objects 
		/// </summary>
		/// <param name="rootObj">The root of the tree.</param>
		/// <returns>The list of bound commands to execute.</returns>
		IEnumerable<BoundCommand> Execute(object rootObj);
 
		/// <summary>
		/// Binds a command and type mapper to a given object instance, returning the resulting bound command
		/// Invoked internally when strategy is executed.
		/// </summary>
		/// <param name="obj">The object instance.</param>
		/// <returns>The bound command or null if no mapping found.</returns>
		BoundCommand Bind(object obj);
	}
}
 
namespace CoNatural.Data {
	/// <summary>
	/// IStrategy base class
	/// Binds commands to IDataObject instances by inspecting the object state (Inserted, Updated, Deleted),
	/// or to the default command otherwise.
	/// </summary>
	public class StrategyBase : IStrategy {
		private Dictionary<Type, ICommand> inserts = new Dictionary<Type, ICommand>();
		private Dictionary<Type, ICommand> updates = new Dictionary<Type, ICommand>();
		private Dictionary<Type, ICommand> deletes = new Dictionary<Type, ICommand>();
		private Dictionary<Type, ICommand> defaults = new Dictionary<Type, ICommand>();
		private Dictionary<Type, List<PropertyInfo>> oneToManyMap = new Dictionary<Type, List<PropertyInfo>>();
 
		/// <summary>
		/// Adds CRUD command mappings 
		/// </summary>
		/// <typeparam name="T">The object type.</typeparam>
		/// <param name="insertCommand">The command used to insert instances of T when T.State = Inserted</param>
		/// <param name="updateCommand">The command used to update instances of T when T.State = Updated</param>
		/// <param name="deleteCommand">The command used to delete instances of T when T.State = Deleted</param>
		public void BindTo<T>(ICommand insertCommand, ICommand updateCommand, ICommand deleteCommand) where T : IDataObject {
			if (insertCommand != null) inserts[typeof(T)] = insertCommand;
			if (updateCommand != null) updates[typeof(T)] = updateCommand;
			if (deleteCommand != null) deletes[typeof(T)] = deleteCommand;
		}
 
		/// <summary>
		/// Adds default command mappings when T is not IDataObject
		/// </summary>
		/// <typeparam name="T">The object type.</typeparam>
		/// <param name="defaultCommand">The default command.</param>
		public void BindTo<T>(ICommand defaultCommand) {
			if (defaultCommand != null) defaults[typeof(T)] = defaultCommand;
		}
 
		#region IStrategy Members
 
		public virtual void OneToMany<T>(params Expression<Func<T, IEnumerable>>[] manyExps) {
			List<PropertyInfo> list;
			if(!oneToManyMap.TryGetValue(typeof(T), out list)) {
				list = new List<PropertyInfo>();
				oneToManyMap.Add(typeof(T), list);
			}
 
			foreach(var manyExp in manyExps) {
				MemberExpression mex = manyExp.Body.ToMemberExpression();
				if(mex == null)
					throw new Exception(string.Format("Invalid many mapping [{0}] -> [{1}].", typeof(T).Name, manyExp.Body.ToString()));
				PropertyInfo property = typeof(T).GetProperty(mex.Member.Name);
				list.Add(property);
			}
		}
 
		public virtual IEnumerable<BoundCommand> Execute(object rootObj) {
			// yield the root first 
			yield return Bind(rootObj as IDataObject);
 
			// traverse one-to-many
			List<PropertyInfo> nav = null;
			if(oneToManyMap.TryGetValue(rootObj.GetType(), out nav)) {
				foreach (var p in nav) {
					IEnumerable children = (IEnumerable)p.GetValue(rootObj, null);
					foreach (var obj in children)
						foreach (var cmd in Execute(obj))
							yield return cmd;
				}
			}
		}
 
		public virtual BoundCommand Bind(object obj) {
			// find the command that applies in the mappings
			ICommand cmd = null;
			IDataObject dataObj = obj as IDataObject;
			if (dataObj == null) {
				defaults.TryGetValue(obj.GetType(), out cmd);
			}
			else {
				switch (dataObj.State) {
					case DataObjectStateEnum.Inserted:
						inserts.TryGetValue(obj.GetType(), out cmd);
						break;
 
					case DataObjectStateEnum.Updated:
						updates.TryGetValue(obj.GetType(), out cmd);
						break;
 
					case DataObjectStateEnum.Deleted:
						deletes.TryGetValue(obj.GetType(), out cmd);
						break;
				}
			}
 
			// return null if no command found in mappings
			if (cmd == null)
				return null;
 
			// create the data-bound command
			return new BoundCommand(obj, cmd);
		}
 
		#endregion
	}
}

The execute method starts traversing the tree from the root, biding objects to commands (following some specific binding rules that can be overridden). In this case there are only one-to-many mappings registered by type but this can also be customized.

The IDataObject interface is used to flag the object as inserted, updated, deleted, or unchanged. This interface only requires one property with the persistence state, and it’s useful if you want to implement data transfer objects (DTOs) that will cross service boundaries and will somehow self-track and send changes back to the DAL. I’m not going to discuss how to do this right now, I will only say that CoNatural.Data is not responsible for tracking changes, that’s the job of other frameworks, and this is an important difference when compared to other tools like LINQ for Entities or NHibernate.

Now let’s make some changes to orders and order details, here is the model (generated with the VS Add-In):

 
//-------------------------------------------------------
//-- SalesOrderDetail.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:30:55 PM
//-------------------------------------------------------
 
using System;
 
namespace ConsoleApplication1.Model.Sales {
	public class SalesOrderDetail : CoNatural.Data.DataObjectBase {
		public SalesOrderDetail() {}
 
		public Int32 SalesOrderDetailID { get; set; }
		public string CarrierTrackingNumber { get; set; }
		public Int16 OrderQty { get; set; }
		public Int32 ProductID { get; set; }
		public Int32 SpecialOfferID { get; set; }
		public Decimal UnitPrice { get; set; }
		public Decimal UnitPriceDiscount { get; set; }
		public Decimal LineTotal { get; set; }
		public Guid rowguid { get; set; }
		public DateTime ModifiedDate { get; set; }
 
		public SalesOrderHeader SalesOrderHeader { get; set; } // SalesOrderID
	}
}
 
 
//-------------------------------------------------------
//-- SalesOrderHeader.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:30:56 PM
//-------------------------------------------------------
 
using System;
using System.Collections.Generic;
 
namespace ConsoleApplication1.Model.Sales {
	public class SalesOrderHeader : CoNatural.Data.DataObjectBase {
		public SalesOrderHeader() {}
 
		public Int32 SalesOrderID { get; set; }
		public Byte RevisionNumber { get; set; }
		public DateTime OrderDate { get; set; }
		public DateTime DueDate { get; set; }
		public DateTime? ShipDate { get; set; }
		public Byte Status { get; set; }
		public Boolean OnlineOrderFlag { get; set; }
		public string SalesOrderNumber { get; set; }
		public string PurchaseOrderNumber { get; set; }
		public string AccountNumber { get; set; }
		public Int32 BillToAddressID { get; set; }
		public Int32 ShipToAddressID { get; set; }
		public Int32 ShipMethodID { get; set; }
		public Int32? CreditCardID { get; set; }
		public string CreditCardApprovalCode { get; set; }
		public Int32? CurrencyRateID { get; set; }
		public Decimal SubTotal { get; set; }
		public Decimal TaxAmt { get; set; }
		public Decimal Freight { get; set; }
		public Decimal TotalDue { get; set; }
		public string Comment { get; set; }
		public Guid rowguid { get; set; }
		public DateTime ModifiedDate { get; set; }
 
		public Customer Customer { get; set; } // CustomerID
		public SalesPerson SalesPerson { get; set; } // SalesPersonID
		public SalesTerritory SalesTerritory { get; set; } // TerritoryID
 
		// navigation
		public List<SalesOrderDetail> SalesOrderDetails { get; set; }
 	}
}
 
 
 
//-------------------------------------------------------
//-- Customer.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:30:55 PM
//-------------------------------------------------------
 
using System;
 
namespace ConsoleApplication1.Model.Sales {
	public class Customer {
		public Customer() {}
 
		public Int32 CustomerID { get; set; }
		public Int32? PersonID { get; set; }
		public Int32? StoreID { get; set; }
		public Int32? TerritoryID { get; set; }
		public string AccountNumber { get; set; }
		public Guid rowguid { get; set; }
		public DateTime ModifiedDate { get; set; }
	}
}
 
 
 
//-------------------------------------------------------
//-- SalesPerson.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:30:56 PM
//-------------------------------------------------------
 
using System;
 
namespace ConsoleApplication1.Model.Sales {
	public class SalesPerson {
		public SalesPerson() {}
 
		public Int32 BusinessEntityID { get; set; }
		public Int32? TerritoryID { get; set; }
		public Decimal? SalesQuota { get; set; }
		public Decimal Bonus { get; set; }
		public Decimal CommissionPct { get; set; }
		public Decimal SalesYTD { get; set; }
		public Decimal SalesLastYear { get; set; }
		public Guid rowguid { get; set; }
		public DateTime ModifiedDate { get; set; }
	}
}
 
 
 
//-------------------------------------------------------
//-- SalesTerritory.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:30:56 PM
//-------------------------------------------------------
 
using System;
 
namespace ConsoleApplication1.Model.Sales {
	public class SalesTerritory {
		public SalesTerritory() {}
 
		public Int32 TerritoryID { get; set; }
		public string Name { get; set; }
		public string CountryRegionCode { get; set; }
		public string Group { get; set; }
		public Decimal SalesYTD { get; set; }
		public Decimal SalesLastYear { get; set; }
		public Decimal CostYTD { get; set; }
		public Decimal CostLastYear { get; set; }
		public Guid rowguid { get; set; }
		public DateTime ModifiedDate { get; set; }
	}
}

and the commands:

 
//-------------------------------------------------------
//-- SalesOrderHeaderUpdate.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderHeaderUpdate : ICommand {
		public SalesOrderHeaderUpdate() {}
 
		public string GetScript() { 
			return 
@"
UPDATE [Sales].[SalesOrderHeader] SET
   [DueDate] = @DueDate,
   [ShipDate] = @ShipDate,
   [Status] = @Status,
   [Comment] = @Comment,
   [ModifiedDate] = getdate()
WHERE
   [SalesOrderID] = @SalesOrderID"; 
		}
 
		public Int32 SalesOrderID { get; set; }
 		public DateTime DueDate { get; set; }
		public DateTime? ShipDate { get; set; }
		public Byte Status { get; set; }
 		[Parameter(128)] public string Comment { get; set; }
	}
}
 
 
//-------------------------------------------------------
//-- SalesOrderHeaderSelectAll.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderHeaderSelectAll : ICommand {
		public SalesOrderHeaderSelectAll() {}
 
		public string GetScript() { 
			return 
@"
SELECT
   [SalesOrderID],
   [RevisionNumber],
   [OrderDate],
   [DueDate],
   [ShipDate],
   [Status],
   [OnlineOrderFlag],
   [SalesOrderNumber],
   [PurchaseOrderNumber],
   [AccountNumber],
   [CustomerID],
   [SalesPersonID],
   [TerritoryID],
   [BillToAddressID],
   [ShipToAddressID],
   [ShipMethodID],
   [CreditCardID],
   [CreditCardApprovalCode],
   [CurrencyRateID],
   [SubTotal],
   [TaxAmt],
   [Freight],
   [TotalDue],
   [Comment],
   [rowguid],
   [ModifiedDate]
FROM
   [Sales].[SalesOrderHeader]
"; 
		}
 
	}
}
 
 
//-------------------------------------------------------
//-- SalesOrderHeaderInsert.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderHeaderInsert : ICommand {
		public SalesOrderHeaderInsert() {}
 
		public string GetScript() { 
			return 
@"
INSERT INTO [Sales].[SalesOrderHeader] (
   [RevisionNumber],
   [OrderDate],
   [DueDate],
   [ShipDate],
   [Status],
   [OnlineOrderFlag],
   [PurchaseOrderNumber],
   [AccountNumber],
   [CustomerID],
   [SalesPersonID],
   [TerritoryID],
   [BillToAddressID],
   [ShipToAddressID],
   [ShipMethodID],
   [CreditCardID],
   [CreditCardApprovalCode],
   [CurrencyRateID],
   [SubTotal],
   [TaxAmt],
   [Freight],
   [Comment],
   [rowguid],
   [ModifiedDate])
VALUES (
@RevisionNumber,
@OrderDate,
@DueDate,
@ShipDate,
@Status,
@OnlineOrderFlag,
@PurchaseOrderNumber,
@AccountNumber,
@CustomerID,
@SalesPersonID,
@TerritoryID,
@BillToAddressID,
@ShipToAddressID,
@ShipMethodID,
@CreditCardID,
@CreditCardApprovalCode,
@CurrencyRateID,
@SubTotal,
@TaxAmt,
@Freight,
@Comment,
newid(),
getdate())
SET @SalesOrderID = SCOPE_IDENTITY();
"; 
		}
 
		public Byte RevisionNumber { get; set; }
		public DateTime OrderDate { get; set; }
		public DateTime DueDate { get; set; }
		public DateTime? ShipDate { get; set; }
		public Byte Status { get; set; }
		public Boolean OnlineOrderFlag { get; set; }
		[Parameter(25)] public string PurchaseOrderNumber { get; set; }
		[Parameter(15)] public string AccountNumber { get; set; }
		public Int32 CustomerID { get; set; }
		public Int32? SalesPersonID { get; set; }
		public Int32? TerritoryID { get; set; }
		public Int32 BillToAddressID { get; set; }
		public Int32 ShipToAddressID { get; set; }
		public Int32 ShipMethodID { get; set; }
		public Int32? CreditCardID { get; set; }
		[Parameter(15)] public string CreditCardApprovalCode { get; set; }
		public Int32? CurrencyRateID { get; set; }
		[Parameter(19, 4)] public Decimal SubTotal { get; set; }
		[Parameter(19, 4)] public Decimal TaxAmt { get; set; }
		[Parameter(19, 4)] public Decimal Freight { get; set; }
		[Parameter(128)] public string Comment { get; set; }
		[Parameter(ParameterDirection.Output)] public Int32 SalesOrderID { get; set; }
	}
}
 
 
//-------------------------------------------------------
//-- SalesOrderHeaderDelete.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderHeaderDelete : ICommand {
		public SalesOrderHeaderDelete() {}
 
		public string GetScript() { 
			return 
@"
DELETE FROM [Sales].[SalesOrderHeader] WHERE
   [SalesOrderID] = @SalesOrderID"; 
		}
 
		public Int32 SalesOrderID { get; set; }
	}
}
 
 
//-------------------------------------------------------
//-- SalesOrderDetailUpdate.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderDetailUpdate : ICommand {
		public SalesOrderDetailUpdate() {}
 
		public string GetScript() { 
			return 
@"
UPDATE [Sales].[SalesOrderDetail] SET
   [OrderQty] = @OrderQty,
   [UnitPrice] = @UnitPrice,
   [ModifiedDate] = getdate()
WHERE
   [SalesOrderID] = @SalesOrderID AND
   [SalesOrderDetailID] = @SalesOrderDetailID"; 
		}
 
		public Int32 SalesOrderID { get; set; }
		public Int32 SalesOrderDetailID { get; set; }
		public Int16 OrderQty { get; set; }
		[Parameter(19, 4)] public Decimal UnitPrice { get; set; }
	}
}
 
 
 
//-------------------------------------------------------
//-- SalesOrderDetailInsert.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderDetailInsert : ICommand {
		public SalesOrderDetailInsert() {}
 
		public string GetScript() { 
			return 
@"
INSERT INTO [Sales].[SalesOrderDetail] (
   [SalesOrderID],
   [CarrierTrackingNumber],
   [OrderQty],
   [ProductID],
   [SpecialOfferID],
   [UnitPrice],
   [UnitPriceDiscount],
   [rowguid],
   [ModifiedDate])
VALUES (
@SalesOrderID,
@CarrierTrackingNumber,
@OrderQty,
@ProductID,
@SpecialOfferID,
@UnitPrice,
@UnitPriceDiscount,
newid(),
getdate())
SET @SalesOrderDetailID = SCOPE_IDENTITY();
"; 
		}
 
		public Int32 SalesOrderID { get; set; }
		[Parameter(25)] public string CarrierTrackingNumber { get; set; }
		public Int16 OrderQty { get; set; }
		public Int32 ProductID { get; set; }
		public Int32 SpecialOfferID { get; set; }
		[Parameter(19, 4)] public Decimal UnitPrice { get; set; }
		[Parameter(19, 4)] public Decimal UnitPriceDiscount { get; set; }
		[Parameter(ParameterDirection.Output)] public Int32 SalesOrderDetailID { get; set; }
	}
}
 
 
 
//-------------------------------------------------------
//-- SalesOrderHeaderSelect.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderRead : ICommand {
		public SalesOrderRead(int orderId) { 
			SalesOrderID = orderId; 
		}
 
		public string GetScript() { 
			return 
@"
-- Read Order Header
SELECT
   [SalesOrderID],
   [RevisionNumber],
   [OrderDate],
   [DueDate],
   [ShipDate],
   [Status],
   [OnlineOrderFlag],
   [SalesOrderNumber],
   [PurchaseOrderNumber],
   [AccountNumber],
   [CustomerID],
   [SalesPersonID],
   [TerritoryID],
   [BillToAddressID],
   [ShipToAddressID],
   [ShipMethodID],
   [CreditCardID],
   [CreditCardApprovalCode],
   [CurrencyRateID],
   [SubTotal],
   [TaxAmt],
   [Freight],
   [TotalDue],
   [Comment],
   [rowguid],
   [ModifiedDate]
FROM
   [Sales].[SalesOrderHeader]
WHERE
   [SalesOrderID] = @SalesOrderID
 
-- Read Order Details
SELECT
   [SalesOrderID],
   [SalesOrderDetailID],
   [CarrierTrackingNumber],
   [OrderQty],
   [ProductID],
   [SpecialOfferID],
   [UnitPrice],
   [UnitPriceDiscount],
   [LineTotal],
   [rowguid],
   [ModifiedDate]
FROM
   [Sales].[SalesOrderDetail]
WHERE
	SalesOrderID = @SalesOrderID
"; 
		}
 
		public Int32 SalesOrderID { get; set; }
	}
}
 
 
 
//-------------------------------------------------------
//-- SalesOrderDetailDelete.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 4/23/2010 12:29:11 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.DAL.Sales {
	public class SalesOrderDetailDelete : ICommand {
		public SalesOrderDetailDelete() {}
 
		public string GetScript() { 
			return 
@"
DELETE FROM [Sales].[SalesOrderDetail] WHERE
   [SalesOrderID] = @SalesOrderID AND
   [SalesOrderDetailID] = @SalesOrderDetailID"; 
		}
 
		public Int32 SalesOrderID { get; set; }
		public Int32 SalesOrderDetailID { get; set; }
	}
}

We can now start playing with orders:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ConsoleApplication1.Model.Sales;
using ConsoleApplication1.DAL.Sales;
using CoNatural.Data;
using CoNatural.Data.Helpers;
using CoNatural.Data.SqlClient;
using System.Transactions;
using System.Diagnostics;
 
namespace ConsoleApplication1 {
	class Program {
		static IConnection conn;
 
		static void Main(string[] args) {
			conn = new SqlConnectionWithTrace(@"Data Source=PHENOM-II\PHENOM_II;Initial Catalog=AdventureWorks2008;Integrated Security=True");
			//conn.ExecutionMode = System.Data.CommandType.Text;
			conn.ExecutionMode = System.Data.CommandType.StoredProcedure;
 
			// deploy commands
			if(conn.ExecutionMode == System.Data.CommandType.StoredProcedure)
				DeployCommands(conn);
 
			// execute tests
			SalesOrderHeader soh = SaveNewOrderTest(true);
			ModifyOrderTest(soh.SalesOrderID, true);
		}
 
		private static void DeployCommands(IConnection conn) {
			var deployer = new CoNatural.Data.SqlServer.SqlServerCommandDeployer();
			deployer.Deploy(conn, 
			   new SalesOrderHeaderInsert(), 
			   new SalesOrderDetailInsert(),
			   new SalesOrderRead(0),
			   new SalesOrderHeaderUpdate(),
			   new SalesOrderDetailUpdate(),
			   new SalesOrderDetailDelete()
			);
		}
 
		private static SalesOrderHeader SaveNewOrderTest(bool inBatch) {
			SalesOrderHeader order = CreateNewOrder("test1");
			AddNewOrderDetails(order, 1, 706, 33.44M);
			AddNewOrderDetails(order, 2, 707, 55.55M);
			SaveOrder(order, inBatch);
			return order;
		}
 
		private static void ModifyOrderTest(int orderId, bool inBatch) {
			SalesOrderHeader order = ReadOrder(orderId);
			ModifyOrder(order, "new comments", DateTime.Today.AddDays(4));
			DeleteOrderDetail(order.SalesOrderDetails[0]);
			ModifyOrderDetail(order.SalesOrderDetails[1], 1000, 1.23M);
			AddNewOrderDetails(order, 1, 707, 100.100M);
			AddNewOrderDetails(order, 3, 708, 300.3M);
			SaveOrder(order, inBatch);
		}
 
		private static SalesOrderHeader ReadOrder(int orderId) {
			SalesOrderHeader order = null;
			ITypeMaterializer<SalesOrderDetail> detailMaterializer = null;
			conn.ExecuteReader<SalesOrderHeader>(order, new SalesOrderRead(orderId), (o, r, i) => {
				if (i == 1) {
					order = conn.DefaultTypeMaterializerFactory.Materialize<SalesOrderHeader, SalesOrderRead>(r);
					order.SalesOrderDetails = new List<SalesOrderDetail>();
				}
				else {
					if (detailMaterializer == null)
						detailMaterializer = conn.DefaultTypeMaterializerFactory.CreateTypeMaterializer<SalesOrderDetail, SalesOrderRead>(r);
					var detail = detailMaterializer.Callback(r, i);
					detail.SalesOrderHeader = order;
					order.SalesOrderDetails.Add(detail);
				}
			});
 
			return order;
		}
 
		private static void SaveOrder(SalesOrderHeader order, bool inBatch) {
			var f = new TypeMapperFactoryBase();
			//var f = new OptimizedTypeMapperFactoryBase();
			f.Map<SalesOrderHeader>(
				r => r.Customer.CustomerID,
				r => r.SalesTerritory.TerritoryID);
			f.Map<SalesOrderHeader>(r => r.SalesPerson.BusinessEntityID, "SalesPersonID");
			f.Map<SalesOrderDetail>(r => r.SalesOrderHeader.SalesOrderID);
 
 
			var s = new StrategyBase();
			s.BindTo<SalesOrderHeader>(new SalesOrderHeaderInsert(), new SalesOrderHeaderUpdate(), null);
			s.BindTo<SalesOrderDetail>(new SalesOrderDetailInsert(), new SalesOrderDetailUpdate(), new SalesOrderDetailDelete());
			s.OneToMany<SalesOrderHeader>(r => r.SalesOrderDetails);
 
			using (var ts = new TransactionScope()) {
				conn.Execute(order, s, f, inBatch);
				ts.Complete();
			}
		}
 
		private static void SaveOrderDetail(SalesOrderDetail detail, bool inBatch) {
			var f = new TypeMapperFactoryBase();
			//var f = new OptimizedTypeMapperFactoryBase();
			f.Map<SalesOrderDetail>(r => r.SalesOrderHeader.SalesOrderID);
 
			var s = new StrategyBase();
			s.BindTo<SalesOrderDetail>(new SalesOrderDetailInsert(), new SalesOrderDetailUpdate(), new SalesOrderDetailDelete());
 
			using (var ts = new TransactionScope()) {
				conn.Execute(detail, s, f, inBatch);
				ts.Complete();
			}
		}
 
		private static SalesOrderHeader CreateNewOrder(string comment) {
			return new SalesOrderHeader {
				State = DataObjectStateEnum.Inserted,
				RevisionNumber = 1,
				OrderDate = DateTime.Now,
				DueDate = DateTime.Now.AddDays(3),
				ShipDate = DateTime.Now.AddDays(1),
				Status = 5,
				OnlineOrderFlag = false,
				PurchaseOrderNumber = "PO404040404040",
				AccountNumber = "10-4020-000510",
				Customer = new Customer { CustomerID = 29614, AccountNumber = "AW00029614", PersonID = 591, StoreID = 592, TerritoryID = 6 },
				SalesPerson = new SalesPerson { BusinessEntityID = 282, TerritoryID = 6, SalesQuota = 250000, Bonus = 5000, CommissionPct = 0.015M },
				SalesTerritory = new SalesTerritory { TerritoryID = 6, Name = "Canada", CountryRegionCode = "CA", Group = "North America" },
				BillToAddressID = 529,
				ShipToAddressID = 529,
				ShipMethodID = 5,
				CreditCardID = 1566,
				CreditCardApprovalCode = "85817Vi8045",
				CurrencyRateID = 4,
				SubTotal = 50000,
				TaxAmt = 500,
				Freight = 0,
				Comment = comment,
				SalesOrderDetails = new List<SalesOrderDetail>()
			};
		}
 
		private static void AddNewOrderDetails(SalesOrderHeader order, short qty, int productId, decimal unitPrice) {
			if (order.SalesOrderDetails == null)
				order.SalesOrderDetails = new List<SalesOrderDetail>();
			order.SalesOrderDetails.Add(new SalesOrderDetail {
				State = DataObjectStateEnum.Inserted,
				SalesOrderHeader = order,
				CarrierTrackingNumber = "4911-403C-98",
				OrderQty = qty,
				ProductID = productId,
				SpecialOfferID = 1,
				UnitPrice = unitPrice,
				UnitPriceDiscount = 0M
			});
		}
 
		private static void ModifyOrder(SalesOrderHeader order, string comments, DateTime newDueDate) {
			order.Comment = comments;
			order.DueDate = newDueDate;
			order.ShipDate = newDueDate.AddDays(2);
			order.State = DataObjectStateEnum.Updated;
		}
 
		private static void ModifyOrderDetail(SalesOrderDetail detail, short newQty, decimal newUnitPrice) {
			detail.OrderQty = newQty;
			detail.UnitPrice = newUnitPrice;
			detail.State = DataObjectStateEnum.Updated;
		}
 
		private static void DeleteOrderDetail(SalesOrderDetail detail) {
			detail.State = DataObjectStateEnum.Deleted;
		}
	}
 
	public class SqlConnectionWithTrace : CoNatural.Data.SqlClient.SqlClientConnection {
		public SqlConnectionWithTrace(string connectionString)
			: base(connectionString) {
			Trace.Listeners.Add(new ConsoleTraceListener());
		}
 
		public override void BeforeExecute(ICommand iCommand, IDataCommand dataCommand, System.Data.IDbCommand dbCommand) {
			if (iCommand != null)
				Trace.WriteLine("Executing " + iCommand.GetType().ToString());
			foreach (System.Data.IDbDataParameter p in dbCommand.Parameters)
				if (p.Direction != System.Data.ParameterDirection.Output)
					Trace.WriteLine(p.ParameterName + " = " + p.Value);
			Trace.WriteLine(dbCommand.CommandText);
		}
	}
}

Here we are mapping multiple indirect references to properties, with one mapping SalesPerson.BusinessEntityID to SalesPersonID (This is the name of the parameter in all SalesOrderHeader commands).

f.Map<SalesOrderHeader>(
	r => r.Customer.CustomerID,
	r => r.SalesTerritory.TerritoryID);
f.Map<SalesOrderHeader>(r => r.SalesPerson.BusinessEntityID, "SalesPersonID");
f.Map<SalesOrderDetail>(r => r.SalesOrderHeader.SalesOrderID);

Our strategy is very simple, we just need to register the CRUD commands that will bind to IDataObject types, and set the one-to-many relashionship from SalesOrderHeader to SalesOrderDetail:

var s = new StrategyBase();
s.BindTo<SalesOrderHeader>(new SalesOrderHeaderInsert(), new SalesOrderHeaderUpdate(), null);
s.BindTo<SalesOrderDetail>(new SalesOrderDetailInsert(), new SalesOrderDetailUpdate(), new SalesOrderDetailDelete());
s.OneToMany<SalesOrderHeader>(r => r.SalesOrderDetails);

If you compile and execute this example, you should get the following results:

I will release a new version to codeplex in the near future, including a .NET 4.0 build.

Happy coding!

CoNatural Blogging

Working on new features.

Leave a comment

My Links

Recent Posts

Archives

Categories

Meta/RSS

Recent Comments