CoNatural Blogging

CoNatural Components v1.8

Roger Torres - November 20th, 2010
Comments

The latest CoNatural package was published last week. Visit http://conatural.codeplex.com to download the bits and source code. I’m updating the documentation with some examples for this version.

CoNatural Components v1.7

Roger Torres - July 27th, 2010
Comments

CoNatural Components v1.7 released last week. Check out conatural.codeplex.com to get the latest bits. I’m also writing a quickstart tutorial in case you are new to these components.

Working on new features.

Roger Torres - May 14th, 2010
Comments

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 Components v1.6

Roger Torres - March 26th, 2010
Comments

You can find the latest CoNatural Components (v1.6) Here.

This version is mainly an upgrade to the Visual Studio AddIn, incorporating many new features like asynchronous wizards and support for T4 templates.

If you are planning to use T4 to generate your CRUD commands and/or Model types from SQL tables, I’m providing basic templates, but you can modify them to fit your requirements. For example, if you use a datetime field named “ModifiedDate” in your tables to record the last time a record was modified, you can use the following T4 template:

Update.tt

<#@ template language="C#v3.5" HostSpecific="True" #>
<#
	CrudHost host = (CrudHost)Host;
#>
//-------------------------------------------------------
// CoNatural.Data Visual Studio AddIn T4 Template Engine
//-- <#= host.Name #>.cs
//--
//-- Created by <#= Environment.UserName #> 
//-- Created on <#= DateTime.Now #>
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace <#= host.Namespace #> {
	public class <#= host.Name #> : ICommand {
		public <#= host.Name #>() {}
 
		public string GetScript() {
			return 
@"UPDATE
	[<#= host.Schema #>].[<#= host.Table #>]
SET
<#
int a = 0;
for(int i = 0; i < host.Columns.Length; i++) {
	if (host.InPrimaryKey[i]) continue;
	if (host.Columns[i] == "ModifiedDate") {
#>
	<#= a++ > 0 ? ", " : "  " #>[<#= host.Columns[i] #>] = getdate()
<#
	}
	else {
#>
	<#= a++ > 0 ? ", " : "  " #>[<#= host.Columns[i] #>] = @<#= host.Properties[i] #>
<#
	}
}
#>
WHERE
<#
int b = 0;
for(int i = 0; i < host.Columns.Length; i++) {
	if (host.InPrimaryKey[i] || host.Columns[i] == "ModifiedDate") {
#>
	<#= b++ > 0 ? "AND " : "    " #>[<#= host.Columns[i] #>] = @<#= host.Properties[i] #>
<#
	}
}
#>
 
IF @@ROWCOUNT = 0
	RAISERROR(N'Update failed. Check if original record was modified by another user.', 16, 1)
"; 
		}
 
<#  
for(int i = 0; i < host.Columns.Length; i++) {
#>
		<#= host.PropertyAttributes[i] #>public <#= host.PropertyTypes[i] #> <#= host.Properties[i] #> { get; set; }
<#
}
#>
	}
}

The SQL statement will raise an error if the ModifiedDate field doesn’t match, which means someone else changed the record. This is a very basic example, but you get the idea right?

All the T4 templates must be located under the {Project Root}/T4 folder as shown below:

Project with T4 templates

Just make sure you remove the “Custom Tool” option when adding .tt template files to your project.

The AddIn provides a custom text templating host to process CoNatural templates. The templates are identified by name (Insert.tt, Select.tt, SelectAll.tt, Update.tt, Delete.tt, and Model.tt).

If you are planning to modify a template, you must cast the Host property to the type CrudHost (this is our custom host).

This type provides many properties you can use to build your logic:

using System;
using System.Data;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Smo;
using CoNatural.Data.VisualStudio.AddIn.Helpers;
 
namespace CoNatural.Data.VisualStudio.AddIn.Tools {
	[Serializable]
	public class CrudHost : TextTemplatingEngineHostBase {
		public string Schema { get; private set; }
		public string Table { get; private set; }
		public int IdentityIndex { get; private set; }
		public string[] Columns { get; private set; }
		public string[] ColumnTypes { get; private set; }
		public bool[] InPrimaryKey { get; private set; }
		public string[] Properties { get; private set; }
		public string[] PropertyTypes { get; private set; }
		public string[] PropertyAttributes { get; private set; }
 
		internal CrudHost(Table table, string _namespace, string name, CrudOptionsEnum commandType) {
			Namespace = _namespace;
			Name = name;
 
			Schema = table.Schema;
			Table = table.Name;
 
			IdentityIndex = -1;
 
			int colCount = table.Columns.Count;
			Columns = new string[colCount];
			ColumnTypes = new string[colCount];
			InPrimaryKey = new bool[colCount];
			Properties = new string[colCount];
			PropertyTypes = new string[colCount];
			PropertyAttributes = new string[colCount];
 
			for (int i = 0; i < colCount; i++) {
				Column col = table.Columns[i];
				if(col.Identity)
					IdentityIndex = i;
				Columns[i] = col.Name;
				ColumnTypes[i] = col.DataType.Name;
				InPrimaryKey[i] = col.InPrimaryKey;
 
				ParameterDirection direction = ParameterDirection.Input;
				switch (commandType) {
					case CrudOptionsEnum.Insert:
						direction = col.Identity ? ParameterDirection.Output : ParameterDirection.Input;
						break;
 
					case CrudOptionsEnum.Select:
						direction = col.InPrimaryKey ? ParameterDirection.InputOutput : ParameterDirection.Output;
						break;
 
					case CrudOptionsEnum.SelectAll:
						direction = ParameterDirection.Output;
						break;
 
					case CrudOptionsEnum.Update:
						direction = ParameterDirection.Input;
						break;
 
					case CrudOptionsEnum.Delete:
						direction = ParameterDirection.Input;
						break;
				}
				var ph = PropertyHelper.CreatePropertyHelper(table.Parent, col.Name, col.DataType, col.Nullable, direction);
				Properties[i] = ph.Name;
				PropertyTypes[i] = ph.TypeName;
				PropertyAttributes[i] = ph.Attribute;
			}
		}
	}
}

Just a couple of final notes:

1) T4 templates are optional. If you remove some .tt files from the T4 folder (or remove the folder entirely), the AddIn will use the internal CRUD generator (released in the previous version) to generate CRUD commands/model with no .tt files.

2) The AddIn CommandBar buttons are now showing at the end of the Popup Menus (in case you missed them )

Enjoy.

CoNatural Components v1.5 Released to CodePlex

Roger Torres - March 5th, 2010
Comments

I just released version 1.5 to CodePlex. You can find the latest binaries, source code, and release notes there.

This version supports new data types, contains new optimized type materializer/mapper classes, and improved visual studio add-in.

Let’s say you need to start a new data centric application from scratch. For simplicity, we will create a new C# Console application and connect to AdventureWorks2008 (the one that’s compatible with Sql Server 2008, including the new data types.)

Here are the steps you need to follow:

Make sure you have the CoNatural.Data.VisualStudio.AddIn.AddIn file under your {User Profile}\Documents\Visual Studio 2008\AddIns folder. This file is a pointer to the add-in library, here is what I have in my development system (note that yours must point to the location where you saved the binaries.)

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<Extensibility xmlns="http://schemas.microsoft.com/AutomationExtensibility">
	<HostApplication>
		<Name>Microsoft Visual Studio</Name>
		<Version>9.0</Version>
	</HostApplication>
	<Addin>
		<FriendlyName>CoNatural Data VisualStudio AddIn</FriendlyName>
		<Description>Manage CoNatural data commands in Visual Studio 2008.</Description>
		<Assembly>C:\Users\Roger\Documents\Visual Studio 2008\Projects\conatural\CoNatural Components\bin\CoNatural.Data.VisualStudio.AddIn.dll</Assembly>
		<FullClassName>CoNatural.Data.VisualStudio.AddIn.Connect</FullClassName>
		<LoadBehavior>1</LoadBehavior>
		<CommandPreload>1</CommandPreload>
		<CommandLineSafe>0</CommandLineSafe>
	</Addin>
</Extensibility>

Open Visual Studio 2008 and create a new C# console application. Name it “AdventureWorks2008Console”.
Create a new folder named “Commands”, and start the table import wizard (in this case we will import only “SelectAll” commands, but you can play with other options if you like).

Point the wizard to your AdventureWorks database in step 1 of 5.
Hit the Refresh button and select all the tables in step 2 of 5.

In step 3 of 5, make sure “Include Schema” is checked, and “Generate .sql file” is unchecked. This is to follow the default naming conventions when generating the commands, and include the sql scripts inside the .cs class files.

In step 4 of 5, make sure you are generating only “SelectAll” commands and the Model classes (under the /Model folder that will be created automatically).

Complete the wizard and go to project/References to add references to CoNatural.Data (binaries folder) and Microsoft.SqlServer.Types (this should be in the gac if you have installed the sql server 2008 feature pack - with support to the new sql data types). If you don’t find the second assembly in your system, the project won’t compile, but you can always find the files with the missing references and delete them from the project.
There is one final step. In the model you might find a couple of classes that won’t compile because a property and the type share the same name. You can rename the classes to anything you like. Here is a view of the project so far:

Make sure the project compiles now. Then copy the following code to Program.cs. Note that you must change the connection string to fit your environment.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoNatural.Data;
using CoNatural.Data.SqlClient;
 
namespace AdventureWorks2008Console {
	class Program {
		static void Main(string[] args) {
			try {
				IConnection c1 = new SqlClientConnection(@"Data Source=PHENOM-II\PHENOM_II;Initial Catalog=AdventureWorks2008;Integrated Security=True");
 
				RunTest("Reading with default connection", c1);
 
				IConnection c2 = new ConnectionBase(@"Data Source=PHENOM-II\PHENOM_II;Initial Catalog=AdventureWorks2008;Integrated Security=True",
					new DataCommandFactoryBase(new SqlClientDbProvider()),
					new OptimizedTypeMaterializerFactoryBase(),
					new OptimizedTypeMapperFactoryBase());
 
				RunTest("Reading with optimized connection", c2);
			}
			catch (Exception ex) {
				Console.WriteLine(ex.Message);
			}
 
			Console.WriteLine("DONE");
			Console.ReadLine();
		}
 
		static void RunTest(string testName, IConnection c) {
			DateTime startTime = DateTime.Now;
			int iterations = 2;
			while (iterations-- > 0) {
				ReadAll<Commands.Sales.SalesOrderDetailSelectAll, Model.Sales.SalesOrderDetail>(c);
				ReadAll<Commands.Production.TransactionHistorySelectAll, Model.Production.TransactionHistory>(c);
				ReadAll<Commands.Production.TransactionHistoryArchiveSelectAll, Model.Production.TransactionHistoryArchive>(c);
				ReadAll<Commands.Production.WorkOrderSelectAll, Model.Production.WorkOrder>(c);
				ReadAll<Commands.Production.WorkOrderRoutingSelectAll, Model.Production.WorkOrderRouting>(c);
				ReadAll<Commands.Sales.SalesOrderHeaderSelectAll, Model.Sales.SalesOrderHeader>(c);
				ReadAll<Commands.Sales.SalesOrderHeaderSalesReasonSelectAll, Model.Sales.SalesOrderHeaderSalesReason>(c);
				ReadAll<Commands.Person.BusinessEntitySelectAll, Model.Person.BusinessEntity>(c);
				ReadAll<Commands.Person.PersonSelectAll, Model.Person.Person>(c);
				ReadAll<Commands.Purchasing.PurchaseOrderDetailSelectAll, Model.Purchasing.PurchaseOrderDetail>(c);
			}
			Console.WriteLine("Test [{0}] completed in {1} seconds.", testName, DateTime.Now.Subtract(startTime).TotalSeconds);
		}
 
		static void ReadAll<TCommand, TModel>(IConnection connection) where TCommand : ICommand {
			ICommand command = (ICommand)Activator.CreateInstance<TCommand>();
			int rows = 0;
			connection.ExecuteReader<TModel>(command).All(d => {
				rows++;
				return true;
			});
			Console.WriteLine("ReadAll returned {0} rows of {1}.", rows, typeof(TModel).Name);
		}
	}
}

Here we selected 10 of the largest tables and decided to execute the “SelectAll” commands twice, using the standard type materializer and the new optimized type materializer. Here is the resulting console.

And that’s all. You have created a DAL to read from AdventureWorks to your own PONO model, it’s up to you now to play with this and find a more useful application.

I hope you enjoyed it!

New CoNatural Components 1.4 Released to CodePlex

Roger Torres - February 4th, 2010
Comments

This morning I released a new version of the CoNatural Components (version 1.4) to the CodePlex repository. There are some significant changes to CoNatural.Data, so existing code will require some modifications in order to compile with this version. The main theme is the re-factoring work started in v1.3 to follow interface oriented programming practices in order to make the framework more testable and IoC friendly. There are also some new features I will list below.

Release Notes

New interfaces
- IParameter: Data parameter to command property mapping interface. A concrete command factory instantiates commands with parameter mappings. A default implementation can be found in the /Base folder (ParameterBase).
- ITypeMaterializer<T>, ITypeMaterializerFactory: The components in charge of processing data reader results, instantiating types of T, and populating them with the results of each data record returned by the command reader. Default implementation using reflection with caching can be found in the /Base folder. Other implementations, using XML mappings for example, are possible.
- ICommandDeployer: The component in charge of deploying CoNatural data commands as sql stored procedures (creating or updating existing procedures). This functionality was moved from IConnection-IDbProvider to this new interface to decouple the basic framework from deployment scenarios. The default base class in /Base as usual, and a concrete SqlServer implementation can be found in the CoNatural.Data.SqlServer project (SqlServerCommandDeployer ).
- ITypeMapper<T>, ITypeMapperFactory: The components in charge of mapping commands bound to instances of type T. This basically replaces the ICommand<T> pattern in version 1.3, thus a code change is required to support the new pattern. Default TypeMapperBase, TypeMapperFactoryBase implementations can be found in the /Base folder, very similar to the default type materializer (using reflection and caching the mappings). IConnection now supports DefaultTypeMapperFactory and DefaultTypeMaterializerFactory properties (using /base by default) as a fallback mechanism to automatically perform the mapping of properties when no mapper is passed to the execution methods.
- ICommand: Added GetScript() method to ICommand interface. This allows users to embed SQL scripts inside command classes, making the extra .sql embedded resource file in projects an option (not a requirement). Modified basic command factory, script provider, and Visual Studio templates/Add-In to accommodate this change. When GetScript() returns null, the runtime calls the script provider associated to the connection. A CommandBase class was also added to the /Base folder with GetScript() returning null by default (useful to find/replace : ICommand to : CommandBase for a quick fix to existing commands).
Project organization: As noted in the previous point, the project was reorganized to hold the interfaces in the main folder, and the default base class implementations in the /Base sub-folder (All base classes now with the XXXBase suffix). There are also folders for the attributes (/Attributes), and for each concrete database provider (/Odbc, /OleDb, /SqlClient). Renamed ICommandFactory to IDataCommandFactory, and moved default command factory to /Base folder.
Helpers to simplify the API: New connection and dbprovider constructors added to all providers (SqlClient, Odbc, OleDb) using the default command and script factories.
New Add-In features: A new wizard was implemented to generate table adapters (CRUD commands) from Sql Server tables. As mentioned above, there is a new option to generete .sql embedded resource files or inline SQL returned from GetScript() when importing stored procedures and tables.

Here are some AdventureWorks commands I just imported with the SQL script embedded in the .cs file:

//-------------------------------------------------------
//-- uspGetEmployeeManagers.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 1:38:42 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.dbo {
	public class uspGetEmployeeManagers : ICommand {
		public uspGetEmployeeManagers() {}
 
		public string GetScript() { 
			return 
@"
BEGIN
SET NOCOUNT ON;
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee
FROM [HumanResources].[Employee] e 
INNER JOIN [Person].[Person] as p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e 
INNER JOIN [EMP_cte]
ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
INNER JOIN [Person].[Person] p 
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
[EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
FROM [EMP_cte] 
INNER JOIN [HumanResources].[Employee] e 
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p 
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25) 
END;
"; 
		}
 
		public Int32? BusinessEntityID { get; set; }
	}
}

These are CRUD commands for the Department table:

 
//-------------------------------------------------------
//-- DepartmentInsert.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentInsert : ICommand {
		public DepartmentInsert() {}
 
		public string GetScript() { 
			return 
@"
INSERT INTO [HumanResources].[Department] (
   [Name],
   [GroupName],
   [ModifiedDate])
VALUES (
   @Name,
   @GroupName,
   @ModifiedDate)
SET @DepartmentID = SCOPE_IDENTITY();
"; 
		}
 
		[Parameter(50)] public string Name { get; set; }
		[Parameter(50)] public string GroupName { get; set; }
		public DateTime? ModifiedDate { get; set; }
		[Parameter(ParameterDirection.Output)] public Int16? DepartmentID { get; set; }
	}
}

 
//-------------------------------------------------------
//-- DepartmentSelect.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentSelect : ICommand {
		public DepartmentSelect() {}
 
		public string GetScript() { 
			return 
@"
SELECT
   @DepartmentID = [DepartmentID],
   @Name = [Name],
   @GroupName = [GroupName],
   @ModifiedDate = [ModifiedDate]
FROM
   [HumanResources].[Department]
WHERE
   [DepartmentID] = @DepartmentID"; 
		}
 
		[Parameter(ParameterDirection.InputOutput)] public Int16? DepartmentID { get; set; }
		[Parameter(ParameterDirection.Output, 50)] public string Name { get; set; }
		[Parameter(ParameterDirection.Output, 50)] public string GroupName { get; set; }
		[Parameter(ParameterDirection.Output)] public DateTime? ModifiedDate { get; set; }
	}
}

 
//-------------------------------------------------------
//-- DepartmentUpdate.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentUpdate : ICommand {
		public DepartmentUpdate() {}
 
		public string GetScript() { 
			return 
@"
UPDATE [HumanResources].[Department] SET
   [Name] = @Name,
   [GroupName] = @GroupName,
   [ModifiedDate] = @ModifiedDate
WHERE
   [DepartmentID] = @DepartmentID"; 
		}
 
		public Int16? DepartmentID { get; set; }
		[Parameter(50)] public string Name { get; set; }
		[Parameter(50)] public string GroupName { get; set; }
		public DateTime? ModifiedDate { get; set; }
	}
}

 
//-------------------------------------------------------
//-- DepartmentDelete.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentDelete : ICommand {
		public DepartmentDelete() {}
 
		public string GetScript() { 
			return 
@"
DELETE FROM [HumanResources].[Department] WHERE
   [DepartmentID] = @DepartmentID"; 
		}
 
		public Int16? DepartmentID { get; set; }
	}
}

Let’s read all the managers for business entities 1 to 4, and print the results:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoNatural.Data;
using CoNatural.Data.SqlClient;
 
namespace ConsoleApplication1 {
	class Program {
		static void Main(string[] args) {
			try {
				IConnection c = new SqlClientConnection(@"Data Source=PHENOM-II\PHENOM_II;Initial Catalog=AdventureWorks2008;Integrated Security=True");
				Commands.dbo.uspGetEmployeeManagers cmd = new Commands.dbo.uspGetEmployeeManagers();
 
				new int[] { 1, 2, 3, 4 }.All(i => {
					Console.WriteLine("BusinessEntityId = " + i);
					cmd.BusinessEntityID = i;
					c.ExecuteReader<Model.Employee>(cmd).All(e => {
						Console.WriteLine(e);
						return true;
					});
					return true;
				});
			}
			catch (Exception ex) {
				Console.WriteLine(ex.Message);
			}
			Console.ReadLine();
		}
	}
}
 
namespace ConsoleApplication1.Model {
	public class Employee {
		public string FirstName { get; set; }
		public string LastName { get; set; }
		public string ManagerFirstName { get; set; }
		public string ManagerLastName { get; set; }
 
		public override string ToString() {
			return string.Format("{0} {1} - managed by {2} {3}", FirstName, LastName, ManagerFirstName, ManagerLastName);
		}
	}
}

Can you see the default type materializer in action?

Conclusion

I believe this framework is now pretty stable, so I don’t anticipate any major design changes … but you never know what the next requirement is going to look like. I hope we will always find a good concrete implementation

CoNatural Components 1.3 Notes

Roger Torres - December 7th, 2009
Comments

This weekend I updated the CodePlex repository with the latest build (1.3) and source code. All source code files/links have been removed from this Blog — a lot of broken links ;-(, so there is only one valid repository (CodePlex). Note that the latest changes to the framework will break old code, and the examples in the original posts will require some modifications. I will try to post new examples based on the latest interfaces, but you can always read the posts explaining the reasoning behind these changes and make your modifications accordingly.

CoNatural.Data v1.3 just released.

Roger Torres - September 11th, 2009
Comments

I just released a new version of the CoNatural.Data components (version 1.3). You can find the latest source code at http://conatural.codeplex.com.

This version required some code refactoring to support multiple CoNatural command factories in a single application. A command factory is just a concrete implementation of a mechanism to translate CoNatural commands to the internal representation used by the framework.

In the previous releases, there was only one command factory embedded inside the Command constructor, and the only mechanism supported was reading command assemblies via reflection and extracting parameters from properties and SQL scripts from the “.sql” file associated with the reflected types. In this new version, the same factory has been implemented in a new class “DefaultCommandFactory”, also optimized to process and cache command definitions by type.

A new ICommandFactory interface is also available for other implementations, making it more IoC (Dependency Injection) friendly, and also more testable. Now, user defined command types won’t need to inherit from CoNatural.Data.Command, but implement the empty ICommand interface. Note that this change is not backward compatible, but a simple project wide find/replace from “Command” to “ICommand” will take care of the problems.

If you have been using this framework, you might be wondering why the change now? Well, one of my clients needed to build CoNatural commands dynamically from metadata stored in a SQL Server database. After trying with static commands, modifying the scripts on the fly and applying IgnoreParameter attributes to the type properties, I decided that the best way to accommodate this requirement was by defining the command factory abstraction and move the default implementation to a new concrete class. This solution is cleaner, more testable, allows me to better mock some parts of the system and leaves my client with the responsibility to build its own command factory without further framework pollution. I hope we can describe the resulting work in future posts.

If you have any problems with the new changes or suggestions for new features, please let me know here, by email, or at the Codeplex site.

Thanks

Roger

CoNatural Components Visual Studio AddIn

Roger Torres - June 25th, 2009
1 Comment

This is just a quick note to let you know that today I uploaded the first functional version of the CoNatural Data Visual Studio AddIn to CodePlex (http://conatural.codeplex.com). The main feature is a wizard to import existing stored procedures as CoNatural Commands. Users will be able to select stored procedures from a data source, and apply naming conventions to generate commands in a folder structure.

I’m currently using this wizard in a project I’m migrating from regular ADO.NET with stored procedures to the CoNatural.Data framework. This client is very happy now that he doesn’t have to maintain stored procedures detached from the main source control system.

Note that this version only supports Visual Studio 2008 C# projects and the .NET 3.5 framework. The only data source supported is Sql Server 2005/2008.

CoNatural Components - CodePlex Project

Roger Torres - June 14th, 2009
1 Comment

I just published a CodePlex project to host the DAL and Asynchronous framework I have been presenting in this blog. This will allow me to track issues and manage releases the right way. Thanks to the readers who suggested this option.

You can find the project here: CoNatural Components

Please note that I made the following modifications to the original CoNatural.Data component:

The IDbProvider interface was simplified.
The implementation of the Sql Server provider was moved to a new assembly named CoNatural.Data.SqlServer

There are other minor (performance related) modifications, but this should be transparent to the users.

CoNatural Blogging

CoNatural Components v1.8

CoNatural Components v1.7

Working on new features.

CoNatural Components v1.6

CoNatural Components v1.5 Released to CodePlex

New CoNatural Components 1.4 Released to CodePlex

CoNatural Components 1.3 Notes

CoNatural.Data v1.3 just released.

CoNatural Components Visual Studio AddIn

CoNatural Components - CodePlex Project

My Links

Recent Posts

Archives

Categories

Meta/RSS

Recent Comments