SQL Server - SSQL Server2000和2005都能用的分页方法

精选转载

luoyi107 2010-04-09 08:10:42 博主文章分类：数据库

文章标签 SQL Server 数据库存储过程分布 文章分类 .Net 后端开发

(1)存储过程(SQL2000和2005下都可用):

USE [Northwind]
GO
/****** 对象:    StoredProcedure [dbo].[p_GetOrders]        脚本日期: 03/29/2010 23:00:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER    PROCEDURE [dbo].[p_GetOrders]
@strWhere     varchar(1500),     -- 查询条件 (注意: 不要加 where)
@PageSize        int = 50,                    -- 页尺寸
@PageIndex     int = 1,                     -- 页码
@PageCount     int out,                     --总页数
@RecordCount int out                        --总记录数
AS
BEGIN
declare @strSQL     varchar(5000)             -- 主语句
declare @strTmp     varchar(110)                -- 临时变量
declare @strOrder varchar(400)                -- 排序类型
select @RecordCount=count(*) from SaleInvoices
select @PageCount=ceiling(@RecordCount/@PageSize)
set @strTmp = '<(select min'
set @strOrder = ' order by OrderID desc'
if @PageIndex = 1
begin
         if @strWhere != ''
         set @strSQL = 'select top ' + str(@PageSize) +' *    from Orders where ' + @strWhere + ' ' + @strOrder
         else
         set @strSQL = 'select top ' + str(@PageSize) +'    *     from Orders '+ @strOrder
         --如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +'    *    from Orders
where OrderID' + @strTmp + '(OrderID) from (select top ' + str((@PageIndex-1)*@PageSize) + ' OrderID from Orders' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
         set @strSQL = 'select top ' + str(@PageSize) +'    * from Orders
            where OrderID' + @strTmp + '(OrderID) from
        (select top ' + str((@PageIndex-1)*@PageSize) + ' OrderID from Orders where ' + @strWhere + ' '
                 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
--print (@strSQL)
exec (@strSQL)
END

注:按钮中的文本设为"1"和"2"
(3)页面后台:

private int PageSize = 5;    //全局变量
             private int PageIndex = 1;    //全局变量
//获取数据方法
protected DataTable getPageDataSource(string sqlWhere, int pageIndex, int pageSize)
             {
                     string connStr = ConfigurationManager.AppSettings["MsSql2nd"].ToString();
                     SqlConnection conn = new SqlConnection(connStr);
                     conn.Open();
                     string sql = "p_GetPaging";
                     SqlCommand cmd = new SqlCommand(sql, conn);
                     cmd.CommandType = CommandType.StoredProcedure;
                     cmd.Parameters.Add("@strWhere",SqlDbType.VarChar);
                     cmd.Parameters["@strWhere"].Value = sqlWhere;
                     cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
                     cmd.Parameters["@PageIndex"].Value = pageIndex;
                     cmd.Parameters.Add("@PageSize", SqlDbType.Int);
                     cmd.Parameters["@PageSize"].Value = pageSize;
        cmd.Parameters.Add("@RecordCount",SqlDbType.Int);
                     cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                     cmd.Parameters.Add("@PageCount", SqlDbType.Int);
                     cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
                     SqlDataReader sdr = cmd.ExecuteReader();
                     DataTable dt = new DataTable();
                     dt.Load(sdr);
                     sdr.Dispose();
                     conn.Close();
                     return dt;
             }
//获取总页数
protected int getPageCount(string sqlWhere, int pageIndex, int pageSize)
             {
                     string connStr = ConfigurationManager.AppSettings["MsSql2nd"].ToString();
                     SqlConnection conn = new SqlConnection(connStr);
                     conn.Open();
                     string sql = "p_GetPaging";
                     SqlCommand cmd = new SqlCommand(sql, conn);
                     cmd.CommandType = CommandType.StoredProcedure;
                     cmd.Parameters.Add("@strWhere",SqlDbType.VarChar);
                     cmd.Parameters["@strWhere"].Value = sqlWhere;
                     cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
                     cmd.Parameters["@PageIndex"].Value = pageIndex;
                     cmd.Parameters.Add("@PageSize", SqlDbType.Int);
                     cmd.Parameters["@PageSize"].Value = pageSize;
        cmd.Parameters.Add("@RecordCount",SqlDbType.Int);
                     cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                     cmd.Parameters.Add("@PageCount", SqlDbType.Int);
                     cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
                     SqlDataReader sdr = cmd.ExecuteReader();
                     DataTable dt = new DataTable();
                     dt.Load(sdr);
                     sdr.Dispose();
                     conn.Close();
                     //return dt;
        int pageCount = (int)cmd.Parameters["@PageCount"].Value;
                     return pageCount;
             }
//搜索按钮事件
protected void btnAllSearch_Click(object sender, EventArgs e)
             {
                     string sestr = this.txtAllSearch.Text.Trim();
                     string sqlWhere = "";
                     if (sestr == "")
                     {
                             sqlWhere = "";
                     }
                     else
                     {
                             sqlWhere = "[no] like \"%" + sestr + "%\""; //注意转义字符的使用
                     }
                     DataTable dt = getPageDataSource(sqlWhere, PageIndex, PageSize);
                     this.GvSAll.DataSource = dt; ;
                     this.GvSAll.DataBind();
             }
//前一页事件
protected void lbtnPre_Click(object sender, EventArgs e)
             {
                     string sestr = this.txtAllSearch.Text.Trim();
                     string sqlWhere = "";
                     if (sestr == "")
                     {
                             sqlWhere = "";
                     }
                     else
                     {
                             sqlWhere = "[no] like \"%" + sestr + "%\"";
                     }
                     PageIndex = Convert.ToInt16(this.lbtnPre.Text);
                     PageIndex -= 1;
                     if (PageIndex<=1)
                     {
                             PageIndex = 1;
                     }
                     DataTable dt = getPageDataSource(sqlWhere, PageIndex, PageSize);
                     this.lbtnPre.Text = PageIndex.ToString();
                     this.lbtnNext.Text = (PageIndex + 1).ToString();
                     this.GvSAll.DataSource = dt; ;
                     this.GvSAll.DataBind();
             }
//后一页事件
protected void lbtnNext_Click(object sender, EventArgs e)
             {
                     string sestr = this.txtAllSearch.Text.Trim();
                     string sqlWhere = "";
                     if (sestr == "")
                     {
                             sqlWhere = "";
                     }
                     else
                     {
                             sqlWhere = "[no] like \"%" + sestr + "%\"";
                     }
                     PageIndex=Convert.ToInt16(this.lbtnNext.Text);
                     PageIndex += 1;
        int PageCount = getPageCount("", 1, 5);
                     if (PageIndex>PageCount+1)
                     {
                             PageIndex = PageCount+1;
                     }
                     DataTable dt = getPageDataSource(sqlWhere, PageIndex, PageSize);
                     this.lbtnNext.Text = PageIndex.ToString();
                     this.lbtnPre.Text = (PageIndex - 1).ToString();
                     this.GvSAll.DataSource = dt; ;
                     this.GvSAll.DataBind();
             }

注:在两个翻页事件中利用了两按钮自身的文本值来保存参数,并相与影响