Asp.net paging Stored Procedure + paging class + paging Control

• The paging stored procedure is as follows: pages. SQL:

Create procedure pages <br/> @ tblname varchar (255), -- table name <br/> @ strgetfields varchar (1000) = '*', -- columns to be returned <br/> @ fldname varchar (255) = '', -- Name of the sorted field <br/> @ pagesize Int = 10, -- page size <br/> @ pageindex Int = 1, -- page number <br/> @ docount bit = 0, -- total number of returned records, if the value is not 0, the return value is <br/> @ ordertype bit = 0, -- set the sorting type. If the value is not 0, the return value is in descending order. <br/> @ strwhere varchar (1500) = '', -- Query condition (Note: Do not add where) <br/> @ primarykey varchar (60) <br/> as <br/> d Eclare @ strsql varchar (5000) -- subject sentence <br/> declare @ strorder varchar (400) -- sorting type <br/> declare @ strtmp1_varchar (10) <br/> set @ strtmpsequence = 'tmpsequence '-- temporary field name !!! Important !!! <Br/> If @ docount! = 0 <br/> begin <br/> If @ strwhere! = ''<Br/> set @ strsql = 'select count (*) as total from '+ @ tblname + 'where' + @ strwhere <br/> else <br/> set @ strsql = 'select count (*) as total from '+ @ tblname + ''<br/> end <br/> -- the preceding Code indicates that if @ docount is not 0, the total number of statistics is executed. All the following codes are <br/> -- yes when @ docount is 0 <br/> else <br/> begin <br/> If @ ordertype! = 0 <br/> set @ strorder = 'ORDER BY' + @ fldname + 'desc' -- If @ ordertype is not 0, execute descending order. This sentence is very important! <Br/> else <br/> set @ strorder = 'ORDER BY' + @ fldname + 'asc '<br/> If @ pageindex = 1 <br/> begin <br /> If @ strwhere! = ''<Br/> set @ strsql = 'select top' + STR (@ pagesize) + ''+ @ strgetfields + 'from' + @ tblname + 'where' + @ strwhere +'' + @ strorder <br/> else <br/> set @ strsql =' select top '+ STR (@ pagesize) + ''+ @ strgetfields + 'from' + @ tblname +'' + @ strorder <br/> -- execute the above Code on the first page, this will speed up the execution <br/> end <br/> else <br/> begin <br/> -- the following code gives @ strsql the SQL code to be actually executed <br/> If @ strwhere = ''<br/> set @ strsql = 'select top' + STR (@ pagesize) + ''+ @ strgetfields + 'from' + @ tblname + 'where' + @ primarykey + 'not in ('+ 'select top' + STR (@ pageIndex-1) * @ pagesize) + ''+ @ primarykey + 'from' + @ tblname +'' + @ strorder + ') '+ @ strorder <br/> else <br/> set @ strsql = 'select top' + STR (@ pagesize) + ''+ @ strgetfields + 'from' + @ tblname + 'where' + @ primarykey + 'not in ('+ 'select top' + STR (@ pageIndex-1) * @ pagesize) + ''+ @ primarykey + 'from' + @ tblname +'' + 'where' + @ strwhere + ''+ @ strorder + ') and '+ @ strwhere + ''+ @ strorder <br/> end <br/> -- print @ strsql <br/> exec (@ strsql) <br/> 

• Pages. CS:
• Using system; <br/> using system. data; <br/> using system. configuration; <br/> using system. LINQ; <br/> using system. web; <br/> using system. web. security; <br/> using system. web. ui; <br/> using system. web. UI. htmlcontrols; <br/> using system. web. UI. webcontrols; <br/> using system. web. UI. webcontrols. webparts; <br/> using system. XML. LINQ; <br/> using system. data. sqlclient; <br/> /// <summary> <br/> // Summary of class1 <Br/> // </Summary> <br/> public class pages <br/> {<br/> private string tblnames = ""; // The name of the table to be queried. required. <br/> private string fldnames = ""; // The sorting field. required. <br/> private string returnfields = ""; // returned field <br/> private int pagesize = 10; // page size <br/> private int pageindex = 1; // page number <br/> // Private byte docount = 0; // whether to return the total number of records. 0 indicates the total number of returned records, if the value is not 0, the query is <br/> private byte ordertype = 0; // The sorting method. If the value is 0, the query is in ascending order. If the value is not 0, the query is in descending order. <br/> private s Tring condition = ""; // query condition. Do not add "where" <br/> private string m_primarykey; <br/> /// <summary> <br/> // sorting method <br/> /// </Summary> <br/> Public Enum ordertypes {ASC, desc };< br/> # region attributes <br/> /// <summary> <br/> // name of the queried table. Multiple tables are separated by commas, required <br/> /// </Summary> <br/> Public String tablenames <br/> {<br/> get {return tblnames ;} <br/> set {tblnames = value ;}< br/>}< br/> /// <summary> <br/> /// Returned field. All columns are <br/> /// </Summary> <br/> Public String returnfields <br/>{< br/> get {return returnfields;} by default ;} <br/> set {returnfields = value ;}< br/>}< br/> /// <summary> <br/> // sorting field, multiple fields are separated by commas, required <br/> /// </Summary> <br/> Public String orderfields <br/> {<br/> get {return fldnames ;} <br/> set {fldnames = value ;}< br/>}< br/> /// <summary> <br/> // query condition, do not add "where" <br/> /// </Summary> <Br/> Public String condition <br/>{< br/> get {return condition ;}< br/> set {condition = value ;} <br/>}< br/> /// <summary> <br/> // page size, default Value: 10 <br/> /// </Summary> <br/> Public int pagesize <br/>{< br/> get {return pagesize ;} <br/> set {pagesize = value ;} <br/>}< br/> /// <summary> <br/> // The total number of records <br/> /// </Summary> <br/> Public int recordcount <br/>{< br/> get {return getrecord Count () ;}< br/>}< br/> /// <summary> <br/> // page number, default Value: 1 <br/> /// </Summary> <br/> Public int pageindex <br/> {<br/> get {return pageindex ;} <br/> set {pageindex = value ;} <br/>}< br/> //// <summary> <br/> //// total number of returned records, no result is returned by default. <br/> //// </Summary> <br/> // public bool docount <br/> // {<br/> // get {return docount = 0? False: True ;}< br/> // set {docount = (byte) (value = true? 1: 0) ;}< br/>/}< br/> /// <summary> <br/> // sorting method, default ASC <br/> /// </Summary> <br/> Public ordertypes ordertype <br/> {<br/> get {return ordertype = 0? Ordertypes. ASC: ordertypes. DESC;} <br/> set {ordertype = (byte) (value = ordertypes. ASC? 0: 1) ;}< br/>}< br/> Public String primarykey <br/>{< br/> get {return m_primarykey ;} <br/> set {m_primarykey = value ;}< br/>}< br/> # endregion <br/> Public pages () <br/> {<br/> // todo: add the constructor logic here <br/> // <br/>}< br/> /// <summary> <br/> /// <br/> // // </Summary> <br/> // <Param name = "tablenames"> name of the queried table, multiple tables are separated by commas </param> <br/> // <Param name = "orderfields"> sort fields. Separate multiple fields with commas </para M> <br/> Public pages (string tablenames, string orderfields, string primarykey) <br/>{< br/> tblnames = tablenames; <br/> fldnames = orderfields; <br/> m_primarykey = primarykey; <br/>}< br/> /// <summary> <br/> // return to the query page <br/> /// </Summary> <br/> /// <returns> </returns> <br/> Public dataset getpage () <br/>{< br/> return query (makequerystring ()); <br/>}< br/> /// <summary> <br/> // query the total number of records <br // >/// </Summary> <br/> /// <returns> </returns> <br/> Public int getrecordcount () <br/>{< br/> string strsql = string. format ("{0}, @ docount = 1", makequerystring (); <br/> dataset DST = query (strsql); <br/> return convert. toint32 (DST. tables [0]. rows [0] [0]); <br/>}< br/> private dataset query (string strsql) <br/>{< br/> sqlconnection con = new sqlconnection (configurationmanager. connectionstrings ["O Nlineresconnectionstring "]. tostring (); <br/> sqlcommand cmd = new sqlcommand (strsql, con); <br/> dataset DST = new dataset (); <br/> sqldataadapter da = new sqldataadapter (CMD); <br/> da. fill (DST, "page"); <br/> return DST; <br/>}< br/> /// <summary> <br/> // combined execution statement <br/> /// </Summary> <br/> /// <returns> </returns> <br/> private string makequerystring () <br/>{< br/> string strsql = string. form At ("pages @ tblname = '{0}', @ fldname = '{1}', @ primarykey = '{2}'", tblnames, fldnames, m_primarykey ); <br/> If (returnfields. length! = 0) <br/> strsql + = (", @ strgetfields = '" + returnfields + "'"); <br/> If (condition. length! = 0) <br/> strsql + = (", @ strwhere = '" + condition + "'"); <br/> If (pagesize! = 10) <br/> strsql + = (", @ pagesize =" + pagesize); <br/> If (pageindex! = 1) <br/> strsql + = (", @ pageindex =" + pageindex); <br/> If (ordertype! = 0) <br/> strsql + = (", @ ordertype =" + ordertype); <br/> // If (docount! = 0) <br/> // strsql + = (", @ docount =" + docount); <br/> return strsql; <br/>}< br/> // using system; <br/> // using system. collections; <br/> // using system. LINQ; <br/> // using system. web; <br/> // using system. web. services; <br/> // using system. web. services. protocols; <br/> // using system. XML. LINQ; <br/> // using system. configuration; <br/> // using system. data. SQL; <br/> // using system. data. sqlclient; <B R/> // using system. data; <br/> //// <summary> <br/> //// Summary of WebService <br/> ///// </Summary> <br // [WebService (namespace = "http://tempuri.org/")] <br/> // [webservicebinding (conformsto = wsiprofiles. basicprofile1_1)] <br/> /// to allow ASP. net Ajax calls this web service from the script. Please cancel the comments to the downstream. <Br/> /// [system. web. script. services. scriptservice] <br/> // public class WebService: system. web. services. webService {<br/> // public static string resname = ""; <br/> // public static string conectionstring = configurationmanager. connectionstrings ["onlineresconnectionstring"]. tostring (); <br/> // public static string tbname = "@ tblname"; <br/> // public static string tbstrgetfields = "@ strgetfields "; <br/> // public static string tbfldname = "@ fldname"; <br/> // public static string tbpagesize = "@ pagesize "; <br/> // public static string tbpageindex = "@ pageindex"; <br/> // public static string tbdocount = "@ docount "; <br/> // public static string tbordertype = "@ ordertype"; <br/> // public static string tbstrwhere = "@ strwhere "; <br/> // public WebService () <br/> // {<br/> //} <br/> // public dataset getcustomerds () <br/> // {<br/> // sqlconnection conn = new sqlconnection (conectionstring); <br/> // Conn. open (); <br/> // sqlcommand cmd = new sqlcommand (); <br/> // cmd. connection = conn; <br/> // cmd. commandtext = "pages"; <br/> // cmd. commandtype = commandtype. storedprocedure; <br/> // sqlparameter param1 = new sqlparameter (tbname, sqldbtype. nvarchar); <br/> // sqlparameter param2 = new sqlparameter (tbstrgetfields, sqldbtype. nvarchar); <br/> // sqlparameter param3 = new sqlparameter (tbfldname, sqldbtype. nvarchar); <br/> // sqlparameter param4 = new sqlparameter (tbpagesize, sqldbtype. INT); <br/> // sqlparameter param5 = new sqlparameter (tbpageindex, sqldbtype. INT); <br/> // sqlparameter param6 = new sqlparameter (tbdocount, sqldbtype. bit); <br/> // sqlparameter param7 = new sqlparameter (tbordertype, sqldbtype. bit); <br/> // sqlparameter param8 = new sqlparameter (tbstrwhere, sqldbtype. nvarchar); <br/> // param1.value = "customer"; <br/> // param2.value = "username"; <br/> // param3.value = "score "; <br/> // param4.value = 5; <br/> // param5.value = 1; <br/> // param6.value = 0; <br/> // param7.value = 1; <br/> // param8.value = ""; <br/> // cmd. parameters. add (param1); <br/> // cmd. parameters. add (param2); <br/> // cmd. parameters. add (param3); <br/> // cmd. parameters. add (param4); <br/> // cmd. parameters. add (param5); <br/> // cmd. parameters. add (param6); <br/> // cmd. parameters. add (param7); <br/> // cmd. parameters. add (param8); <br/> // dataset DS = new dataset (); <br/> // sqldataadapter SDA = new sqldataadapter (CMD ); <br/> // SDA. fill (DS); <br/> // Ds. tables [0]. columns [0]. columnname = "member"; <br/> // Ds. tables [0]. columns [1]. columnname = "Popularity Index"; <br/> // return Ds; <br/>//} <br/> // public dataset getincluantname () <br/> // {</P> <p> // sqlconnection conn = new sqlconnection (conectionstring); <br/> // Conn. open (); <br/> // sqlcommand cmd = new sqlcommand (); <br/> // cmd. connection = conn; <br/> // cmd. commandtext = "select * from restaurant where id = '" + resname + "'"; <br/> // dataset DS = new dataset (); <br/> // sqldataadapter SDA = new sqldataadapter (CMD); <br/> // SDA. fill (DS); <br/> // return Ds; <br/>/}</P> <p> //} <br/> 
• The page control aspnetpager. dll is downloaded from my resources or searched for the control "Page Control aspnetpager. dll" on the download page ".
• The following describes how to use these three to build Asp.net pages:

1. Create a new project, add reference aspnetpager. dll, add a tab in the toolbox, and add a selection item.
2. Add pages. CS.
3. Run the stored procedure pages. SQL IN THE sqlserver2000 Enterprise Manager.
4. The test code is as follows:
5. Private void binddata () <br/>{< br/> pages P = new pages ("restaurant, registrant anttype", "avgscore", "restaurant. ID "); <br/> P. returnfields = "restaurant. ID, restaurant. name, incluanttype. name resttype, address, Introduction, avgscore, num "; <br/> P. condition = "restaurant. resttypeid = required anttype. ID "; <br/> P. primarykey = "restaurant. ID "; <br/> P. ordertype = pages. ordertypes. DESC; <br/> P. pagesize = aspnetpager1.pagesize; <br/> P. pageindex = aspnetpager1.currentpageindex; <br/> aspnetpager1.recordcount = P. recordcount; <br/> dataset DST = P. getpage (); <br/> rptrestaurant. datasource = DST; <br/> rptrestaurant. databind (); <br/>}