30 ORM questions and answers for ERP/MIS development llbl Gen 3.x Adapter

Frequently Used content for llbl gen-based projects. It is used for querying and sharing when you forget it.

1. How to read the content of a specified table to the set

Dataaccessadapter adapter = new dataaccessadapter ();

Entitycollection allcustomers = new entitycollection (New customerentityfactory ());

Adapter. fetchentitycollection (allcustomers, null );

2. How to add a database record

Productentity newproduct = new productentity ();

Newproduct. categoryid = 1;

Newproduct. discontinued = false;

Newproduct. productname = "toocool ";

Newproduct. unitsonorder = 0;

Dataaccessadapter adapter = new dataaccessadapter ();

Adapter. saveentity (newproduct );

3. How to delete a database record

1) The database has been loaded into the memory.
Customerentity customer = new customerentity ("fissa ");
Dataaccessadapter adapter = new dataaccessadapter ();
Adapter. fetchentity (customer );
2) set the object primary key and delete it directly from the database.
Customerentity customer = new customerentity ("fissa ");
Adapter. deleteentity (customer );
3) use the predicate expression to directly delete it from the database.

Irelationpredicatebucket bucket = new relationpredicatebucket (customerfields. customerid = "fissa "));
Dataaccessadapter adapter = new dataaccessadapter ();
Adapter. deleteentitiesdirectly ("customerentity", bucket );

4. How to pass the database connection string

Dataaccessadapter adapter = new dataaccessadapter ("Data Source = myserver; initial catalog = northwind; uid = myuserid; Pwd = secret ");

5. How to test whether an object is loaded from a database

 
Customerentity customer = new customerentity ("chops"); dataaccessadapter adapter = new dataaccessadapter (); bool loadedcorrectly = Adapter. fetchentity (customer );
 

Read the return value of fetchentity. True indicates loading from the database.

 
Customerentity customer = new customerentity ("chops"); dataaccessadapter adapter = new dataaccessadapter (); adapter. fetchentity (customer); bool loadedcorrectly = (customer. fields. state = entitystate. fetched );
View the value of fields. state. fetched indicates that it has been loaded from the database.
 

6. How to view a primary key field of an object at runtime

Two Methods: query the object property primarykeyfields (type: arraylist). You can also traverse the object property and check its isprimarykey value.

7. How to give a new object a property null value

If you do not specify any value for this field, you can ensure that the database value is null. You can also specify NULL to set it to null.

Customerentity customer = new customerentity ("CDC ");

Customer. contacttitle = NULL;

This will cause the database Field Value of contacttitle to be null. This sentence can also be left blank and is often omitted.

8. How to set the null attribute for an existing object

Customerentity customer = new customerentity ("chops ");

Customer. setnewfieldvalue (INT) customerfieldindex. contacttitle, null );

You can also write it like this.

Customerentity customer = new customerentity ("chops ");

Customer. setnewfieldvalue ("contacttitle", null );

Note: The new object is created in the memory and is not associated with the database or saved to the database.

An existing object often reads the record value from the database and fills it in the attribute value of the object.

9. How to update the values of a series of database records directly?

Orderentity newvalues = new orderentity ();

Newvalues. employeeid = 5;

Irelationpredicatebucket filter = new relationpredicatebucket (orderfields. employeeid = 2); dataaccessadapter adapter = new dataaccessadapter ();

Adapter. updateentitiesdirectly (newvalues, filter );

In the preceding statements, all the values with the employee ID of orderentity as 2 are updated to 5.

10. How to query the read sorted types list

Sortexpression sorter = (orderfields. orderdate | sortoperator. Descending) & (customerfields. companyName | sortoperator. ascending );

11 how to write a where field = 3 filter statement

Ipredicateexpression filter = new predicateexpression ();

Filter. Add (yourentityfields. Field = 3 );

12 how to write the filter Statement of WHRE fielda = fieldb

Ipredicateexpression filter = new predicateexpression ();

Filter. Add (orderfields. orderdate = orderfields. shippingdate );

13 How to Write a where field like '% Foo %' filter statement

Ipredicateexpression filter = new predicateexpression ();

Filter. Add (yourentityfields. Field % "% Foo % ");

14. How to Write filter statements for where field between 1 and 10?

Ipredicateexpression filter = new predicateexpression ();

Filter. Add (New fieldbetweenpredicate (yourentityfields. Field, null, 1, 10 ));

15. How to Write a where field in (1, 2, 5) filter statement

Ipredicateexpression filter = new predicateexpression ();

Int [] values = new int [3] {1, 2, 5 };

Filter. Add (yourentityfields. Field = values );

You can also use arraylist to implement

Ipredicateexpression filter = new predicateexpression ();

Arraylist values = new arraylist ();

Values. Add (1); values. Add (2); values. Add (5 );

Filter. Add (yourentityfields. Field = values );

This method is more direct

Ipredicateexpression filter = new predicateexpression ();

Filter. Add (New fieldcomparerangepredicate (yourentityfields. Field, nothing, 1, 2, 5 ));

16. How to Write a filter statement for where field in (select fieldb From Foo)

Ipredicateexpression filter = new predicateexpression ();

Filter. Add (New fieldcomparesetpredicate (yourentityfields. Field, null, foofields. fieldb, null, setoperator. In, null ));

17. How to Write a where field is null filter statement

Ipredicateexpression filter = new predicateexpression ();

Filter. Add (yourentityfields. Field = dbnull. Value ));

18 how to query dynamics list groups

Resultsetfields fields = new resultsetfields (3 );

Fields. definefield (employeefields. firstname, 0, "firstnamemanager", "manager ");

Fields. definefield (employeefields. lastname, 1, "lastnamemanager", "manager ");

Fields. definefield (employeefields. lastname, 2, "numberofemployees", "employee", aggregatefunction. count); irelationpredicatebucket bucket = new relationpredicatebucket (); bucket. relations. add (employeeentity. relations. employeeentityusingemployeeidreportsto, "employee", "manager", joinhint. none );

Igroupbycollection groupbyclause = new groupbycollection ();

Groupbyclause. Add (fields [0]);

Groupbyclause. Add (fields [1]);

Dataaccessadapter adapter = new dataaccessadapter ();

Datatable tlist = new datatable ();

Adapter. fetchtypedlist (fields, tlist, bucket, 0, null, true, groupbyclause );

19 how to get the sorted value of collection

Entityview2 view = new entityview2 (MERS mers );

View. sorter = (New entityproperty ("propertyname") | sortoperator. ascending );

20. How to obtain the maximum difference between the delivery date and the order date in a purchase order, that is, Max (order. shippingdate-order. orderdate)

Dataaccessadapter adapter = new dataaccessadapter ();

Int maxvalue = (INT) Adapter. getscalar (orderfields. orderid, (orderfields. shippeddate-orderfields. orderdate), aggregatefunction. Max, (orderfields. customerid = _ customerid ));

21. How to add Aggregate functions to queries

Resultsetfields fields = new resultsetfields (2 );

Fields. definefield (customerfieldindex. Country, 0, "country ");

Fields. definefield (customerfieldindex. customerid, 1, "amountcustomers ");

Fields [1]. aggregatefunctiontoapply = aggregatefunction. countdistinct;

22. How to obtain the calculated value of an aggregate function

Dataaccessadapter adapter = new dataaccessadapter ();

Decimal orderprice = (decimal) Adapter. getscalar (orderdetailsfields. orderid, (orderdetailsfields. Quantity * orderdetailsfields. unitprice), vertex. Sum, (orderdetailsfields. orderid = 10254 ));

22. How to use expressions in queries

-- SQL query column rowtotal is an expression

Select orderid, productid, (unitprice * quantity) as rowtotal

From [Order Details]

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

23. How to read Custom Attributes

Dictionary <string, string> customproperties = customerentity. customproperties;

String description = customproperties ["Description"];

You can also use custompropertiesoftype

Dictionary <string, string> customproperties = Customer. custompropertiesoftype;

String description = customproperties ["Description"];

If you want to obtain a specific custom attribute instead of all attributes, you can write

Dictionary <string, string> fieldcustomproperties = customerentity. fieldscustomproperties ["customerid"];

String description = fieldcustomproperties ["Description"];

24 add verification before saving the object

Set the verification class for the object and override validateentitybeforesave.

Public override void validateentitybeforesave (ientitycore involvedentity)

{Customerentity tovalidate = (customerentity) involvedentity;

}

25 how to verify each attribute value of a real break

Protected override bool validatefieldvalue (ientitycore involvedentity, int fieldindex, object value)

{

Bool toreturn = true; Switch (orderfieldindex) fieldindex)

{

Case orderfieldindex. orderid: // ID is valid if the value is> 0

Toreturn = (INT) value> 0); break;
Default:

Toreturn = true; break;

}
Return toreturn;

}

26. How to call a stored procedure

Int outputvalue;

Datatable resultset = retrievalprocedures. mystoredprocedure (1, 2, 3, 4, ref outputvalue );

27. How can I query only the fields specified by the object? Do not query other fields not specified?

For example, customer (customerno, name, address, telephone, contactname, country) only wants to write such a query

Select contactname, country from customer, that is, only query the customer name and country

Excludeincludefieldslist excludedfields = new excludeincludefieldslist (false );

Excludedfields. Add (customerfields. contactname );

Excludedfields. Add (customerfields. Country );
Customercollection MERs = new customercollection ();

Sortexpression sorter = new sortexpression (customerfields. customerid | sortoperator. Descending); customers. getmulti (null, 0, Sorter, null, null, excludedfields, 0, 0 );

In this way, llbl gen will generate SQL: Select contactname, country from customer, instead of querying all fields

If, in turn, excludeincludefieldslist excludedfields = new excludeincludefieldslist ();

If you use this sentence to replace the first one, it indicates that the contactname and country fields are not queried.

28. Search for matching records in the collection

Ipredicate filter = (customerfields. Country = "UK ");

List <int> indexes = mycustomers. findmatches (filter );

29 how to call database functions

Create Function fn_calculateordertotal (@ orderid int, @ usediscounts bit) returns decimal as begin ...... End

Resultsetfields fields = new resultsetfields (1 );

Fields. definefield (orderfields. orderid, 0, "orderid ");

Fields [4]. expressiontoapply = new dbfunctioncall ("DBO", "fn_calculateordertotal", new object [] {orderfields. orderid, 1 });

Datatable Results = new datatable ();

Using (dataaccessadapter adapter = new dataaccessadapter ())

{Adapter. fetchtypedlist (fields, results, null );}

29 serialization and deserialization Custom Attributes

Serialization

Protected override void ongetobjectdata (serializationinfo info, streamingcontext context)

{

Info. Add ("_ ordertotal", _ ordertotal );

}

Deserialization

Protected override void ondeserialized (serializationinfo info, streamingcontext context)

{

_ Ordertotal = info. getdecimal ("_ ordertotal ");

}

Database field attributes are serialized by default. If you add custom attributes to the entity, You need to serialize them as in the preceding example.

30. Obtain the total number of database records

Irelationpredicatebucket filter = new relationpredicatebucket ();

Filter. predicateexpression. Add (customerfields. Country = "France ");

Filter. relations. Add (orderentity. relations. customerentityusingcustomerid );

Dataaccessadapter adapter = new dataaccessadapter ();

Int amount = (INT) Adapter. getdbcount (New orderentityfactory (). createfields (), filter, null, false );

tips: llbl GEN has provided debugger visualizers. During debugging, you can view the pseudo SQL of An ORM statement to facilitate debugging. Put the SD in the frameworks \ llblgen PRO \ runtimelibraries \ debuggervisualizers directory. llblgen. pro. debugvisualizers. copy the DLL to the My Documents \ Visual Studio xxyy \ visualizers directory.