#妙笔生花创作挑战#

子查询分页的原理

使用上表,通过子查询实现数据分页结果;要实现数据分页前提是需要先使用order by语句将查询的结果集进行排序,之前说过通过SQL语句查询的结果集都是无序的,使用排序order by语句后对数据进行分页获取的数据才是真正分页所需数据

--高效的数据分页
SELECT * FROM dbo.Student
--假设每页有5条数据(页容量)
SELECT TOP 5 * FROM dbo.Student ORDER BY SId --第一页
SELECT TOP 10 * FROM dbo.Student ORDER BY SId --第二页

由上图可看出第一页使用top 5还可以查询出来,单纯使用top方式只能获取到前两页的数据,因此需要使用子查询的方式将得到的数据去除第一页数据后才可以得到第二页数据

--通过子查询获取第二页的数据
SELECT * FROM (SELECT TOP 10 * FROM dbo.Student ORDER BY SId )AS stu
WHERE stu.SId NOT IN(SELECT TOP 5 SId FROM dbo.Student ORDER BY SId) 

注释
1)第一个子查询:SELECT TOP 10 * FROM dbo.Student ORDER BY SId
被当做 上面行号2 查询语句的数据源
2)as stu(别名) 相当于SELECT * FROM student 中的 student
第二个子查询:SELECT TOP 5 SId FROM dbo.Student ORDER BY SId
被当做 上面行号3 条件语句中的条件,相当于 where sid not in(1,2,3,4,5)

第二页查询的逻辑是:首先将前10页的数据全部查询出来
然后将此数据作为子查询的数据源
通过排除此数据源的前5条得到第二页的数据

--表中共有24条数据,页容量 5 总页数分为5
--通过子查询获取第三页的数据
SELECT * FROM(SELECT TOP 15 * FROM dbo.Student ORDER BY SId )AS stu
WHERE stu.SId NOT IN(SELECT TOP 10 SId FROM dbo.Student ORDER BY SId) 
--通过子查询获取第四页的数据
SELECT * FROM(SELECT TOP 20 * FROM dbo.Student ORDER BY SId )AS stu
WHERE stu.SId NOT IN(SELECT TOP 15 SId FROM dbo.Student ORDER BY SId) 
--通过子查询获取第五页的数据
SELECT * FROM(SELECT TOP 25 * FROM dbo.Student ORDER BY SId )AS stu
WHERE stu.SId NOT IN(SELECT TOP 20 SId FROM dbo.Student ORDER BY SId)

子查询版的存储过程

根据上面查询数据分页的规律得出
第一页:获取前5条数据 1 * 5 去除前0条数据 0*5
第二页:获取前10条数据 2 * 5 去除前5条数据 1*5
第三页:获取前15条数据 3 * 5 去除前10条数据 2*5
第四页:获取前20条数据 4 * 5 去除前15条数据 3*5
第五页:获取前25条数据 5 * 5 去除前20条数据 4*5

--按规律创建子查询版数据分页的存储过程
CREATE PROC usp_student_datapage
--声明变量
@pageIndex INT=1,--页码(默认第一页)
@pageSize INT=5  --页容量(默认页容量 5)
AS 
BEGIN
SELECT * FROM(SELECT TOP (@pageIndex*@pageSize) * FROM dbo.Student ORDER BY SId)AS stu
WHERE stu.SId NOT IN(SELECT TOP ((@pageIndex-1)*@pageSize) SId FROM dbo.Student ORDER BY SId)
END
--执行时使用默认参数

在类库的SQLHelper类中添加执行存储过程的SP_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;
    }
}

1)应该在窗体的加载事件中就显示数据(自己实现)
2)在查询按钮的单击事件中编写实现代码
private void btnQuery_Click(object sender, EventArgs e)
{
    //为参数赋值
    SqlParameter[] ps = { 
    new SqlParameter("@pageIndex",txtPIndex.Text),
    new SqlParameter("@pageSize",txtPSize.Text) };
    //调用类库的存储过程方法,获取分页数据
    DataTable dt = Helper.SQLHelper.SP_ExecuteDataTable("usp_student_datapage", ps);
    //将数据绑定到DataGrridView控件上
    dgv1.DataSource = dt;
}