ASP. NET and MSSQL high-performance paging, asp. netmssql high-performance

ASP. NET and MSSQL high-performance paging, asp. netmssql high-performance

The first is the stored procedure. Only the data that I need is retrieved. If the number of pages exceeds the total number of data records, the records on the last page are automatically returned:

Set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO -- ============================== ============ -- Author: clear -- Description: High-Performance paging -- http://www.cnblogs.com/roucheng/-- ========================================================== ===== Alter PROCEDURE [dbo]. [Tag_Page_Name_Select] -- input the maximum number of records displayed and the current page number @ MaxPageSize int, @ PageNum int, -- SET an output parameter to return the total number of records for the paging list to use @ Count int outputASBEGIN set nocount on; DECLARE -- Define the sorting Name parameter @ Name nvarchar (50 ), -- Define the Cursor position @ Cursor int -- first obtain the total number of records Select @ Count = count (tag_Name) FROM [viewdatabase0716]. [dbo]. [view_tag]; -- defines the position where the Cursor needs to start Set @ Cursor = @ MaxPageSize * (@ PageNum-1) + 1 -- IF the Cursor is greater than the total number of records, place the Cursor at the beginning of the last page IF @ Cursor> @ Count BEGIN -- IF the last page is equal to the maximum number of records each time, return to the last page IF @ Count % @ MaxPageSize = 0 begin if @ Cursor> @ MaxPageSize Set @ Cursor = @ Count-@ MaxPageSize + 1 ELSE Set @ Cursor = 1 END -- otherwise, return the last ELSE Set @ Cursor = @ Count-(@ Count % @ MaxPageSize) + 1 END -- point the pointer to this page and start Set Rowcount @ Cursor -- get the start position of the record Select @ Name = tag_Name FROM [viewdatabase0716]. [dbo]. [view_tag] orDER BY tag_Name; -- Set the start position Set Rowcount @ MaxPageSize -- Obtain the record Select * From [viewdatabase0716]. [dbo]. [view_tag] Where tag_Name >=@ Name order By tag_Name Set Rowcount 0END

Then there is the paging control (... For the omitted HTML code generation method ):

Using System. data; using System. configuration; using System. web; using System. web. security; using System. web. UI; using System. web. UI. webControls; using System. web. UI. webControls. webParts; using System. web. UI. htmlControls; using System. text; // <summary> /// extended connection string /// </summary> public class ExStringBuilder {private StringBuilder InsertString; private StringBuilder PageString; private int PrivatePageNum = 1; private int PrivateMaxPageSize = 25; private int PrivateMaxPages = 10; private int PrivateCount; private int PrivateAllPage; public ExStringBuilder () {InsertString = new StringBuilder ("");} /// <summary> /// the generated HTML // </summary> public string GetHtml {get {return InsertString. toString () ;}/// <summary> // obtain the generated page HTML /// </summary> public string GetPageHtml {get {return PageString. toString () ;}/// <summary> /// set or obtain the current page number /// </summary> public int PageNum {get {return PrivatePageNum ;} set {if (value >=1) {PrivatePageNum = value ;}}} /// <summary> /// set or obtain the maximum number of pages /// </summary> public int MaxPageSize {get {return PrivateMaxPageSize ;} set {if (value >=1) {PrivateMaxPageSize = value ;}}} /// <summary> /// set or obtain the maximum number of pages displayed each time. // </summary> public int MaxPages {get {return PrivateMaxPages ;} set {PrivateMaxPages = value ;}/// <summary> // set or retrieve the total number of data /// </summary> public int DateCount {get {return PrivateCount ;} set {PrivateCount = value ;}/// <summary> // obtain the total number of data pages /// </summary> public int AllPage {get {return PrivateAllPage ;}} /// <summary> /// initialize the page /// </summary> public void Pagination () {PageString = new StringBuilder (""); // obtain the total number of pages PrivateAllPage = (int) Math. ceiling (decimal) PrivateCount/(decimal) PrivateMaxPageSize); // prevents superscripts or subscripts from crossing the border if (PrivatePageNum> PrivateAllPage) {PrivatePageNum = PrivateAllPage ;} // scroll cursor paging: int LeftRange, RightRange, LeftStart, RightEnd; LeftRange = (PrivateMaxPages + 1)/2-1; RightRange = (PrivateMaxPages + 1)/2; if (PrivateMaxPages> = PrivateAllPage) {LeftStart = 1; RightEnd = PrivateAllPage;} else {if (condition <= LeftRange) {LeftStart = 1; RightEnd = LeftStart + PrivateMaxPages-1 ;} else if (response-initiated <RightRange) {RightEnd = PrivateAllPage; LeftStart = RightEnd-PrivateMaxPages + 1;} else {LeftStart = response-LeftRange; RightEnd = Response + RightRange ;}} // generate PageString for page number list statistics. append (...); stringBuilder previusstring = new StringBuilder (""); // if on the first page if (PrivatePageNum> 1 ){...} else {...} // if the first page is in if (PrivatePageNum> PrivateMaxPages ){...} else {...} pageString. append (previusstring); // generates an intermediate page http://www.cnblogs.com/roucheng/ For (int I = LeftStart; I <= RightEnd; I ++) {// if (I = PrivatePageNum ){...} else {...}} stringBuilder LastString = new StringBuilder (""); // if in the last page if (PrivatePageNum <PrivateAllPage ){...} else {...} // if in the last group if (PrivatePageNum + PrivateMaxPages) <PrivateAllPage ){...} else {...} pageString. append (LastString) ;}/// <summary> /// generate a Tag classification table /// </summary> public void TagTable (ExDataRow myExDataRow) {InsertString. append (...);}

Call method:

// Get the page setting and put it in Session ExRequest myExRequest = new ExRequest (); myExRequest. pageSession ("Tag _", new string [] {"page", "size"}); // generate Tag pagination ExStringBuilder Tag = new ExStringBuilder (); // set how many record tags are displayed each time. maxPageSize = Convert. toInt32 (Session ["Tag_size"]); // you can specify the maximum number of tags displayed. maxPages = 9; // set the current page Tag. pageNum = Convert. toInt32 (Session ["Tag_page"]); string [] [] myNamenValue = new string [2] [] {new string [] {"MaxPageSize", "PageNum ", "Count"}, new string [] {Tag. maxPageSize. toString (), Tag. pageNum. toString () }}; // call the Stored Procedure DataTable myDataTable = mySQL. batchGetDB ("Tag_Page_Name_Select", myNamenValue, "Count"); Tag. dateCount = (int) mySQL. outputCommand. parameters ["@ Count"]. value; Tag. pagination (); HeadPage. innerHtml = FootPage. innerHtml = Tag. getPageHtml; for (int I = 0, j = myDataTable. rows. count; I <j; I ++) {Tag. tagTable (new ExDataRow (myDataTable. rows);} TagBox. innerHtml = Tag. getHtml;