SQL - 高效数据分页的存储过程 178

#妙笔生花创作挑战''#

1 通过ROW_NUMBER()函数创建高效数据分页的存储过程

--在地图表(Area)上创建数据分页的存储过程
ALTER PROC usp_Area_datapage
@pageIndex INT=1,--页码(默认第一页)
@pageSize INT=5,  --页容量(默认页容量 5)
@pageCount INT OUTPUT, --总页数(输出到窗体显示)
@recordCount INT OUTPUT --总条数(输出到窗体显示)
AS
BEGIN
SELECT @recordCount=COUNT(*) FROM dbo.Area --获取数据的总条数
SET @pageCount=@recordCount/@pageSize --获取当前页容量下的总页数(窗体页码)
--判断当有小数时,总页数+1
IF @recordCount/@pageSize<>0 SET @pageCount=@pageCount+1
--通过row_number函数获取分页数据
SELECT * FROM(SELECT ROW_NUMBER()OVER(ORDER BY ID ASC)AS AId,* FROM dbo.Area)AS a
WHERE a.AId BETWEEN (@pageIndex-1)*@pageSize+1 AND(@pageIndex*@pageSize)
END

注释:
BETWEEN (@pageIndex-1)*@pageSize+1 AND (@pageIndex*@pageSize)
假设页容量为 10
(@pageIndex-1)*@pageSize+1
第一页起点: (1-1)*10+1 结果为1 
 (@pageIndex*@pageSize)
第一页终点:(1*10) 结果为 10
WHERE a.AId BETWEEN (@pageIndex-1)*@pageSize+1 AND(@pageIndex*@pageSize)
第一页数据: where a.AId BETWEEN 1 and 10 
相当于where a.AId>=1 and a.AId<=10

--测试存储过程
DECLARE @totalpage INT 用于接收输出参数的值
DECLARE @totalrecord INT  用于接收输出参数的值
EXEC usp_Area_datapage 1,10,@totalpage OUTPUT,@totalrecord OUTPUT
--输出时需要进行类型转换
PRINT N'总页数'+CONVERT(VARCHAR(10),@totalpage)
PRINT N'总条数'+CAST(@totalrecord AS VARCHAR(10))

1 添加类库的引用,编写配置文件信息,修改ExecuteDataTable()方法为存储过程类型

public static DataTable SP_ExecuteDataTable(string sp_name, params SqlParameter[] pms)
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(sp_name, constr))
    {
        //为adapter设置执行类型为存储过程
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        if (pms != null)
        {
            //执行存储过程,加入参数
            adapter.SelectCommand.Parameters.AddRange(pms);
        }
        DataTable dt = new DataTable();
        //将查询出的数据填充进dt中
        adapter.Fill(dt);
        return dt;
    }
}

2 窗体加载事件中绑定要显示的数据(示例:默认显示第一页的前20条数据)

//声明类的成员变量,用于接收4个变量的值,后面会用到
int RCount = 0;//总条数
int PCount = 0;//总页数
int PIndex = 1;//当前页码
int PSize = 20;//页容量
DataTable dt = null;//接收分页数据
private void Form1_Load(object sender, EventArgs e)
{
    //数据太多只截取前20条数据显示
    //为存储过程的参数赋值
    SqlParameter[] ps = {
   //输入参数(默认第一页,页容量20)
   new SqlParameter("@pageIndex",PIndex),
   new SqlParameter("@pageSize",PSize),
   //输出参数,不能直接赋值,只需设置为数据库中的数据类型
   new SqlParameter("@pageCount",SqlDbType.Int),
   new SqlParameter("@recordCount",SqlDbType.Int)
   };
    //设置两个输出参数的类型为输出参数
    ps[2].Direction = ParameterDirection.Output;
    ps[3].Direction = ParameterDirection.Output;
    //执行存储过程的方法
    dt = Helper.SQLHelper.SP_ExecuteDataTable("usp_Area_datapage", ps);
    //执行ExecuteDataTable()方法后
    //获取两个输出参数传出的值
    //将对应的值赋值给label标签显示
    //获取存储过程返回的总页数
    PCount = Convert.ToInt32(ps[2].Value);
     //标签显示总页数
    lbPCount.Text = PCount.ToString();
    //获取存储过程返回的总条数
    RCount = Convert.ToInt32(ps[3].Value);
    //标签显示总条数
    lbRCount.Text = RCount.ToString();
    //设置页容量文本框
    txtPSize.Text = PSize.ToString();
    //绑定数据
    dgv1.DataSource = dt;
    //为页码绑定显示文本
    for (int i = 1; i <= (int)ps[2].Value; i++)
    {
        cmbPageIndex.Items.Add(i);
    }
    ////向下拉列表框中添加第一个选项
    //cmbPageIndex.Items.Insert(0, "请选择");
    //默认第一页被选中
    cmbPageIndex.SelectedIndex = 0;
    //设置下拉列表框显示的高度
    cmbPageIndex.DropDownHeight = 350;
}

注意事项:1)设置参数为输出参数的类型 2)向下拉列表框中添加页码与请选择的选项

3 为页容量文本框控件添加文本框别人改变事件,当改变页容量数字时重新加载数据

private void txtPSize_TextChanged(object sender, EventArgs e)
{
    //检测输入的字符串是大于0的正整数(自己编写)
    //如果没有这个检测就会报异常
    PSize = Convert.ToInt32(txtPSize.Text);
    //获取当前页码
    PIndex = cmbPageIndex.SelectedIndex + 1;
    //为参数赋值
    SqlParameter[] ps = {
   new SqlParameter("@pageIndex",PIndex),
   new SqlParameter("@pageSize",PSize),
   //输出参数,不能直接赋值,只需设置为数据库中的数据类型
   new SqlParameter("@pageCount",SqlDbType.Int),
   new SqlParameter("@recordCount",SqlDbType.Int)
   };
    //设置两个输出参数的类型为输出参数
    ps[2].Direction = ParameterDirection.Output;
    ps[3].Direction = ParameterDirection.Output;
    //执行存储过程的方法
    dt = Helper.SQLHelper.SP_ExecuteDataTable("usp_Area_datapage", ps);
    //执行ExecuteDataTable()方法后
    //获取两个输出参数传出的值
    //将对应的值赋值给label标签显示
    //获取存储过程返回的总页数
    PCount = Convert.ToInt32(ps[2].Value);
    //标签显示总页数
    lbPCount.Text = PCount.ToString();
    //获取存储过程返回的总条数
    RCount = Convert.ToInt32(ps[3].Value);
    //标签显示总条数
    lbRCount.Text = RCount.ToString();
    //重新绑定数据
    dgv1.DataSource = dt;
    //清除之前添加的页码选项
    cmbPageIndex.Items.Clear();
    //为页码绑定显示文本
    for (int i = 1; i <= PCount; i++)
    {
        cmbPageIndex.Items.Add(i);
    }
    //默认第一页被选中
    cmbPageIndex.SelectedIndex = 0;
}

4 为下拉列表框添加索引改变事件(cmbPageIndex_SelectedIndexChanged)

private void cmbPageIndex_SelectedIndexChanged(object sender, EventArgs e)
{
    //检测输入的字符串是大于0的正整数(自己编写)
    //如果没有这个检测就会报异常
    PSize = Convert.ToInt32(txtPSize.Text);
    //获取当前页码
    PIndex = cmbPageIndex.SelectedIndex + 1;
    // PIndex = Convert.ToInt32(cmbPageIndex.Text);
    //为参数赋值
    SqlParameter[] ps = {
   new SqlParameter("@pageIndex",PIndex),
   new SqlParameter("@pageSize",PSize),
   //输出参数,不能直接赋值,只需设置为数据库中的数据类型
   new SqlParameter("@pageCount",SqlDbType.Int),
   new SqlParameter("@recordCount",SqlDbType.Int)
   };
    //设置两个输出参数的类型为输出参数
    ps[2].Direction = ParameterDirection.Output;
    ps[3].Direction = ParameterDirection.Output;
    //执行存储过程的方法
    dt = Helper.SQLHelper.SP_ExecuteDataTable("usp_Area_datapage", ps);
    //执行ExecuteDataTable()方法后
    //获取两个输出参数传出的值
    //将对应的值赋值给label标签显示
    //获取存储过程返回的总页数
    PCount = Convert.ToInt32(ps[2].Value);
    //标签显示总页数
    lbPCount.Text = PCount.ToString();
    //获取存储过程返回的总条数
    RCount = Convert.ToInt32(ps[3].Value);
    //标签显示总条数
    lbRCount.Text = RCount.ToString();
    //重新绑定数据
    dgv1.DataSource = dt;
    //设置下拉列表框显示的文本
    cmbPageIndex.Text = (cmbPageIndex.SelectedIndex + 1).ToString();           
}