Llbl Gen multi-Table operations in ERP/MIS Development

Previous ArticleArticleAs mentioned in, llbl Gen queries a single table. Here we will analyze llbl Gen's operations on multiple tables.
Adapter mode. The variable adapter used is defined as dataaccessadapter adapter = new dataaccessadapter ();

Query, save, and delete a Master/Slave table

Salesorder is the main table of a purchase order. It has a detailed list of salesorderdetail, which is used to record the material information of the purchase order.
Query the purchase order and its material details numbered oe20100821
Salesorderentity salesorder = new salesorderentity ("oe20100821 ");
Iprefetchpath2 prefetchpath = new prefetchpath2 (INT) entitytype. salesorderentity );

Prefetchpath. Add (salesorderentity. prefetchpathsalesorderdetails );
Bool found = Adapter. fetchentity (salesorder, prefetchpath, null, null );
Prefetchpath indicates that records of multiple tables can be read to the memory at a time due to different depths. Llbl Gen requires that the depth of the table to be read be specified.

Save the purchase order and its material details. First, save the master table of the purchase order, and then save the details of the purchase order from the table.
Salesorderentity salesorder = new salesorderentity ("oe20100821 ");
Adapter. saveentity (salesorder, true, false );
Foreach (salesorderdetailentity detail in salesorder. salesorderdetails)
{
Adapter. saveentity (detail );

}

Delete a purchase order. First, delete the details of the purchase order from the table, and then delete the master table of the purchase order.
Salesorderentity salesorder; // orderno = "oe20100821"
Foreach (salesorderdetailentity detail in salesorder. salesorderdetails)
{
Adapter. deleteentity (detail );

}

Adapter. deleteentity (salesorder );

The llbl Gen framework can differentiate the insert/update commands and use the saveentity method.
Determine whether an object is newly added or read from the database. The following method can be used:
Bool loadedcorrectly = (salesorder. Fields. State = entitystate. fetched );

 

Custom query of typedlist, which can be used to read any field data from multiple tables

The SQL statement is as follows. This example also demonstrates the expression usage.
Select orderid, productid, (unitprice * quantity) as rowtotal from [Order Details]
The corresponding llbl Gen statement is as follows:
Resultsetfields fields = new resultsetfields (3 );

Fields. definefield (orderdetailsfields. orderid, 0 );

Fields. definefield (orderdetailsfields. productid, 1 );

Fields. definefield (New entityfield2 ("rowtotal", (orderdetailsfields. unitprice * orderdetailsfields. Quantity), 2 );

Datatable Results = new datatable ();

Dataaccessadapter adapter = new dataaccessadapter ();

Adapter. fetchtypedlist (fields, results, null );

Let's look at a complex example of querying results from multiple tables.
Define the datatable for storing the result first, and design the corresponding table structure according to the field requirements.

datatable inventorybalance = new fastserializabledatatable ("inventorybalance");
inventorybalance. columns. add ("Loc", typeof (string);
inventorybalance. columns. add ("Description", typeof (string);
inventorybalance. columns. add ("qtyonhand", typeof (decimal);
inventorybalance. columns. add ("qtyoninspect", typeof (decimal);
define the source of the data item

Resultsetfields inventorybalancefields = new resultsetfields (4 );
Inventorybalancefields. definefield (inventorybalancefields. Loc, 0 );
Inventorybalancefields. definefield (locationfields. Description, 1 );
Inventorybalancefields. definefield (inventorybalancefields. qtyonhand, 2 );
Inventorybalancefields. definefield (inventorybalancefields. qtyoninspect, 3 );
Define query filter conditions
Irelationpredicatebucket filterbucket = new relationpredicatebucket ();
Filterbucket. predicateexpression. Add (inventorybalancefields. itemno = itemno );
Set the relationship between tables of the above data source items
Filterbucket. relations. Add (inventorybalanceentity. relations. locationentityusingloc );

Sort results
Isortexpression sorter = new sortexpression (inventorybalancefields. itemno | sortoperator. ascending)

Finally, load the data to the memory table.
Adapter. fetchtypedlist (inventorybalancefields, inventorybalance, filterbucket, 0, Sorter, false, null );
The correspondence between the locationentity of the warehouse and the inventorybalanceentity of the inventory balance is one-to-one, and the LOC warehouse encoding is used for association.

This query can be more complex. The fields to be queried are not necessarily in the database, such as the result of the aggregate (sum, average) function.
We are adding a field for the preceding query. The ORM statement is as follows, indicating the total number of items in the purchase order.
Dbfunctioncall dbfunqtyunused = new dbfunctioncall ("isnull", new object [] {(salesorderdetailfields. qtyordered), 0 });
Entityfield2 eqtyorder = new entityfield2 ("qtyordered", dbfunqtyunused );
Inventorybalancefields. definefield (eqtyorder, 4, "qtyordered ");
Inventorybalancefields [4]. aggregatefunctiontoapply = aggregatefunction. sum;

Because the salesorderentity table is added, the relationship needs to be added. The relationship is associated with the warehouse as the warehouse for each item in the purchase order.
Filterbucket. relations. Add (inventorybalanceentity. relations. salesorderdetailentityusingloc );

 

Subquery scalar Query expressions

First, let's look at an example. The SQL statement is as follows to query the customer number in the customer table and the total number of all orders.
Select customerid,
(Select count (*) from orders where customerid = customers. customerid) as numberoforders from customers

The format of the ORM in adapter mode is as follows:
Resultsetfields fields = new resultsetfields (2 );
Fields. definefield (customerfields. customerid, 0 );
Fields. definefield (New entityfield2 ("numberoforders", new scalarqueryexpression (orderfields. orderid. setaggregatefunction (aggregatefunction. count), (customerfields. customerid = orderfields. customerid), 1 );
Datatable Results = new datatable ();
Adapter. fetchtypedlist (fields, results, null );

If you only need to query a single value, dataaccessadapter has more direct support. See the following example.
Irelationpredicatebucket filterbucket = new relationpredicatebucket ();

filterbucket. predicateexpression. add (salesorderdetailfields. itemno = 'pc');
filterbucket. predicateexpression. add (salesorderfields. orderno = 'oe20151121');
filterbucket. relations. add (salesorderentity. relations. salesorderdetailentityusingorderno);
dbfunctioncall dbfunqtyordered = new dbfunctioncall ("isnull", new object [] {(salesorderdetailfields. qtyordered), 0});
Object result = adapter. getscalar (salesorderdetailfields. qtyordered, dbfunqtyordered, aggregatefunction. sum, filterbucket);
This ORM statement is used to query the total number of materials in the purchase order oe20110921 for PC. The SQL statement is used to write the data
select sum (qtyordered) from salesorder A, salesorderdetail B where. orderno = B. orderno
and. orderno = 'e20151121' and B. itemno = 'pc'

The application scenarios of Aggregate functions are often as follows:
Resultsetfields fields = new resultsetfields (2 );
Fields. definefield (customerfieldindex. Country, 0, "country ");
Fields. definefield (customerfieldindex. customerid, 1, "amountcustomers ");
Fields [1]. aggregatefunctiontoapply = aggregatefunction. countdistinct

 

Entitycollection Query

If the data to be queried comes from the database, you can apply the preceding methods. If the data to be queried comes from the memory set, the following mode is applied.
For example, if you want to find the materials that are not shipped in the purchase order list, use the following statement:
List <int> indices = salesorderdetails. findmatches (salesorderdetailfields. qtypacked <= 0 );
For (INT I = indices. Count-1; I> = 0; I --)
{
Salesorderdetails unpackedorder = salesorderdetails [indices [I]);
}
The findmatches method can also input the following types

Ipredicate filterbucket = (salesorderdetailfields. qtypacked <= 0 );

If the data to be operated has different order, and the data in entitycollection is not the order required by the Business Rule operation, you can sort and filter the data using entityview2 in the following mode, obtain the data sorted by the specified rule again.

Ipredicate filterbucket = (salesorderdetailfields. itemno = "PC ");
Isortexpression sortexpression = new sortexpression ();
Sortexpression. Add (salesorderdetailfields. itemno | sortoperator. ascending );

Entitycollection <salesorderdetailentity> detailentities = salesorder. salesorderdetails;

Entityview2 <salesorderdetailentity> entityview = new entityview2 <salesorderdetailentity> (detailentities );
Entityview. Filter = filterbucket;
Entityview. sorter = sortexpression;
Construct entityview2 Based on entitycollection and input the specified sorting and filtering conditions.

Entitycollection <salesorderdetailentity> tmpcollection = (entitycollection <salesorderdetailentity>) entityview. toentitycollection ();
Foreach (salesorderdetailentity detail in tmpcollection)
{
// Here, the material details in the purchase order have been sorted in ascending order by the material number
}

since the application of ORM, query statements such as select * are rarely written, because this is too time-consuming and affects performance. This mode is as follows
includefieldslist fieldlist = new includefieldslist ();
fieldlist. add (salesorderfields. dueday);
fieldlist. add (salesorderfields. ordreno);
salesorderentity sales = adapter. fetchentity (salesorder, prefetchpath, null, fieldlist);
the read values dueday and ordreno will be applied. If salesorderentity has another field: salesman (string, varchar (50 )),
In this read mode, the value is always string. empty.