ASP.NET通用分页代码利用了AspNetPage 7.0.2 for VS2005/vs2008 控件和通用存储过程(SQL2005)完成的。
1、新建存储过程写如下代码:
2、在VS2005新建一个类(PageList.cs),代码如下:
1、新建存储过程写如下代码:
CREATE PROCEDURE dbo.PageList
@Select NVARCHAR(500), -- 要查询的列名,用逗号隔开(Select后面From前面的内容)
@From NVARCHAR(100), -- From后的内容
@Where NVARCHAR(500) = NULL, -- Where后的内容
@OrderBy NVARCHAR(100) = NULL, -- 排序字段
@PageIndex INT, -- 当前页 ***计数从1开始***
@PageSize INT -- 每页大小
AS
BEGIN
SET NOCOUNT ON;
Declare @Sql nVarchar(1000)
Set @Sql=
'Select *
From (Select '+@Select+',ROW_NUMBER() OVER(ORDER BY '+@OrderBy+') AS ROWNUMBER
From '+@From+
Case IsNull(@Where,'') When '' Then '' Else ' Where '+@Where End+')T
Where ROWNUMBER Between '+Cast((@PageIndex-1)*@PageSize+1 As nVarchar(10))+' And '+Cast((@PageIndex-1)*@PageSize+@PageSize As nVarchar(10))
Exec(@Sql)
END
@Select NVARCHAR(500), -- 要查询的列名,用逗号隔开(Select后面From前面的内容)
@From NVARCHAR(100), -- From后的内容
@Where NVARCHAR(500) = NULL, -- Where后的内容
@OrderBy NVARCHAR(100) = NULL, -- 排序字段
@PageIndex INT, -- 当前页 ***计数从1开始***
@PageSize INT -- 每页大小
AS
BEGIN
SET NOCOUNT ON;
Declare @Sql nVarchar(1000)
Set @Sql=
'Select *
From (Select '+@Select+',ROW_NUMBER() OVER(ORDER BY '+@OrderBy+') AS ROWNUMBER
From '+@From+
Case IsNull(@Where,'') When '' Then '' Else ' Where '+@Where End+')T
Where ROWNUMBER Between '+Cast((@PageIndex-1)*@PageSize+1 As nVarchar(10))+' And '+Cast((@PageIndex-1)*@PageSize+@PageSize As nVarchar(10))
Exec(@Sql)
END
2、在VS2005新建一个类(PageList.cs),代码如下:
public DataSet PageListTable(string select, string from, string where, string orderby, int PageIndex, int pagesize)
{
SqlConnection conn = new SqlConnection((new Conn()).qxrc());
// 创建选择命令对象。
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand("PageList",conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
// 给命令对象添加参数
da.SelectCommand.Parameters.Add("@Select", SqlDbType.NVarChar, 500).Value = select;
da.SelectCommand.Parameters.Add("@From", SqlDbType.NVarChar, 500).Value = from;
da.SelectCommand.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = where;
da.SelectCommand.Parameters.Add("@OrderBy", SqlDbType.NVarChar, 100).Value = orderby;
da.SelectCommand.Parameters.Add("@Page", SqlDbType.Int).Value = page;
da.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public int PageListCount(string from,string where)
{
string strsql;
int RecordCount;
if(where=="")
strsql = "Select count(1) from "+from;
else
strsql = "Select count(1) from " + from + " where " + where;
SqlConnection conn = new SqlConnection((new Conn()).qxrc());
SqlCommand myCommand = new SqlCommand(strsql, conn);
conn.Open();
return RecordCount = (int)myCommand.ExecuteScalar();
conn.Close();
}
{
SqlConnection conn = new SqlConnection((new Conn()).qxrc());
// 创建选择命令对象。
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand("PageList",conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
// 给命令对象添加参数
da.SelectCommand.Parameters.Add("@Select", SqlDbType.NVarChar, 500).Value = select;
da.SelectCommand.Parameters.Add("@From", SqlDbType.NVarChar, 500).Value = from;
da.SelectCommand.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = where;
da.SelectCommand.Parameters.Add("@OrderBy", SqlDbType.NVarChar, 100).Value = orderby;
da.SelectCommand.Parameters.Add("@Page", SqlDbType.Int).Value = page;
da.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public int PageListCount(string from,string where)
{
string strsql;
int RecordCount;
if(where=="")
strsql = "Select count(1) from "+from;
else
strsql = "Select count(1) from " + from + " where " + where;
SqlConnection conn = new SqlConnection((new Conn()).qxrc());
SqlCommand myCommand = new SqlCommand(strsql, conn);
conn.Open();
return RecordCount = (int)myCommand.ExecuteScalar();
conn.Close();
}
注意:要记得引用命名空间:using System.Data.SqlClient;
3、在数据显示页中加入AspNetPage这个控件(不要跟我说你不会),设置其参数,如:PageSize,UrlPaging,showcustominfosection等。
在代码页中代码如下:
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
AspNetPager1.RecordCount = (new PageList()).PageListCount("表","条件");
Bind();
}
}
protected void Bind()
{
DataSet ds = (new PageList()).PageListTable("字段", "表","条件", "排序", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize);
GridView.DataSource = ds.Tables[0].DefaultView;
GridView.DataBind();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
Bind();
}
注:上面代码中的"字段"、"表"、"条件"、"排序"都不要加如select、from、where等。其中"条件"可不填,不填时请写"",其它的都是必填!
{
if (!this.IsPostBack)
{
AspNetPager1.RecordCount = (new PageList()).PageListCount("表","条件");
Bind();
}
}
protected void Bind()
{
DataSet ds = (new PageList()).PageListTable("字段", "表","条件", "排序", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize);
GridView.DataSource = ds.Tables[0].DefaultView;
GridView.DataBind();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
Bind();
}