This project is read-only.

Knak.Data Tutorial

Reader Mapping Convention

  1. MATCH fields in the data reader to public writable properties in the target type
  2. WHERE the names are exactly equal
  3. AND the types are compatible (same type or typecasting defined from S to T)

The mapper will try to match all fields in the data record to target properties.

Parameter Mapping Convention

When executing SQL commands with parameters, the convention is to:
  1. CREATE and feed input parameters from each public readable property in the command class or type holding parameters. Input properties can be annotated with InputAttribute, but they are considered for input by default.
  2. CREATE output parameters from each "public writable property" in the command class that is annotated with OutputAttribute.
  3. For input/output parameters, the property must have public setter and getter, and must be annotated with InputAttribute and OutputAttribute.

Setup

Before we do anything with Knak.Data, we must create a connection. We are going to use AdventureWorks in all examples.

IConnection c = new SqlClientConnection(
   @"Data Source=localhost\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True");

// By default the connection is opened and closed after executing each command, but we can control
// that manually to improve performance when executing multiple commands.
c.AutoClose = false; 

// Error event is invoked by the framework when DAL exceptions occur 
HandleErrors(c);

// BeforeExecute event is invoked by the framework before executing commands.
TraceExecution(c);

And let's define some helpers to control the flow of exceptions and tracing.

static void HandleErrors(IConnection c) {
	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 test
			a.IgnoreErrors = true;
		}
	});
}

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

Loading the Model

Ok, so let's load some currencies.

public class Currency {
	[Required]
	[StringLength(3)]
	public string CurrencyCode { get; set; }
	[Required]
	[StringLength(50)]
	public string Name { get; set; }
	public DateTime ModifiedDate { get; set; }
}

var currencies = c.ExecuteReader<Currency>(
   @"SELECT [CurrencyCode], [Name], [ModifiedDate] FROM [Sales].[Currency]");

foreach (var currency in currencies)
	Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", 
            currency.CurrencyCode, currency.Name, currency.ModifiedDate));
All three currency fields are mapped and a currency list is materialized. To read a single currency, we can do this:

var currency = c.Execute<Currency>(
	@"SELECT [CurrencyCode], [Name], [ModifiedDate] FROM [Sales].[Currency] 
	WHERE [CurrencyCode] = @CurrencyCode", new { CurrencyCode = "USD" });

Console.WriteLine(string.Format("Currency: [{0}] {1} modified on {2}", 
	currency.CurrencyCode, currency.Name, currency.ModifiedDate));
Here not only the reader is mapped, but an input parameter is created with the currency code we want to load.

CUDs

We can also execute scripts to create, update, and delete records. In this example, instead of passing the script directly to the execute methods, we are going to define Knak Commands as types that not only encapsulate the script, but also the input/output parameters and possibly other operations that might be needed in complex scenarios.

public class CurrencyInsert : ICommand {
	public string GetScript() {
		return
@"INSERT INTO [Sales].[Currency] (
[CurrencyCode]
,	[Name]
,	[ModifiedDate]
)
VALUES (
@CurrencyCode
,	@Name
,	@ModifiedDate)";
	}

	[Parameter(3)]
	public string CurrencyCode { get; set; }
	[Parameter(50)]
	public string Name { get; set; }
	public DateTime ModifiedDate { get; set; }
}

public class CurrencyDelete : ICommand {
	public string GetScript() {
		return @"DELETE FROM [Sales].[Currency] WHERE [CurrencyCode] = @CurrencyCode";
	}

	[Parameter(3)]
	public string CurrencyCode { get; set; }
}

We are using ParameterAttribute in our commands to specify the size of the nvarchar parameters. There are other options to define binary size, decimal precision and scale, and when nvarchar fields are nullable. In combination with InputAttribute and OutputAttribute, we have enough flexibility to cover most use cases.

Inserting and deleting currencies is as simple as:

c.Execute(new CurrencyInsert { 
   CurrencyCode = "ABC", Name = "Test Currency ABC", ModifiedDate = DateTime.Now 
});
c.Execute(new CurrencyInsert { 
   CurrencyCode = "123", Name = "Test Currency 123", ModifiedDate = DateTime.Now 
});

c.Execute(new CurrencyDelete { CurrencyCode = "ABC" });
c.Execute(new CurrencyDelete { CurrencyCode = "123" });

ICommand vs. Scripts

Although direct scripts are simpler and faster to write, we recommend the use of commands for the following reasons:
  • Commands can be organized in namespaces, and because they are just .NET types, you can dedicate special data libraries just for commands.
  • Commands can pack more functionality, and parameters are clearly defined.
  • Commands support output parameters.
  • Command mappings are cached.
  • Commands can be deployed and executed as stored procedures (and in batch).

Repository Pattern

It's also a good practice to isolate the business logic from specific data operations. We define a repository interface IRepository<T, K> following the repository pattern as another way to organize and encapsulate commands in Knak.

This example shows how to insert currency rates using a repository of CurrencyRate:

public class CurrencyRate {
	public Int32 CurrencyRateID { get; set; }
	public DateTime CurrencyRateDate { get; set; }
	[Required]
	[StringLength(3)]
	public string FromCurrencyCode { get; set; }
	[Required]
	[StringLength(3)]
	public string ToCurrencyCode { get; set; }
	public Decimal AverageRate { get; set; }
	public Decimal EndOfDayRate { get; set; }
	public DateTime ModifiedDate { get; set; }
}

public class CurrencyRateRepository : IRepository<CurrencyRate, int> {
	public CurrencyRateRepository(IConnection connection) {
		Connection = connection;
	}

	public IConnection Connection { get; private set; }

	#region "IRepository"

	public IEnumerable<CurrencyRate> LoadAll() {
		return Connection.ExecuteReader<CurrencyRate>(new CurrencyRateSelectAll());
	}

	public CurrencyRate Load(int key) {
		return Connection.Execute<CurrencyRate>(new CurrencyRateSelect { CurrencyRateID = key });
	}

	public int Save(CurrencyRate instance) {
		if (instance.CurrencyRateID == 0)
			return Connection.MapExecute(new CurrencyRateInsert(), instance);
		else
			return Connection.MapExecute(new CurrencyRateUpdate(), instance);
	}

	public int Delete(int key) {
		return Connection.Execute(new CurrencyRateDelete { CurrencyRateID = key });
	}

	#endregion "IRepository"

	#region "Commands"

	public class CurrencyRateSelectAll : ICommand {
		public string GetScript() {
			return
@"SELECT
[CurrencyRateID]
,  [CurrencyRateDate]
,  [FromCurrencyCode]
,  [ToCurrencyCode]
,  [AverageRate]
,  [EndOfDayRate]
,  [ModifiedDate]
FROM
[Sales].[CurrencyRate]";
		}
	}

	public class CurrencyRateSelect : ICommand {
		public string GetScript() {
			return
@"SELECT
[CurrencyRateID]
,  [CurrencyRateDate]
,  [FromCurrencyCode]
,  [ToCurrencyCode]
,  [AverageRate]
,  [EndOfDayRate]
,  [ModifiedDate]
FROM
[Sales].[CurrencyRate]
WHERE
[CurrencyRateID] = @CurrencyRateID";
		}

		public Int32 CurrencyRateID { get; set; }
	}

	public class CurrencyRateInsert : ICommand {
		public string GetScript() {
			return
@"INSERT INTO [Sales].[CurrencyRate] (
[CurrencyRateDate]
,  [FromCurrencyCode]
,  [ToCurrencyCode]
,  [AverageRate]
,  [EndOfDayRate]
,  [ModifiedDate]
)
VALUES (
@CurrencyRateDate
,  @FromCurrencyCode
,  @ToCurrencyCode
,  @AverageRate
,  @EndOfDayRate
,  @ModifiedDate
)

SET @CurrencyRateID = SCOPE_IDENTITY();";
		}

		[Output]
		public Int32 CurrencyRateID { get; set; }
		public DateTime CurrencyRateDate { get; set; }
		[Parameter(3)]
		public string FromCurrencyCode { get; set; }
		[Parameter(3)]
		public string ToCurrencyCode { get; set; }
		[Parameter(19, 4)]
		public Decimal AverageRate { get; set; }
		[Parameter(19, 4)]
		public Decimal EndOfDayRate { get; set; }
		public DateTime ModifiedDate { get; set; }
	}

	public class CurrencyRateUpdate : ICommand {
		public string GetScript() {
			return
@"UPDATE
[Sales].[CurrencyRate]
SET
[CurrencyRateDate] = @CurrencyRateDate
,  [FromCurrencyCode] = @FromCurrencyCode
,  [ToCurrencyCode] = @ToCurrencyCode
,  [AverageRate] = @AverageRate
,  [EndOfDayRate] = @EndOfDayRate
,  [ModifiedDate] = @ModifiedDate
WHERE
[CurrencyRateID] = @CurrencyRateID";
		}

		public Int32 CurrencyRateID { get; set; }
		public DateTime CurrencyRateDate { get; set; }
		[Parameter(3)]
		public string FromCurrencyCode { get; set; }
		[Parameter(3)]
		public string ToCurrencyCode { get; set; }
		[Parameter(19, 4)]
		public Decimal AverageRate { get; set; }
		[Parameter(19, 4)]
		public Decimal EndOfDayRate { get; set; }
		public DateTime ModifiedDate { get; set; }
	}

	public class CurrencyRateDelete : ICommand {
		public string GetScript() {
			return @"DELETE FROM [Sales].[CurrencyRate] WHERE [CurrencyRateID] = @CurrencyRateID";
		}

		public Int32 CurrencyRateID { get; set; }
	}

	#endregion "Commands"
}

// create new repository
var crr = new CurrencyRateRepository(c);

// insert new rate from USD to CAD and display new rate id
var cr1 = new CurrencyRate {
	FromCurrencyCode = "USD",
	ToCurrencyCode = "CAD",
	ModifiedDate = DateTime.Now,
	CurrencyRateDate = DateTime.Now,
	AverageRate = 1.5M,
	EndOfDayRate = 1.5M
};
crr.Save(cr1);

// load to verify the output parameter
var cr2 = crr.Load(cr1.CurrencyRateID);
Note the use of output parameters, and how the model (CurrencyRate) is mapped to the commands inside the repository.

Sending Batches

Finally, there are some scenarios where multiple commands need to be bundled in a batch and sent to the server at once. Let's say we need to enter a new order to AdventureWorks, and for performance reasons the order and detail commands must be sent in a batch.

Let's take a look at the model and repositories first:

public class ShipMethod {
	public Int32 ShipMethodID { get; set; }
	[Required]
	[StringLength(50)]
	public string Name { get; set; }
	public Decimal ShipBase { get; set; }
	public Decimal ShipRate { get; set; }
	public Guid rowguid { get; set; }
	public DateTime ModifiedDate { get; set; }
}

public class SalesOrderHeader {
	public SalesOrderHeader() {
		// Create details
		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; }
	[Required]
	[StringLength(25)]
	public string SalesOrderNumber { get; set; }
	[StringLength(25)]
	public string PurchaseOrderNumber { get; set; }
	[StringLength(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; }
	[StringLength(15)]
	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; }
	[StringLength(128)]
	public string Comment { get; set; }
	public Guid rowguid { get; set; }
	public DateTime ModifiedDate { get; set; }

	// We want to keep the details in the model
	public IList<SalesOrderDetail> OrderDetails { get; private set; }
}

public class SalesOrderDetail {
	public Int32 SalesOrderDetailID { get; set; }
	[StringLength(25)]
	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; }

	// Reference the order header
	public SalesOrderHeader SalesOrder { get; set; }
}

public class SpecialOfferProduct {
	public Int32 SpecialOfferID { get; set; }
	public Int32 ProductID { get; set; }
	public Guid rowguid { get; set; }
	public DateTime ModifiedDate { get; set; }
}

public class SalesOrderHeaderRepository : IRepository<SalesOrderHeader, int> {
	public SalesOrderHeaderRepository(IConnection connection) {
		Connection = connection;
	}

	public IConnection Connection { get; private set; }

	#region "IRepository"

	public IEnumerable<SalesOrderHeader> LoadAll() {
		return Connection.ExecuteReader<SalesOrderHeader>(new SalesOrderHeaderSelectAll());
	}

	public SalesOrderHeader Load(int key) {
		return Connection.Execute<SalesOrderHeader>(new SalesOrderHeaderSelect { SalesOrderID = key });
	}

	public int Save(SalesOrderHeader instance) {
		if (instance.SalesOrderID == 0)
			return Connection.MapExecute(new SalesOrderHeaderInsert(), instance);
		else
			return Connection.MapExecute(new SalesOrderHeaderUpdate(), instance);
	}

	public int Delete(int key) {
		return Connection.Execute(new SalesOrderHeaderDelete { SalesOrderID = key });
	}

	#endregion "IRepository"

	#region "Commands"

	//-------------------------------------------------------
	// SelectAll Command
	//-------------------------------------------------------
	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]
";
		}
	}

	//-------------------------------------------------------
	// -- Select Command
	//-------------------------------------------------------
	public class SalesOrderHeaderSelect : ICommand {
		public SalesOrderHeaderSelect() { }

		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]
WHERE
[SalesOrderID] = @SalesOrderID
";
		}

		public Int32 SalesOrderID { get; set; }
	}

	//-------------------------------------------------------
	// Insert Command
	//-------------------------------------------------------
	public class SalesOrderHeaderInsert : ICommand {
		public SalesOrderHeaderInsert() { }

		public string GetScript() {
			return
@"INSERT INTO [Sales].[SalesOrderHeader] (
[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]
)
VALUES (
@RevisionNumber
,  @OrderDate
,  @DueDate
,  @ShipDate
,  @Status
,  @OnlineOrderFlag
--,  @SalesOrderNumber
,  @PurchaseOrderNumber
,  @AccountNumber
,  @CustomerID
,  @SalesPersonID
,  @TerritoryID
,  @BillToAddressID
,  @ShipToAddressID
,  @ShipMethodID
,  @CreditCardID
,  @CreditCardApprovalCode
,  @CurrencyRateID
,  @SubTotal
,  @TaxAmt
,  @Freight
--,  @TotalDue
,  @Comment
,  newid()
,  @ModifiedDate
)

SET @SalesOrderID = SCOPE_IDENTITY();
";
		}

		[Output]
		public Int32 SalesOrderID { get; set; }

		[Input]
		public Byte RevisionNumber { get; set; }

		[Input]
		public DateTime OrderDate { get; set; }

		[Input]
		public DateTime DueDate { get; set; }

		[Input]
		public DateTime? ShipDate { get; set; }

		[Input]
		public Byte Status { get; set; }

		[Input]
		public Boolean OnlineOrderFlag { get; set; }

		[Input]
		[Parameter(25)]
		public string SalesOrderNumber { get; set; }

		[Input]
		[Parameter(25, true)]
		public string PurchaseOrderNumber { get; set; }

		[Input]
		[Parameter(15, true)]
		public string AccountNumber { get; set; }

		[Input]
		public Int32 CustomerID { get; set; }

		[Input]
		public Int32? SalesPersonID { get; set; }

		[Input]
		public Int32? TerritoryID { get; set; }

		[Input]
		public Int32 BillToAddressID { get; set; }

		[Input]
		public Int32 ShipToAddressID { get; set; }

		[Input]
		public Int32 ShipMethodID { get; set; }

		[Input]
		public Int32? CreditCardID { get; set; }

		[Input]
		[Parameter(15, true)]
		public string CreditCardApprovalCode { get; set; }

		[Input]
		public Int32? CurrencyRateID { get; set; }

		[Parameter(19, 4)]
		[Input]
		public Decimal SubTotal { get; set; }

		[Parameter(19, 4)]
		[Input]
		public Decimal TaxAmt { get; set; }

		[Parameter(19, 4)]
		[Input]
		public Decimal Freight { get; set; }

		[Input]
		[Parameter(19, 4)]
		public Decimal TotalDue { get; set; }

		[Input]
		[Parameter(128, true)]
		public string Comment { get; set; }

		public DateTime ModifiedDate { get; set; }
	}

	//-------------------------------------------------------
	// Update Command
	//-------------------------------------------------------
	public class SalesOrderHeaderUpdate : ICommand {
		public SalesOrderHeaderUpdate() { }

		public string GetScript() {
			return
@"UPDATE
[Sales].[SalesOrderHeader]
SET
[RevisionNumber] = @RevisionNumber
,  [OrderDate] = @OrderDate
,  [DueDate] = @DueDate
,  [ShipDate] = @ShipDate
,  [Status] = @Status
,  [OnlineOrderFlag] = @OnlineOrderFlag
--,  [SalesOrderNumber] = @SalesOrderNumber
,  [PurchaseOrderNumber] = @PurchaseOrderNumber
,  [AccountNumber] = @AccountNumber
,  [CustomerID] = @CustomerID
,  [SalesPersonID] = @SalesPersonID
,  [TerritoryID] = @TerritoryID
,  [BillToAddressID] = @BillToAddressID
,  [ShipToAddressID] = @ShipToAddressID
,  [ShipMethodID] = @ShipMethodID
,  [CreditCardID] = @CreditCardID
,  [CreditCardApprovalCode] = @CreditCardApprovalCode
,  [CurrencyRateID] = @CurrencyRateID
--,  [SubTotal] = @SubTotal
,  [TaxAmt] = @TaxAmt
,  [Freight] = @Freight
--,  [TotalDue] = @TotalDue
,  [Comment] = @Comment
--,  [rowguid] = @rowguid
,  [ModifiedDate] = @ModifiedDate
WHERE
[SalesOrderID] = @SalesOrderID
";
		}

		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 SalesOrderNumber { get; set; }
		[Parameter(25, true)]
		public string PurchaseOrderNumber { get; set; }
		[Parameter(15, true)]
		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, true)]
		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(19, 4)]
		public Decimal TotalDue { get; set; }
		[Parameter(128, true)]
		public string Comment { get; set; }
		public Guid rowguid { get; set; }
		public DateTime ModifiedDate { get; set; }
	}

	//-------------------------------------------------------
	// Delete Command
	//-------------------------------------------------------
	public class SalesOrderHeaderDelete : ICommand {
		public SalesOrderHeaderDelete() { }

		public string GetScript() {
			return
@"DELETE FROM
[Sales].[SalesOrderHeader]
WHERE
[SalesOrderID] = @SalesOrderID
";
		}

		public Int32 SalesOrderID { get; set; }
	}
	#endregion "Commands"
}

public class SalesOrderDetailRepository : IRepository<SalesOrderDetail, int> {
	public SalesOrderDetailRepository(IConnection connection) {
		Connection = connection;
	}

	public IConnection Connection { get; private set; }

	#region "IRepository"

	public IEnumerable<SalesOrderDetail> LoadAll() {
		return Connection.ExecuteReader<SalesOrderDetail>(new SalesOrderDetailSelectAll());
	}

	public SalesOrderDetail Load(int key) {
		return Connection.Execute<SalesOrderDetail>(new SalesOrderDetailSelect { SalesOrderDetailID = key });
	}

	public int Save(SalesOrderDetail instance) {
		if (instance.SalesOrderDetailID == 0)
			return Connection.MapExecute(new SalesOrderDetailInsert(), instance);
		else
			return Connection.MapExecute(new SalesOrderDetailUpdate(), instance);
	}

	public int Delete(int key) {
		return Connection.Execute(new SalesOrderDetailDelete { SalesOrderDetailID = key });
	}

	#endregion "IRepository"

	#region "Commands"

	//-------------------------------------------------------
	// SelectAll Command
	//-------------------------------------------------------
	public class SalesOrderDetailSelectAll : ICommand {
		public SalesOrderDetailSelectAll() { }

		public string GetScript() {
			return
@"SELECT
[SalesOrderID]
,  [SalesOrderDetailID]
,  [CarrierTrackingNumber]
,  [OrderQty]
,  [ProductID]
,  [SpecialOfferID]
,  [UnitPrice]
,  [UnitPriceDiscount]
,  [LineTotal]
,  [rowguid]
,  [ModifiedDate]
FROM
[Sales].[SalesOrderDetail]
";
		}
	}

	//-------------------------------------------------------
	// -- Select Command
	//-------------------------------------------------------
	public class SalesOrderDetailSelect : ICommand {
		public SalesOrderDetailSelect() { }

		public string GetScript() {
			return
@"SELECT
[SalesOrderID]
,  [SalesOrderDetailID]
,  [CarrierTrackingNumber]
,  [OrderQty]
,  [ProductID]
,  [SpecialOfferID]
,  [UnitPrice]
,  [UnitPriceDiscount]
,  [LineTotal]
,  [rowguid]
,  [ModifiedDate]
FROM
[Sales].[SalesOrderDetail]
WHERE
[SalesOrderID] = @SalesOrderID
AND [SalesOrderDetailID] = @SalesOrderDetailID
";
		}

		public Int32 SalesOrderID { get; set; }
		public Int32 SalesOrderDetailID { get; set; }
	}

	//-------------------------------------------------------
	// Insert Command
	//-------------------------------------------------------
	public class SalesOrderDetailInsert : ICommand {
		public SalesOrderDetailInsert() { }

		public string GetScript() {
			return
@"INSERT INTO [Sales].[SalesOrderDetail] (
[SalesOrderID]
,  [CarrierTrackingNumber]
,  [OrderQty]
,  [ProductID]
,  [SpecialOfferID]
,  [UnitPrice]
,  [UnitPriceDiscount]
--,  [LineTotal]
,  [rowguid]
,  [ModifiedDate]
)
VALUES (
@SalesOrderID
,  @CarrierTrackingNumber
,  @OrderQty
,  @ProductID
,  @SpecialOfferID
,  @UnitPrice
,  @UnitPriceDiscount
--,  @LineTotal
,  newid()
,  @ModifiedDate
)

SET @SalesOrderDetailID = SCOPE_IDENTITY();
";
		}

		public Int32 SalesOrderID { get; set; }
		[Output]
		public Int32 SalesOrderDetailID { get; set; }
		[Parameter(25, true)]
		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(38, 6)]
		public Decimal LineTotal { get; set; }
		public DateTime ModifiedDate { get; set; }
	}

	//-------------------------------------------------------
	// Update Command
	//-------------------------------------------------------
	public class SalesOrderDetailUpdate : ICommand {
		public SalesOrderDetailUpdate() { }

		public string GetScript() {
			return
@"UPDATE
[Sales].[SalesOrderDetail]
SET
[CarrierTrackingNumber] = @CarrierTrackingNumber
,  [OrderQty] = @OrderQty
,  [ProductID] = @ProductID
,  [SpecialOfferID] = @SpecialOfferID
,  [UnitPrice] = @UnitPrice
,  [UnitPriceDiscount] = @UnitPriceDiscount
--,  [LineTotal] = @LineTotal
--,  [rowguid] = @rowguid
,  [ModifiedDate] = @ModifiedDate
WHERE
[SalesOrderID] = @SalesOrderID
AND [SalesOrderDetailID] = @SalesOrderDetailID
";
		}

		public Int32 SalesOrderID { get; set; }
		public Int32 SalesOrderDetailID { get; set; }
		[Parameter(25, true)]
		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(38, 6)]
		public Decimal LineTotal { get; set; }
		public Guid rowguid { get; set; }
		public DateTime ModifiedDate { get; set; }
	}

	//-------------------------------------------------------
	// Delete Command
	//-------------------------------------------------------
	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; }
	}
	#endregion "Commands"
}
Note: I generated the previous model and repositories with a code generation tool

We can explore the mappings between the model and commands in the repositories:
Mapper.Of<SalesOrderHeader, SalesOrderHeaderRepository.SalesOrderHeaderInsert>.Mappings, Mapper.Of<SalesOrderHeader, SalesOrderHeaderRepository.SalesOrderHeaderInsert>.Expression);
Mapper.Of<SalesOrderHeaderRepository.SalesOrderHeaderInsert, SalesOrderHeader>.Mappings, Mapper.Of<SalesOrderHeaderRepository.SalesOrderHeaderInsert, SalesOrderHeader>.Expression);
Mapper.Of<SalesOrderDetail, SalesOrderDetailRepository.SalesOrderDetailInsert>.Mappings, Mapper.Of<SalesOrderDetail, SalesOrderDetailRepository.SalesOrderDetailInsert>.Expression);
Mapper.Of<SalesOrderDetailRepository.SalesOrderDetailInsert, SalesOrderDetail>.Mappings, Mapper.Of<SalesOrderDetailRepository.SalesOrderDetailInsert, SalesOrderDetail>.Expression);

---------------- Mappings from SalesOrderHeader to SalesOrderHeaderInsert  ---------------------
System.Byte : RevisionNumber -> System.Byte : RevisionNumber
System.DateTime : OrderDate -> System.DateTime : OrderDate
System.DateTime : DueDate -> System.DateTime : DueDate
System.Nullable`1[System.DateTime] : ShipDate -> System.Nullable`1[System.DateTi
me] : ShipDate
System.Byte : Status -> System.Byte : Status
System.Boolean : OnlineOrderFlag -> System.Boolean : OnlineOrderFlag
System.String : SalesOrderNumber -> System.String : SalesOrderNumber
System.String : PurchaseOrderNumber -> System.String : PurchaseOrderNumber
System.String : AccountNumber -> System.String : AccountNumber
System.Int32 : CustomerID -> System.Int32 : CustomerID
System.Nullable`1[System.Int32] : SalesPersonID -> System.Nullable`1[System.Int3
2] : SalesPersonID
System.Nullable`1[System.Int32] : TerritoryID -> System.Nullable`1[System.Int32]
 : TerritoryID
System.Int32 : BillToAddressID -> System.Int32 : BillToAddressID
System.Int32 : ShipToAddressID -> System.Int32 : ShipToAddressID
System.Int32 : ShipMethodID -> System.Int32 : ShipMethodID
System.Nullable`1[System.Int32] : CreditCardID -> System.Nullable`1[System.Int32
] : CreditCardID
System.String : CreditCardApprovalCode -> System.String : CreditCardApprovalCode

System.Nullable`1[System.Int32] : CurrencyRateID -> System.Nullable`1[System.Int
32] : CurrencyRateID
System.Decimal : SubTotal -> System.Decimal : SubTotal
System.Decimal : TaxAmt -> System.Decimal : TaxAmt
System.Decimal : Freight -> System.Decimal : Freight
System.Decimal : TotalDue -> System.Decimal : TotalDue
System.String : Comment -> System.String : Comment
System.DateTime : ModifiedDate -> System.DateTime : ModifiedDate

---------------- Expression from SalesOrderHeader to SalesOrderHeaderInsert ---------------------
.Lambda Mapper<Knak.Mapper+PropertyMapper`2+MapperCallback[KnackConsole.SalesOrd
erHeader,KnackConsole.SalesOrderHeaderRepository+SalesOrderHeaderInsert]>(
    KnackConsole.SalesOrderHeader $source,
    KnackConsole.SalesOrderHeaderRepository+SalesOrderHeaderInsert $target) {
    .Block() {
        $target.RevisionNumber = $source.RevisionNumber;
        $target.OrderDate = $source.OrderDate;
        $target.DueDate = $source.DueDate;
        $target.ShipDate = $source.ShipDate;
        $target.Status = $source.Status;
        $target.OnlineOrderFlag = $source.OnlineOrderFlag;
        $target.SalesOrderNumber = $source.SalesOrderNumber;
        $target.PurchaseOrderNumber = $source.PurchaseOrderNumber;
        $target.AccountNumber = $source.AccountNumber;
        $target.CustomerID = $source.CustomerID;
        $target.SalesPersonID = $source.SalesPersonID;
        $target.TerritoryID = $source.TerritoryID;
        $target.BillToAddressID = $source.BillToAddressID;
        $target.ShipToAddressID = $source.ShipToAddressID;
        $target.ShipMethodID = $source.ShipMethodID;
        $target.CreditCardID = $source.CreditCardID;
        $target.CreditCardApprovalCode = $source.CreditCardApprovalCode;
        $target.CurrencyRateID = $source.CurrencyRateID;
        $target.SubTotal = $source.SubTotal;
        $target.TaxAmt = $source.TaxAmt;
        $target.Freight = $source.Freight;
        $target.TotalDue = $source.TotalDue;
        $target.Comment = $source.Comment;
        $target.ModifiedDate = $source.ModifiedDate
    }
}

---------------- Mappings from SalesOrderHeaderInsert to SalesOrderHeader ---------------------
System.Int32 : SalesOrderID -> System.Int32 : SalesOrderID
System.DateTime : ModifiedDate -> System.DateTime : ModifiedDate

---------------- Expression from SalesOrderHeaderInsert to SalesOrderHeader  ---------------------
.Lambda Mapper<Knak.Mapper+PropertyMapper`2+MapperCallback[KnackConsole.SalesOrd
erHeaderRepository+SalesOrderHeaderInsert,KnackConsole.SalesOrderHeader]>(
    KnackConsole.SalesOrderHeaderRepository+SalesOrderHeaderInsert $source,
    KnackConsole.SalesOrderHeader $target) {
    .Block() {
        $target.SalesOrderID = $source.SalesOrderID;
        $target.ModifiedDate = $source.ModifiedDate
    }
}

---------------- Mappings from SalesOrderDetail to SalesOrderDetailInsert ---------------------
System.String : CarrierTrackingNumber -> System.String : CarrierTrackingNumber
System.Int16 : OrderQty -> System.Int16 : OrderQty
System.Int32 : ProductID -> System.Int32 : ProductID
System.Int32 : SpecialOfferID -> System.Int32 : SpecialOfferID
System.Decimal : UnitPrice -> System.Decimal : UnitPrice
System.Decimal : UnitPriceDiscount -> System.Decimal : UnitPriceDiscount
System.Decimal : LineTotal -> System.Decimal : LineTotal
System.DateTime : ModifiedDate -> System.DateTime : ModifiedDate
System.Int32 : SalesOrder.SalesOrderID -> System.Int32 : SalesOrderID

----------------Expression from SalesOrderDetail to SalesOrderDetailInsert ---------------------
.Lambda Mapper<Knak.Mapper+PropertyMapper`2+MapperCallback[KnackConsole.SalesOrd
erDetail,KnackConsole.SalesOrderDetailRepository+SalesOrderDetailInsert]>(
    KnackConsole.SalesOrderDetail $source,
    KnackConsole.SalesOrderDetailRepository+SalesOrderDetailInsert $target) {
    .Block() {
        $target.CarrierTrackingNumber = $source.CarrierTrackingNumber;
        $target.OrderQty = $source.OrderQty;
        $target.ProductID = $source.ProductID;
        $target.SpecialOfferID = $source.SpecialOfferID;
        $target.UnitPrice = $source.UnitPrice;
        $target.UnitPriceDiscount = $source.UnitPriceDiscount;
        $target.LineTotal = $source.LineTotal;
        $target.ModifiedDate = $source.ModifiedDate;
        .If ($source.SalesOrder != null) {
            .Block() {
                $target.SalesOrderID = ($source.SalesOrder).SalesOrderID
            }
        } .Else {
            .Default(System.Void)
        }
    }
}

---------------- Mappings from SalesOrderDetailInsert to SalesOrderDetail ---------------------
System.Int32 : SalesOrderDetailID -> System.Int32 : SalesOrderDetailID
System.String : CarrierTrackingNumber -> System.String : CarrierTrackingNumber
System.Int16 : OrderQty -> System.Int16 : OrderQty
System.Int32 : ProductID -> System.Int32 : ProductID
System.Int32 : SpecialOfferID -> System.Int32 : SpecialOfferID
System.Decimal : UnitPrice -> System.Decimal : UnitPrice
System.Decimal : UnitPriceDiscount -> System.Decimal : UnitPriceDiscount
System.Decimal : LineTotal -> System.Decimal : LineTotal
System.DateTime : ModifiedDate -> System.DateTime : ModifiedDate

----------------Expression from SalesOrderDetailInsert to SalesOrderDetail ---------------------
.Lambda Mapper<Knak.Mapper+PropertyMapper`2+MapperCallback[KnackConsole.SalesOrd
erDetailRepository+SalesOrderDetailInsert,KnackConsole.SalesOrderDetail]>(
    KnackConsole.SalesOrderDetailRepository+SalesOrderDetailInsert $source,
    KnackConsole.SalesOrderDetail $target) {
    .Block() {
        $target.SalesOrderDetailID = $source.SalesOrderDetailID;
        $target.CarrierTrackingNumber = $source.CarrierTrackingNumber;
        $target.OrderQty = $source.OrderQty;
        $target.ProductID = $source.ProductID;
        $target.SpecialOfferID = $source.SpecialOfferID;
        $target.UnitPrice = $source.UnitPrice;
        $target.UnitPriceDiscount = $source.UnitPriceDiscount;
        $target.LineTotal = $source.LineTotal;
        $target.ModifiedDate = $source.ModifiedDate
    }
}
Now, in order to execute a batch, all commands must be deployed as stored procedures. This is another feature that will not only help with performance, but will also make a system more secure if commands are organized in namespaces that are mapped to SQL Server schemas with different access levels. I usually encapsulate all my command in a class library, and deploy them all every time the library changes using post-build actions.

Here we are deploying only the four commands we need to use, but there is a method to deploy all commands in a given assembly:

var deployer = new Knak.Data.SqlServer.SqlServerCommandDeployer();
deployer.Deploy(c,
	new SalesOrderHeaderRepository.SalesOrderHeaderInsert(),
	new SalesOrderDetailRepository.SalesOrderDetailInsert(),
	new SalesOrderHeaderRepository.SalesOrderHeaderDelete(),
	new SalesOrderDetailRepository.SalesOrderDetailDelete()
);

Now that we have our stored procedures, we can create a new order:

// -----------------------------------------------------------------------------------------------------
// Get any customer's info
var cinfoScript =
@"
SELECT TOP 1
        C.[CustomerID]
,	C.[AccountNumber]   
,	A.[AddressID]
,	BA.[AddressTypeID]
FROM
[Sales].[Customer] C
join [Person].[Person] P on C.PersonID = P.BusinessEntityID
join [Person].[BusinessEntityAddress] BA on P.BusinessEntityID = BA.BusinessEntityID 
join [Person].[Address] A on BA.AddressID = A.AddressID 
";

var cinfo = cn.Execute<dynamic>(cinfoScript, callback: DynamicReader.Materialize);

// Find a ship method
var shipmethod = c.ExecuteReader<ShipMethod>(@"SELECT * FROM [Purchasing].[ShipMethod]").First();
// Load special offers
var specialOffers = c.ExecuteReader<SpecialOfferProduct>(@"SELECT * FROM [Sales].[SpecialOfferProduct]").ToList();
// -----------------------------------------------------------------------------------------------------

// create new order
var order = new SalesOrderHeader {
	RevisionNumber = 0,
	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()
});

And with the fresh order, we just setup the connection to execute stored procedures and send away our batch:

c.ExecutionMode = System.Data.CommandType.StoredProcedure;

var batch = new Batch();
var orderitem = batch.Add(new SalesOrderHeaderRepository.SalesOrderHeaderInsert(), order);
foreach (var od in order.OrderDetails) {
	var detailitem = batch.Add(new SalesOrderDetailRepository.SalesOrderDetailInsert(), od);
	orderitem.BindTo(detailitem, order => order.SalesOrderID, detail => detail.SalesOrderID);
}

Console.WriteLine("-------------------------------------------------------------------------");
Console.WriteLine(batch.GetScript(c));
Console.WriteLine("-------------------------------------------------------------------------");

using (var ts = new TransactionScope()) {
	batch.Execute(c);
	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));

You probably noticed the bindings we are creating between the order and the order details. This is required when working with batches where the output parameters of one item are used by other items in the batch. In this case the new order id is not known until the first command is executed, so we have to make sure the details know where to find that id in the batch.

Last edited Dec 6, 2012 at 10:16 PM by knak, version 10

Comments

No comments yet.