CoNatural Blogging

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.

Async Search with ASP.NET MVC + jQuery + CoNatural - Part II

Roger Torres - May 3rd, 2009
4 Comments

In Part I of this article, I described how to write an asynchronous search component that spawns multiple parallel search operations, and notify the caller when completed or canceled. It’s now time to build a user interface to Start, Cancel, Delete, and Monitor our search operations asynchronously.

After playing with the ASP.NET MVC framework for a while, this technology is quickly becoming my favorite presentation platform, specially for small to medium size projects where I don’t need fancy controls… so I decided to develop this GUI with ASP.NET MVC. I will also write a small jQuery plugin to encapsulate the client side of the search, including a protocol to exchange AJAX/JSON messages with the controllers.

Preparing the Solution

Let’s start by creating a new ASP.NET MVC application. [Download ASP.NET MVC Here].

Create ASP.NET MVC Application

This step is very straightforward, just follow the default application template. Don’t forget to build and execute the solution to make sure all the pieces are in the right place. Note: I’m using Visual Studio 2008 SP1 and the .NET framework 3.5 SP1 which is a requirement for ASP.NET MVC.

Now you can add a reference to the AsyncOp library from Part I, and we are done configuring our solution.

Preparing the Site

Before we start writing code, let’s make sure our master page has a reference to the jQuery library under the /Scripts folder:

<script src="../../Scripts/jquery-1.3.2.js" type="text/javascript"></script>

We are going to write a jQuery plugin, so let’s add that reference in advance:

<script src="../../Scripts/async.js" type="text/javascript"></script>

And finally, let’s define a block of CSS styles for the panel that will hold our search operations:

    <style type="text/css">
        .async ul {background-color:White;border:solid 1px black;padding:5px;list-style-type:none;}
        .async li {border-bottom:dotted 1px silver; padding:5px;}
        .async span {border:solid 1px silver;background-color:Yellow;padding:5px;font-size:80%;font-style:italic;margin:5px;}
        .async input {border:solid 1px silver;padding:5px;margin:5px;}
        .async input:hover {background-color:Gray;}
    </style>

The master page should look like this:

Site.Master

<head runat="server">
    <title><asp:ContentPlaceHolder ID="TitleContent" runat="server" /></title>
    <link href="../../Content/Site.css" rel="stylesheet" type="text/css" />
    <script src="../../Scripts/jquery-1.3.2.js" type="text/javascript"></script>
    <script src="../../Scripts/async.js" type="text/javascript"></script>
 
    <style type="text/css">
        .async ul {background-color:White;border:solid 1px black;padding:5px;list-style-type:none;}
        .async li {border-bottom:dotted 1px silver; padding:5px;}
        .async span {border:solid 1px silver;background-color:Yellow;padding:5px;font-size:80%;font-style:italic;margin:5px;}
        .async input {border:solid 1px silver;padding:5px;margin:5px;}
        .async input:hover {background-color:Gray;}
    </style>
</head>

Managing Search Operations

Our goal is to provide a GUI to manage our asynchronous search operations, so we will need cover the following scenarios:

List and monitor all running and completed operations.
Start new search operations with different criteria.
Cancel a running operation.
Delete a completed operation (so it won’t be listed anymore).
Present the results of a completed operation.

In order to simplify this example, the server side logic will be implemented inside the HomeController class, and I’m going to store the active search operations in the ASP.NET Session. In a production environment, I recommend to develop a more scalable “Pool” of operations where the administrator can control the maximum number of active searches and the memory used by the results.

We will start by writing a new Search action method to return all the active operations to the client. This method will only accept HTTP GET verbs, and will serialize the results back to the client using the new DataContractJsonSerializer implemented in .NET 3.5 SP1 under System.Runtime.Serialization.Json.

In Part I we annotated our AsyncOp class with DataContract and DataMember attributes to make sure only the elements defined in our contract are serialized to the client. The JsonResult class that ships with ASP.NET MVC Version 1.0 doesn’t use DataContractJsonSerializer, so the entire class (including the results) is serialized. I hope the MVC team is taking care of this little issue, but in the mean time, we can write our own JsonResult class as follows:

JsonResult.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Runtime.Serialization.Json;
 
namespace MvcAsyncSearch.MyTools {
   public class JsonResult : ActionResult {
      public Encoding ContentEncoding { get; set; }
      public string ContentType { get; set; }
      public object Data { get; set; }
 
      public override void ExecuteResult(ControllerContext context) {
         if (context == null)
            throw new ArgumentNullException("context");
 
         HttpResponseBase response = context.HttpContext.Response;
         if (!String.IsNullOrEmpty(ContentType))
            response.ContentType = ContentType;
         else
            response.ContentType = "application/json";
 
         if (ContentEncoding != null)
            response.ContentEncoding = ContentEncoding;
 
         if (Data != null) {
            DataContractJsonSerializer serializer = new DataContractJsonSerializer(Data.GetType());
            serializer.WriteObject(response.OutputStream, Data);
         }
      }
   }
}

Going back to our Action method, the following code takes care of presenting/monitoring all active searches. Note that I’m using a couple of helper methods to find the operations in the ASP.NET Session, and refresh the Status property of the AsyncOp instances before the results are serialized to the client.

[AcceptVerbs(HttpVerbs.Get)]
public ActionResult Search() {
   Dictionary<string, AsyncOp.AsyncOp> operations = GetAsyncOps(HttpContext.Session);
   SetStatus(operations);
   MyTools.JsonResult result = new MvcAsyncSearch.MyTools.JsonResult();
   result.Data = operations.Values.ToArray();
   return result;
}
 
private Dictionary<string, AsyncOp.AsyncOp> GetAsyncOps(HttpSessionStateBase session) {
   object ops = session["async"];
   if (ops == null) {
      ops = new Dictionary<string, AsyncOp.AsyncOp>();
      session["async"] = ops;
   }
   return (Dictionary<string, AsyncOp.AsyncOp>)ops;
}
 
private void SetStatus(Dictionary<string, AsyncOp.AsyncOp> operations) {
   foreach(AsyncOp.AsyncOp op in operations.Values) {
      if (op.Error != null)
         op.Status = "Error: " + op.Error.Message;
      else {
         if (op.Completed || op.Cancelled) {
            if (op.Completed)
               op.Status = "DONE.";
            else
               op.Status = "Cancelled.";
            if (op.Results.Count > 0)
               op.Status += " <a href=\"" + Url.Action("Results", 
                  new { asyncId = op.AsyncId }) + "\">" + op.Results.Count + " results found.</a>";
            else
               op.Status += " No results found.";
         }
         else
            op.Status = op.Results.Count + " results found ...";
      }
   }
}

Initially, we won’t have any active searches to display… so, we need another Search action method to Start, Cancel, and Remove operations from the pool. In this case, only HTTP POST verbs will be allowed, and the client side will be responsible for configuring the request with “Form” parameters as follows:

To cancel an operation, include parameter “cancel={AsyncId}”
To delete an operation, include parameter “dismiss={AsyncId}”
To start an operation, include parameter “criteria={Search Criteria}”

This Action will also return all the active operations to the client (same JSON results), effectively updating the user interface without having to wait for the next refresh request.

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Search(FormCollection collection) {
   Dictionary<string, AsyncOp.AsyncOp> operations = GetAsyncOps(HttpContext.Session);
   AsyncOp.AsyncOp op;
 
   if (collection.AllKeys.Contains("cancel")) {
      string asyncId = collection["cancel"];
      if (operations.TryGetValue(asyncId, out op)) {
         op.Cancel();
         op.Status = "Cancelling...";
      }
   }
   else if (collection.AllKeys.Contains("dismiss")) {
      string asyncId = collection["dismiss"];
      operations.Remove(asyncId);
   }
   else {
      string criteria = collection["criteria"];
      op = new AsyncOp.AsyncOp(criteria.Trim().ToUpper());
      operations.Add(op.AsyncId.ToString(), op);
      op.Start();
   }
 
   SetStatus(operations);
   MyTools.JsonResult result = new MvcAsyncSearch.MyTools.JsonResult();
   result.Data = operations.Values.ToArray();
   return result;
}

Here is how the final HomeController class should look like:

HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using AsyncOp;
 
namespace MvcAsyncSearch.Controllers {
   [HandleError]
   public class HomeController : Controller {
      public ActionResult Index() {
         ViewData["Message"] = "Welcome to ASP.NET MVC!";
 
         return View();
      }
 
      public ActionResult About() {
         return View();
      }
 
      [AcceptVerbs(HttpVerbs.Get)]
      public ActionResult Search() {
         Dictionary<string, AsyncOp.AsyncOp> operations = GetAsyncOps(HttpContext.Session);
         SetStatus(operations);
         MyTools.JsonResult result = new MvcAsyncSearch.MyTools.JsonResult();
         result.Data = operations.Values.ToArray();
         return result;
      }
 
      [AcceptVerbs(HttpVerbs.Post)]
      public ActionResult Search(FormCollection collection) {
         Dictionary<string, AsyncOp.AsyncOp> operations = GetAsyncOps(HttpContext.Session);
         AsyncOp.AsyncOp op;
         if (collection.AllKeys.Contains("cancel")) {
            string asyncId = collection["cancel"];
            if (operations.TryGetValue(asyncId, out op)) {
               op.Cancel();
               op.Status = "Cancelling...";
            }
         }
         else if (collection.AllKeys.Contains("dismiss")) {
            string asyncId = collection["dismiss"];
            operations.Remove(asyncId);
         }
         else {
            string criteria = collection["criteria"];
            op = new AsyncOp.AsyncOp(criteria.Trim().ToUpper());
            operations.Add(op.AsyncId.ToString(), op);
            op.Start();
         }
 
         SetStatus(operations);
         MyTools.JsonResult result = new MvcAsyncSearch.MyTools.JsonResult();
         result.Data = operations.Values.ToArray();
         return result;
      }
 
      [AcceptVerbs(HttpVerbs.Get)]
      public ActionResult Results(string asyncId) {
         Dictionary<string, AsyncOp.AsyncOp> operations = GetAsyncOps(HttpContext.Session);
         AsyncOp.AsyncOp op;
         if (operations.TryGetValue(asyncId, out op))
            return View(op.Results);
         else
            return View(new List<AsyncOp.AsyncOp.AsyncOpResult>());
      }
 
      private Dictionary<string, AsyncOp.AsyncOp> GetAsyncOps(HttpSessionStateBase session) {
         object ops = session["async"];
         if (ops == null) {
            ops = new Dictionary<string, AsyncOp.AsyncOp>();
            session["async"] = ops;
         }
         return (Dictionary<string, AsyncOp.AsyncOp>)ops;
      }
 
      private void SetStatus(Dictionary<string, AsyncOp.AsyncOp> operations) {
         foreach(AsyncOp.AsyncOp op in operations.Values) {
            if (op.Error != null)
               op.Status = "Error: " + op.Error.Message;
            else {
               if (op.Completed || op.Cancelled) {
                  if (op.Completed)
                     op.Status = "DONE.";
                  else
                     op.Status = "Cancelled.";
                  if (op.Results.Count > 0)
                     op.Status += " <a href=\"" + Url.Action("Results", 
                        new { asyncId = op.AsyncId }) + "\">" + op.Results.Count + " results found.</a>";
                  else
                     op.Status += " No results found.";
               }
               else
                  op.Status = op.Results.Count + " results found ...";
            }
         }
      }
   }
}

Implementing the client side with jQuery.

I really started loving client side JavaScript after I discovered jQuery. I don’t want to get into the details here (tales of my previous suffering with JS), but I will tell you this: If you haven’t tried jQuery yet, please do so and you won’t regret it.

One of the nicest things about jQuery is that you can extend the API very naturally. In this case, I’m writing a plugin to encapsulate the client side of my async search module, including presentation of active searches, polling the server for updates via AJAX requests with JSON serialization, and handling client events to start, cancel, and delete operations.

The first thing we should do when writing a jQuery plugin (or any other JavaScript library) is define a “namespace” where we can implement our own private and public variables and interfaces. I usually start with a JavaScript closure, passing the jQuery instance (to be extended internally). Next, declare the private namespace to define my private variables and functions (visible only inside the closure), and extend jQuery with a public namespace to expose my public interface.

Let’s begin our plugin from the following template. In this case we will be extending jQuery with two public methods ($.async.get and $.async.post):

// create closure
(function($) {
 
    // "async" namespace for private variables and functions
    var async = {
        var1: {},
        var2: [],
        f1 : function() {...},
        f2 : function() {...}
    };
 
    // extend jQuery with "async" namespace
    $.async = {
        defaults: {
             //TODO good practice to define default values
        },
 
        // GET ajax request to update the status of running async operations
        get: function(url, options) {
            // TODO ajax get (url) to the Search action defined previously in HomeController
            // TODO options to override the default parameters
        },
 
        // POST ajax request to start,cancel,delete async operations
        post: function(url, data, options) {
            // TODO ajax post (url) to the Search action defined previously in HomeController
            // TODO data with form parameters (criteria={criteria} | cancel={asyncId} | dismiss={asyncId})
            // TODO options to override the default parameters
        }
    };
 
})(jQuery);

Our plugin is initialized the first time a “Get” or “Post” operation is invoked from a Page, and by default it will use the “body” element as the container for the panels showing the async search operations. The plugin will also monitor the operations by polling the server every 5 seconds (by default) until all the searches are completed. Of course, I will override these defaults later to display the progress of my searches inside a smaller panel (styled with CSS). Let’s take a look at the public interface:

    // extend jQuery with "async" namespace
    $.async = {
        defaults: {
            container: "body",
            pollingInterval: 5000
        },
 
        // GET ajax request to update the status of running async operations
        get: function(url, options) {
            async.options = $.extend({ url: url }, $.async.defaults, options, async.options);
            async.poll();
        },
 
        // posts request to start a new async operation
        post: function(url, data, options) {
            async.options = $.extend({ url: url }, $.async.defaults, options, async.options);
            $.post(url, data, async.callback, "json");
        }
    };

There is nothing special here other than storing the options inside a private variable and invoking the server actions. The core of the plugin is encapsulated inside the private namespace, where a group of callback functions are responsible for adding and removing search operation panels, polling the server for updates, and handling click events (to start-cancel-remove operations). Here I keep a list of panels synchronized with the server, so the plugin will know when to display a new panel. The “cancel” and “dismiss” handlers should be straightforward (unless you are new to jQuery).

The main engine is implemented inside the “callback” function, which receives a list of JSON-formatted active operations from AJAX requests to the server, and depending on the status of each operation, it creates a new panel, updates the status labels and buttons, and restarts the timer to keep polling the server for updates:

    // "async" namespace private variables and functions
    var async = {
        // panels showing async operations
        panels: [],
 
        // cancel handler
        cancel: function() {
            var asyncId = $(this).attr("asyncId");
            var panel = async.panels[asyncId];
            if (panel) {
                $("input", panel).hide();
                $.post(async.options.url, { cancel: asyncId }, async.callback, "json");
            }
        },
 
        // dismiss handler
        dismiss: function() {
            var asyncId = $(this).attr("asyncId");
            var panel = async.panels[asyncId];
            if (panel) {
                $.post(async.options.url, { dismiss: asyncId }, function() {
                    $("#" + asyncId, async.panelcontainer).remove();
                    async.panels[asyncId] = null;
                }, "json");
            }
        },
 
        // ajax callback to handle async operations
        callback: function(result) {
            var pending = 0;
            for (var i in result) {
                var asyncOp = result[i];
                var panel = async.panels[asyncOp.AsyncId];
 
                // handle new operations
                if (!panel) {
                    // create panel container on demand
                    if (!async.panelcontainer) {
                        async.panelcontainer = $("<ul id='async'></ul>");
                        $(async.options.container).prepend(async.panelcontainer);
                    }
 
                    // create panel for this operation
                    panel = async.panels[asyncOp.AsyncId] = $("<li id='" + asyncOp.AsyncId + "'></li>");
                    async.panelcontainer.append(panel);
 
                    // add status and cancel button to panel
                    panel.append("<b>Searching [" + asyncOp.Criteria + "]:&nbsp;</b>");
                    panel.append("<span>Starting...</span>");
                    var cancelBtn = $("<input type='button' value='Cancel' asyncId='" + asyncOp.AsyncId + "' />").click(async.cancel);
                    panel.append(cancelBtn);
                }
 
                // check op status
                if (asyncOp.Completed) {
                    $("input", panel).val("Dismiss").unbind("click", async.cancel).click(async.dismiss).show();
                }
                else if (asyncOp.Cancelled) {
                    $("input", panel).val("Dismiss").unbind("click", async.cancel).click(async.dismiss).show();
                }
                else {
                    // report progress
                    pending++;
                }
                $("span", panel).html(asyncOp.Status);
            }
 
            // polling
            if (async.timeoutId) {
                clearTimeout(async.timeoutId);
                async.timeoutId = null;
            }
            if (pending > 0)
                async.timeoutId = setTimeout(async.poll, async.options.pollingInterval);
        },
 
        // polling timeout callback
        poll: function() {
            $.get(async.options.url, {}, async.callback, "json");
        }
    };

The final async.js JavaScript file should be added to the /Scripts folder and will look like this:

async.js

// create closure
(function($) {
    // "async" namespace private variables and functions
    var async = {
        // panels showing async operations
        panels: [],
 
        // cancel handler
        cancel: function() {
            var asyncId = $(this).attr("asyncId");
            var panel = async.panels[asyncId];
            if (panel) {
                $("input", panel).hide();
                $.post(async.options.url, { cancel: asyncId }, async.callback, "json");
            }
        },
 
        // dismiss handler
        dismiss: function() {
            var asyncId = $(this).attr("asyncId");
            var panel = async.panels[asyncId];
            if (panel) {
                $.post(async.options.url, { dismiss: asyncId }, function() {
                    $("#" + asyncId, async.panelcontainer).remove();
                    async.panels[asyncId] = null;
                }, "json");
            }
        },
 
        // ajax callback to handle async operations
        callback: function(result) {
            var pending = 0;
            for (var i in result) {
                var asyncOp = result[i];
                var panel = async.panels[asyncOp.AsyncId];
 
                // handle new operations
                if (!panel) {
                    // create panel container on demand
                    if (!async.panelcontainer) {
                        async.panelcontainer = $("<ul id='async'></ul>");
                        $(async.options.container).prepend(async.panelcontainer);
                    }
 
                    // create panel for this operation
                    panel = async.panels[asyncOp.AsyncId] = $("<li id='" + asyncOp.AsyncId + "'></li>");
                    async.panelcontainer.append(panel);
 
                    // add status and cancel button to panel
                    panel.append("<b>Searching [" + asyncOp.Criteria + "]:&nbsp;</b>");
                    panel.append("<span>Starting...</span>");
                    var cancelBtn = $("<input type='button' value='Cancel' asyncId='" + asyncOp.AsyncId + "' />").click(async.cancel);
                    panel.append(cancelBtn);
                }
 
                // check op status
                if (asyncOp.Completed) {
                    $("input", panel).val("Dismiss").unbind("click", async.cancel).click(async.dismiss).show();
                }
                else if (asyncOp.Cancelled) {
                    $("input", panel).val("Dismiss").unbind("click", async.cancel).click(async.dismiss).show();
                }
                else {
                    // report progress
                    pending++;
                }
                $("span", panel).html(asyncOp.Status);
            }
 
            // polling
            if (async.timeoutId) {
                clearTimeout(async.timeoutId);
                async.timeoutId = null;
            }
            if (pending > 0)
                async.timeoutId = setTimeout(async.poll, async.options.pollingInterval);
        },
 
        // polling timeout callback
        poll: function() {
            $.get(async.options.url, {}, async.callback, "json");
        }
    };
 
    // extend jQuery with "async" namespace
    $.async = {
        defaults: {
            container: "body",
            pollingInterval: 5000
        },
 
        // GET ajax request to update the status of running async operations
        get: function(url, options) {
            async.options = $.extend({ url: url }, $.async.defaults, options, async.options);
            async.poll();
        },
 
        // posts request to start a new async operation
        post: function(url, data, options) {
            async.options = $.extend({ url: url }, $.async.defaults, options, async.options);
            $.post(url, data, async.callback, "json");
        }
    };
 
})(jQuery);

Almost there

OK, we have the server and client sides ready to go. It’s now time to decide which pages will display or initiate our async search operations. We are going to use the main page in this case.

Let’s insert the following HTML block to /Views/Home/Index.aspx:

    <div id="container" class="async">
    </div>
 
    <h2>Search</h2>
    <input id="searchCriteria" type="text" maxlength="20" />
    <br />
    <input id="startSearch" type="button" value="Start Search" />
    <br />
 
    <script type="text/javascript">
        $(function() {
            $("#startSearch").click(function() {
                $.async.post("/Home/Search", { criteria: $("#searchCriteria").val() });
            });
 
            // refresh
            $.async.get("/Home/Search", { container: $("#container") });
        });
    </script>

Here we are defining a container to hold our search panels (styled with CSS class “async”), a couple of input elements to initiate new search operations, and a block of JavaScript code that gets called when the page is ready. We want to display all active searches every time we refresh this page, that’s why we need to invoke $.async.get(GetUrl, options) to synchronize with the server. The click handler to initiate new operations is also trivial, invoking $.async.post(PostUrl, data, options) and passing the “criteria” argument from the input element #searchCriteria. The application is almost ready!, we just need to take care of one final detail…

Creating the View to display Results.

Let’s review the HomeController for a second. You might have noticed the “Results” action, and how the operation status is updated with a link to this action when the search completed with some results.

HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using AsyncOp;
 
namespace MvcAsyncSearch.Controllers {
   [HandleError]
   public class HomeController : Controller {
      public ActionResult Index() {... }
      public ActionResult About() {... }
 
      [AcceptVerbs(HttpVerbs.Get)]
      public ActionResult Search() {...}
 
      [AcceptVerbs(HttpVerbs.Post)]
      public ActionResult Search(FormCollection collection) {...}
 
      [AcceptVerbs(HttpVerbs.Get)]
      public ActionResult Results(string asyncId) {
         Dictionary<string, AsyncOp.AsyncOp> operations = GetAsyncOps(HttpContext.Session);
         AsyncOp.AsyncOp op;
         if (operations.TryGetValue(asyncId, out op))
            return View(op.Results);
         else
            return View(new List<AsyncOp.AsyncOp.AsyncOpResult>());
      }
 
      private Dictionary<string, AsyncOp.AsyncOp> GetAsyncOps(HttpSessionStateBase session) {...}
 
      private void SetStatus(Dictionary<string, AsyncOp.AsyncOp> operations) {
         foreach(AsyncOp.AsyncOp op in operations.Values) {
            if (op.Error != null)
               op.Status = "Error: " + op.Error.Message;
            else {
               if (op.Completed || op.Cancelled) {
                  if (op.Completed)
                     op.Status = "DONE.";
                  else
                     op.Status = "Cancelled.";
                  if (op.Results.Count > 0)
                     op.Status += " <a href=\"" + Url.Action("Results", 
                        new { asyncId = op.AsyncId }) + "\">" + op.Results.Count + " results found.</a>";
                  else
                     op.Status += " No results found.";
               }
               else
                  op.Status = op.Results.Count + " results found ...";
            }
         }
      }
   }
}

This is the last scenario we needed to cover, and with MVC we just create a new View under /Views/Home/Result.aspx (the view name matching the action name is a nice MVC shortcut) as follows:

Results.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<IEnumerable<AsyncOp.AsyncOp.AsyncOpResult>>" %>
 
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
	Results
</asp:Content>
 
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
 
    <h2>Results</h2>
 
    <table>
        <thead>
            <tr>
                <th>Type</th>
                <th>Name</th>
            </tr>
        </thead>
        <tbody>
        <% foreach (AsyncOp.AsyncOp.AsyncOpResult r in ViewData.Model) { %>
            <tr>
                <td><%= r.Type %></td>
                <td><%= r.Name %></td>
            </tr>
        <% } %>
        </tbody>
    </table>
 
</asp:Content>

So here is how it goes. The plugin eventually receives a status with a link to the “Results”/{asyncId} action for each completed operation with results… and when clicked… the HomeController finds the operation in the Session cache and redirects to the Results.aspx view, passing the operations results as arguments.

Conclusion

In this article I wanted to show how to solve a common programming problem with a combination of modern technologies and some of the components I use in my projects. But I’m only scratching the surface… so, if you really want to learn how to exploit these technologies, I recommend some reading first [ASP.NET MVC, jQuery], and many butt hours with your debuggers on

CoNatural Blogging

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

Async Search with ASP.NET MVC + jQuery + CoNatural - Part II

Preparing the Solution

Preparing the Site

Site.Master

Managing Search Operations

JsonResult.cs

HomeController.cs

Implementing the client side with jQuery.

async.js

Almost there

Creating the View to display Results.

HomeController.cs

Results.aspx

Conclusion

My Links

Recent Posts

Archives

Categories

Meta/RSS

Recent Comments