首页 > 代码库 > GridView真分页全套复用代码(转)
GridView真分页全套复用代码(转)
原文地址：GridView真分页全套复用代码作者：jacland
1、页面部分：
<PagerTemplate>
<div style="display: inline; float:right;">
<asp:TextBox ID="txbPage" runat="server" BorderStyle="none" Text=‘<%# "总共"+ TotalRecord.ToString() + "条记录 当前第" + Convert.ToString(PageIndex+1) + "/" + PageCount + "页" %>‘ ReadOnly="true" Width="250"></asp:TextBox>
<asp:LinkButton ID="FirstPage" runat="server" CausesValidation="false" CommandArgument="First" CommandName="Page" Enable="<%# PageIndex != 0 %>">第一页</asp:LinkButton>
<asp:LinkButton ID="PreviousPage" runat="server" CommandArgument=‘<%# PageIndex %>‘ CommandName="Page" Enable="<%# PageIndex != 0 %>">上一页</asp:LinkButton>
<asp:LinkButton ID="NextPage" runat="server" CommandArgument=‘<%# PageIndex + 2 %>‘ CommandName="Page" Enable="<%# PageIndex != PageCount - 1 %>">下一页</asp:LinkButton>
<asp:LinkButton ID="LastPage" runat="server" CommandArgument=‘<%# PageCount %>‘ CommandName="Page" Enable="<%# PageIndex != PageCount - 1 %>">最后一页</asp:LinkButton>
<asp:TextBox ID="TextGotoPage" runat="server" CssClass="TextBox" Width="30"></asp:TextBox>
<asp:Button ID="PageGo" causesvalidation="False" commandargument="-1" commandname="Page" runat="server" CssClass="Button" Text="Go" Width="25" /></div>
</PagerTemplate>
2、页面代码字段属性
private int totalRcord = 0;
private int pageIndex = 0;
/// <summary>
/// GridView的当前页，存储在ViewState中
/// </summary>
protected int PageIndex
{
get
{
if (int.TryParse(ViewState["pageIndex"].ToString(), out pageIndex))
{
return pageIndex;
}
else
{
return 0;
}
}
set
{
ViewState.Add("pageIndex", value);
}
}
/// <summary>
/// 页数总计
/// </summary>
protected int PageCount
{
get { return Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(TotalRecord) / gvwBudInfo.PageSize)); }
}
/// <summary>
/// 记录总条数
/// </summary>
protected int TotalRecord
{
get
{
if (int.TryParse(ViewState["totalRecord"].ToString(), out totalRcord))
{
return totalRcord;
}
else
{
return 0;
}
}
set
{
ViewState.Add("totalRecord", value);
}
}
3、数据绑定
DataSet ds = new BLL().QueryInfo(model, gvw.PageSize, PageIndex, out totalRcord);
gvw.PageIndex = PageIndex == 0 ? 0 : 1;//跳过前面空行
TotalRecord = totalRcord;
gvw.DataSource = ds;
4、翻页事件
GridView theGrid = (GridView)sender;
int newPageIndex = 0;
GridViewRow pagerRow = theGrid.BottomPagerRow;
//GridViewRow pagerRow = theGrid.Controls[0].Controls[theGrid.Controls[0].Controls.Count - 1] as GridViewRow; // refer to PagerTemplate
//GridView较DataGrid提供了更多的API，获取分页块可以使用BottomPagerRow 或者TopPagerRow，当然还增加了HeaderRow和FooterRow
if (-2 == e.NewPageIndex)//点击按钮的事件
{
TextBox txtNewPageIndex = null;
if (null != pagerRow)
{
// refer to the TextBox with the NewPageIndex value
txtNewPageIndex = (TextBox)pagerRow.FindControl("TextGotoPage");
}
if (null != txtNewPageIndex && txtNewPageIndex.Text != "")
{
newPageIndex = int.Parse(txtNewPageIndex.Text) - 1;//Get the NewPageIndex
}
else
{
newPageIndex = 0;
}
}
else
{
//当点击分页连接的时候
newPageIndex = e.NewPageIndex;
}
//处理超出范围的分页
newPageIndex = newPageIndex < 0 ? 0 : newPageIndex;
newPageIndex = newPageIndex >= PageCount ? PageCount - 1 : newPageIndex;
((TextBox)pagerRow.FindControl("TextGotoPage")).Text = Convert.ToString(newPageIndex + 1);
//绑定数据源
PageIndex = newPageIndex;
BindInfo();
5、数据操作层
StringBuilder sb = new StringBuilder(" 1 = 1 ");
if (!string.IsNullOrEmpty(model.YY)) //年度
{
sb.Append(string.Format(" AND yy = ‘{0}‘ ", model.YY.Replace("‘", "‘‘")));
}
//……//查询条件
SqlParameter[] parameters =
{
new SqlParameter("@tblName", SqlDbType.VarChar),
new SqlParameter("@PKName", SqlDbType.VarChar),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@OrderField_Type", SqlDbType.VarChar),
new SqlParameter("@strWhere", SqlDbType.VarChar),
new SqlParameter("@FieldList", SqlDbType.VarChar),
new SqlParameter("@TotalRecord", SqlDbType.Int),
};
parameters[0].Value = "http://www.mamicode.com/v_AppBudInfo";//表名或视图名
parameters[1].Value = "http://www.mamicode.com/yy,subID,deptID";//主键及排序字段，逗号后不可有空格
parameters[2].Value = http://www.mamicode.com/pageSize;
parameters[3].Value = http://www.mamicode.com/pageIndex + 1;
parameters[4].Value = "http://www.mamicode.com/yy asc,deptID asc,subID asc";//区分大小写， asc，desc必小写，逗号后不可有空格
parameters[5].Value = http://www.mamicode.com/sb.ToString();
parameters[6].Value = "http://www.mamicode.com/yy,subID,subName,compID,compName,deptID,DeptName,budtotal,qbudtotal,payouttotal,qpayouttotal,lybudamt,budamt,sbudamt,remark,setdate,userid,downdate,uptodate,upuserid,auddate,auduserid,cleartype,cleartypeName";
parameters[7].Direction = ParameterDirection.Output; //查询字段,逗号后不可有空格
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringProfile))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("GetRecordByPage", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
totalRecord = Convert.ToInt32(parameters[7].Value);
if (totalRecord > 0 && ds.Tables[0].Rows.Count == 0)
{//当删除最后一行数据时，可能引起页数变化，重新查询最后一页
parameters[3].Value = http://www.mamicode.com/Math.Ceiling(Convert.ToDecimal(totalRecord) / pageSize);
da.Fill(ds);
}
//生成伪数据,便于使GridView显示分页栏
for (int i = 0; i < (pageIndex == 0 ? 0 : pageSize); i++)
{
ds.Tables[0].Rows.InsertAt(ds.Tables[0].NewRow(), 0);
}
if (totalRecord > (pageIndex + 1) * pageSize)
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
}
return ds;
}
catch (SqlException)
{
totalRecord = 0;
return null;
}
}
6、存储过程
CREATE PROCEDURE GetRecordByPage
--自写，采用反向排序的方式,非主键或唯一键操作引起严重后果，添加不合理的where条件也是
--字段列表用逗号隔开，不可有任何空格
--排序字段也当作主键字段传入
@tblName varchar(255), -- 表名
@PKName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderField_Type varchar(255) = ‘‘, -- 排序字段及方式，用逗号隔开, asc，desc必须小写
@strWhere varchar(1000) = ‘‘, -- 查询条件 (注意: 不要加 where)
@FieldList varchar(1000) , -- 返回的字段，用逗号隔开
@TotalRecord int = 0 out -- 返回记录总数
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(400) -- 临时逆向排序表达
declare @strOrder varchar(400) -- 排序子句
declare @RecordCount integer -- 记录总数
if @PageSize < 1 --页面大小不小于1
set @PageSize = 10;
if @PageIndex < 1 --页码不小于1
set @PageIndex = 1
if @strWhere != ‘‘ --生成where条件
set @strWhere = ‘ where ‘ + @strWhere
--查询总数
set @strSQL = ‘select @Count = count(*) from [‘ + @tblName + ‘] ‘+ @strWhere
declare @a nvarchar(4000) --把@strSQL转换为nvarchar的临时变量
set @a = cast(@strSQL as nvarchar(4000))
exec sp_executesql @a,N‘@Count int output‘,@RecordCount output
set @TotalRecord = @RecordCount
--查询记录
if @PageIndex = 1 --页数为第1页时，直接查询
begin
if @OrderField_Type != ‘‘ --有排序字段
set @strOrder = ‘ order by ‘ + @OrderField_Type
else --无排序字段
set @strOrder = ‘ order by ‘ + @PKName +‘ ‘
set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ ‘ + @FieldList + ‘ from [‘+ @tblName + ‘] ‘ + @strWhere + ‘ ‘ + @strOrder
end
else
begin
if @OrderField_Type != ‘‘ --有排序字段
begin --此处区分大小写！！！！
set @strTmp = replace(@OrderField_Type, ‘asc‘, ‘temp‘)
set @strTmp = replace(@strTmp, ‘desc‘, ‘asc‘)
set @strTmp = replace(@strTmp, ‘temp‘, ‘desc‘)
--把排序字段加上前缀
set @strOrder = ‘ order by ‘+ @tblName + ‘.‘ + replace(@OrderField_Type, ‘,‘, ‘,‘ + @tblName + ‘.‘)
set @strTmp = ‘ order by temp.‘ + replace(@strTmp, ‘,‘, ‘,temp.‘)
end
if @RecordCount < (@PageIndex*@PageSize) --根据记录总条数决定PageSize大小
set @RecordCount = @RecordCount - (@PageIndex - 1) * @PageSize
else
set @RecordCount = @PageSize
--把字段列表中的主键加上表名前缀 --生成内联条件
declare @joinField varchar(500) --内联条件
declare @i int --字符旧位置
set @i = 0
declare @index int --字符新位置
set @index = charindex(‘,‘,@PKName, 0)
if @index = 0 --没有逗号，只有一个字段
begin
set @FieldList = ‘,‘ + @FieldList --加个逗号，区分完整字段
set @FieldList = replace(@FieldList,‘,‘ + @PKName + ‘,‘ ,‘,‘ + @tblName + ‘.‘ + @PKName + ‘,‘)
set @FieldList = substring(@FieldList, 2, Len(@FieldList) - 1)
set @joinField = @tblName + ‘.‘ + @PKName + ‘ = temp2.‘ + @PKName
end
else --有多个字段
begin
set @joinField = ‘ 1 = 1 ‘
set @FieldList = ‘,‘ + @FieldList --加个逗号，区分完整字段
declare @PKTemp varchar(255)--临时主键列表
WHILE @index > 0
BEGIN
set @PKTemp = substring(@PKName, @i + 1, @index-@i - 1) --左边第一个字段
set @joinField = @joinField + ‘ AND ‘ + @tblName + ‘.‘ + @PKTemp + ‘ = temp2.‘ + @PKTemp --生成内联条件
set @FieldList = replace(@FieldList,‘,‘ + @PKTemp + ‘,‘ ,‘,‘ + @tblName + ‘.‘ + @PKTemp + ‘,‘) --替换
set @i = @index
set @index = charindex(‘,‘, @PKName, @i+1)--下一个逗号
END
set @PKTemp = substring(@PKName, @i + 1, len(@PKName)-@i) --左边第一个字段
set @joinField = @joinField + ‘ AND ‘ + @tblName + ‘.‘ + @PKTemp + ‘ = temp2.‘ + @PKTemp --生成内联条件
set @FieldList = replace(@FieldList,‘,‘ + @PKTemp + ‘,‘ ,‘,‘ + @tblName + ‘.‘ + @PKTemp + ‘,‘) --替换
set @FieldList = substring(@FieldList, 2, Len(@FieldList) - 1)
end
--把主键列表加上表名前缀
set @PKName = @tblName + ‘.‘ + @PKName;--给第一个主键加上表名前缀
set @PKName = replace(@PKName, ‘,‘, ‘,‘ + @tblName + ‘.‘)--给后面的主键加上表名前缀
set @strSQL = ‘select top ‘+str(@RecordCount)+‘ ‘+@FieldList+‘ from [‘+@tblName+‘] inner join
(select top ‘+str(@RecordCount)+‘ ‘+replace(@PKName, @tblName , ‘temp‘)+‘ from
(select top ‘+str(@PageIndex*@PageSize)+‘ ‘+@PKName+‘ from [‘+@tblName+‘] ‘+@strWhere+‘ ‘+@strOrder+‘ ) as temp
‘+@strTmp+‘
) as temp2 on ‘ + @joinField + ‘ ‘ + @strOrder+‘ ‘
end
exec (@strSQL)
GO