简单、高效这是我们追求的分页效果。
现在有三种很常见的分页：
1、分页用的html和后台代码都自己写 ，设计和代码一般都只对应某个网页，难以在其他页面很好的重用
2、最简单的当然是数据控件自带的分页功能，他的那些缺陷已经被讨论很多年了，我就不重复了，相信
稍微有点魄力和职业态度的程序员都不会用那个分页
3、自制的分页控件，可以实现代码和设计的分离，可以在多个页面重用控件，但是缺陷是：每个页面都得调用控件
而且还要在页面的后台代码里初始化控件，例如向控件里传送总页数、当前分页序号、页面大小等
综合以上分析，我打算自己做个简单的分页控件，思路如下：
1、首先必须实现分页时代码和设计的分离，例如“下一页”，“上一页”，他们的样式写在一个文件里，而
把控制他们怎么显示写在另一个文件里，例如，到了最后一页，“最后一页”这个按钮不能用。
所以我写了个template.html文件，这个描述了分页时的样式
Code
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<div style="width: 100%; height: 30px; overflow: hidden; clear: both; font-size: 12px;" id="MyPagingString{10}">
<div style="float: left; width: 50px; line-height: 20px; text-align: center; height: 20px;
border: 1px solid #d8dfea; margin-left: 15px; cursor: pointer; display: {0}"
onmouseover="p.on({1},this)" onmouseout="p.out({1},this)" onclick="p.direct({1},'first','{10}')">
第一页
</div>
<div style="float: left; width: 50px; line-height: 20px; text-align: center; height: 20px;
border: 1px solid #d8dfea; margin-left: 15px; cursor: pointer; display: {0}"
onmouseover="p.on({2},this)" onmouseout="p.out({2},this)" onclick="p.direct({2},'previous','{10}')">
上一页
</div>
<div style="float: left; width: 50px; line-height: 20px; text-align: center; height: 20px;
border: 1px solid #d8dfea; margin-left: 15px; cursor: pointer; display: {0}"
onmouseover="p.on({3},this)" onmouseout="p.out({3},this)" onclick="p.direct({3},'next','{10}')">
下一页
</div>
<div style="float: left; width: 65px; line-height: 20px; text-align: center; height: 20px;
border: 1px solid #d8dfea; margin-left: 15px; cursor: pointer; display: {0}"
onmouseover="p.on({4},this)" onmouseout="p.out({4},this)" onclick="p.direct({4},'end','{10}',{11})">
最后一页
</div>
<div style="float: left; height: 20px; line-height: 20px; margin-left: 20px; display: {9}">
每页记录：<span style=" color:Red;">{5}</span> 当前页： <span style=" color:red;">{6} </span> 总页数： <span style=" color:Red;">{7}</span> 总记录数： <span style=" color:Red;">{8}</span>
</div>
</div>
<script type="text/javascript">
var divTab='MyPagingString{10}';
var div=[{1},{2},{3},{4}];
var p = {
init: function() {
var pstr=document.getElementById(divTab).getElementsByTagName("div");
if (!div[0]){
pstr[0].style.color = '#ccc';}
if (!div[1]){
pstr[1].style.color = '#ccc';}
if (!div[2]){
pstr[2].style.color = '#ccc';}
if (!div[3]){
pstr[3].style.color = '#ccc';}
},
on: function(v, this_) {
if (v) {
this_.style.backgroundColor = '#3b5998'; this_.style.color = '#fff';
}},
out: function(v, this_) {
if (v) {
this_.style.backgroundColor = '#fff'; this_.style.color = '#000';
}},
direct:function(v,t,i){
if (!v) {return;}
var index=parseInt(i.split('|')[1]);
var temp=i.split('|')[0]+'_paging_index=';
var _cookie=document.cookie;
var cookiekey=_cookie.substring(_cookie.indexOf(temp)+temp.length,_cookie.indexOf(";",_cookie.indexOf(temp)));
document.cookie="paging_table="+i.split('|')[0];
switch(t){
case "first":
document.cookie=temp+"0";
break;
case "previous":
document.cookie=temp+(--index);
break;
case "next":
document.cookie=temp+(++index);
break;
case "end":
document.cookie=temp+arguments[3];
break;
}
document.cookie="paging=1";
document.forms[0].submit();
}};
p.init();
</script>
</body>
</html>
当程序第一次加载时，从硬盘读取分页模板文件template.html并且放入缓存，如果第二次有分页请求时就从缓存读取，
如果，template.html,被修改则再次从硬盘读取，类似asp.net里的配置文件读取机制，
缓存代码如下：
Code
public static string GetPageHtml()
{
pagingHtml = (string)(HttpContext.Current.Cache["paging"]);
if (string.IsNullOrEmpty(pagingHtml))
{
string path = null;
CacheDependency cd;
path = GetPagingTemplePath();
cd = new CacheDependency(path);
ReadPagingHtmlsFromDisk(path);
HttpContext.Current.Cache.Insert("paging", pagingHtml, cd);
}
return pagingHtml;
}
2、对数据源的获取的sql实现了优化，下面是两种常用的分页语句，第二条语句的优势在于：
not in 会引起全表扫描，而且不会使用聚集索引，而第二条语句没有这样的缺陷
select top size * from table where id not in (select top index*size id from table )
select top size * from table where id > (select max (id) from (select top index*size id from table) as T )
对用户输入的sql语句，例如“select * from table”
自动优化成上面的第二种格式
下面这个方法实现了复杂sql语句转化
Code
public static string AnalyticsSql(string sql, int index, int size)
{
string keyid = null, columns = null, table = null, orderby = null, wherestr = null, originalSql = null;
originalSql = sql;
originalSql = originalSql.Replace(originalSql.Substring(originalSql.IndexOf(" select ") + 8, originalSql.IndexOf(" from ") - 8 - originalSql.IndexOf(" select ")), " count(*) ");
if (sql.IndexOf(" * ") != -1)
{
if (sql.IndexOf("|") != -1)
{
keyid = sql.Substring(sql.IndexOf("|") + 1, sql.IndexOf(" ", sql.IndexOf("|")) - sql.IndexOf("|") - 1);
}
else
{
keyid = "id";
}
columns = "*";
}
else
{
keyid = sql.Substring(sql.IndexOf("select") + 6, sql.IndexOf(",") - sql.IndexOf("select") - 6);
columns = sql.Substring(sql.IndexOf("select") + 6, sql.IndexOf(" from ") - 6 - sql.IndexOf("select"));
}
if (sql.IndexOf(" where ") != -1)
{
wherestr = " where ";
if (sql.IndexOf(" order ") != -1)
wherestr += sql.Substring(sql.IndexOf(" where ") + 7, sql.IndexOf(" order ") - sql.IndexOf(" where ") - 7);
else
wherestr += sql.Substring(sql.IndexOf(" where ") + 7);
}
table = GetSqlTable(sql);
if (sql.IndexOf(" order ") != -1)
{
orderby = sql.Substring(sql.LastIndexOf("by") + 2);
}
else
{
orderby = keyid;
}
sql = "select top " + size.ToString() + " " + columns + " from " + table + " where " + keyid + ">isnull((select max (" + keyid + ") from (select top " + (index * size).ToString() + " " + keyid.ToString() + " from " + table + wherestr + " order by " + orderby + ") as T),0) order by " + keyid;
return originalSql + ";" + sql;
}
需要补充的是分页排序时id问题：
如果你的sql语句写成了这样：
1、select * from table where ... order ...
则优化后的sql以id排序
2、select *|CustomerId from table where ... order ...
则优化后的sql以CustomerId排序
2、select CustomerId,CustomerName,... from table where ... order ...
则优化后的sql以CustomerId排序
==================================
然后根据当前信息格式化分页显示的htmls，例如，页数、页号、总记录数、以及上下页按钮是否可用。具体代码：
Code
public static string AnalyticsPagingHtmls(string tableAndindex,int count, int size, int index)
{
string _GetPageHtml = GetPageHtml();
return string.Format
(
_GetPageHtml.Substring(0, _GetPageHtml.IndexOf(",{4}];") + 6),
count == 0 || count <= size ? "none" : "",
index == 0 ? "0" : "1",
index == 0 ? "0" : "1",
(index + 1 == ((count % size) == 0 ? count / size : ((count / size) + 1))) ? "0" : "1",
(index + 1 == ((count % size) == 0 ? count / size : ((count / size) + 1))) ? "0" : "1",
size,
index + 1,
(count % size) == 0 ? count / size : (count / size) + 1,
count,
count == 0 ? "none" : "",
tableAndindex,
((count % size) == 0 ? count / size : ((count / size) + 1))-1
)
+ _GetPageHtml.Substring(_GetPageHtml.IndexOf(",{4}];") + 6);
}
如何使用这个分页方法：
第一步：在配置文件里写下如下代码：
Code
<configSections>
<section name="MyPaging" type="System.Configuration.NameValueSectionHandler"/>
</configSections>
<MyPaging>
<add key="Paging" value="~/Paging/template.htm"/>
</MyPaging>
第二步：在cs文件里,直接调用就行
protected void Page_Load(object sender, EventArgs e)
{
if(MyPaging.IsPaging)
{
p1.InnerHtml = MyPaging.ExecutePaging(MyRep, "select CustomerId,ShipName,ShipAddress,ShippedDate from orders ", 0,5);
p2.InnerHtml = MyPaging.ExecutePaging(MyRep2, "select CustomerID,CompanyName,ContactName,Address from dbo.Customers", 0,5);
}
}
