gridvew+objectDataSource n配置分页
来源：互联网 发布：内八字 知乎 编辑：程序博客网 时间：2024/06/03 14:21
很久以前学习过Asp.net1.1,现在才有机会学习Asp.net2.0,暂时发现GridView是个好东西,练习使用了一下它的自定义分页功能,由于不怎么熟悉,花了一点时间查资料和摸索才搞定,现在整理出来和大家一起分享讨论.
本例程采用类似三层架构编写，但不同于严格的三层架构。
第一步:编写分页通用存储过程
view plaincopy to clipboardprint?
Create Procedure UP_CommonTablePaging
(
@PageSize int,
@StartRowIndex int,
@TotalCount int,
@SelectClause varchar(255),
@TableName varchar(50),
@WhereClause varchar(255),
@OrderClause varchar(255),
@IdField varchar(50)
)
As
Begin
Declare @Sql varchar(1000)
Declare @EndRowIndex int
Declare @PageCount int
Declare @TempTable varchar(50)
Set @TempTable = '##TempResult' + Convert(varchar,getDate(),112) + replace(Convert(varchar,getDate(),108),':','')
if (Select Count(*) From tempdb.dbo.sysobjects where name =@TempTable) > 0
Begin
Select @Sql = 'Drop table ' + @TempTable
Execute (@Sql)
End
If (@IdField = '')
Begin
Select @Sql = @SelectClause + ',Identity(int,1,1) As ID '
+ ' Into ' + @TempTable
+ ' From ' + @TableName
+ @WhereClause
if (@OrderClause != '')
Set @Sql = @Sql + ' Order by ' + + @OrderClause
Execute(@Sql)
Set @TableName = @TempTable
Set @IdField = 'ID'
End
If (@StartRowIndex + @PageSize <@TotalCount)
Set @EndRowIndex = @StartRowIndex + @PageSize
Else
Set @EndRowIndex = @TotalCount
--Select @EndRowIndex
Select @Sql = 'Select * From ' + @TableName + ' Where ' + @IdField + ' >= ' + Convert(varchar(10),@StartRowIndex+1) + ' And ' + @IdField + ' <= ' + Convert(varchar(10),@EndRowIndex)
Execute (@Sql)
Select @Sql = 'Drop table ' + @TempTable
Execute (@Sql)
End
Create Procedure UP_CommonTablePaging
(
@PageSize int,
@StartRowIndex int,
@TotalCount int,
@SelectClause varchar(255),
@TableName varchar(50),
@WhereClause varchar(255),
@OrderClause varchar(255),
@IdField varchar(50)
)
As
Begin
Declare @Sql varchar(1000)
Declare @EndRowIndex int
Declare @PageCount int
Declare @TempTable varchar(50)
Set @TempTable = '##TempResult' + Convert(varchar,getDate(),112) + replace(Convert(varchar,getDate(),108),':','')
if (Select Count(*) From tempdb.dbo.sysobjects where name =@TempTable) > 0
Begin
Select @Sql = 'Drop table ' + @TempTable
Execute (@Sql)
End
If (@IdField = '')
Begin
Select @Sql = @SelectClause + ',Identity(int,1,1) As ID '
+ ' Into ' + @TempTable
+ ' From ' + @TableName
+ @WhereClause
if (@OrderClause != '')
Set @Sql = @Sql + ' Order by ' + + @OrderClause
Execute(@Sql)
Set @TableName = @TempTable
Set @IdField = 'ID'
End
If (@StartRowIndex + @PageSize <@TotalCount)
Set @EndRowIndex = @StartRowIndex + @PageSize
Else
Set @EndRowIndex = @TotalCount
--Select @EndRowIndex
Select @Sql = 'Select * From ' + @TableName + ' Where ' + @IdField + ' >= ' + Convert(varchar(10),@StartRowIndex+1) + ' And ' + @IdField + ' <= ' + Convert(varchar(10),@EndRowIndex)
Execute (@Sql)
Select @Sql = 'Drop table ' + @TempTable
Execute (@Sql)
End
存储过程对查询表是否含有递增的序号字段进行了区分，如果◎IdField参数为空，则需要构造临时表来定义自增加的序号字段，如果不为空，则不需要构造临时表处理。注意这里构造的是全局临时表，原因在于Execute方式构造的局部临时表不能在语句执行完后使用。对于简单的数据查询，可能直接从物理表查询就可以了，对于比较复杂的查询，可以考虑先用存储过程处理出查询结果，并且处理一个自增加序号字段，然后嵌套调用这个通用的自定义分页存储过程。
第二步：构建DataSet，并且建表。(没有和传统三层架构一样去采用TableAdapter构造数据访问层)
第三步：构建数据访问类，处理数据业务逻辑，这里实际上是综合了三层架构中的数据访问和业务逻辑层，对于已经有通用数据访问基类的情况下，这种变通我觉得应该是有必要的。数据访问类可以放到App_Code文件夹中也可以采用单独项目管理，由于我习惯于C/S模式下分项目管理，所以这里也单独建一个类库项目来处理业务逻辑，个人觉得对于项目比较大、层次开发分工明确的，独立出项目来处理业务逻辑应该会好些。
+ expand sourceview plaincopy to clipboardprint?
[DataObjectAttribute]
public class DataEntityGoodsInfo : DataBase
{
[DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
public static int GetMrOrgGoodsTotal()
{
string str = "Select Count(*) From MrOrgGoods ";
return (int)ExecuteScalar(str);
}
[DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
public static DataSetGoodsInfo.MrOrgGoodsDataTable FillMrOrgGoods(int maximumRows,int startRowIndex,int totalCount,string selectClause,string tableName,string whereClause,string orderClause,string idField)
{
OleDbCommand cmd = GetCommand("UP_CommonTablePaging", CommandType.StoredProcedure);
cmd.Parameters.Add("@PageSize", OleDbType.Integer).Value = maximumRows;
cmd.Parameters.Add("@PageIndex", OleDbType.Integer).Value = startRowIndex;
cmd.Parameters.Add("@TotalCount",OleDbType.Integer).Value = totalCount;
cmd.Parameters.Add("@SelectClause",OleDbType.VarChar).Value = selectClause;
cmd.Parameters.Add("@TableName", OleDbType.VarChar).Value = tableName;
cmd.Parameters.Add("@WhereClause", OleDbType.VarChar).Value = whereClause;
cmd.Parameters.Add("@OrderClause", OleDbType.VarChar).Value = orderClause;
cmd.Parameters.Add("@IdField", OleDbType.VarChar).Value = idField;
OleDbDataAdapter ada = GetDataAdapter(cmd);
DataSetGoodsInfo ds = new DataSetGoodsInfo();
ada.TableMappings.Add("Table","MrOrgGoods");
ada.Fill(ds);
return ds.MrOrgGoods;
}
}
[DataObjectAttribute]
public class DataEntityGoodsInfo : DataBase
{
[DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
public static int GetMrOrgGoodsTotal()
{
string str = "Select Count(*) From MrOrgGoods ";
return (int)ExecuteScalar(str);
}
[DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
public static DataSetGoodsInfo.MrOrgGoodsDataTable FillMrOrgGoods(int maximumRows,int startRowIndex,int totalCount,string selectClause,string tableName,string whereClause,string orderClause,string idField)
{
OleDbCommand cmd = GetCommand("UP_CommonTablePaging", CommandType.StoredProcedure);
cmd.Parameters.Add("@PageSize", OleDbType.Integer).Value = maximumRows;
cmd.Parameters.Add("@PageIndex", OleDbType.Integer).Value = startRowIndex;
cmd.Parameters.Add("@TotalCount",OleDbType.Integer).Value = totalCount;
cmd.Parameters.Add("@SelectClause",OleDbType.VarChar).Value = selectClause;
cmd.Parameters.Add("@TableName", OleDbType.VarChar).Value = tableName;
cmd.Parameters.Add("@WhereClause", OleDbType.VarChar).Value = whereClause;
cmd.Parameters.Add("@OrderClause", OleDbType.VarChar).Value = orderClause;
cmd.Parameters.Add("@IdField", OleDbType.VarChar).Value = idField;
OleDbDataAdapter ada = GetDataAdapter(cmd);
DataSetGoodsInfo ds = new DataSetGoodsInfo();
ada.TableMappings.Add("Table","MrOrgGoods");
ada.Fill(ds);
return ds.MrOrgGoods;
}
}
这里一共定义两个方法：
GetMrOrgGoodsTotal 用来返回查询记录数
FillMrOrgGoods 调用存储过程返回查询结果
第四步：建立aspx页面，这里是三层架构中的表示层。
在aspx页面拖入一个GridView和一个ObjectDataSource控件
4.1 ObjectDataSource配置数据源方法如下：
然后单级下一步后不对参数进行设置，直接点击完成。
这里MaxiumRowsParameterName属性是指每页最大行数，StartRowIndexParameterName是指每页上面开始行的索引，这里采用了默认的属性设置名称maxiumRows和startRowIndex，由于这两个参数会自动作为获取数据方法的参数，所以名称要和业务处理类中FillMrOrgGoods方法中参数名一致。
4.2 GridView属性设置
AllowPaging,AllowSort设为True,DataSourceID设为ObjectDataSource1,PageSize设定一个每页的最大行数，这个属性很重要，它会作为maxiumRows参数的值最终传递到存储过程。
4.3编辑分页模版
4.4 源文件如下
view plaincopy to clipboardprint?
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<div style="text-align:center;" mce_style="text-align:center;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CopGNo" DataSourceID="ObjectDataSource1" AllowPaging="True" AllowSorting="True" PageSize="20" OnDataBound="GridView1_DataBound" Height="478px">
<Columns>
<asp:BoundField DataField="CopGNo" HeaderText="CopGNo" ReadOnly="True" SortExpression="CopGNo" />
<asp:BoundField DataField="GName" HeaderText="GName" SortExpression="GName" />
<asp:BoundField DataField="GModel" HeaderText="GModel" SortExpression="GModel" />
<asp:BoundField DataField="Unit" HeaderText="Unit" SortExpression="Unit" />
<asp:BoundField DataField="Curr" HeaderText="Curr" SortExpression="Curr" />
<asp:BoundField DataField="CountryCode" HeaderText="CountryCode" SortExpression="CountryCode" />
</Columns>
<PagerTemplate>
<asp:Label ID="labelPage" runat="server" Font-Bold="True" Font-Size="13px" ForeColor="Blue"
Text="Label"></asp:Label>
<asp:LinkButton ID="btnFirst" runat="server" OnClick="btnFirst_Click">首页</asp:LinkButton>
<asp:LinkButton ID="btnPre" runat="server" OnClick="btnPre_Click">上一页</asp:LinkButton>
<asp:LinkButton ID="btnNext" runat="server" OnClick="btnNext_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="btnLast" runat="server" OnClick="btnLast_Click">尾页</asp:LinkButton>
转到第<asp:TextBox ID="txtPageIndex" runat="server"
Height="12px" Width="19px"></asp:TextBox>页
<asp:Button ID="btnGo" runat="server" OnClick="btnGo_Click" Text="GO" Width="25px" />
</PagerTemplate>
<RowStyle CssClass="GridViewRow" />
<SelectedRowStyle CssClass="GridViewSelectRow" />
<PagerStyle HorizontalAlign="Center" />
<HeaderStyle CssClass="GridViewHead" />
<AlternatingRowStyle CssClass="GridViewAlternateRow" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="FillMrOrgGoods" TypeName="WebApp.EmsBusiness.Goods.DataEntityGoodsInfo" EnablePaging="True" OnSelecting="ObjectDataSource1_Selecting" OldValuesParameterFormatString="{0}" SelectCountMethod="GetMrOrgGoodsTotal">
<SelectParameters>
<asp:Parameter Name="maximumRows" Type="Int32" />
<asp:Parameter Name="startRowIndex" Type="Int32" />
<asp:Parameter Name="totalCount" Type="Int32" />
<asp:Parameter Name="selectClause" Type="String" />
<asp:Parameter Name="tableName" Type="String" />
<asp:Parameter Name="whereClause" Type="String" />
<asp:Parameter Name="orderClause" Type="String" />
<asp:Parameter Name="idField" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
</div>
</asp:Content>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<div style="text-align:center;" mce_style="text-align:center;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CopGNo" DataSourceID="ObjectDataSource1" AllowPaging="True" AllowSorting="True" PageSize="20" OnDataBound="GridView1_DataBound" Height="478px">
<Columns>
<asp:BoundField DataField="CopGNo" HeaderText="CopGNo" ReadOnly="True" SortExpression="CopGNo" />
<asp:BoundField DataField="GName" HeaderText="GName" SortExpression="GName" />
<asp:BoundField DataField="GModel" HeaderText="GModel" SortExpression="GModel" />
<asp:BoundField DataField="Unit" HeaderText="Unit" SortExpression="Unit" />
<asp:BoundField DataField="Curr" HeaderText="Curr" SortExpression="Curr" />
<asp:BoundField DataField="CountryCode" HeaderText="CountryCode" SortExpression="CountryCode" />
</Columns>
<PagerTemplate>
<asp:Label ID="labelPage" runat="server" Font-Bold="True" Font-Size="13px" ForeColor="Blue"
Text="Label"></asp:Label>
<asp:LinkButton ID="btnFirst" runat="server" OnClick="btnFirst_Click">首页</asp:LinkButton>
<asp:LinkButton ID="btnPre" runat="server" OnClick="btnPre_Click">上一页</asp:LinkButton>
<asp:LinkButton ID="btnNext" runat="server" OnClick="btnNext_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="btnLast" runat="server" OnClick="btnLast_Click">尾页</asp:LinkButton>
转到第<asp:TextBox ID="txtPageIndex" runat="server"
Height="12px" Width="19px"></asp:TextBox>页
<asp:Button ID="btnGo" runat="server" OnClick="btnGo_Click" Text="GO" Width="25px" />
</PagerTemplate>
<RowStyle CssClass="GridViewRow" />
<SelectedRowStyle CssClass="GridViewSelectRow" />
<PagerStyle HorizontalAlign="Center" />
<HeaderStyle CssClass="GridViewHead" />
<AlternatingRowStyle CssClass="GridViewAlternateRow" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="FillMrOrgGoods" TypeName="WebApp.EmsBusiness.Goods.DataEntityGoodsInfo" EnablePaging="True" OnSelecting="ObjectDataSource1_Selecting" OldValuesParameterFormatString="{0}" SelectCountMethod="GetMrOrgGoodsTotal">
<SelectParameters>
<asp:Parameter Name="maximumRows" Type="Int32" />
<asp:Parameter Name="startRowIndex" Type="Int32" />
<asp:Parameter Name="totalCount" Type="Int32" />
<asp:Parameter Name="selectClause" Type="String" />
<asp:Parameter Name="tableName" Type="String" />
<asp:Parameter Name="whereClause" Type="String" />
<asp:Parameter Name="orderClause" Type="String" />
<asp:Parameter Name="idField" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
</div>
</asp:Content>
第五步：代码处理
view plaincopy to clipboardprint?
private int recordCount = 0;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (e.ExecutingSelectCount)
{
e.InputParameters.Clear();
e.InputParameters.Add("whereClause", this.Session["whereClause"]);
}
else
{
e.InputParameters["selectClause"] = "Select CopGNo,GName,GModel,Unit,Curr,CountryCode ";
e.InputParameters["tableName"] = "MrOrgGoods";
this.recordCount = DataEntityGoodsInfo.GetMrOrgGoodsTotal(this.Session["whereClause"].ToString());
e.InputParameters["totalCount"] = recordCount;
e.InputParameters["whereClause"] = this.Session["whereClause"];
string order = this.GridView1.SortExpression;
if (order != "")
order = order + (this.GridView1.SortDirection == SortDirection.Ascending ? " Asc " : " Desc");
e.InputParameters["orderClause"] = order;
e.InputParameters["idField"] = "";
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
this.GridView1.PageIndex = int.Parse(((TextBox)this.GridView1.BottomPagerRow.FindControl("txtPageIndex")).Text) -1;
this.ObjectDataSource1.Select();
}
protected void GridView1_DataBound(object sender, EventArgs e)
{
Label a = (Label)this.GridView1.BottomPagerRow.FindControl("labelPage");
int pageIndex = this.GridView1.PageIndex + 1;
a.Text = "找到" + this.recordCount + "条记录,共" + this.GridView1.PageCount + "页,当前第" + pageIndex + "页" ;
this.protectedButton();
}
protected void btnFirst_Click(object sender, EventArgs e)
{
this.loadPageData(0);
}
protected void btnPre_Click(object sender, EventArgs e)
{
this.loadPageData(this.GridView1.PageIndex - 1);
}
protected void btnNext_Click(object sender, EventArgs e)
{
this.loadPageData(this.GridView1.PageIndex + 1);
}
protected void btnLast_Click(object sender, EventArgs e)
{
this.loadPageData(this.GridView1.PageCount-1);
}
private void loadPageData(int pageIndex)
{
this.GridView1.PageIndex = pageIndex;
this.ObjectDataSource1.Select();
}
private void protectedButton()
{
int currentPageIndex = this.GridView1.PageIndex;
int pageCount = this.GridView1.PageCount;
LinkButton btnFirst = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnFirst");
LinkButton btnPre = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnPre");
LinkButton btnNext = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnNext");
LinkButton btnLast = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnLast");
btnFirst.Enabled = currentPageIndex > 0;
btnPre.Enabled = currentPageIndex > 0;
btnNext.Enabled = currentPageIndex < pageCount-1;
btnLast.Enabled = currentPageIndex < pageCount - 1;
}
private int recordCount = 0;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (e.ExecutingSelectCount)
{
e.InputParameters.Clear();
e.InputParameters.Add("whereClause", this.Session["whereClause"]);
}
else
{
e.InputParameters["selectClause"] = "Select CopGNo,GName,GModel,Unit,Curr,CountryCode ";
e.InputParameters["tableName"] = "MrOrgGoods";
this.recordCount = DataEntityGoodsInfo.GetMrOrgGoodsTotal(this.Session["whereClause"].ToString());
e.InputParameters["totalCount"] = recordCount;
e.InputParameters["whereClause"] = this.Session["whereClause"];
string order = this.GridView1.SortExpression;
if (order != "")
order = order + (this.GridView1.SortDirection == SortDirection.Ascending ? " Asc " : " Desc");
e.InputParameters["orderClause"] = order;
e.InputParameters["idField"] = "";
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
this.GridView1.PageIndex = int.Parse(((TextBox)this.GridView1.BottomPagerRow.FindControl("txtPageIndex")).Text) -1;
this.ObjectDataSource1.Select();
}
protected void GridView1_DataBound(object sender, EventArgs e)
{
Label a = (Label)this.GridView1.BottomPagerRow.FindControl("labelPage");
int pageIndex = this.GridView1.PageIndex + 1;
a.Text = "找到" + this.recordCount + "条记录,共" + this.GridView1.PageCount + "页,当前第" + pageIndex + "页" ;
this.protectedButton();
}
protected void btnFirst_Click(object sender, EventArgs e)
{
this.loadPageData(0);
}
protected void btnPre_Click(object sender, EventArgs e)
{
this.loadPageData(this.GridView1.PageIndex - 1);
}
protected void btnNext_Click(object sender, EventArgs e)
{
this.loadPageData(this.GridView1.PageIndex + 1);
}
protected void btnLast_Click(object sender, EventArgs e)
{
this.loadPageData(this.GridView1.PageCount-1);
}
private void loadPageData(int pageIndex)
{
this.GridView1.PageIndex = pageIndex;
this.ObjectDataSource1.Select();
}
private void protectedButton()
{
int currentPageIndex = this.GridView1.PageIndex;
int pageCount = this.GridView1.PageCount;
LinkButton btnFirst = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnFirst");
LinkButton btnPre = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnPre");
LinkButton btnNext = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnNext");
LinkButton btnLast = (LinkButton)this.GridView1.BottomPagerRow.FindControl("btnLast");
btnFirst.Enabled = currentPageIndex > 0;
btnPre.Enabled = currentPageIndex > 0;
btnNext.Enabled = currentPageIndex < pageCount-1;
btnLast.Enabled = currentPageIndex < pageCount - 1;
}
5.1 ObjectDataSource1_Selecting说明
由于ObjectDataSource同时指定了SelectMethod和SelectCountMethod,但是这两个具体方法的参数是不一样的，所以需要通过e.ExecutingSelectCount来判断，避免出现提示“GetMrOrgGoodsTotal方法找不到参数”的错误。
5.2 注意页索引GridView.PageIndex是从0开始的，在处理页面跳转时需要注意。效果如下：
小结：总体来讲,GridView的自定义分页应该比1.1下DataGrid方便一些，由于我们指定了SelectCountMethod方法，所以GridView控件自身能够知道总记录数，这样控件就可以提供PageCount,PageIndex以及我们需要的每页的第一行索引startRowIndex,而不需要前台写代码来处理。
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/suosuoyyy/archive/2009/03/19/4004339.aspx
- gridvew+objectDataSource n配置分页
- ObjectDataSource自定义分页
- C# 分页 gridview+objectdatasource
- 使用ObjectDataSource实现GridView分页
- 使用ObjectDataSource和GridView分页
- ObjectDataSource+DataPager+GridView+Nhibernate分页
- ObjectDataSource与GridView的分页操作
- 使用ObjectDataSource实现GridView的高效分页
- ObjectDataSource实现GridView的分页总结
- 使用临时表配合ObjectDataSource进行分页
- ASP.NET ObjectDataSource 分页排序 筛选
- ObjectDataSource+ListView高效分页(SQL2005适用)
- ObjectDataSource 数据源分页机制（转）
- objectdatasource
- ObjectDataSource
- 如何实现ObjectDataSource与GridView的分页操作
- GridView&ObjectDataSource新特性小记 懒人篇(一) 分页上路
- GridView&ObjectDataSource 新特性小记 懒人篇(二) 分页加速
- 50种方法巧妙优化你的SQL Server数据库
- 流的管理
- 关于 Collection 接口
- STL list为双向循环链表
- JAVA面试题解惑系列（一）——类的初始化顺序
- gridvew+objectDataSource n配置分页
- Table restore and filegroups
- JAVA面试题解惑系列（二）——到底创建了几个String对象？
- 路上看水
- ubuntu10.04 LAMP
- JAVA面试题解惑系列（三）——变量（属性）的覆盖
- test
- struts2 实现图片验证码(完整代码)
- Facebook放弃HTML5转战原生应用的4个原因
