asp.net 结合mysql存储过程进行分页代码

不过在网上找了一些，发现都有一个特点——就是不能传出总记录数，干脆自己研究吧。终于，算是搞出来了，效率可能不是很好，但是我也觉得不错了。贴代码吧直接：也算是对自己学习mysql的一个记录。

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; -- 总记录数
set @limitstart = (m_pageno - 1)*m_perpagecnt;
set @limitend = m_perpagecnt;
set @sqlcnt = concat('select count(1) into @pagecnt from ',m_table); -- 这条语句很关键，用来得到总数值
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

/// <summary>
/// 分页显示
/// </summary>
/// <param name="conn">连接数据库字符串</param>
/// <param name="perpage">每页显示条数</param>
/// <param name="columnlist">查询的字段字符</param>
/// <param name="tablename">查询的表名</param>
/// <param name="condition">where条件(不用写where)</param>
/// <param name="orderstr">排序条件(不用写order by)</param>
/// <param name="pageinfo">返回页码的数组，0，1，2，3分别为 总记录集数，总页数，上一页，下一页，4开始为页码</param>
/// <returns>此页的数据记录集</returns>
public static datatable pagelist(string conn, int perpage, string url, string columnlist, string tablename, string condition, string orderstr, out string[] pageinfo)
{
int pageno = 1; //当前页码
int totalcnt = 1; //记录集总数
int pagecnt = 0; //总页数
datatable dt = new datatable(); //用于返回的datatable
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
{
}
}//得到当前页面值
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;
}//如果当前页码大于总页数，则当前页为最后一页
//处理页码地址参数
string pagenumlink = string.empty; //页码的链接地址
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=";
}
//上一页，下一页字符串赋值
string abovepage = pagenumlink + (pageno-1).tostring()+ "\">上一页</a>";
string nextpage = pagenumlink + (pageno + 1).tostring() + "\">下一页</a>";
if (pageno == 1)
{
abovepage = string.empty;
}//如果当前页为第一页，则不显示“上一页”字符串
if (pageno == pagecnt)
{
nextpage = string.empty;
}//如果当前页为最后一页，则不显示“下一页”字符串
pageinfo = new string[14];
pageinfo[0] = totalcnt.tostring(); //显示记录集总数
pageinfo[1] = pagecnt.tostring(); //显示总页数
pageinfo[2] = abovepage; //显示上一页
pageinfo[3] = nextpage; //显示下一页
int startindex;
int endindex;
startindex = (pageno / 10) * 10 + 1; //起始页
if (pageno % 10 == 0)
{
startindex = startindex - 10;
}
endindex = startindex + 9; //结束页
if (endindex > pagecnt)
{
endindex = pagecnt;
}//如果结束页大于总页数，则结束页为最后一页值
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;
}