asp.net with MySQL stored procedures for pagination code _ Practical Skills

But find some on the internet, found that there is a feature-that is, the total number of records can not be outgoing, simply study it yourself. At last, the efficiency may not be very good, but I feel good too. Post code directly: It's also a record of learning MySQL for yourself.

Copy Code code as follows:

CREATE PROCEDURE P_pagelist
(
M_pageno int,
m_perpagecnt int,
M_column varchar (1000),
M_table varchar (1000),
M_condition varchar (1000),
M_orderby varchar (200),
Out m_totalpagecnt int
)
BEGIN
SET @pageCnt = 1; --Total number of records
SET @limitStart = (m_pageno-1) *m_perpagecnt;
SET @limitEnd = m_perpagecnt;
SET @sqlCnt = CONCAT (' Select COUNT (1) into @pageCnt from ', m_table); --This statement is critical to get the total value
SET @sql = CONCAT (' Select ', M_column, ' from ', m_table);
IF M_condition is not NULL and m_condition <> ' THEN
SET @sql = CONCAT (@sql, ' where ', m_condition);
SET @sqlCnt = CONCAT (@sqlCnt, ' where ', m_condition);
End IF;
IF M_orderby is not NULL and M_orderby <> ' THEN
SET @sql = CONCAT (@sql, ' ORDER by ', M_orderby);
End IF;
SET @sql = CONCAT (@sql, ' limit ', @limitStart, ', ', @limitEnd);
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
Deallocate PREPARE s_cnt;
SET m_totalpagecnt = @pageCnt;
PREPARE record from @sql;
EXECUTE record;
Deallocate PREPARE Record;
End

asp.net

Copy Code code as follows:

<summary>
Paging display
</summary>
<param name= "conn" > Connection Database String </param>
<param name= "Perpage" > per-page display number of bars </param>
<param name= "columnlist" > Query field Characters </param>
<param name= "tablename" > Query table name </param>
<param name= "condition" >where conditions (do not write where) </param>
<param name= "Orderstr" > Sorting criteria (Do not write order by) </param>
<param name= "PageInfo" > Returns an array of page numbers, 0,1,2,3 total Recordset, total number of pages, previous page, next page, 4 starting page </param>
<returns> data recordset for this page </returns>
public static DataTable PageList (String conn, int perpage, string url, String columnlist, String tablename, String Conditi On, String orderstr, out string[] pageInfo)
{
int pageno = 1; Current page number
int totalcnt = 1; Total set of records
int pagecnt = 0; Total pages
DataTable dt = new DataTable (); The DataTable used to return
using (mysqlconnection myconn = new Mysqlconnection (conn))
{
Mysqldataadapter ADP = new Mysqldataadapter ();
Mysqlcommand cmd = new Mysqlcommand ();
if (!string. IsNullOrEmpty (system.web.httpcontext.current.request["PageNo"))
{
Try
{
pageno = Int. Parse (system.web.httpcontext.current.request["PageNo"). ToString ());
}
Finally
{
}
}//get current page face value
Cmd. Connection = myconn;
MyConn.Open ();
Cmd.commandtext = "P_pagelist";
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.Add ("M_pageno", Mysqldbtype.int32, 11);
Cmd. Parameters.Add ("m_perpagecnt", Mysqldbtype.int32, 11);
Cmd. Parameters.Add ("M_column", Mysqldbtype.varchar, 1000);
Cmd. Parameters.Add ("M_table", Mysqldbtype.varchar, 1000);
Cmd. Parameters.Add ("M_condition", Mysqldbtype.varchar, 1000);
Cmd. Parameters.Add ("M_orderby", Mysqldbtype.varchar, 200);
Cmd. Parameters.Add ("m_totalcnt", Mysqldbtype.int32, 11);
Cmd. parameters["M_pageno"]. Value = PageNo;
Cmd. parameters["m_perpagecnt"]. Value = Perpage;
Cmd. parameters["M_column"]. Value = columnlist;
Cmd. parameters["M_table"]. Value = tablename;
Cmd. parameters["M_condition"]. Value = condition;
Cmd. parameters["M_orderby"]. Value = Orderstr;
Cmd. parameters["m_totalcnt"]. Direction = ParameterDirection.Output;
Cmd. ExecuteNonQuery ();
Adp. SelectCommand = cmd;
Adp. Fill (DT);
totalcnt = Int. Parse (cmd. parameters["m_totalcnt"]. Value.tostring ());
}
pagecnt = Convert.ToInt32 (math.ceiling (convert.todouble (totalcnt)/perpage));
if (PageNo > Pagecnt)
{
PageNo = pagecnt;
}//If the current page is greater than the total number of pages, the current is the last page
Handling page Number Address parameters
String Pagenumlink = String. Empty; Link Address of page number
if (string. IsNullOrEmpty (URL. Trim ())
{
Pagenumlink = "<a href=\"? pageno= ";
}
else if (URL. IndexOf ('? ') >= 0)
{
Pagenumlink = "<a href=\" "+ URL +" &pageno= ";
}
Else
{
Pagenumlink = "<a href=\" + URL + "? pageno=";
}
Previous page, next page string assignment
String abovepage = Pagenumlink + (pageNo-1). ToString () + "\" > Prev </a>;
String nextPage = Pagenumlink + (PageNo + 1). ToString () + "\" > Next page </a> ";
if (PageNo = 1)
{
Abovepage = string. Empty;
}//If the current page is the first page, the previous page string is not displayed
if (PageNo = = pagecnt)
{
NextPage = string. Empty;
}//the next page string is not displayed if the current page is the last page
PageInfo = new STRING[14];
Pageinfo[0] = totalcnt.tostring (); Display recordset Totals
PAGEINFO[1] = pagecnt.tostring (); Show Total Pages
PAGEINFO[2] = abovepage; Show Previous Page
PAGEINFO[3] = nextPage; Show Next Page
int startIndex;
int endindex;
StartIndex = (PAGENO/10) * 10 + 1; Start Page
if (pageno% 10 = 0)
{
StartIndex = startIndex-10;
}
Endindex = StartIndex + 9; End Page
if (Endindex > Pagecnt)
{
Endindex = pagecnt;
}//If the end page is greater than the total number of pages, the end page is the last page value
int pageIndex = 4;
for (int i = StartIndex i <= endindex; i++)
{
String pagevalue = Pagenumlink + i.tostring () + ">" + i.tostring () + "</a>";
if (i = = PageNo)
{
Pagevalue = i.ToString ();
}
Pageinfo[pageindex] = Pagevalue;
pageindex++;
}
return DT;
}