GridView存储过程分页

抽空写一下自己用的关于GridView存储过程分页的代码，优点就不用多说了，主要是用于数据库端分页，解决数据量过大的分页问题。存储过程还是以前写的CTE方法的分页，还是在这里贴一下代码吧，具体请见存储过程分页实现代码，2005 T-SQL新增功能一文。

USE [WGSMSDbase]

/****** 对象: StoredProcedure [dbo].[sp_CTE] 脚本日期: 08/25/2007 11:39:01 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- 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 --查询条件

)

-- 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

(

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。

<tr>

<asp:LinkButton ID="lbtnFirst" runat="server" OnClick="PageIndex_Changed">首页</asp:LinkButton></td>

<asp:LinkButton ID="lbtnPrev" runat="server" OnClick="PageIndex_Changed" >上一页</asp:LinkButton></td>

<asp:LinkButton ID="lbtnNext" runat="server" OnClick="PageIndex_Changed" >下一页</asp:LinkButton></td>

<asp:LinkButton ID="lbtnLast" runat="server" OnClick="PageIndex_Changed" >末页</asp:LinkButton></td>

<asp:Label ID="Label1" runat="server" Text="当前页:"></asp:Label>

 <asp:Label ID="lblCurrentInfo" runat="server" Text="Label"></asp:Label></td>

<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();

}

posted on 2007-08-25 11:54 microant 阅读(4539) 评论(6) 收藏举报