Writing related queries and statistics based on several tables is indeed a cumbersome task. Since a lot of information based on character-based descriptions is difficult to extract and share, it must be rewritten when this situation is needed elsewhere. In order to avoid this situation, SQL Artisan references the view object and describes the data query information through the view object; one of the biggest benefits of the view object is inheritance, which can be inherited from an existing object (entity object or view object) to extend new queries. Functional description. In actual application, you can create a basic statistical view object, and then derive specific statistical objects according to the situation (such as: grouping according to different information, displaying those field information, etc.).
The following is some simple routine code:
Order sales amount statistical basic view Object
/// <summary>
/// Create a simple order sales amount statistics class
/// </summary>
[TableMap("", TableType.View)]
public class OrderStat:HFSoft.Data.ITableView
{
#region ITableView Member
public virtual Table GetTable()
{
// TODO: Add OrderV.GetTable implementation
return DBMapping.Orders.INNER(DBMapping.Employees, DBMapping.Employees.EmployeeID)& DBMapping.Orders.INNER(DBMapping.OrderDetails, DBMapping.Orders.OrderID )& DBMapping.OrderDetails.INNER(DBMapping.Products, DBMapping.Products.ProductID);
}
private Double mTotalize;
[StatColumn("Quantity*[Order Details].UnitPrice*(1-Discount)", StatType.Sum)]
public Double Totalize
{
get
{
return mTotalize;
}
set
{
mTotalize = value;
}
}
#endregion
}
Inherited implementation of group statistics by employees
/// <summary>
/// Group statistics by employees
/// </summary>
[TableMap ("", TableType.View)]
public class EmployeeTotal:OrderStat
{
private int mEmployeeID;
[ViewColumn("Employees.EmployeeID")]
public int EmployeeID
{
get
{
return mEmployeeID;
}
set
{
mEmployeeID = value;
}
}
private string mEmployeeName ;
[ViewColumn("FirstName+LastName")]
public string EmployeeName
{
get
{
return mEmployeeName;
}
set
{
mEmployeeName = value;
}
}
}
Inherited implementation of group statistics by product
/// <summary>
/// Group statistics by product
/// </summary>
[TableMap("", TableType.View)]
public class ProductTotal : OrderStat
{
private int mProductID;
[ViewColumn("Products.ProductID")]
public int ProductID
{
get
{
return mProductID;
}
set
{
mProductID = value;
}
}
private string mProductName;
[ViewColumn("ProductName")]
public string ProductName
{
get
{
return mProductName;
}
set
{
mProductName = value;
}
}
}
During statistics, without changing the conditions, you only need to load Different description types can realize data query and statistical functions with different needs.
Expression exp = new Expression();
exp &= new HFSoft.Data.Mapping.NumberField("year(" + DBMapping.Orders.OrderDate.Name + ")", null) == 1997;
List<EmployeeTotal> empt= exp .List<EmployeeTotal>();
List<ProductTotal> prot= exp.List<ProductTotal>();
Association Loading Related Table Information Fields
When it is necessary to load related table information fields, you can create a view object inherited from the entity object; however, you can also create a brand new view object based on the implementation.
Product information view object
/// <summary>
/// Product information view object
/// </summary>
[TableMap("",TableType.View)]
public class ProductsView:Products,HFSoft.Data.ITableView
{
#region ITableView member
public virtual Table GetTable()
{
return DBMapping.Products.INNER(DBMapping.Categories, DBMapping.Categories.CategoryID)
& DBMapping.Products.INNER(DBMapping.Suppliers, DBMapping.Suppliers.SupplierID);
}
#endregion
private string mCategoryName;
[ViewColumn("CategoryName")]
public string CategoryName
{
get
{
return mCategoryName;
}
set
{
mCategoryName = value;
}
}
private string mCompanyName;
[ViewColumn("CompanyName")]
public string CompanyName
{
get
{
return mCompanyName;
}
set
{
mCompanyName = value;
}
}
}
Expression exp = new Expression();
exp &= DBMapping.Suppliers.City == "GuangZhou";
exp.List<ProductsView>();
In order to facilitate display, the product view object introduces product category and supplier information.
The above is a simple routine to introduce the SQL Artisan multi-query statistics function. The component attempts to embody all data output in the form of entity objects (mainly to simplify access operability); however, SQL Artisan does not fully support the functions of all SQL statements. Implemented most commonly used functions.
http://www.cnblogs.com/henryfan/archive/2006/10/30/544540.html