ASP. NET MVC5 for paging queries

For a large number of data query and display use paging is a good choice, this article briefly introduces its own implementation of the idea of paged query.

Paging requires three variables: the total amount of data, the number of data bars displayed per page, and the current page number.

Total data int datacount;//The number of data bars displayed per page int pagedatacount;int pagenumber;

Calculates the total number of pages based on the total amount of data and data bars displayed per page, calculating the starting and ending line numbers of the data read from the database based on the current page number and the number of data bars displayed per page.

Total pages int pagecount = (int) math.ceiling (datacount/(pagedatacount* 1.0)); int startLine = (pageNumber-1) * Pagedatacount + 1;int Endline=startline + PageDataCount-1;

The query operation for the database is implemented using the lightweight ORM framework dapper, with the following specific code:

Protected IDbConnection createconnection () {IDbConnection dbConnection = new SqlConnection (ConnectionString);  Dbconnection.open (); return dbConnection;} Gets the total number of data in the database bar public virtual int Querydatacount (string tableName) {using (IDbConnection dbConnection = createconnection    ()) {var queryresult = dbconnection.query<int> ("SELECT count (Id) from" + TableName);    if (QueryResult = = NULL | |!queryresult.any ()) {return 0;  } return Queryresult.first (); }}public Virtual ienumerable<t> rangequery<t> (string tableName, int startline, int endline) {if (string.  IsNullOrEmpty (TableName)) {throw new ArgumentNullException ("table name must not be null or null");  } if (startline <= 0) {throw new ArgumentOutOfRangeException ("The starting line number must be greater than 0");  } if (Endline-startline < 0) {throw new ArgumentOutOfRangeException ("The end line number must not be less than the starting line number");  } using (IDbConnection dbConnection = CreateConnection ()) {var queryresult = dbconnection.query<t> ("SELECT top "+ (Endline-staRtline + 1) + "* from" + TableName + "where ID not in (select Top" + (STARTLINE-1) + "id from" + tableName + "Ord    ER by id desc) ORDER BY id DESC ");    if (QueryResult! = null && queryresult.any ()) {return queryresult; }} return null;}

Draw a paging button

Add the pagehelper.cshtml file to the App_Code folder to encapsulate the code for the draw button, it is important to note that files in the App_Code folder are not packaged when you use the VS publishing site, and you need to manually copy the files in the App_Code folder to the site.

@* Amount: Total data, Count: The number of data bars displayed per page, Rediercturl on the Jump link page when you click the button: bootstrap.min.css*@ @helper createpaginatebutton (int Amount, int count, string redirecturl) {<p id= "pagenumber" style= "Position:fixed;bottom:-15px;text-align:center"; width:84% "> <nav style=" text-align:center "> <ul class=" pagination "> <li><a href=" @r EDIRECTURL/1 "rel=" external nofollow "> Home </a></li> @{int pagenumber = (int) math.ceiling (AM          Ount/(Count * 1.0)); PageNumber = pagenumber < 1?          1:pagenumber;          The number of buttons displayed on the page (excluding first, last, previous, next, etc.), draw the button delimiter const int button_count = 7 If the total number of pages exceeds this value;          Button Delimiter Const string button_separator = "...";          Button delimiter number of buttons left (excluding first, last, previous, Next, etc buttons) Const int left_button_count = 4;          Button delimiter the number of buttons to the right (excluding the first, last, previous, Next, etc buttons) const int right_button_count = 2;          string[] urlsegments = Request.Url.Segments;          int selectedindex = 0; Int. TryParse (Urlsegments[urlsegments.length-1], out selectedindex); int previous = (selectedIndex-1) <= 0?          1:selectedindex-1; int next = (SelectedIndex + 1 > PageNumber)?          Pagenumber:selectedindex + 1;          var r=request.cookies[""]; if (PageNumber > Button_count) {<li><a id= "next" href= "@redirectUrl/@previous" rel= "external Nofollow "> Previous </a></li> for (int i = 1; I <= button_count; i++) {if (SelectedIndex >= left_button_count && selectedindex%left_button_count==0 && i <= left_button_ COUNT) {<li><a name= "Pagebutton" id= "@selectedIndex" href= "@redirectUrl/@selectedIndex" rel=                "External nofollow" > @selectedIndex </a></li> int step = SelectedIndex;                int tag = 0;           for (i = 1; I <= left_button_count; i++) {tag = i + step;       if (Tag > Pagenumber-right_button_count) {if (I <= left_button_count)                    {i = Left_button_count + 1;                  } break; } <li><a name= "Pagebutton" id= "@tag" href= "@redirectUrl/@tag" rel= "external nofollow" rel= "external nofoll ow "> @tag </a></li>}} else if (I <= left_button_count &&am P  Selectedindex<left_button_count) {<li><a name= "Pagebutton" id= "@i" href= "@redirectUrl/@i" Rel= "External nofollow" rel= "External nofollow" >@i</a></li>} else if (I < L Eft_button_count && selectedindex>left_button_count) {int step = selectedindex/l                Eft_button_count;        int tag = 0; <li><a name= "Pagebutton" id= "@ (step*left_button_count)" href= "@redirecturl/@ (step*left_button_count) "rel=" External nofollow ">@ (step*left_button_count) </a></li>                  for (i = 1; I <= left_button_count; i++) {tag = i + step * left_button_count; if (Tag > Pagenumber-right_button_count) {if (I <= Left_button_coun                    T) {i = Left_button_count + 1;                  } break; } <li><a name= "Pagebutton" id= "@tag" href= "@redirectUrl/@tag" rel= "external nofollow" rel= "external nofoll ow "> @tag </a></li>}}//Draw button delimiter right button if (I==button_co UNT-1) {<li><a name= "Pagebutton" id= "@ (pageNumber-1)" href= "@redirectUrl/@ (pageNumber-1)" Re              L= "External nofollow" >@ (pageNumber-1) </a></li>} else if (I==button_count)   {     <li><a name= "Pagebutton" id= "@pageNumber" href= "@redirectUrl/@pageNumber" rel= "external nofollow" rel= "Exte Rnal nofollow "> @pageNumber </a></li>}//Draw the button separator else if (i >= BU Tton_count-right_button_count) {<li><span name= "Pagebutton" > @BUTTON_SEPARATOR &LT;/SPAN&G T;</li>}} <li><a id= "Next" href= "@redirectUrl/@next" rel= "external Nofollo            W "> next page </a></li>} else {for (int i = 1; I <= pagenumber; i++) {<li><a name= "Pagebutton" id= "@i" href= "@redirectUrl/@i" rel= "external nofollow" rel= "External n Ofollow ">@i</a></li>}} <li><a href=" @redirectUrl/@pageNumbe R "rel=" external nofollow "rel=" external nofollow "> End </a></li> </ul> </nav> </p&gt  ; <script> $ (FuNction () {//Set the background color of the selected button var selected = $ (' # @selectedIndex ');      if (Selected! = undefined) {selected.css (' background-color ', ' #E1E1E1 '); } </script>}

Draw a paging button in the foreground page

@PageHelper. Createpaginatebutton (246, "/usermanager/attentionlist/")

Here are a few paging buttons:

The corresponding HTML code:

The above is the whole content of this article, I hope that everyone's learning has helped, but also hope that we support topic.alibabacloud.com.