SQL artisan Multiple table queries and statistics

sql| Statistics

Writing associative queries and statistics based on several tables is really cumbersome, because many of the information based on character descriptions is difficult to extract and share, and must be rewritten when this situation is needed elsewhere. To avoid this situation, SQL artisan references the View object and describes the information of the data query through the View object; One of the greatest benefits of view objects is inheritance, which can be inherited from an existing object (Entity object or view object) to extend the new query feature description. In practice, you can create a basic statistical view object, and then derive specific statistical objects (e.g., grouped according to different information, display those field information, and so on).

Here are some simple routine code:

Order Sales Amount Statistics Base View object

<summary>

Establish a simple order sales Amount Statistics Class

</summary>

[Tablemap ("", Tabletype.view)]

public class OrderStat:HFSoft.Data.ITableView

{

#region Itableview Members

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

}

Group statistics by Employee inheritance implementation

<summary>

Group statistics by employee

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

}

}

}

By product group Statistics inheritance Implementation

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

}

}

}

When the statistics are not changed, you only need to load different description types to realize the data query statistic function of different requirements.

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 you need to load relational table-related field information, you can build a visual object that inherits from the entity object, but you can also create an entirely 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 Members

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

For ease of display, product view objects introduce product categories and vendor information.

The above is a simple routine to introduce the SQL Artisan query statistics function, the component tries to put all the data output in the form of entity objects (mainly simplified access operability), but the SQL artisan does not fully support the functionality of all SQL statements, only to achieve the most commonly used functions.