发布于 2016-05-25 07:37:33 | 150 次阅读 | 评论: 0 | 来源: 网友投递

这里有新鲜出炉的精品教程,程序狗速度看过来!

ASP.NET

ASP.NET 是.NET FrameWork的一部分,是一项微软公司的技术,是一种使嵌入网页中的脚本可由因特网服务器执行的服务器端脚本技术,它可以在通过HTTP请求文档时再在Web服务器上动态创建它们。 指 Active Server Pages(动态服务器页面) ,运行于 IIS(Internet Information Server 服务,是Windows开发的Web服务器)之中的程序 。


最近用mysql + 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; 
}


最新网友评论  共有(0)条评论 发布评论 返回顶部

Copyright © 2007-2017 PHPERZ.COM All Rights Reserved   冀ICP备14009818号  版权声明  广告服务