LigerGrid Ajax get sorting by PAGE

LigerGrid Ajax get sorting by PAGE

At the beginning, it was a headache to use ligerGrid for paging sorting. The search, top and bottom pages, and other processing backend cannot accurately obtain the values in the front-end lookup control. There is no detailed explanation in the API.

Finally, I found some incomplete information on the Internet for a long time. Now I want to take a note for myself.

 

First, the previous result Graph

 

Note the following points for paging: page size, current page, sorting field, sorting type (reverse or order), and query condition, so here we write a public method to return the required sorting data and the total number of data entries under the query condition.

This is a method for processing the call.

////// Get a page of log data /////////////////////////////////
 Public List
 
  
GetPagerData (out long allCount, int pageSize = 15, int pageIndex = 1, string title = "", string type = "", string date1 = "", string date2 = "", string userName = "", string orderBy = "") {StringBuilder where = new StringBuilder (); List
  
   
ParList = new List
   
    
(); If (! String. isNullOrEmpty (title) {where. append ("and charindex (@ Title, Title)> 0"); parList. add (new SqlParameter ("@ Title", SqlDbType. NVarChar) {Value = title});} if (! String. isNullOrEmpty (type) {where. append ("AND Type = @ Type"); parList. add (new SqlParameter ("@ Type", SqlDbType. NVarChar) {Value = type});} if (date1.IsDateTime () {where. append ("AND WriteTime >=@ Date1"); parList. add (new SqlParameter ("@ Date1", SqlDbType. dateTime) {Value = date1.ToDateTime (). toString ("yyyy-MM-dd 00:00:00")});} if (date2.IsDateTime () {where. append ("AND WriteTime <= @ Date2 "); ParList. add (new SqlParameter ("@ Date2", SqlDbType. dateTime) {Value = date2.ToDateTime (). addDays (1 ). toString ("yyyy-MM-dd 00:00:00")});} if (! UserName. isNullOrEmpty () {where. append ("AND UserName like '%" + userName + "%'");} string SQL = Solution. HPK. coreArea. utility. dataBase. DBHelper. getPaerSql ("Log", "ID, Title, Type, WriteTime, UserID, UserName, IPAddress, URL, Contents, Others, OldXml, NewXml", where. toString (), orderBy, pageSize, pageIndex, out allCount, parList. toArray (); SqlDataReader dataReader = Solution. HPK. coreArea. utility. dataBase. DBHelper. execSelect (SQL, parList. toArray (); List
    
     
List = DataReaderToList (dataReader); dataReader. Close (); return List ;}
    
   
  
 

 

There is a Solution. HPK. CoreArea. Utility. DataBase. DBHelper. GetPaerSql method which is the public method used.

////// Obtain the paging SQL /////////
 Public static string GetPaerSql (string table, string fileds, string where, string order, int size, int number, out long count, SqlParameter [] param = null) {string where1 = string. empty; if (where. isNullOrEmpty () {where1 = "";} else {where1 = where. trim (); if (where1.StartsWith ("and", StringComparison. currentCultureIgnoreCase) {where1 = where1.Substring (3) ;}} string where2 = where1.IsNullO REmpty ()? "": "Where" + where1; string SQL = string. format ("select {0}, ROW_NUMBER () OVER (order by {1}) as PagerAutoRowNumber from {2} {3}", fileds, order, table, where2 ); string count1 = GetFieldValue (string. format ("select count (*) from {0} {1}", table, where2), param); long I; count = count1.IsLong (out I )? I: 0; StringBuilder sql1 = new StringBuilder (); sql1.AppendFormat ("select {0} from (", fileds. IsNullOrEmpty ()? "*": Fileds); sql1.Append (SQL); sql1.AppendFormat (") as PagerTempTable"); if (count> size) {// (page-1) * page size + 1 an page number * page size sql1.AppendFormat ("where PagerAutoRowNumber between {0} and {1}", (number-1) * size + 1, number * size);} return sql1.ToString ();}

 

This method can be used directly. You can understand the specific parameters at a glance.

 

The core method of the data obtained in the background is the front-end UI design.

Var mainGrid = $ ("# mainGrid "). ligerGrid ({columns: [{display: 'sequence number ', name: 'index', align: 'center', width: 40}, {display: 'title', name: 'title', align: 'left'}, {display: 'category', name: 'type', align: 'center', width: 140}, {display: 'IP address', name: 'ipaddress', align: 'center', width: 140}, {display: 'date of creation ', name: 'writetime', align: 'center', width: 140}, {display: 'operator ', name: 'username', align: 'center', width: 120},], usePager: true, height: '200', checkbox: true, rownumbers: false, pageSizeOptions: [16, 32, 48], pageSize: 16, page: 1, url: "Handler/GetPage. ashx ", sortName: 'writetime', sortOrder: 'desc', onToFirst: onToFirst, onToPrev: onToPrev, onToNext: onToNext, onToLast: onToLast });

If you do not understand the ligerGrid method, you can refer to the Api. Pay attention to it.

The url attribute is a path used for Ajax requests.

SortName

The two sortOrder attributes are sorted by which field by default. If this parameter is left blank, the default value must be assigned to the backend. We recommend that you write this attribute directly at the front end.

OnToFirst, onToPrev, onToNext, onToLast homepage, Previous Page, next page, and last page, these events must be handled manually at the front end. Otherwise, the query conditions required on the interface cannot be transferred to the background.

The specific implementation is also very simple. Just write a method with the default value of Ajax parameters. The first four methods can call that method.

// Set the Ajax Parameter function setParms () {mainGrid. setParm ("title", $ ("# txtTitle "). val (); mainGrid. setParm ("type", $ ("# txtType "). val () = "= All = "? "": $ ("# TxtType "). val (); mainGrid. setParm ("userName", $ ("# txtUserName "). val (); mainGrid. setParm ("startDate", $ ("# txtStartDate "). val (); mainGrid. setParm ("endDate", $ ("# txtEndDate "). val ());}

 

// Home page function onToFirst () {setParms () ;}// last page function onToLast () {setParms () ;}// Previous Page function onToPrev () {setParms ();} // function onToNext () {setParms ();}

Note the following when you click the query button.

// Query button $ ("# btnSearch "). ligerButton ({click: function () {// reset the query page number, starting from the first page mainGrid. set ({page: 1, newPage: 1}); // set the Ajax parameter setParms (); // request mainGrid again. loadData (mainGrid. url );}});

// The above sentence mainGrid. set ({page: 1, newPage: 1 });

It took a long time to figure it out.

This is mainly used to solve a problem. For example, if the current page is 3 pages and the total page is 8 pages, if you click search without mainGrid. set ({page: 1, newPage: 1}); this

The results will be set on the 3rd page, so there will be problems. Generally, the first page is displayed when you click search.

The front-end is just like this. Next we will go to the back-end.

By default, ligerGuid Ajax is submitted in post mode. By default, the current page, page size, sorting field, and sorting type can be accepted directly.

// Int page = context on the current page. request. form ["page"]. toInt32 (); // page size int pageSize = context. request. form ["pageSize"]. toInt32 (); // sorting field string sortName = context. request. form ["sortName"]; // sort order string sortOrder = context. request. form ["sortOrder"];

 

Then retrieve the query parameter from the front-end bed and return a json object like {Total: xxxx, Rows: []}. The format of Total and Rows must exist. Otherwise, ligerGuid is not recognized.

For example

Public void ProcessRequest (HttpContext context) {context. response. contentType = "text/plain"; // the current page int page = context. request. form ["page"]. toInt32 (); // page size int pageSize = context. request. form ["pageSize"]. toInt32 (); // sorting field string sortName = context. request. form ["sortName"]; // sort order string sortOrder = context. request. form ["sortOrder"]; // other custom Ajax parameters string title = context. request. form ["title"]; string type = context. request. form ["type"]; string userName = context. request. form ["userName"]; string startDate = context. request. form ["startDate"]; string endDate = context. request. form ["endDate"]; LogBll logBll = new LogBll (); // The total number of queried data long allCount; // query data by PAGE var logList = logBll. getPagerData (out allCount, pageSize, page, title, type, startDate, endDate, userName, sortName + "" + sortOrder); // The returned Json array List
 
  
JsonList = new List
  
   
(); For (int I = 0; I <logList. count; I ++) {var log = logList [I]; jsonList. add ("{\" ID \ ": \" "+ log. ID + "\", \ "Index \": \ "" + (I + 1) + (page-1) * pageSize) + "\", \ "Title \": \ "" + log. title + "\", \ "Type \": \ "" + log. type + "\", \ "IPAddress \": \ "" + log. IPAddress + "\", \ "WriteTime \": \ "" + log. writeTime. toString ("yyyy-MM-dd HH: mm: ss") + "\", \ "UserName \": \ "" + log. userName + "\"} ");} context. response. write ("{\" Total \ ": \" "+ allCount +" \ ", \" Rows \ ": [" + string. join (",", jsonList) + "]}");}
  
 

 

 

 

So much better. It fully complies with the paging, searching, and field sorting functions. This code can be used directly.