Download the entire VS2010 solution here: http://download.codeplex.com/download?ProjectName=conatural&DownloadId=243415

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoNatural.Data;
using CoNatural.Data.SqlClient;
using TestConsole.Repositories.Sales;
using TestConsole.Repositories.Purchasing;

namespace TestConsole {
	class Program {
		static IConnection c;

		static void Main(string[] args) {
			// 5. Creating default connection to Sql Server
			c = new SqlClientConnection(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True");

			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 
				// Create new command to retrieve the last modified currency before a given date
				GetLastModifiedCurrencyBefore(DateTime.Today.AddYears(-5));
				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. Execute in sp mode
				var order = CreateOrder();
				c.ExecutionMode = System.Data.CommandType.StoredProcedure;
				InsertSalesOrder(order, true);

				// 20. Dynamic read currencies
				c.ExecutionMode = System.Data.CommandType.Text;
				DynamicReadCurrencies("B%");
				DynamicReadCurrencies("R%");

				// 21. Object binding
				BindObjects();
			}
			catch(Exception ex) {
				Console.WriteLine(ex.Message);
			}

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

		#region "Helpers"

		static void HandleErrors() {
			c.AfterExecute += new EventHandler<CommandExecutionEventArgs>((s, a) => {
				if (a.Context.Error != null) {
					Console.WriteLine(string.Format("!!! Error executing command {0}. {1}.", a.Context.CommandName, a.Context.Error.Message));

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

		static void TraceExecution() {
			c.BeforeExecute += new EventHandler<CommandExecutionEventArgs>((s, a) => {
				Console.WriteLine(string.Format("-> Executing command {0}.", a.Context.CommandName));
			});
		}

		static void PrintCurrencies(IEnumerable<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<ShipMethod> shipMethods) {
			shipMethods.All(sm => {
				Console.WriteLine(string.Format("Ship Method: [{0}] {1} modified on {2}", sm.ShipMethodID, sm.Name, sm.ModifiedDate));
				return true;
			});
		}

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

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

		static SalesOrderHeader CreateOrder() {
			// -----------------------------------------------------------------------------------------------------
			// Get any customer's info
			var cinfoScript = new Script(
@"
SELECT TOP 1
	C.[CustomerID]
,	C.[AccountNumber]   
,	CA.[AddressID]
,	CA.[AddressTypeID]
,	I.[ContactID] 
FROM
	[Sales].[Customer] C
	join [Sales].[CustomerAddress] CA on C.CustomerID = CA.CustomerID
	join [Sales].[Individual] I on C.[CustomerID] = I.[CustomerID]
"
);

			var cinfo = c.Execute<dynamic>(cinfoScript, DynamicReader.Materialize);

			// Find a ship method
			var shipmethod = new ShipMethodRepository(c).LoadAll().First();
			// Load special offers
			var specialOffers = new SpecialOfferProductRepository(c).LoadAll().ToList(); 
			// -----------------------------------------------------------------------------------------------------

			// create new order
			var order = new SalesOrderHeader {
				ContactID = cinfo.ContactID,
				CustomerID = cinfo.CustomerID,
				AccountNumber = cinfo.AccountNumber,
				BillToAddressID = cinfo.AddressID,
				ShipToAddressID = cinfo.AddressID,
				ShipMethodID = shipmethod.ShipMethodID,
				Comment = "Testing Insert Order",
				DueDate = DateTime.Today.AddDays(2),
				OrderDate = DateTime.Today,
				PurchaseOrderNumber = "0303030303",
				CreditCardApprovalCode = "XYZ",
				SalesOrderNumber = "ABC123",
				ModifiedDate = DateTime.Now,
				rowguid = Guid.NewGuid()
			};

			// create line item 1
			order.OrderDetails.Add(new 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",
				ModifiedDate = DateTime.Now,
 				rowguid = Guid.NewGuid()
			});

			// create line item 2
			order.OrderDetails.Add(new 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",
				ModifiedDate = DateTime.Now,
 				rowguid = Guid.NewGuid()
			});

			return order;
		}

		#endregion "Helpers"

		#region "Test"

		static void InsertCurrenciesWithRate() {
			int records;

			try {
				// insert new currency ABC
				records = c.Execute(new CurrencyRepository.CurrencyInsert { CurrencyCode = "ABC", Name = "Test Currency ABC", ModifiedDate = DateTime.Now });
				Console.WriteLine(records + " inserted.");
			}
			catch(Exception ex) {
				Console.WriteLine(ex.Message);
			}

			try {
				// insert new currency 123
				records = c.Execute(new CurrencyRepository.CurrencyInsert { CurrencyCode = "123", Name = "Test Currency 123", ModifiedDate = DateTime.Now });
				Console.WriteLine(records + " inserted.");
			}
			catch(Exception ex) {
				Console.WriteLine(ex.Message);
			}

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

		static void GetLastModifiedCurrencyBefore(DateTime givenDate) {
			// The first column of the first row in the resultset as T. Extra columns or rows are ignored.
			var code = c.ExecuteScalar<string>(new Script("SELECT TOP 1 [CurrencyCode] FROM [Sales].[Currency] WHERE [ModifiedDate] < @GivenDate ORDER BY [ModifiedDate] DESC", new { GivenDate = givenDate }));
			Console.WriteLine(string.Format("Last currency modified before {0} is {1}.", givenDate, code));
		}

		static void ReadCurrency(string code) {
			// read using repository
			var cr = new CurrencyRepository(c);
			var currency = cr.Load(new Currency { CurrencyCode = code });
			Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", currency.CurrencyCode, currency.Name, currency.ModifiedDate));

			// read using custom materializer
			currency = c.Execute<Currency>(new CurrencyRepository.CurrencySelect { CurrencyCode = code }, ctx => {
				var r = ctx.Record;
				var ccy = new 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));
		}

		static void ReadAllCurrencies() {
			// read using repository
			var cr = new CurrencyRepository(c);
			PrintCurrencies(cr.LoadAll());

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

		static void ReadDictionaryOfCurrencies() {
			// read using repository
			var cr = new CurrencyRepository(c);
			var currencies = cr.LoadAll().ToDictionary<Currency, string>(ccy => ccy.CurrencyCode);
			PrintCurrencies(currencies.Values);
		}

		static void LoadLookups() {
			var lookups = new Lookups();
			var cmd = new Script(
@"
SELECT [CurrencyCode], [Name], [ModifiedDate] FROM	[Sales].[Currency];
SELECT [ShipMethodID], [Name], [ShipBase], [ShipRate], [rowguid], [ModifiedDate] FROM [Purchasing].[ShipMethod];
");

			// read using custom materializer for lookups
			c.ExecuteReader<Lookups>(lookups, cmd, ctx => {
				var r = ctx.Record;
				if (ctx.ResultIndex == 1) {
					var ccy = new Currency();
					ccy.CurrencyCode = r.GetString(0) + " Custom";
					ccy.Name = r.GetString(1) + " Custom";
					ccy.ModifiedDate = r.GetDateTime(2);
					ctx.Target.Currencies[ccy.CurrencyCode] = ccy;
				}
				else if (ctx.ResultIndex == 2) {
					var sm = new ShipMethod();
					sm.ShipMethodID = r.GetInt32(0);
					sm.Name = r.GetString(1) + " Custom";
					sm.ModifiedDate = r.GetDateTime(5);
					ctx.Target.ShipMethods[sm.ShipMethodID] = sm;
				}
				return ctx.Target;
			});
			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, ctx => {
				if (ctx.ResultIndex == 1) {
					var ccy = ctx.Materialize<Currency>();
					ctx.Target.Currencies[ccy.CurrencyCode] = ccy;
				}
				else if (ctx.ResultIndex == 2) {
					var sm = ctx.Materialize<ShipMethod>();
					ctx.Target.ShipMethods[sm.ShipMethodID] = sm;
				}
				return ctx.Target;
			});
			PrintCurrencies(lookups.Currencies.Values);
			PrintShipMethods(lookups.ShipMethods.Values);
		}

		static void InsertBoundCurrenciesWithRate() {
			// let's do this inside a transaction
			using (var ts = new System.Transactions.TransactionScope()) {
				// delete existing XYZ rates and currency before we start 
				c.Execute(new Script("DELETE FROM [Sales].[CurrencyRate] WHERE 'XYZ' IN (FromCurrencyCode, ToCurrencyCode)"));
				new CurrencyRepository(c).Delete(new Currency { CurrencyCode = "XYZ" });

				// insert new currency XYZ
				int records = c.Execute(
					new CurrencyRepository.CurrencyInsert(),
					new 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 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 CurrencyRateRepository.CurrencyRateInsert(), rate);
				Console.WriteLine(string.Format("{0} inserted. New CurrencyRateID = {1}.", records, rate.CurrencyRateID));

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

		static void InsertMultipleBoundCurrencies() {
			var currencies = new Currency[] {
				new Currency { CurrencyCode = "CC1", Name = "Test Currency CC1", ModifiedDate = DateTime.Now },
				new Currency { CurrencyCode = "CC2", Name = "Test Currency CC2", ModifiedDate = DateTime.Now },
				new Currency { CurrencyCode = "CC3", Name = "Test Currency CC3", ModifiedDate = DateTime.Now }
			};
	
			
			// let's do this inside a transaction
			using (var ts = new System.Transactions.TransactionScope()) {
				// delete existing first
				c.Execute(
					new CurrencyRepository.CurrencyDelete(),
					currencies,
					false // when stored procedure mode is used, we can execute all three commands in a single batch
				);

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

				// commit changes
				ts.Complete();
			}

			GetLastModifiedCurrencyBefore(DateTime.Now);
		}

		static void InsertMultipleCurrencies() {
			var c1 = new CurrencyRepository.CurrencyDelete[] {
				new CurrencyRepository.CurrencyDelete { CurrencyCode = "CC4" },
				new CurrencyRepository.CurrencyDelete { CurrencyCode = "CC5" },
				new CurrencyRepository.CurrencyDelete { CurrencyCode = "CC6" }
			};

			var c2 = new CurrencyRepository.CurrencyInsert[] {
				new CurrencyRepository.CurrencyInsert { CurrencyCode = "CC4", Name = "Test Currency CC4", ModifiedDate = DateTime.Now },
				new CurrencyRepository.CurrencyInsert { CurrencyCode = "CC5", Name = "Test Currency CC5", ModifiedDate = DateTime.Now },
				new CurrencyRepository.CurrencyInsert { CurrencyCode = "CC6", Name = "Test Currency CC6", ModifiedDate = DateTime.Now }
			};

			// let's do this inside a transaction
			using (var ts = new System.Transactions.TransactionScope()) {
				// delete existing first
				c.Execute(c1);

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

				// commit changes
				ts.Complete();
			}

			GetLastModifiedCurrencyBefore(DateTime.Now);
		}

		static void InsertSalesOrder(SalesOrderHeader order, bool inBatch) {
			if (inBatch) {
				var script = c.ScriptBatch(
					new object[] { order }.Concat(order.OrderDetails),
					o => {
						if (o is SalesOrderHeader)
							return new Tuple<object, ICommand>(o, new SalesOrderHeaderRepository.SalesOrderHeaderInsert());
						else
							return new Tuple<object, ICommand>(o, new SalesOrderDetailRepository.SalesOrderDetailInsert());
					}
				);
				Console.WriteLine(script);
			}

			// create new order
			using (var ts = new System.Transactions.TransactionScope()) {
				// 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 SalesOrderHeader)
							return new Tuple<object, ICommand>(o, new SalesOrderHeaderRepository.SalesOrderHeaderInsert());
						else
							return new Tuple<object, ICommand>(o, new SalesOrderDetailRepository.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));
			}
		}

		static void DeployCommands() {
			var deployer = new CoNatural.Data.SqlServer.SqlServerCommandDeployer();
			deployer.Deploy(c,
				new SalesOrderHeaderRepository.SalesOrderHeaderInsert(),
				new SalesOrderDetailRepository.SalesOrderDetailInsert()
			);
		}

		static void DynamicReadCurrencies(string nameLikeThis) {
			Script s = new Script(@"
SELECT
	  [CurrencyCode]
	, [Name]
	, [ModifiedDate]
	, newid() as AnythingElse
FROM
	[Sales].[Currency]
where
	[Name] like @LikeThis", new { LikeThis = nameLikeThis });

			// read dynamic objects
			IEnumerable<dynamic> currencies = c.ExecuteReader<dynamic>(s, DynamicReader.Materialize);
			currencies.All(currency => {
				Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2} with {3}",
					currency.CurrencyCode, currency.Name, currency.ModifiedDate, currency.AnythingElse));
				return true;
			});
		}

		static void BindObjects() {
			var source = CreateOrder().OrderDetails[0];
			var target = new SalesOrderDetailRepository.SalesOrderDetailInsert();
			var factory = new CoNatural.Binders.PropertyBinderDelegateFactory();
			var binder = factory.CreateBinder(source, target.GetType());
			binder.Bind(source, target);
			var map = binder.Map(source);
			foreach (var m in map)
				Console.WriteLine(m.Source + " -> " + m.Target + ", " + m.IndirectReference);
		}

		static void ScriptNames() {
			foreach (var t in System.Reflection.Assembly.GetExecutingAssembly().GetTypes()) {
				if (t.GetInterface(typeof(ICommand).Name) != null) {
					ICommand cmd = (ICommand)Activator.CreateInstance(t);
					Console.WriteLine(c.DataBinder.DbProvider.ScriptName(cmd));
				}
			}
		}

		#endregion "Test"
	}
}

Last edited Sep 20, 2011 at 8:14 PM by conatural, version 5

Comments

No comments yet.