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(@"Data Source=MICONBRSW03\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.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. Execute in sp mode
				var order = CreateOrder();
				c.ExecutionMode = System.Data.CommandType.StoredProcedure;
				InsertSalesOrder(order, true);

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

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

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


		// CHANGED
		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;
				}
			});
		}
		// CHANGED


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


		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));
		}

		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 DAL.GetLastModifiedCurrencyBefore { GivenDate = givenDate });
			Console.WriteLine(string.Format("Last currency modified before {0} is {1}.", givenDate, code));
		}

		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;
			});
		}

		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 custom materializer
			// CHANGED
			currency = c.Execute<Model.Sales.Currency>(cmd1, ctx => {
				var r = ctx.Record;
				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;
			});
			// CHANGED
			Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", currency.CurrencyCode, currency.Name, currency.ModifiedDate));
		}

		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 custom materializer
			// CHANGED
			currencies = c.ExecuteReader<Model.Sales.Currency>(cmd, ctx =>
			{
				var r = ctx.Record;
				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;
			});
			// CHANGED
			PrintCurrencies(currencies);
		}

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

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

			// read using custom materializer
			// CHANGED
			currencies = c.ExecuteReader<Model.Sales.Currency>(cmd, ctx =>
			{
				var r = ctx.Record;
				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;
			}).ToDictionary<Model.Sales.Currency, string>(ccy => ccy.CurrencyCode);
			// CHANGED
			PrintCurrencies(currencies.Values);
		}

		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; }
		}


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

			// CHANGED
			c.ExecuteReader<Lookups>(lookups, cmd, ctx =>
			{
				var r = ctx.Record;
				if(ctx.ResultIndex == 1) {
					var ccy = new Model.Sales.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 Model.Purchasing.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;
			});
			// CHANGED
			PrintCurrencies(lookups.Currencies.Values);
			PrintShipMethods(lookups.ShipMethods.Values);

			// read using custom materializer for lookups and default materializers for internal types
			lookups = new Lookups();
			// CHANGED
			c.ExecuteReader<Lookups>(lookups, cmd, ctx =>
			{
				if(ctx.ResultIndex == 1) {
					var ccy = ctx.Materialize<Model.Sales.Currency>();
					ctx.Target.Currencies[ccy.CurrencyCode] = ccy;
				}
				else if(ctx.ResultIndex == 2) {
					var sm = ctx.Materialize<Model.Purchasing.ShipMethod>();
					ctx.Target.ShipMethods[sm.ShipMethodID] = sm;
				}
				return ctx.Target;
			});
			// CHANGED
			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()) {
				// 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();
			}
		}

		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();
			}
		}

		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();
			}
		}

		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;
		}

		static void InsertSalesOrder(Model.Sales.SalesOrderHeader order, bool inBatch) {
 			// 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 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));  
			}
		}

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

		// CHANGED
		static void DynamicReadCurrencies(string nameLikeThis) {
			//dynamic p = new System.Dynamic.ExpandoObject();
			//p.LikeThis = 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;
			});
		}
		// CHANGED

		static void BindObjects() {
			var source = CreateOrder();
			var target = new DAL.Sales.SalesOrderDetailInsert();
			var factory = new CoNatural.Binders.PropertyBinderFactory();
			string code = factory.GetCode(source.OrderDetails[0].GetType(), target.GetType(), false);
			Console.WriteLine(code);
			code = factory.GetCode(source.OrderDetails[0].GetType(), target.GetType(), true);
			Console.WriteLine(code);
		}
	}
}

Last edited Mar 21, 2011 at 9:46 PM by conatural, version 2

Comments

No comments yet.