Start with "3" condition query (Criteria Queries) and AspNetPager paging instances

In addition to SQL and HQL queries, nhib.pdf also provides conditional query Criteria. This article will organize some Criteria theories and small instances from the Internet, finally, we can better understand it by combining AspNetPager pages. The paging function is often required in normal projects. I. Conditional query (Criteria Queries) theoretically, to correspond to various query conditions of HQL, nhib.pdf predefines a large number of Expression methods, including the following: eq = condition GT = Greater thanLt = Less thanLike = LikeNot = NotIsNull = Is Null 1.1 condition query (Criteria Queries): an intuitive and scalable condition query API Is characteristic of nhib.pdf. The nhib.pdf. ICriteria interface indicates a query of a specific persistent class. ISession is the factory of the ICriteria instance. // Create the query object ICriteria criteria = session associated with a class. createCriteria (typeof (Person); // Add the expression criteria. add (Expression. eq ("Name", "Jackie Chan"); IList list = criteria. list (); Eq is the abbreviation of Equal, meaning to add a query expression, Person. name = "Jackie Chan" corresponds to HQL: from Person p where p. name = "Jackie Chan" 1.1.1 there is often a multi-condition query in the object, you can also write it as follows, simply create a person object, and then fill in its attributes, no longer need to construct the ugly condition judgment statement: copy the code ICriteria criteria = session. createCriteria (typeof (Pers On); Person person = new Person (); person. name = "Jackie Chan"; person. age = 50; // create an Example object criteria. add (Example. create (person); IList list = criteria. list (); copy Code 1.2 to limit the result set content: A separate query condition is nhib.pdf. expression. an instance of the ICriterion interface. The nhib.pdf. Expression. Expression class defines factory methods for obtaining some built-in ICriterion types. IList cats = sess. createCriteria (typeof (Cat )). add (Expression. like ("Name", "Fritz % ")). add (Expression. between ("Weight", minWeight, maxWeight )). list (); 1.2.1 constraints can be copied by logical grouping code IList cats = sess. createCriteria (typeof (Cat )). add (Expression. like ("Name", "Fritz % ")). add (Expression. or (Expression. eq ("Age", 0), Expression. isNull ("Age "))). list (); IList cats = sess. createCriteria (typeof (Cat )). add (Expression. in ("Name", new String [] {"Fritz", "Izi", "Pk "})). add (Expression. disjunction (). add (Expression. isNull ("Age ")). add (Expression. eq ("Age", 0 )). add (Expression. eq ("Age", 1 )). add (Expression. eq ("Age", 2 )))). list (); copy code 1.2.2 allows you to use SQL directly. The {alias} placeholder should be replaced with the column alias IList cats = sess OF THE queried object. createCriteria (typeof (Cat )). add (Expression. SQL ("lower ({alias }. name) like lower (?) "," Fritz % ", NHibernateUtil. string ). list (); 1.3 result set sorting: nhib.pdf can be used. expression. order to sort the query results. The method has two orders. asc () and Order. desc () IList cats = sess. createCriteria (typeof (Cat )). add (Expression. like ("Name", "F % "). addOrder (Order. asc ("Name ")). addOrder (Order. desc ("Age ")). setMaxResults (50 ). list (); 1.4 The record range is ICriteria criteria = session. createCriteria (typeof (Person); // obtain criteria from the first 10th records. setFirstResu Lt (10); // obtain 20 Record criteria. setMaxResults (20); IList list = criteria. list (); 2: Criteria works with the AspNetPager paging instance function in the first project. You can use this instance to complete a simple paging function. In this article, we will post the main code, the complete source code article is finally available for download; 2.1 CREATE a T_School TABLE and CREATE a stored procedure to insert data cyclically for paging. The script is as follows: copy the code create table [dbo]. [T_School] ([ID] [int] IDENTITY (255) not null, [SchoolName] [nvarchar] () COLLATE Chinese_PRC_CI_AS NULL, [BuildDate] [datetime] NULL, [Address] [nvarchar] (50) COLLAT E Chinese_PRC_CI_AS NULL, [IsSenior] [bit] NULL, [StudentNum] [int] NULL, CONSTRAINT [PK_T_School] primary key clustered ([ID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] copy the code copy Code Create PROCEDURE [dbo]. [Pro_Insert] ASdeclare @ ID intBEGIN set @ ID = 0 while @ ID <200000 begin insert into T_School (SchoolName, BuildDate, Address, IsSenior, StudentNum) VALUES ('secondary Education '+ cast (@ ID as varchar), '2017/4 21:49:32 ', 'xiamen software keypa', 1,390) set @ ID = @ ID + 1 endEND copy code 2.2 define several classes to be used on the object Layer and encapsulate some attributes, the following is only the PageInfo class. For code of other classes, you can directly view the source code. Copy the namespace Wujy code. modelLibrary. pagination {public class PageInfo {public PageInfo () {} public PageInfo (Type entityType, int pageIndex) {this. _ entityType = entityType; this. _ pageIndex = pageIndex;} public PageInfo (Type entityType, int pageIndex, params NCondition [] conditions) {this. _ EntityType = entityType; this. _ pageIndex = pageIndex; this. _ conditions = conditions;} public PageInfo (Type entityType, int pageIndex, NCondition [] conditions, NOrder [] orders) {this. _ entityType = entityType; this. _ pageIndex = pageIndex; this. _ conditions = conditions; this. _ orderFields = orders;} private Type _ entityType; // class public Type EntityType {get {return _ entityType;} set {_ entityType = Value ;}} private int _ pageIndex = 1; // page number public int PageIndex {get {return _ pageIndex;} set {_ pageIndex = value ;}} private NCondition [] _ conditions; // condition public NCondition [] Conditions {get {return _ conditions;} set {_ conditions = value ;}} private NOrder [] _ orderFields; // sort public NOrder [] OrderFields {get {return _ orderFields;} set {_ orderFields = value ;}} private int pageSiz E = 10; public int PageSize {get {return pageSize;} set {pageSize = value ;}} private int _ recordCount; public int RecordCount {get {return _ recordCount ;} set {_ recordCount = value ;}} private int pageCount; public int PageCount {get {return pageCount ;}set {pageCount = value ;}} private System. collections. IList list; public IList List {get {return list;} set {list = value ;}}} Copy code 2.3 to create a base class in the DAL layer and write the paging code here. Below are some important code: copy the code public void DoPager (PageInfo pi) {if (pi. entityType = null) {throw new Exception ("the paging class name cannot be blank");} using (ISession session = new NHibernateHelper (). getSession () {ICriteria qbc = session. createCriteria (pi. entityType); // The total number of items qbc. setProjection (nhib.pdf. criterion. projections. rowCount (); prepareConditions (qbc, pi. conditions); pi. recordCount = Qbc. SetMaxResults (1). UniqueResult <int> (); // the total number of pages pi. PageCount = pi. RecordCount % pi. PageSize = 0? Pi. recordCount/pi. pageSize: pi. recordCount/pi. pageSize + 1; // qbc. setProjection (null); // The paging result ICriteria _ qbc = session. createCriteria (pi. entityType); prepareConditions (_ qbc, pi. conditions); // you can specify prepareOrder (_ qbc, pi. orderFields); // The paging result pi. list = _ qbc. setFirstResult (pi. pageIndex-1) * pi. pageSize ). setMaxResults (pi. pageSize ). list () ;}/// <summary> /// processing condition /// </summary> /// <param n Ame = "qbc"> </param> // <param name = "conditions"> </param> private void prepareConditions (ICriteria qbc, params NCondition [] conditions) {if (qbc = null | conditions. length = 0) {return;} foreach (NCondition condition in conditions) {switch (condition. operate) {case Operation. EQ: qbc. add (Expression. eq (condition. propertyName, condition. propertyValue); break; case Operati On. GT: qbc. add (Expression. gt (condition. propertyName, condition. propertyValue); break; case Operation. LT: qbc. add (Expression. lt (condition. propertyName, condition. propertyValue); break; case Operation. GE: qbc. add (Expression. ge (condition. propertyName, condition. propertyValue); break; case Operation. LE: qbc. add (Expression. le (condition. propertyName, condition. propertyValue); break; case Operation. N E: qbc. add (Expression. not (Expression. eq (condition. propertyName, condition. propertyValue); break; case Operation. BETWEEN: qbc. add (Expression. between (condition. propertyName, (condition. propertyValue as Object []) [0], (condition. propertyValue as Object []) [1]); break; case Operation. LIKE: qbc. add (Expression. like (condition. propertyName, condition. propertyValue. toString (), MatchMode. anywhere )); Break; case Operation. IN: qbc. add (Expression. in (condition. propertyName, condition. propertyValue as object []); break ;}}} /// <summary> /// sort by processing /// </summary> /// <param name = "qbc"> </param> /// <param name = "orderFields"> </param> private void prepareOrder (ICriteria qbc, params Wujy. modelLibrary. pagination. NOrder [] orderFields) {if (qbc = null | orderFields. length = 0) {Return;} foreach (Wujy. ModelLibrary. Pagination. NOrder order in orderFields) {qbc. AddOrder (order. OrderType = Wujy. ModelLibrary. Pagination. NOrder. OrderDirection. ASC? Order. asc (order. propertyName): Order. desc (order. propertyName);} copy the code 2.4 UI Layer and copy the code private void BindData () {ModelLibrary with no conditions in combination with AspNetPager. pagination. pageInfo pi = new ModelLibrary. pagination. pageInfo (typeof (SchoolModel), this. aspNetPager2.CurrentPageIndex); new PaginationBLL (). getToPager (pi); this. aspNetPager2.RecordCount = pi. recordCount; this. dataList1.DataSource = pi. list; this. dataList1.DataBind (); this. label1.Text = "current number" + this. aspNetPager2.CurrentPageIndex + "Page total" + pi. pageCount + "page";} protected void AspNetPager2_PageChanged (object sender, EventArgs e) {BindData ();}