GridView存储过程分页
抽空写一下自己用的关于GridView存储过程分页的代码,优点就不用多说了,主要是用于数据库端分页,解决数据量过大的分页问题。存储过程还是以前写的CTE方法的分页,还是在这里贴一下代码吧,具体请见存储过程分页实现代码,2005 T-SQL新增功能一文。
客户端代码如下,主要包含首页,末页,上一页,下一页,当前页码,总页码等一些必要信息,最主要的就是给按钮添加处理事件OnClick。
在服务器端主要是初始化gridview,也就是第一页。然后就是页码按钮的处理事件,代码如下:
USE [WGSMSDbase]
GO
/****** 对象: StoredProcedure [dbo].[sp_CTE] 脚本日期: 08/25/2007 11:39:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,microant>
-- Create date: <Create Date,,20070705>
-- Description: <Description,,CTE分页>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CTE](
-- Add the parameters for the stored procedure here
@TableName nvarchar(200) = 'testTable', --表名
@PageSize int = 15, --页面大小
@PageIndex int =2 , --页面的序号
--@IsCountNull bit =1, --返回记录是否为空
@IsAsc bit = 1 , --是否卫升序,升序为1,降序为0
@OderColumName nvarchar(200) = null, --排序字段名
@KeyID nvarchar(50) = 'id', --主键
@Conditions nvarchar(500) = null --查询条件
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @strSql nvarchar(1000)
declare @tempstr nvarchar(1000)
declare @orderstr nvarchar(400)
declare @ctestr nvarchar(400)
--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
if @IsAsc = 1
begin
if(@OderColumName is null or @OderColumName = '')
set @orderstr = ' order by ' + @KeyID + ' asc'
else
set @orderstr = ' order by ' + @OderColumName + ' asc'
end
else
begin
if(@OderColumName is null or @OderColumName = '')
set @orderstr = ' order by ' + @KeyID + ' desc'
else
set @orderstr = ' order by ' + @OderColumName + ' desc'
end
--CTE
set @ctestr ='with Table_CET
as
(
select
CEILING((ROW_NUMBER() OVER (' + @orderstr + '))/' + str(@PageSize) + ') as page_num, *
from ' + @TableName +
')' ;
begin
if(@Conditions is null or @Conditions = '')
set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex);
else
set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex)+ ' and ' + @Conditions;
print @strSql
end
begin
exec sp_executesql @strSql
end
GO
/****** 对象: StoredProcedure [dbo].[sp_CTE] 脚本日期: 08/25/2007 11:39:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,microant>
-- Create date: <Create Date,,20070705>
-- Description: <Description,,CTE分页>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CTE](
-- Add the parameters for the stored procedure here
@TableName nvarchar(200) = 'testTable', --表名
@PageSize int = 15, --页面大小
@PageIndex int =2 , --页面的序号
--@IsCountNull bit =1, --返回记录是否为空
@IsAsc bit = 1 , --是否卫升序,升序为1,降序为0
@OderColumName nvarchar(200) = null, --排序字段名
@KeyID nvarchar(50) = 'id', --主键
@Conditions nvarchar(500) = null --查询条件
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @strSql nvarchar(1000)
declare @tempstr nvarchar(1000)
declare @orderstr nvarchar(400)
declare @ctestr nvarchar(400)
--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
if @IsAsc = 1
begin
if(@OderColumName is null or @OderColumName = '')
set @orderstr = ' order by ' + @KeyID + ' asc'
else
set @orderstr = ' order by ' + @OderColumName + ' asc'
end
else
begin
if(@OderColumName is null or @OderColumName = '')
set @orderstr = ' order by ' + @KeyID + ' desc'
else
set @orderstr = ' order by ' + @OderColumName + ' desc'
end
--CTE
set @ctestr ='with Table_CET
as
(
select
CEILING((ROW_NUMBER() OVER (' + @orderstr + '))/' + str(@PageSize) + ') as page_num, *
from ' + @TableName +
')' ;
begin
if(@Conditions is null or @Conditions = '')
set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex);
else
set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex)+ ' and ' + @Conditions;
print @strSql
end
begin
exec sp_executesql @strSql
end
客户端代码如下,主要包含首页,末页,上一页,下一页,当前页码,总页码等一些必要信息,最主要的就是给按钮添加处理事件OnClick。
<table style="width: 328px; height: 28px">
<tr>
<td style="width: 117px; height: 28px">
<asp:LinkButton ID="lbtnFirst" runat="server" OnClick="PageIndex_Changed">首页</asp:LinkButton></td>
<td style="width: 217px; height: 28px">
<asp:LinkButton ID="lbtnPrev" runat="server" OnClick="PageIndex_Changed" >上一页</asp:LinkButton></td>
<td style="width: 240px; height: 28px">
<asp:LinkButton ID="lbtnNext" runat="server" OnClick="PageIndex_Changed" >下一页</asp:LinkButton></td>
<td style="width: 137px; height: 28px">
<asp:LinkButton ID="lbtnLast" runat="server" OnClick="PageIndex_Changed" >末页</asp:LinkButton></td>
<td style="width: 370px; height: 28px">
<asp:Label ID="Label1" runat="server" Text="当前页:"></asp:Label>
<asp:Label ID="lblCurrentInfo" runat="server" Text="Label"></asp:Label></td>
<td style="width: 326px; height: 28px">
<asp:Label ID="lblCount" runat="server" Text="Label"></asp:Label></td>
</tr>
</table>
<tr>
<td style="width: 117px; height: 28px">
<asp:LinkButton ID="lbtnFirst" runat="server" OnClick="PageIndex_Changed">首页</asp:LinkButton></td>
<td style="width: 217px; height: 28px">
<asp:LinkButton ID="lbtnPrev" runat="server" OnClick="PageIndex_Changed" >上一页</asp:LinkButton></td>
<td style="width: 240px; height: 28px">
<asp:LinkButton ID="lbtnNext" runat="server" OnClick="PageIndex_Changed" >下一页</asp:LinkButton></td>
<td style="width: 137px; height: 28px">
<asp:LinkButton ID="lbtnLast" runat="server" OnClick="PageIndex_Changed" >末页</asp:LinkButton></td>
<td style="width: 370px; height: 28px">
<asp:Label ID="Label1" runat="server" Text="当前页:"></asp:Label>
<asp:Label ID="lblCurrentInfo" runat="server" Text="Label"></asp:Label></td>
<td style="width: 326px; height: 28px">
<asp:Label ID="lblCount" runat="server" Text="Label"></asp:Label></td>
</tr>
</table>
在服务器端主要是初始化gridview,也就是第一页。然后就是页码按钮的处理事件,代码如下:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGV(this.gvTest, "A101", 5,1, 1, "CID", "CID", null);
this.lbtnPrev.Enabled = false;
this.lblCurrentInfo.Text = "1";
this.lblCount.Text = getPageNum("A101", 5).ToString() ;
}
}
public int getPageNum(string tableName, int pagesize)
{
using (SqlDataObject sodo = new SqlDataObject())
{
string strSql = "select count(*) from " + tableName;
int count =Convert.ToInt32(sodo.ExecuteScalar(strSql));
int pagenum =Convert.ToInt32(Math.Ceiling(Convert.ToDouble(count/pagesize)));
return pagenum;
}
}
/// <summary>
/// 按钮处理事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void PageIndex_Changed(object sender, EventArgs e)
{
LinkButton linkbtn = (LinkButton)sender;
switch (linkbtn.ID.Trim())
{
case "lbtnFirst":
BindGV(this.gvTest, "A101", 5, 1, 1, "CID", "CID", null);
this.lblCurrentInfo.Text = "1";
break;
case "lbtnPrev":
BindGV(this.gvTest, "A101", 5, Convert.ToInt32(this.lblCurrentInfo.Text) - 1, 1, "CID", "CID", null);
this.lblCurrentInfo.Text = (Convert.ToInt32(this.lblCurrentInfo.Text) - 1).ToString();
break;
case "lbtnNext":
BindGV(this.gvTest, "A101", 5, Convert.ToInt32(this.lblCurrentInfo.Text) + 1, 1, "CID", "CID", null);
this.lblCurrentInfo.Text = (Convert.ToInt32(this.lblCurrentInfo.Text) + 1).ToString();
break;
case "lbtnLast":
BindGV(this.gvTest, "A101", 5, Convert.ToInt32(this.lblCount.Text), 1, "CID", "CID", null);
this.lblCurrentInfo.Text = this.lblCount.Text;
break;
}
if (Convert.ToInt32(this.lblCurrentInfo.Text) >= Convert.ToInt32(this.lblCount.Text) && Convert.ToInt32(this.lblCurrentInfo.Text) <=1)
{
this.lbtnPrev.Enabled = false;
this.lbtnNext.Enabled = false;
this.lbtnFirst.Enabled = false;
this.lbtnLast.Enabled = false;
}
else if (Convert.ToInt32(this.lblCurrentInfo.Text) >= Convert.ToInt32(this.lblCount.Text))
{
this.lbtnPrev.Enabled = true;
this.lbtnNext.Enabled = false;
this.lbtnFirst.Enabled = true;
this.lbtnLast.Enabled = false;
}
else if (Convert.ToInt32(this.lblCurrentInfo.Text) <= 1)
{
this.lbtnPrev.Enabled = false;
this.lbtnNext.Enabled = true;
this.lbtnFirst.Enabled = false;
this.lbtnLast.Enabled = true;
}
else
{
this.lbtnPrev.Enabled = true;
this.lbtnNext.Enabled = true;
this.lbtnFirst.Enabled = true;
this.lbtnLast.Enabled = true;
}
}
/// <summary>
/// 绑定到gridview
/// </summary>
/// <param name="gv">gridview</param>
/// <param name="tableName">分页的表名</param>
/// <param name="size">页码大小</param>
/// <param name="pageindex">第几页</param>
/// <param name="isAsc">升降序</param>
/// <param name="OderColumName">排序的列</param>
/// <param name="KeyID">主键</param>
/// <param name="condictions">查询条件</param>
private void BindGV(GridView gv, string tableName, int size, int pageindex, int isAsc, string OderColumName,string KeyID, string condictions)
{
using (SqlDataObject sodo = new SqlDataObject())
{
SqlParameter[] sps = new SqlParameter[7];
sps[0] = new SqlParameter("@TableName", tableName);
sps[1] = new SqlParameter("@PageSize", size);
sps[2] = new SqlParameter("@PageIndex", pageindex);
sps[3] = new SqlParameter("@IsAsc", 1);
sps[4] = new SqlParameter("@OderColumName", OderColumName);
sps[5] = new SqlParameter("@KeyID", KeyID);
sps[6] = new SqlParameter("@Conditions", condictions);
DataTable dt = sodo.getDataTable("sp", "sp_CTE", sps);
gv.DataSource = dt.DefaultView;
gv.DataBind();
}
}
{
if (!IsPostBack)
{
BindGV(this.gvTest, "A101", 5,1, 1, "CID", "CID", null);
this.lbtnPrev.Enabled = false;
this.lblCurrentInfo.Text = "1";
this.lblCount.Text = getPageNum("A101", 5).ToString() ;
}
}
public int getPageNum(string tableName, int pagesize)
{
using (SqlDataObject sodo = new SqlDataObject())
{
string strSql = "select count(*) from " + tableName;
int count =Convert.ToInt32(sodo.ExecuteScalar(strSql));
int pagenum =Convert.ToInt32(Math.Ceiling(Convert.ToDouble(count/pagesize)));
return pagenum;
}
}
/// <summary>
/// 按钮处理事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void PageIndex_Changed(object sender, EventArgs e)
{
LinkButton linkbtn = (LinkButton)sender;
switch (linkbtn.ID.Trim())
{
case "lbtnFirst":
BindGV(this.gvTest, "A101", 5, 1, 1, "CID", "CID", null);
this.lblCurrentInfo.Text = "1";
break;
case "lbtnPrev":
BindGV(this.gvTest, "A101", 5, Convert.ToInt32(this.lblCurrentInfo.Text) - 1, 1, "CID", "CID", null);
this.lblCurrentInfo.Text = (Convert.ToInt32(this.lblCurrentInfo.Text) - 1).ToString();
break;
case "lbtnNext":
BindGV(this.gvTest, "A101", 5, Convert.ToInt32(this.lblCurrentInfo.Text) + 1, 1, "CID", "CID", null);
this.lblCurrentInfo.Text = (Convert.ToInt32(this.lblCurrentInfo.Text) + 1).ToString();
break;
case "lbtnLast":
BindGV(this.gvTest, "A101", 5, Convert.ToInt32(this.lblCount.Text), 1, "CID", "CID", null);
this.lblCurrentInfo.Text = this.lblCount.Text;
break;
}
if (Convert.ToInt32(this.lblCurrentInfo.Text) >= Convert.ToInt32(this.lblCount.Text) && Convert.ToInt32(this.lblCurrentInfo.Text) <=1)
{
this.lbtnPrev.Enabled = false;
this.lbtnNext.Enabled = false;
this.lbtnFirst.Enabled = false;
this.lbtnLast.Enabled = false;
}
else if (Convert.ToInt32(this.lblCurrentInfo.Text) >= Convert.ToInt32(this.lblCount.Text))
{
this.lbtnPrev.Enabled = true;
this.lbtnNext.Enabled = false;
this.lbtnFirst.Enabled = true;
this.lbtnLast.Enabled = false;
}
else if (Convert.ToInt32(this.lblCurrentInfo.Text) <= 1)
{
this.lbtnPrev.Enabled = false;
this.lbtnNext.Enabled = true;
this.lbtnFirst.Enabled = false;
this.lbtnLast.Enabled = true;
}
else
{
this.lbtnPrev.Enabled = true;
this.lbtnNext.Enabled = true;
this.lbtnFirst.Enabled = true;
this.lbtnLast.Enabled = true;
}
}
/// <summary>
/// 绑定到gridview
/// </summary>
/// <param name="gv">gridview</param>
/// <param name="tableName">分页的表名</param>
/// <param name="size">页码大小</param>
/// <param name="pageindex">第几页</param>
/// <param name="isAsc">升降序</param>
/// <param name="OderColumName">排序的列</param>
/// <param name="KeyID">主键</param>
/// <param name="condictions">查询条件</param>
private void BindGV(GridView gv, string tableName, int size, int pageindex, int isAsc, string OderColumName,string KeyID, string condictions)
{
using (SqlDataObject sodo = new SqlDataObject())
{
SqlParameter[] sps = new SqlParameter[7];
sps[0] = new SqlParameter("@TableName", tableName);
sps[1] = new SqlParameter("@PageSize", size);
sps[2] = new SqlParameter("@PageIndex", pageindex);
sps[3] = new SqlParameter("@IsAsc", 1);
sps[4] = new SqlParameter("@OderColumName", OderColumName);
sps[5] = new SqlParameter("@KeyID", KeyID);
sps[6] = new SqlParameter("@Conditions", condictions);
DataTable dt = sodo.getDataTable("sp", "sp_CTE", sps);
gv.DataSource = dt.DefaultView;
gv.DataBind();
}
}