说明:分页显示在实际业务中经常需要用到,其SQL语句分两种
1:分页显示SQL语句
--方法一:跳过多少行,选中多少行
--每页n条,选择第m页--n= m=
--select top(n) * from 表 where 主键 not in (select top(m-)*n 主键 from 表);
select * from UserInfo
select top() * from UserInfo where Empid not in (select top((-)*) EmpId from UserInfo);
--方法二,通过rowNumber函数,但是只能当作临时表
select * from(select * ,ROW_NUMBER() over (order by EmpId) as num from UserInfo) as T
where T.num between (-)*+ and *;
--over开窗函数的的另一个用法
select top() * ,AVG(StuAge) over() as 平均年龄 from UserInfo;
2:分页显示存储过程
--- 判断存储过程是否存在,如果存在则进行删除
if(exists(select * from sys.all_objects where name ='usp_UserInfo_GetPageData'))
drop proc usp_UserInfo_GetPageData
go --- 创建分页的存储过程
create procedure usp_UserInfo_GetPageData
@PageSize int,
@PageIndex int,
@TotalCount int output
as
BEGIN
select * from
(select * ,ROW_NUMBER() over (Order by EmpId) as rowNumber from UserInfo where Delflag = ) as temp
where temp.rowNumber between (@PageSize*(@PageIndex-)+) and (@PageSize*@PageIndex)
select @TotalCount = count() from UserInfo where Delflag =
END
--- 调用存储过程
declare @TotalCount int
exec usp_UserInfo_GetPageData ,,@TotalCount out
select @TotalCount
3:在c#中实现
3.1,发现输出参数没有什么用,修改存储过程
ALTER procedure [dbo].[usp_UserInfo_GetPageData]
@PageSize int,
@PageIndex int
as
BEGIN
select * from
(select * ,ROW_NUMBER() over (Order by EmpId) as rowNumber from UserInfo where Delflag = ) as temp
where temp.rowNumber between (@PageSize*(@PageIndex-)+) and (@PageSize*@PageIndex)
END
3.2 调用代码
#region //06-06 跳到某一页---存储过程
private void btnSkip_Click(object sender, EventArgs e)
{
//01-00 设置强类型数据源
List<UserInfo> userInfoList = new List<UserInfo>();
int pageIndex =int.Parse(txtSkipPage.Text);
using (SqlConnection conn = new SqlConnection( ConnStr))
{
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "usp_UserInfo_GetPageData";
cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
cmd.CommandType = CommandType.StoredProcedure; using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//数据封装
UserInfo userInfo = new UserInfo();
userInfo.EmpId = int.Parse(reader["EmpId"].ToString());
userInfo.Pwd = reader["Pwd"].ToString();
userInfo.StuName = reader["StuName"].ToString();
userInfo.StuAge = int.Parse(reader["StuAge"].ToString());
userInfo.Delflag = Char.Parse(reader["Delflag"].ToString());
userInfo.ClassNo = int.Parse(reader["ClassNo"] == DBNull.Value ? "-1" : reader["ClassNo"].ToString());
//添加到列表中
userInfoList.Add(userInfo);
}
}
}
}
//01-06 配置数据源
this.dataGridView1.DataSource = userInfoList;
} #endregion