这个必须得说明下，这个存储过程分页程序是邹建大大写的
CREATE Proc UP_PageShow
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from(select top 100 percent * from'+@QueryStr+@FdOrder+')a
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from(select top 100 percent * from'+@QueryStr+@FdOrder+')a
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
然后调用类是我自己写的 写的不好大家见笑
using System;
using System.Data;
using System.Configuration;
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;
using System.Data.SqlClient;
/// <summary>
/// 执行存储过程进行分页的类
/// </summary>
public class PageClass
{
private static SqlConnection con;
private static SqlCommand com;
public PageClass()
{
}
/// <summary>
/// 打开数据库连接
/// </summary>
private static void Open()
{
con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void Close()
{
///判断连接的状态是否打开
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
/// <summary>
/// 释放资源
/// </summary>
private static void Dispose()
{
// 确认连接是否已经关闭
if (con != null)
{
con.Dispose();
con = null;
}
}
/// <summary>
/// 计算共多少页
/// </summary>
public static int Re_PageCount(string tblname,int pagesize,string strwhere)
{
int i = 1;
string sql = "SELECT @count=COUNT(*) FROM " + tblname;
if (strwhere.Trim() != "")
{
sql += " WHERE " + strwhere;
}
Open();
com = new SqlCommand(sql, con);
SqlParameter outPar = new SqlParameter("@count", SqlDbType.Int);
outPar.Direction = ParameterDirection.Output;
com.Parameters.Add(outPar);
com.ExecuteNonQuery();
Close();
i = Convert.ToInt32(outPar.Value);
if (i > pagesize)
{
if (i % pagesize == 0)
{
i = (i / pagesize);
}
else
{
i = (i / pagesize) + 1;
}
}
else
{
i = 1;
}
return i;
}
/// <summary>
/// 得用存储过程进行分页返回DataSet
/// 邹建
/// </summary>
/// <param name="querystr">查询语句</param>
/// <param name="pagesize">每页的大小(行数)</param>
/// <param name="pagecurrent">要显示的页</param>
/// <param name="fdshow">显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段</param>
/// <param name="fdorder">排序字段列表</param>
/// <returns></returns>
public static DataSet Re_PageDS(string querystr, int pagesize, int pagecurrent, string fdshow, string fdorder)
{
Open();
com = new SqlCommand("UP_PageShow", con);
DataSet _ds = new DataSet();
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] parameters = {
new SqlParameter("@QueryStr", SqlDbType.NVarChar, 4000), //查询语句
new SqlParameter("@PageSize", SqlDbType.Int), //每页的大小(行数)
new SqlParameter("@PageCurrent", SqlDbType.Int), //要显示的页
new SqlParameter("@FdShow", SqlDbType.NVarChar,4000), //显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
new SqlParameter("@FdOrder",SqlDbType.NVarChar,1000) //排序字段列表
};
parameters[0].Value = querystr;
parameters[1].Value = pagesize;
parameters[2].Value = pagecurrent;
parameters[3].Value = fdshow;
parameters[4].Value = fdorder;
for (int i = 0; i < parameters.Length; i++)
{
com.Parameters.Add(parameters[i]);
}
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(_ds);
Close();
if (_ds.Tables.Count == 1 && _ds.Tables[0].Rows.Count == 0)
{
_ds = null;
}
return _ds;
}
}
using System.Data;
using System.Configuration;
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;
using System.Data.SqlClient;
/// <summary>
/// 执行存储过程进行分页的类
/// </summary>
public class PageClass
{
private static SqlConnection con;
private static SqlCommand com;
public PageClass()
{
}
/// <summary>
/// 打开数据库连接
/// </summary>
private static void Open()
{
con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void Close()
{
///判断连接的状态是否打开
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
/// <summary>
/// 释放资源
/// </summary>
private static void Dispose()
{
// 确认连接是否已经关闭
if (con != null)
{
con.Dispose();
con = null;
}
}
/// <summary>
/// 计算共多少页
/// </summary>
public static int Re_PageCount(string tblname,int pagesize,string strwhere)
{
int i = 1;
string sql = "SELECT @count=COUNT(*) FROM " + tblname;
if (strwhere.Trim() != "")
{
sql += " WHERE " + strwhere;
}
Open();
com = new SqlCommand(sql, con);
SqlParameter outPar = new SqlParameter("@count", SqlDbType.Int);
outPar.Direction = ParameterDirection.Output;
com.Parameters.Add(outPar);
com.ExecuteNonQuery();
Close();
i = Convert.ToInt32(outPar.Value);
if (i > pagesize)
{
if (i % pagesize == 0)
{
i = (i / pagesize);
}
else
{
i = (i / pagesize) + 1;
}
}
else
{
i = 1;
}
return i;
}
/// <summary>
/// 得用存储过程进行分页返回DataSet
/// 邹建
/// </summary>
/// <param name="querystr">查询语句</param>
/// <param name="pagesize">每页的大小(行数)</param>
/// <param name="pagecurrent">要显示的页</param>
/// <param name="fdshow">显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段</param>
/// <param name="fdorder">排序字段列表</param>
/// <returns></returns>
public static DataSet Re_PageDS(string querystr, int pagesize, int pagecurrent, string fdshow, string fdorder)
{
Open();
com = new SqlCommand("UP_PageShow", con);
DataSet _ds = new DataSet();
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] parameters = {
new SqlParameter("@QueryStr", SqlDbType.NVarChar, 4000), //查询语句
new SqlParameter("@PageSize", SqlDbType.Int), //每页的大小(行数)
new SqlParameter("@PageCurrent", SqlDbType.Int), //要显示的页
new SqlParameter("@FdShow", SqlDbType.NVarChar,4000), //显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
new SqlParameter("@FdOrder",SqlDbType.NVarChar,1000) //排序字段列表
};
parameters[0].Value = querystr;
parameters[1].Value = pagesize;
parameters[2].Value = pagecurrent;
parameters[3].Value = fdshow;
parameters[4].Value = fdorder;
for (int i = 0; i < parameters.Length; i++)
{
com.Parameters.Add(parameters[i]);
}
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(_ds);
Close();
if (_ds.Tables.Count == 1 && _ds.Tables[0].Rows.Count == 0)
{
_ds = null;
}
return _ds;
}
}
然后是业务逻辑层
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 Admin_Ar_ArManage : AdminBase
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
AdminDataBind(1);
}
}
public void AdminDataBind(int page)
//数据绑定
{
_strSQLTableName = "tb_MavinArticle,tb_User"; //表名
_strSQLWhere = "ArDelete='0' AND ArUserID=UserID"; //条件
_strSQLShow = "*"; //要显示的内容
_strSQLSort = "ArID DESC"; //排序
_intSQLPageRowCount = 10; //每页的行数
_intPageIndex = page; //当前的页码
_strSQL = " SELECT " + _strSQLShow + " FROM " + _strSQLTableName;
if (_strSQLWhere.Trim() != "")
{
_strSQL += " WHERE " + _strSQLWhere;
}
_intPageCount = PageClass.Re_PageCount(_strSQLTableName, _intSQLPageRowCount, _strSQLWhere); //计算出总页数
//Response.Write(_strSQL +" ORDER BY "+ _strSQLSort); //测试
_ds = PageClass.Re_PageDS(_strSQL, _intSQLPageRowCount, page, _strSQLShow, _strSQLSort);
pageDefault();
for (int i = 0; i < _intPageCount; i++)
//用for循环来做出一个页码链接
{
ListItem insertLI = new ListItem("第" + (i + 1) + "页", (i + 1).ToString());
dropPage.Items.Insert(i, insertLI);
}
if (_intPageCount != 0) dropPage.SelectedIndex = _intPageIndex - 1;
lblAllPage.Text = _intPageCount.ToString(); //总页数
lblCountPage.Text = _intPageIndex.ToString(); //第几页
if (_ds != null)
{
dgrdData.Visible = true;
lblError.Visible = false;
dgrdData.DataSource = _ds;
dgrdData.DataBind();
}
else
{
dgrdData.Visible = false;
lblError.Visible = true;
lblError.Text = "暂无信息";
}
}
public void pageDefault()
{
//页码连接初始化
dropPage.Items.Clear();
if (_intPageIndex == 1)
{
lbtnFirst.Enabled = false;
lbtnUP.Enabled = false;
}
else
{
lbtnFirst.Enabled = true;
lbtnUP.Enabled = true;
}
if (_intPageIndex == _intPageCount)
{
lbtnDown.Enabled = false;
lbtnEnd.Enabled = false;
}
else
{
lbtnDown.Enabled = true;
lbtnEnd.Enabled = true;
}
if (_intPageCount == 0)
{
lbtnDown.Enabled = false;
lbtnEnd.Enabled = false;
lbtnFirst.Enabled = false;
lbtnUP.Enabled = false;
dropPage.Visible = false;
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
AdminDataBind(int.Parse(dropPage.SelectedValue));
}
protected void lbtnFirst_Click(object sender, EventArgs e)
{
if (_intPageIndex != 1)
{
AdminDataBind(1);
}
}
protected void lbtnUP_Click(object sender, EventArgs e)
{
if (_intPageIndex != 1)
{
AdminDataBind(_intPageIndex - 1);
}
}
protected void lbtnDown_Click(object sender, EventArgs e)
{
if (_intPageIndex != _intPageCount)
{
AdminDataBind(_intPageIndex + 1);
}
}
protected void lbtnEnd_Click(object sender, EventArgs e)
{
if (_intPageIndex != _intPageCount)
{
AdminDataBind(_intPageCount);
}
}
}
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 Admin_Ar_ArManage : AdminBase
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
AdminDataBind(1);
}
}
public void AdminDataBind(int page)
//数据绑定
{
_strSQLTableName = "tb_MavinArticle,tb_User"; //表名
_strSQLWhere = "ArDelete='0' AND ArUserID=UserID"; //条件
_strSQLShow = "*"; //要显示的内容
_strSQLSort = "ArID DESC"; //排序
_intSQLPageRowCount = 10; //每页的行数
_intPageIndex = page; //当前的页码
_strSQL = " SELECT " + _strSQLShow + " FROM " + _strSQLTableName;
if (_strSQLWhere.Trim() != "")
{
_strSQL += " WHERE " + _strSQLWhere;
}
_intPageCount = PageClass.Re_PageCount(_strSQLTableName, _intSQLPageRowCount, _strSQLWhere); //计算出总页数
//Response.Write(_strSQL +" ORDER BY "+ _strSQLSort); //测试
_ds = PageClass.Re_PageDS(_strSQL, _intSQLPageRowCount, page, _strSQLShow, _strSQLSort);
pageDefault();
for (int i = 0; i < _intPageCount; i++)
//用for循环来做出一个页码链接
{
ListItem insertLI = new ListItem("第" + (i + 1) + "页", (i + 1).ToString());
dropPage.Items.Insert(i, insertLI);
}
if (_intPageCount != 0) dropPage.SelectedIndex = _intPageIndex - 1;
lblAllPage.Text = _intPageCount.ToString(); //总页数
lblCountPage.Text = _intPageIndex.ToString(); //第几页
if (_ds != null)
{
dgrdData.Visible = true;
lblError.Visible = false;
dgrdData.DataSource = _ds;
dgrdData.DataBind();
}
else
{
dgrdData.Visible = false;
lblError.Visible = true;
lblError.Text = "暂无信息";
}
}
public void pageDefault()
{
//页码连接初始化
dropPage.Items.Clear();
if (_intPageIndex == 1)
{
lbtnFirst.Enabled = false;
lbtnUP.Enabled = false;
}
else
{
lbtnFirst.Enabled = true;
lbtnUP.Enabled = true;
}
if (_intPageIndex == _intPageCount)
{
lbtnDown.Enabled = false;
lbtnEnd.Enabled = false;
}
else
{
lbtnDown.Enabled = true;
lbtnEnd.Enabled = true;
}
if (_intPageCount == 0)
{
lbtnDown.Enabled = false;
lbtnEnd.Enabled = false;
lbtnFirst.Enabled = false;
lbtnUP.Enabled = false;
dropPage.Visible = false;
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
AdminDataBind(int.Parse(dropPage.SelectedValue));
}
protected void lbtnFirst_Click(object sender, EventArgs e)
{
if (_intPageIndex != 1)
{
AdminDataBind(1);
}
}
protected void lbtnUP_Click(object sender, EventArgs e)
{
if (_intPageIndex != 1)
{
AdminDataBind(_intPageIndex - 1);
}
}
protected void lbtnDown_Click(object sender, EventArgs e)
{
if (_intPageIndex != _intPageCount)
{
AdminDataBind(_intPageIndex + 1);
}
}
protected void lbtnEnd_Click(object sender, EventArgs e)
{
if (_intPageIndex != _intPageCount)
{
AdminDataBind(_intPageCount);
}
}
}
这个有点乱，我自己能看懂就得了^_^
表示层我就不粘了，其实就是一个DataGrid；四个Linkbutton (上一页 下一页 最后一页 第一页)；一个DropDownList
前面加“_”的都是基类里的变量
