Ok. Let's finish our trip to AdventureWorks mapping. The following steps need to be done:
- Create base class containing ModifiedDate and Timestamp properties. Those properties present in all entities.
- Fulfil SalesPerson and SalesTerritory classes with appropriate properties
- Create Query static property in SalesPerson class.
So, here is complete classes code (single file).
using System;
using System.Collections.Generic;
using System.Text;
using Worm.Entities.Meta;
using Worm.Query;
using System.Xml;
namespace exam1sharp.Sales
{
public class SalesBase
{
public DateTime ModifiedDate { get; set; }
[EntityProperty("rowguid", Field2DbRelations.RowVersion)]
public Guid Timestamp { get; protected set; }
}
[Entity("Sales", "Store", "1")]
public class Store : SalesBase
{
[EntityProperty("CustomerID", Field2DbRelations.PK)]
public int ID { get; set; }
public string Name { get; set; }
public XmlDocument Demographics { get; set; }
[EntityProperty("SalesPersonID")]
public SalesPerson SalesPerson { get; set; }
public static QueryCmd Query
{
get
{
return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
.From(typeof(Store))
.Select(typeof(Store));
}
}
}
[Entity("Sales", "SalesPerson", "1")]
public class SalesPerson : SalesBase
{
[EntityProperty("SalesPersonID", Field2DbRelations.PK)]
public int ID { get; set; }
public decimal? SalesQuota { get; set; }
[EntityProperty("TerritoryID")]
public SalesTerritory SalesTerritory { get; set; }
public decimal Bonus { get; set; }
public decimal CommissionPct { get; set; }
public decimal SalesYTD { get; set; }
public decimal SalesLastYear { get; set; }
public static QueryCmd Query
{
get
{
return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
.From(typeof(SalesPerson))
.Select(typeof(SalesPerson));
}
}
}
[Entity("Sales", "SalesTerritory", "1")]
public class SalesTerritory : SalesBase
{
[EntityProperty("TerritoryID", Field2DbRelations.PK)]
public int ID { get; set; }
public string Name { get; set; }
public string CountryRegionCode { get; set; }
[EntityProperty("[Group]")]
public string Group { get; set; }
public decimal SalesYTD { get; set; }
public decimal SalesLastYear { get; set; }
public decimal CostYTD { get; set; }
public decimal CostLastYear { get; set; }
}
}
Program almoust not changed.
static void Main(string[] args)
{
foreach (exam1sharp.Sales.Store s in exam1sharp.Sales.Store.Query
.Where(Ctor.prop(typeof(exam1sharp.Sales.Store), "Name").like("A%"))
.ToList())
{
Console.WriteLine("Store id: {0}, name: {1}, sales territory: {2}",
s.ID,
s.Name,
s.SalesPerson.SalesTerritory.Name);
}
}
And the most interesting piece of code for the real docs.
declare @p1 VarChar(2);set @p1 = 'A%'
select
t1.Demographics, t1.CustomerID, t1.ModifiedDate, t1.Name, t1.SalesPersonID, t1.rowguid,
t2.Bonus, t2.CommissionPct, t2.SalesPersonID, t2.ModifiedDate, t2.SalesLastYear, t2.SalesQuota, t2.TerritoryID, t2.SalesYTD, t2.rowguid,
t3.CostLastYear, t3.CostYTD, t3.CountryRegionCode, t3.[Group], t3.TerritoryID, t3.ModifiedDate, t3.Name, t3.SalesLastYear, t3.SalesYTD, t3.rowguid
from Sales.Store t1
join Sales.SalesPerson t2 on t2.SalesPersonID = t1.SalesPersonID
join Sales.SalesTerritory t3 on t3.TerritoryID = t2.TerritoryID
where t1.Name like @p1
As you can see, the SQL code is simple, readable and solid. It's very similar to human coding. Nothing superfluous.