CoNatural.Data v1.7 - QuickStart Tutorial

1. Preparing the development environment.

  • Download CoNatural Components v1.7
  • Extract all assemblies to a local folder (LIB FOLDER)
  • Copy CoNatural.Data.VisualStudio.AddIn.AddIn file to your local Visual Studio's \AddIns folder (e.g. \My Documents\Visual Studio 2010\AddIns).
  • Make sure the AddIn file is pointing to CoNatural.Data.VisualStudio.AddIn.dll (int the LIB FOLDER)
  • The package contains binaries compiled with VS2008/.NET 3.5 (under /bin) and VS2010/.NET4.0 (under /bin/2010). Make sure you use the correct files. I'm using VS2010/.NET4.0 to build this tutorial.
  • I'm using the AdventureWorks database for Sql Server 2005. Make sure you download the .msi package and install this DB on your development server before moving to the next step.

2. Creating the console application

  • Open Visual Studio 2010. The AddIn should be loaded if you completed step 1.
  • Create a new console application project (I'm using C# and the default project name ConsoleApplication1).
  • Add a reference to CoNatural.Data.dll (under the LIB FOLDER)
  • Create a new folder under the project root named T4, and copy all T4 template files in the package.
  • Remove the custom tool that Visual Studio assigns to T4 files by default. The AddIn will take care of this.
  • Remove the language reference in all T4 files or the code generator will fail. (This is because we are using .NET4.0 - these files are prepared for .NET3.5)

3. Generating the model using T4 templates

  • Create a new folder /Model under the project root
  • Right-click on the new folder and select the option to Generate {Model} From Tables...
  • Select your connection string and follow the default wizard settings. You can select only a small subset of tables, but make sure you select Sales.Currency and Purchasing.ShipMethod.
  • Build the project

4. Generating basic CRUD commands using T4 templates

  • Create a new folder /DAL under the project root
  • Right-click on the new folder and select the option to Generate {CRUD-Commands} From Tables...
  • Follow the default settings. You can un-check the option to generate separate .sql command files if you prefer the SQL code embedded in your class files. You can select only a small subset of tables, but make sure you select Sales.Currency and Purchasing.ShipMethod.
  • Build the project

5. Creating a connection instance (to Sql Server)

  • Open Program.cs and add using statements to CoNatural.Data and CoNatural.Data.SqlClient
  • Create a new IConnection instance pointing to your AdventureWorks database. This is the same connection string used by the wizards in step 3 and 4.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoNatural.Data;
using CoNatural.Data.SqlClient;

namespace ConsoleApplication1 {
	class Program {
		static IConnection c;

		static void Main(string[] args) {
			// 5. Creating default connection to Sql Server
			c = new SqlClientConnection(@"YOUR CONNECTION STRING");

			try {
				// 6. Error handling. Error event is invoked by the framework when DAL exceptions occur 
				HandleErrors();
			
				// 7. Tracing execution. BeforeExecute event is invoked by the framework before executing commands.
				TraceExecution();

				// 8. Execute simple commands
				// InsertCurrenciesWithRate();
				
				// 9. ExecuteScalar 
				// GetLastModifiedCurrencyBefore(DateTime.Today.AddDays(-1));
				// GetLastModifiedCurrencyBefore(DateTime.Now);

				// 10. Materializing one record as a single instance of Model
				// ReadCurrency("ABC");

				// 11. Materializing multiple records as IEnumerable of Model
				// ReadAllCurrencies();

				// 12. Materializing multiple records as IDictionary of Model
				// ReadDictionaryOfCurrencies();

				// 13. Materializing multiple records inside target Model
				// LoadLookups();

                                                                // 14. Executing simple bound command
				//  InsertBoundCurrenciesWithRate();

				// 15. Multiple objects bound to a single command with optional batch execution
				//  InsertMultipleBoundCurrencies();

				// 16. Execute multiple commands with optional batch execution
				//  InsertMultipleCurrencies();

                                                                // 17. Object trees bound to commands with optional batch execution
				// InsertSalesOrder(CreateOrder(), false);

                                                                // 18. Deploy commands as stored procedures
				//DeployCommands();

				// 19. Use stored procedure execution mode. Execute multiple commands in a single batch (just one call to the database).
				// var order = CreateOrder();
				// c.ExecutionMode = System.Data.CommandType.StoredProcedure;
				// InsertSalesOrder(order, true); // inBatch = true
			}
			catch(Exception ex) {
				Console.WriteLine(ex.Message);
			}

			Console.WriteLine("DONE. Press Enter to end program.");
			Console.ReadLine();
		}
        }
}

6. Centralized error handling.

You can intercept DAL errors before they are raised to the application. The Error event is invoked by the framework when DAL exceptions occur. You have the option to log the errors and let the framework rethrow or just ignore them and continue with the normal execution.

static void HandleErrors() {
   c.Error += new EventHandler<CommandExecutionEventArgs>((s, a) =>
   {
	string command;

                if(a.Context != null) {
                         command = a.Context.Script;
	         if(a.Context is CommandExecutionContext)
		command = ((CommandExecutionContext)a.Context).DataCommand.Name;
                }

	Console.WriteLine(string.Format("!!! Error executing command {0}. {1}.", command, a.Exception.Message));

	// don't rethrow to continue tutorial
	a.IgnoreException = true;
   });
}

7. Tracing command execution.

You also have the option to intercept commands before they are executed. The BeforeExecute event is invoked by the framework when the underliying ADO.NET command is ready (with parameters and script). Here we use the event to log all the commands.

static void TraceExecution() {
   c.BeforeExecute += new EventHandler<CommandExecutionEventArgs>((s, a) =>
   {
	string command = a.Context.DbCommand.CommandText;
	if(a.Context is CommandExecutionContext)
		command = ((CommandExecutionContext)a.Context).DataCommand.Name;

	Console.WriteLine(string.Format("-> Executing command {0}.", command));

	// We can log parameters
	//a.Context.DbCommand.Parameters

	// ICommand and IDataCommand properties are available under CommandExecutionContext
   });
}

8. Execute simple commands

At this point, you are ready to run some CoNatural.Data commands. We are going to start playing with two of the most simple entities in our model (Currency and ShipMethod). Let's start by inserting new currencies and a conversion rate.

static void InsertCurrenciesWithRate() {
	// insert new currency ABC
	int records = c.Execute(new DAL.Sales.CurrencyInsert { CurrencyCode = "ABC", Name = "Test Currency ABC", ModifiedDate = DateTime.Now });
	Console.WriteLine(records + " inserted.");

	// insert new currency 123
	records = c.Execute(new DAL.Sales.CurrencyInsert { CurrencyCode = "123", Name = "Test Currency 123", ModifiedDate = DateTime.Now });
	Console.WriteLine(records + " inserted.");

	// insert new rate from ABC to 123 and display new rate id
	var cmd = new DAL.Sales.CurrencyRateInsert
	{
		FromCurrencyCode = "ABC",
		ToCurrencyCode = "123",
		ModifiedDate = DateTime.Now,
		CurrencyRateDate = DateTime.Now,
		AverageRate = 1.5M,
		EndOfDayRate = 1.5M
	};
	records = c.Execute(cmd);
	Console.WriteLine(string.Format("{0} inserted. New CurrencyRateID = {1}.", records, cmd.CurrencyRateID));
}

Check the console output to verify that all commands are executed correctly. If you try a second time, some DAL exceptions will be raised since the currencies are already in the DB. You can try to wrap the code inside a TransactionScope after a few tries to avoid generating too many rates.

9. ExecuteScalar

Sometimes you need to execute a command and return a scalar value. You can do this with output parameters (using the ParameterAttribute) or executing the command with the ExecuteScalar method. Let's try the second option:
  • Create new command to retrieve the last modified currency before a given date.
  1. Right-click on the /DAL folder and select New {Command}...
  2. Name it GetLastModifiedCurrencyBefore
  3. Open the newly created class file and copy the following code:

public class GetLastModifiedCurrencyBefore : ICommand {
	public GetLastModifiedCurrencyBefore() {}

	public string GetScript() {
		return 
@"
select CurrencyCode from Sales.Currency where ModifiedDate <= @GivenDate order by ModifiedDate desc
"; 
	}

	public DateTime GivenDate { get; set; }
}

Here we have a new command with one input parameter GivenDate. Note that we are selecting multiple rows ordered by ModifiedDate, but we only want to get the first field of the first row.

ExecuteScalar is a generic method, and in this case the type of the scalar field is string.

static void GetLastModifiedCurrencyBefore(DateTime givenDate) {
	// Gets the first column of the first row in the resultset. Extra columns or rows are ignored.
	var code = c.ExecuteScalar<string>(new DAL.GetLastModifiedCurrencyBefore { GivenDate = givenDate });
	Console.WriteLine(string.Format("Last currency modified before {0} is {1}.", givenDate, code));
}

10. Materializing one record from the currency table as a single instance of Currency (the Model)

A more usefull scenario is when you need to materialize an entire record into an instance of an object. There are also two ways to do this, one with output parameters (the default method used by the Select T4 template), and the other with materializers (there are actually three flavors of materializers that we are going to show next).

Let's do some prep work first. We are going to create a new command to select a single currency using the second method by changing a bit the command that was generated following the first method.
  • Clone CurrencySelect as CurrencySelect1 and modify it to return a recordset
  1. Copy the file /DAL/Sales/CurrencySelect.cs and paste it to the same folder as /DAL/Sales/CurrencySelect1.cs
  2. Open the new file and copy the following code:

using System;
using System.Data;
using CoNatural.Data;

namespace ConsoleApplication1.DAL.Sales {
	public class CurrencySelect1 : ICommand {
		public CurrencySelect1() {}

		public string GetScript() {
			return 
@"SELECT
	 [CurrencyCode],
	 [Name],
	 [ModifiedDate]
FROM
	[Sales].[Currency]
WHERE
	    [CurrencyCode] = @CurrencyCode
"; 
		}
		
		[Parameter(3)] public string CurrencyCode { get; set; }
	}
}

We just removed all output parameters and modified the command to return a recordset.

Now lets just copy the following code to Program.cs. These are just helper methods we are going to reuse later in our examples.

class CurrencyReader : IReader<Model.Sales.Currency> {
	#region IReader<Currency> Members
	public Model.Sales.Currency Materialize(ITypeMaterializerFactory factory, System.Data.IDataRecord record, int resultIndex) {
		var ccy = new Model.Sales.Currency();
		ccy.CurrencyCode = record.GetString(0) + " Reader";
		ccy.Name = record.GetString(1) + " Reader";
		ccy.ModifiedDate = record.GetDateTime(2);
		return ccy;
	}
	#endregion
}

static void PrintCurrencies(IEnumerable<Model.Sales.Currency> currencies) {
	currencies.All(currency =>
	{
		Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", currency.CurrencyCode, currency.Name, currency.ModifiedDate));
		return true;
	});
}

static void PrintShipMethods(IEnumerable<Model.Purchasing.ShipMethod> shipMethods) {
	shipMethods.All(sm =>
	{
		Console.WriteLine(string.Format("Ship Method: [{0}] {1} modified on {2}", sm.ShipMethodID, sm.Name, sm.ModifiedDate));
		return true;
	});
}

Now we can write our code to read a single currency given the currency code. We are using both methods (CurrencySelect = method1, CurrencySelect1 = method2). The second method materializes the record into an instance of class Currency defined in /Model/Sales/Currency.cs. Note the three flavors I mentioned before (using the default materializer, the IReader helper, and a custom materializer).

static void ReadCurrency(string code) {
	var cmd = new DAL.Sales.CurrencySelect { CurrencyCode = code };
	var cmd1 = new DAL.Sales.CurrencySelect1 { CurrencyCode = code };

	// read using output parameters
	c.Execute(cmd);
	Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", cmd.CurrencyCode, cmd.Name, cmd.ModifiedDate));

	// read using default materializer
	var currency = c.Execute<Model.Sales.Currency>(cmd1);
	Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", currency.CurrencyCode, currency.Name, currency.ModifiedDate));

	// read using reader
	currency = c.Execute<Model.Sales.Currency>(cmd1,  new CurrencyReader());
	Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", currency.CurrencyCode, currency.Name, currency.ModifiedDate));

	// read using custom materializer
	currency = c.Execute<Model.Sales.Currency>(cmd1, (f, r, i) => {
		var ccy = new Model.Sales.Currency();
		ccy.CurrencyCode = r.GetString(0) + " Custom";
		ccy.Name = r.GetString(1) + " Custom";
		ccy.ModifiedDate = r.GetDateTime(2);
		return ccy;
	});
	Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", currency.CurrencyCode, currency.Name, currency.ModifiedDate));
}

11. Materializing multiple records as IEnumerable of Currency

In case you need to materialize multiple records into a list of objects, change the execution method to ExecuteReader. Again you have the three flavors available as shown below:

static void ReadAllCurrencies() {
	var cmd = new DAL.Sales.CurrencySelectAll();
	IEnumerable<Model.Sales.Currency> currencies;

	// read using default materializer
	currencies = c.ExecuteReader<Model.Sales.Currency>(cmd);
	PrintCurrencies(currencies);

	// read using reader
	currencies = c.ExecuteReader<Model.Sales.Currency>(cmd, new CurrencyReader());
	PrintCurrencies(currencies);

	// read using custom materializer
	currencies = c.ExecuteReader<Model.Sales.Currency>(cmd, (f, r, i) =>
	{
		var ccy = new Model.Sales.Currency();
		ccy.CurrencyCode = r.GetString(0) + " Custom";
		ccy.Name = r.GetString(1) + " Custom";
		ccy.ModifiedDate = r.GetDateTime(2);
		return ccy;
	});
	PrintCurrencies(currencies);
}

12. Materializing multiple records as IDictionary of Currency

If you want to build a dictionary indexed by one of the fields in the recordset, there is an overload of ExecuteReader where you pass two generic parameters (K, T) and the index of the key field. Note that we are still using the same command and IReader helper class.

static void ReadDictionaryOfCurrencies() {
	var cmd = new DAL.Sales.CurrencySelectAll();
	IDictionary<string, Model.Sales.Currency> currencies;

	// read using default materializer
	currencies = c.ExecuteReader<string, Model.Sales.Currency>(cmd, 0);
	PrintCurrencies(currencies.Values);

	// read using reader
	currencies = c.ExecuteReader<string, Model.Sales.Currency>(cmd, 0, new CurrencyReader());
	PrintCurrencies(currencies.Values);

	// read using custom materializer
	currencies = c.ExecuteReader<string, Model.Sales.Currency>(cmd, 0, (f, r, i) =>
	{
		var ccy = new Model.Sales.Currency();
		ccy.CurrencyCode = r.GetString(0) + " Custom";
		ccy.Name = r.GetString(1) + " Custom";
		ccy.ModifiedDate = r.GetDateTime(2);
		return ccy;
	});
	PrintCurrencies(currencies.Values);
}

13. Materializing multiple records inside target object

Finally, there are cases when you already have an object, and you need to fill it with data (think about on-demand or lazy loading). You can use another overload of ExecuteReader where the first parameter is the target object. Let's say we have a Lookups class that holds all the static lookup lists in our system, and we want to load them at once. In this case we will only load a list of currencies and ship methods for simplicity.
  • Create new command to retrieve lookups
  1. Right-click on the /DAL folder and select New {Command}...
  2. Name it LoadLookups
  3. Open the newly created class file and copy the following code:

using System;
using System.Data;
using CoNatural.Data;

namespace ConsoleApplication1.DAL {
	public class LoadLookups : ICommand {
		public LoadLookups() {}

		public string GetScript() {
			return
@"
SELECT top 3
	  [CurrencyCode]
	, [Name]
	, [ModifiedDate]
FROM
	[Sales].[Currency]

SELECT top 3
	  [ShipMethodID]
	, [Name]
	, [ShipBase]
	, [ShipRate]
	, [rowguid]
	, [ModifiedDate]
FROM
	[Purchasing].[ShipMethod]
"; 
		}
		
	}
}

Note that in this case we are returning two recordsets (limited only to 3 records for simplicity), the first with the list of currencies and the second with the ship methods.

Let's define the model and a helper class implementing ITargetReader<Lookups>. The reader is using some custom code to materialize currencies and ship methods inside the target Lookups instance.

class Lookups {
	public Lookups() {
		Currencies = new Dictionary<string, Model.Sales.Currency>();
		ShipMethods = new Dictionary<int, Model.Purchasing.ShipMethod>();
	}

	public Dictionary<string, Model.Sales.Currency> Currencies { get; private set; }
	public Dictionary<int, Model.Purchasing.ShipMethod> ShipMethods { get; private set; }
}

class LookupsReader : ITargetReader<Lookups> {
	#region ITargetReader<Lookups> Members
	public void Materialize(ITypeMaterializerFactory factory, Lookups target, System.Data.IDataRecord record, int resultIndex) {
		if(resultIndex == 1) {
			var ccy = new Model.Sales.Currency();
			ccy.CurrencyCode = record.GetString(0) + " Reader";
			ccy.Name = record.GetString(1) + " Reader";
			ccy.ModifiedDate = record.GetDateTime(2);
			target.Currencies[ccy.CurrencyCode] = ccy;
		}
		else if(resultIndex == 2) {
			var sm = new Model.Purchasing.ShipMethod();
			sm.ShipMethodID = record.GetInt32(0);
			sm.Name = record.GetString(1) + " Reader";
			sm.ModifiedDate = record.GetDateTime(5);
			target.ShipMethods[sm.ShipMethodID] = sm;
		}
	}
	#endregion
}

Now we can instantiate our target and load it using the ITargetReader flavor, or with a custom materializer. We don't support a default target materializer at this point (maybe in the next release ;-) but you can use a mixed approach (as shown below) where the default materializer is used to read the internal types (Currency and ShipMethod).

static void LoadLookups() {
	var cmd = new DAL.LoadLookups();

	// read using reader
	var lookups = new Lookups();
	c.ExecuteReader<Lookups>(lookups, cmd, new LookupsReader());
	PrintCurrencies(lookups.Currencies.Values);
	PrintShipMethods(lookups.ShipMethods.Values);

	// read using custom materializer
	lookups = new Lookups();
	c.ExecuteReader<Lookups>(lookups, cmd, (f, t, r, i) =>
	{
		if(i == 1) {
			var ccy = new Model.Sales.Currency();
			ccy.CurrencyCode = r.GetString(0) + " Custom";
			ccy.Name = r.GetString(1) + " Custom";
			ccy.ModifiedDate = r.GetDateTime(2);
			t.Currencies[ccy.CurrencyCode] = ccy;
		}
		else if(i == 2) {
			var sm = new Model.Purchasing.ShipMethod();
			sm.ShipMethodID = r.GetInt32(0);
			sm.Name = r.GetString(1) + " Custom";
			sm.ModifiedDate = r.GetDateTime(5);
			t.ShipMethods[sm.ShipMethodID] = sm;
		}
	});
	PrintCurrencies(lookups.Currencies.Values);
	PrintShipMethods(lookups.ShipMethods.Values);

                // read using custom materializer for lookups and default materializers for internal types
	lookups = new Lookups();
	c.ExecuteReader<Lookups>(lookups, cmd, (f, t, r, i) =>
	{
		if(i == 1) {
			var ccy = f.Materialize<Model.Sales.Currency, DAL.LoadLookups>(r);
			t.Currencies[ccy.CurrencyCode] = ccy;
		}
		else if(i == 2) {
			var sm = f.Materialize<Model.Purchasing.ShipMethod, DAL.LoadLookups>(r);
			t.ShipMethods[sm.ShipMethodID] = sm;
		}
	});
	PrintCurrencies(lookups.Currencies.Values);
	PrintShipMethods(lookups.ShipMethods.Values);
}

This mechanism allows you to read very complex object structures with just a few database calls. Note that you have total control of your queries and the amount of data that's being transfered.

14. Executing simple bound command

Since real applications deal with object models, we provide the option to bind objects to commands (mapping object properties by name/type to command parameters (in/out)).

We are going to insert currencies and rates bound to CurrencyInsert/CurrencyRateInsert commands inside a transaction:
  • Add a reference to System.Transactions
  • Change the error handler method to re-throw errors (remove the line that sets a.IgnoreException = true)

static void InsertBoundCurrenciesWithRate() {
	// let's do this inside a transaction
	using(var ts = new System.Transactions.TransactionScope()) {
		// insert new currency XYZ
		int records = c.Execute(
			new DAL.Sales.CurrencyInsert(),
			new Model.Sales.Currency { CurrencyCode = "XYZ", Name = "Test Currency XYZ", ModifiedDate = DateTime.Now }
		);
		Console.WriteLine(records + " inserted.");

		// create new currency rate from XYZ to 123
		var rate = new Model.Sales.CurrencyRate
		{
			FromCurrencyCode = "XYZ",
			ToCurrencyCode = "123",
			ModifiedDate = DateTime.Now,
			CurrencyRateDate = DateTime.Now,
			AverageRate = 1.2M,
			EndOfDayRate = 1.2M
		};

		// insert new rate (note the output parameter updating the property CurrencyRateID)
		records = c.Execute(new DAL.Sales.CurrencyRateInsert(), rate);
		Console.WriteLine(string.Format("{0} inserted. New CurrencyRateID = {1}.", records, rate.CurrencyRateID));

		// commit changes
		ts.Complete();
	}
}

Notice that in this case we are creating the commands without setting any property values. The default binding mechanism is taking care of the mappings for us. We can provide a type mapper to customize these mappings, but this is a more advanced feature we are not covering here.

15. Multiple objects bound to a single command with optional batch execution

We can also insert multiple currencies bound to a single CurrencyInsert command.

static void InsertMultipleBoundCurrencies() {
	// let's do this inside a transaction
	using(var ts = new System.Transactions.TransactionScope()) {
		var currencies = new Model.Sales.Currency[] {
			new Model.Sales.Currency { CurrencyCode = "CC1", Name = "Test Currency CC1", ModifiedDate = DateTime.Now },
			new Model.Sales.Currency { CurrencyCode = "CC2", Name = "Test Currency CC2", ModifiedDate = DateTime.Now },
			new Model.Sales.Currency { CurrencyCode = "CC3", Name = "Test Currency CC3", ModifiedDate = DateTime.Now }
		};

		// insert all currencies
		c.Execute(
			new DAL.Sales.CurrencyInsert(),
			currencies,
			false // when stored procedure mode is used, we can execute all three commands in a single batch
		);

		// commit changes
		ts.Complete();
	}
}

16. Execute multiple commands with optional batch execution

But this is not limited to bound commands. We can execute multiple regular commands as shown below. Look at the console trace to verify the three commands being executed.
We will explore the batch execution mode later where we make only one call to the database to execute multiple commands (The trace will show just a single SQL script invoking 3 stored procedures).

static void InsertMultipleCurrencies() {
	// let's do this inside a transaction
	using(var ts = new System.Transactions.TransactionScope()) {
		var currencies = new DAL.Sales.CurrencyInsert[] {
			new DAL.Sales.CurrencyInsert { CurrencyCode = "CC4", Name = "Test Currency CC4", ModifiedDate = DateTime.Now },
			new DAL.Sales.CurrencyInsert { CurrencyCode = "CC5", Name = "Test Currency CC5", ModifiedDate = DateTime.Now },
			new DAL.Sales.CurrencyInsert { CurrencyCode = "CC6", Name = "Test Currency CC6", ModifiedDate = DateTime.Now }
		};

		// insert all currencies
		c.Execute(currencies);

		// commit changes
		ts.Complete();
	}
}

17. Object trees bound to commands with optional batch execution

In this section we are going to insert a new sales order with two line details, but we need to do some prep work first.
  • To create a new sales order from scratch, we need to load some information about the customer ordering the goods. (In a real application all these data elements are usually loaded and presented in the GUI).
  1. Right-click on the /DAL folder and select New {Command}...
  2. Name it GetAnyCustomerInfo
  3. Open the newly created class file and copy the following code:

using System;
using System.Data;
using CoNatural.Data;

namespace ConsoleApplication1.DAL {
	public class GetAnyCustomerInfo : ICommand {
		public GetAnyCustomerInfo() { }

		public string GetScript() {
			return
@"SELECT TOP 1
		@ContactID = [ContactID]
	,	@CustomerID = C.[CustomerID]
	,	@AddressID = [AddressID]
	,  @AccountNumber = [AccountNumber]
FROM
	[Sales].[Customer] C
	join [Sales].[CustomerAddress] A on C.CustomerID = A.CustomerID
	join [Sales].[StoreContact] SC on C.CustomerID = SC.CustomerID
";
		}

		[Parameter(ParameterDirection.Output)]
		public int ContactID { get; set; }

		[Parameter(ParameterDirection.Output)]
		public int CustomerID { get; set; }

		[Parameter(ParameterDirection.Output, 100)]
		public string AccountNumber { get; set; }

		[Parameter(ParameterDirection.Output)]
		public int AddressID { get; set; }
	}
}
  • We also need to modify a bit the model since the default T4 templates are very trivial and don't consider object relationships. In this case we are going to:
  • Replace the SalesOrderID property in /Model/Sales/SalesOrderDetails.cs with a reference to the SalesOrderHeader

namespace ConsoleApplication1.Model.Sales {
	public class SalesOrderDetail {
		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; }

                // the order header
		public SalesOrderHeader SalesOrder { get; set; }
	}
}
  • Add a new property to /Model/Sales/SalesOrderHeader.cs with a list of SalesOrderDetails

using System;
using System.Collections.Generic;

namespace ConsoleApplication1.Model.Sales {
	public class SalesOrderHeader {
		public SalesOrderHeader() {
			OrderDetails = new List<SalesOrderDetail>();
		}

		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 CustomerID { get; set; }
		public Int32 ContactID { 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; }
		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; }

		// order details
		public List<SalesOrderDetail> OrderDetails { get; private set; }
	}
}

We are now ready to write hour helper method in charge of creating a new sales order. Again, this is just a dirty way to create an order for this tutorial.

static Model.Sales.SalesOrderHeader CreateOrder() {
	// -----------------------------------------------------------------------------------------------------
	// Get any customer's info
	var info = new DAL.GetAnyCustomerInfo();
	c.Execute(info);
	// Find a ship method
	var shipmethod = c.Execute<Model.Purchasing.ShipMethod>(new DAL.Purchasing.ShipMethodSelectAll());
	// Load special offers
	var specialOffers = c.ExecuteReader<Model.Sales.SpecialOfferProduct>(new DAL.Sales.SpecialOfferProductSelectAll());  
	// -----------------------------------------------------------------------------------------------------
  
                // create new order
	var order = new Model.Sales.SalesOrderHeader {
		ContactID = info.ContactID,
		CustomerID = info.CustomerID,
		AccountNumber = info.AccountNumber,
		BillToAddressID = info.AddressID,
		ShipToAddressID = info.AddressID,
		ShipMethodID = shipmethod.ShipMethodID, 
		Comment = "Testing Insert Order",
 		DueDate = DateTime.Today.AddDays(2),
		OrderDate = DateTime.Today,
		PurchaseOrderNumber = "0303030303",
		CreditCardApprovalCode = "XYZ"
	};

	// create line item 1
	order.OrderDetails.Add(new Model.Sales.SalesOrderDetail {
		SalesOrder = order, // we don't know the SalesOrderID yet
		UnitPrice = 1000.00M,
		OrderQty = 1,
		ProductID = specialOffers.ElementAt(0).ProductID,
		SpecialOfferID = specialOffers.ElementAt(0).SpecialOfferID,
		CarrierTrackingNumber = "09832-198230019823"
	});

	// create line item 2
	order.OrderDetails.Add(new Model.Sales.SalesOrderDetail {
		SalesOrder = order, // we don't know the SalesOrderID yet
		UnitPrice = 2000.00M,
		OrderQty = 1,
		ProductID = specialOffers.ElementAt(1).ProductID,
		SpecialOfferID = specialOffers.ElementAt(1).SpecialOfferID,
		CarrierTrackingNumber = "09832-198230019823"
	});

	return order;
}

We are going to use two of the insert commands generated at the beginning of this tutorial, but we have to make some adjustments first (again, the T4 templates are very basic).
  • Modify /DAL/Sales/SalesOrderHeaderInsert:
    • Remove SalesOrderNumber and TotalDue since these are calculated columns
    • Set rowguid = newid() and ModifiedDate = getdate() instead of using input parameters when inserting new orders

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]
	, [ContactID]
	, [SalesPersonID]
	, [TerritoryID]
	, [BillToAddressID]
	, [ShipToAddressID]
	, [ShipMethodID]
	, [CreditCardID]
	, [CreditCardApprovalCode]
	, [CurrencyRateID]
	, [SubTotal]
	, [TaxAmt]
	, [Freight]
	, [Comment]
	, [rowguid]
	, [ModifiedDate]
)
VALUES (
	  @RevisionNumber
	, @OrderDate
	, @DueDate
	, @ShipDate
	, @Status
	, @OnlineOrderFlag
	, @PurchaseOrderNumber
	, @AccountNumber
	, @CustomerID
	, @ContactID
	, @SalesPersonID
	, @TerritoryID
	, @BillToAddressID
	, @ShipToAddressID
	, @ShipMethodID
	, @CreditCardID
	, @CreditCardApprovalCode
	, @CurrencyRateID
	, @SubTotal
	, @TaxAmt
	, @Freight
	, @Comment
	, newid()
	, getdate()
)

SET @SalesOrderID = SCOPE_IDENTITY();
"; 
		}
		
		[Parameter(ParameterDirection.Output)] 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; }
		[Parameter(25)] public string PurchaseOrderNumber { get; set; }
		[Parameter(15)] public string AccountNumber { get; set; }
		public Int32 CustomerID { get; set; }
		public Int32 ContactID { 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; }
	}
}
  • Modify /DAL/Sales/SalesOrderDetailInsert:
    • Remove LineTotal since this is a calculated column
    • Set rowguid = newid() and ModifiedDate = getdate() instead of using input parameters when inserting new order details

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(ParameterDirection.Output)] public Int32 SalesOrderDetailID { 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; }
	}
}

Finally, let's insert our new sales order. The object tree will generate three records. Note the indirect mapping from SalesOrderDetail to SalesOrder.SalesOrderID. This is needed by the type mapper to create a link between this property and the SalesOrderID parameter when inserting order line details since the model doesn't have a matching property (we modified the model previously with a reference to the SalesOrderHeader, remember?).

static void InsertSalesOrder(Model.Sales.SalesOrderHeader order, bool inBatch) {
 	// create new order
	using(var ts = new System.Transactions.TransactionScope()) {
		// map indirect reference to SalesOrderID
		c.DefaultTypeMapperFactory.Map<Model.Sales.SalesOrderDetail>(d => d.SalesOrder.SalesOrderID);

		// insert order
		c.Execute(
			// insert order header + all line details
			new object[] { order }.Concat(order.OrderDetails), 
			o => {
				// In this case we are only inserting, but in most scenarios here we check the 
				// object's state to decide if we are inserting, updating, or deleting it.
				// I like to use a static structure to map object state to commands.
				if(o is Model.Sales.SalesOrderHeader)
					return new Tuple<object, ICommand>(o, new DAL.Sales.SalesOrderHeaderInsert());
				else
					return new Tuple<object, ICommand>(o, new DAL.Sales.SalesOrderDetailInsert());
			}, 
			inBatch
		);
		ts.Complete();

		Console.WriteLine(string.Format("Sales order inserted. SalesOrderId={0}, Line1Id= {1}, Line2Id={2}",
			order.SalesOrderID, order.OrderDetails[0].SalesOrderDetailID, order.OrderDetails[1].SalesOrderDetailID));  
	}
}

This is a very simple example, but the idea is to allow the user to create very complex structures, traverse the object graph in any order, and bind the resulting objects to any command according to their persistence state.

18. Deploy commands as stored procedures

Some users prefer stored procedures over sql scripts. Stored procedures provide better security and performance, but are harder to maintain.
Once the application is ready, you have the option to deploy CoNatural commands as stored procedures, and with a simple switch all commands will be executed in SP mode. This mode also allows the use of batch scripts to execute multiple commands in a single database trip.
  • Add a reference to CoNatural.Data.SqlServer.dll (under the LIB FOLDER) - Here we have a concrete implementation of ICommandDeployer for Sql Server.
  • Execute the following code to deploy the commands that take care of inserting orders.

static void DeployCommands() {
	var deployer = new CoNatural.Data.SqlServer.SqlServerCommandDeployer();
	deployer.Deploy(c, 
		new DAL.Sales.SalesOrderHeaderInsert(),
		new DAL.Sales.SalesOrderDetailInsert()
	);
}
  • Make sure you have these two new stored procedures: dbo.ConsoleApplication1_DAL_Sales_SalesOrderHeaderInsert, dbo.ConsoleApplication1_DAL_Sales_SalesOrderDetailInsert

We used the default naming convention, but users can customize the naming of stored procedures following their own rules. Users can also deploy entire assemblies holding commands and setup the execution mode (and security) according to command names or db schemas. You can find more info at http://blogs.conatural.com.

Notice that the connection used to deploy commands must have enough rights to create stored procedures in the underlying database system.

19. Use stored procedure execution mode. Execute multiple commands in a single batch (just one call to the database).

Just change the execution mode to StoredProcedure, and make sure you set the inBatch parameter = true. Take a look at the console to see the trace of the generated sql script before execution.

var order = CreateOrder();
c.ExecutionMode = System.Data.CommandType.StoredProcedure;
InsertSalesOrder(order, true); // inBatch = true

Last edited Mar 21, 2011 at 8:48 PM by conatural, version 1

Comments

No comments yet.