---存储过程
create PROCEDURE [dbo].[SP_PageList] 'SMS_log', 'id', 'id', 25, 1, 0, 1,''
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名(可包含如TABLE.FLDNAME形式)
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @fldName_t varchar(255) -- 在分页时用的排序字段名,不包含多表并列时的表名
set @fldName_t = right(@fldName,len(@fldName)-CHARINDEX('.',@fldName))
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ' + @tblName + ''
end
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' where ' + @fldName + ' ' + @strTmp + ' ('+ @fldName_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' where ' + @fldName + ' ' + @strTmp + ' ('+ @fldName_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
//方法
/// <summary>
/// 分页
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="strGetFields">查询字段</param>
/// <param name="fldName">主键</param>
/// <param name="PageSize">每页数</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">返回总个数</param>
/// <param name="OrderType">排序方式 非0是顺序</param>
/// <param name="strWhere">查询条件</param>
/// <returns></returns>
public IDataReader GetPageList(string tblName, string strGetFields, string fldName, int PageSize,
int PageIndex, int doCount, int OrderType, string strWhere)
{
Database db = new Database();
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.NVarChar,255),
new SqlParameter("@strGetFields", SqlDbType.NVarChar,1000),
new SqlParameter("@fldName", SqlDbType.NVarChar,255),
new SqlParameter("@PageSize", SqlDbType.Int,4),
new SqlParameter("@PageIndex", SqlDbType.Int,4),
new SqlParameter("@doCount", SqlDbType.Bit,1),
new SqlParameter("@OrderType", SqlDbType.Bit,1),
new SqlParameter("@strWhere", SqlDbType.NVarChar,1500)
};
parameters[0].Value = tblName;
parameters[1].Value = strGetFields;
parameters[2].Value = fldName;
parameters[3].Value = PageSize;
parameters[4].Value = PageIndex;
parameters[5].Value = doCount;
parameters[6].Value = OrderType;
parameters[7].Value = strWhere;
SqlDataReader reader;
db.RunProc("SP_PageList", parameters, out reader);
return reader;
}
///调用分页方法
using (SqlDataReader reader = (SqlDataReader)类名.GetPageList("表名", "*", "id", PageSize, PageNo, 0, 1,strWhere))
{
if (reader.HasRows)
{
DataTable tb = new DataTable();
tb.Load(reader);
if (tb.Rows.Count != 0)
{
// this.btnDelete.Visible = true;
//this.btnDelete.Attributes["onclick"] = "javascript:return confirm('您确认要删除吗?');";
dgMailList.DataSource = tb.DefaultView;
dgMailList.DataBind();
// MenuName = tb.Rows[0]["M_Name"].ToString();
using (IDataReader Reader = docdal.GetPageList("SMS_log", "id", "id", PageSize, PageNo, 1, 1, strWhere))
{
Reader.Read();
PageCtl1.RecordCount = int.Parse(Reader[0].ToString());
PageCtl1.PageSize = PageSize;
PageCtl1.PageNo = PageNo;
PageCtl1.Visible = true;
Reader.Close();
}
}
}
/// 页面显示
<%@ Register Src=”UserControl/PageCtl.ascx" TagName="PageCtl" TagPrefix="uc1" %>
<table width="100%" height="100%" border="0" cellspacing="0" cellpadding="0" class="bg">
<tr>
<td align="center">
<uc1:PageCtl ID="PageCtl1" runat="server" />
</td>
</tr>
</table>
page控件代码
.asp代码
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="PageCtl.ascx.cs" Inherits="Controls_PageCtl" %>
<style type="text/css">
ul#hovershow{
list-style-type: none;
margin: 50px;
width:200px;
float: left;
display: inline;
clear: both;
}
ul#hovershow li{
float: left;
display: inline;
width:20px;
height: 20px;
margin: 2px;
}
ul#hovershow li a {
display: block;
width:20px;
height:20px;
border:1px #999 solid;
color:#0984FF;
background-color:#fff;
text-decoration: none;
line-height: 20px;
font-size: 12px;
text-align: center;
font-weight: bold;
}
ul#hovershow li a:hover{
position: absolute;
width:40px;
height: 40px;
line-height: 40px;
font-size: 32px;
z-index:100;
margin: -10px 0 0 -10px;
}
ul#hovershow li:hover + li a{
position: absolute;
width:30px;
height: 30px;
line-height: 30px;
font-size: 24px;
z-index:99;
margin: -5px 0 0 -5px;
}
</style>
<table width="100%" border="0" cellpadding="0" cellspacing="0" align="center">
<tr >
<td align="center" valign="bottom" style="height:27px;">
共 <asp:Label id="lblPageCount" runat="server"></asp:Label> 页
<asp:Label id="lblRecordCount" runat="server"></asp:Label> 条
每页<asp:Label id="lblPageSize" runat="server"></asp:Label>
条
<asp:Label id="lblPageList" runat="server"></asp:Label>
</td>
<%--
<td align="center" valign="top">跳至<INPUT style="WIDTH: 35px; HEIGHT: 22px" type="text" maxLength="4" size="5" id="PageNo"
οnkeydοwn="return offEnter()">页 <INPUT style="WIDTH: 26px; HEIGHT: 22px" type="button" value="GO" οnclick="return GoPage();">
</td>--%>
</tr>
</table>
<%--<ul id="hovershow">
<li><a href="#1" title="#1">1</a></li>
<li><a href="#2" title="#2">2</a></li>
<li><a href="#3" title="#3">3</a></li>
<li><a href="#4" title="#4">4</a></li>
<li><a href="#5" title="#5">5</a></li>
<li><a href="#6" title="#6">6</a></li>
<li><a href="#7" title="#7">7</a></li>
</ul>--%>
<script language="javascript">
function GoPage()
{
if(!Number(document.all.PageNo.value))
{
alert("请输入数字!");
document.all.PageNo.focus();
return false;
}
else
{
self.location.href="PageNo="+document.all.PageNo.value;
return true;
}
return false;
}
function offEnter()
{
var k=window.event.keyCode;
//屏蔽回车键
if(k==10||k==13)
{
window.event.keyCode=0;
window.event.returnValue=false;
return false;
}
return true;
}
</script>
//.cs代码
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Controls_PageCtl : System.Web.UI.UserControl
{
public int RecordCount = 0, PageSize, PageNo;
private int PageCount = 0;
private string PageList, FontColor = "#000000", url = "";
//protected System.Web.UI.WebControls.Label lblRecordCount;
//protected System.Web.UI.WebControls.Label lblPageCount;
//protected System.Web.UI.WebControls.Label lblPageSize;
//protected System.Web.UI.WebControls.Label lblPageList;
protected void Page_Load(object sender, EventArgs e)
{
// 在此处放置用户代码以初始化页面
if (!IsPostBack)
{
LoadData();
}
}
void LoadData()
{
if (RecordCount == 0)
{
return;
}
if (RecordCount % PageSize == 0)
{
PageCount = RecordCount / PageSize;
}
else
{
PageCount = RecordCount / PageSize + 1;
}
//当前页
if (PageNo < 1)
{
PageNo = 1;
}
if (PageNo > PageCount)
{
PageNo = PageCount;
}
//链接地址
if (url == "")
{
string[] tmp;
tmp = Request.ServerVariables["QUERY_STRING"].Split("&".ToCharArray());
url = "?";
for (int i = 0; i < tmp.Length; i++)
{
if (tmp[i].IndexOf("PageNo") == -1)
{
if (url == "?")
{
url += tmp[i].ToString();
}
else
{
url += "&" + tmp[i].ToString();
}
}
}
if (url != "?")
{
url += "&";
}
}
else
{
if (url.IndexOf("?") == -1)
{
url += "?";
}
else
{
if (url.Substring(url.Length - 1, 1) != "?" && url.Substring(url.Length - 1, 1) != "&")
{
url += "&";
}
}
}
//上页和首页
if (PageNo > 1)
{
PageList = "<a href='" + url + "PageNo=1'><font color='" + FontColor + "'>首页</font></a> ";
PageList+="<a href='"+url+"PageNo="+(PageNo-1)+"'><font color='"+FontColor+"'>上页</font></a> ";
}
else
{
PageList = "<font color='" + FontColor + "'>首页</font> ";
PageList+="<font color='"+FontColor+"'>上页</font> ";
}
// 开始页+3=当前页=结束页-3
int ksnum = 1, jsnum = 0;
if (PageCount <= 12)
{
for (int i = 1; i <= PageCount; i++)
{
if (PageNo == i)
{
PageList+=" <font color='#ff0000'>[</font> <font size=1 color='#ff0000'><b>"+i+"</b></font> <font color=red>]</font> ";
}
else
{
PageList += "<a href='" + url + "PageNo=" + i + "'><font color='" + FontColor + "'>" + i + "</font></a> ";
}
}
}
else
{
//开始页
if (PageNo - 6 < 0)
{
ksnum = 1;
}
else
{
ksnum = PageNo - 3;
}
//结束页
if (ksnum + 6 > PageCount)
{
jsnum = PageCount;
ksnum += PageCount - (ksnum + 6);
}
else
{
jsnum = ksnum + 6;
}
}
//显示当前页的前后页
for (int i = ksnum; i <= jsnum; i++)
{
if (PageNo == i)
{
PageList += "<font color='#ff0000'><font color=red>[</font>" + i + "<font color=red>]</font></font> ";
}
else
{
PageList += "<a href='" + url + "PageNo=" + i + "'><font color='" + FontColor + "'>" + i + "</font></a> ";
}
}
//下页和尾页
if (PageNo < PageCount)
{
PageList+="<a href='"+url+"PageNo="+(PageNo+1)+"'><font color='"+FontColor+"'> 下页</font></a> ";
PageList += "<a href='" + url + "PageNo=" + PageCount + "'><font color='" + FontColor + "'>尾页</font></a> ";
}
else
{
PageList+="<font color='"+FontColor+"'> 下页</font> ";
PageList += "<font color='" + FontColor + "'>尾页</font>";
}
this.lblPageList.Text = PageList;
this.lblPageCount.Text = PageCount.ToString();
this.lblRecordCount.Text = RecordCount.ToString();
this.lblPageSize.Text = this.PageSize.ToString();
}
}