使用 Gridview 的默认分页功能当然非常简单方便,问题是数据量比较大的时候会有一些延时(因为 Gridview 是把所有记录都加载到内存进行分页的),如果使用objectDataSource 配合 GridView(或其他控件)使用存储过程分页不存在这种情况,可以轻易实现大批量记录分页。
拿100万条记录试验了一下:sqlDataSource + Gridview 自动分页后每翻一页需要大概10多秒,而使用 objectDataSource + GridView + 存储过程分页,每翻一页只需要不到两秒钟就可以了。
这里面最关键的有几点:
1.存储过程必须的两个参数要搞明白是什么意思,不是随便写个分页的存储过程就能用的!
2.必须给 objectDataSource 的 SelectCountMethod 指定一个返回 int 类型的记录总数
3.objectDataSource 的 EnablePaging 属性值指定为 True 时默认会提交两个参数(maximumRows 和 startRowIndex),就是存储过程中用到的那两个参数。默认在代码中是没有的,但是在 SelectMethod 方法中只管接收就是了(可以手工指定参数名)
数据库结构:
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
CREATE TABLE [dbo].[t12](
[id] [int] IDENTITY(1,1) NOT NULL,
[iname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ipass] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
存储过程:
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
CREATE PROCEDURE [dbo].[SelectT12]
(
@StartRowIndex int = null,
@MaximumRows int = null
)
AS
SET NOCOUNT ON
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @StartRowIndex
SET @PageUpperBound = @PageLowerBound + @MaximumRows + 1
SELECT * FROM T12
WHERE ID > @PageLowerBound And ID < @PageUpperBound
RETURN
页面布局:
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<div>
<asp:ObjectDataSource ID="objectDS" runat="server" EnablePaging="True"
SelectCountMethod="SelectT12Count" SelectMethod="SelectT12" TypeName="T12">
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" DataSourceID="objectDS"
AllowPaging="True" PageSize="5" Font-Names="宋体" Font-Size="9pt"
Width="340px">
</asp:GridView>
<br />
</div>
实体类:
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/// <summary>
/// 实体类
/// </summary>
public class T12
{
#region 私有成员
public string cns
{ get { return ConfigurationManager.ConnectionStrings["mydb"].ConnectionString; } }
#endregion
#region 公共方法
public IEnumerable SelectT12(int startRowIndex,int maximumRows)
{
SqlConnection cn = new SqlConnection(cns);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SelectT13";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Add("@StartRowIndex", SqlDbType.Int).Value = startRowIndex;
cmd.Parameters.Add("@MaximumRows", SqlDbType.Int).Value = maximumRows;
SqlDataAdapter DA = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
cn.Open();
DA.Fill(ds);
cn.Close();
return ds.Tables[0].DefaultView;
}
public int SelectT12Count()
{
SqlConnection cn = new SqlConnection(cns);
SqlCommand cmd = new SqlCommand("select count(0) from T12", cn);
cn.Open();
int count = (int)cmd.ExecuteScalar();
cn.Close();
return count;
}
#endregion
}
换个样式:
再完美一点,增加一个 dropdownlist 选择页码,把翻页换成下边图片中的样式:
Gridview 增加页码样式定义:
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<asp:GridView ID="GridView1" runat="server" DataSourceID="objectDS"
AllowPaging="True" PageSize="50" Font-Names="宋体" Font-Size="9pt"
Width="577px" OnDataBound="GridView1_DataBound">
<PagerSettings Position="Top" />
<PagerTemplate>
<div style="float: left; width: 50%">
当前页:<asp:DropDownList ID="DropDownList1" runat="server" Font-Names="Tahoma"
Font-Size="8pt" AutoPostBack="True"
onselectedindexchanged="DropDownList1_SelectedIndexChanged"/>
</div>
<div style="width: 50%; float: right">
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Page" CommandArgument="First">≤First</asp:LinkButton>
-
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Page" CommandArgument="Prev"><Prev</asp:LinkButton>
-
<asp:LinkButton ID="LinkButton3" runat="server" CommandName="Page" CommandArgument="Next">Next></asp:LinkButton>
-
<asp:LinkButton ID="LinkButton4" runat="server" CommandName="Page" CommandArgument="Last">Last≥</asp:LinkButton>
</div>
</PagerTemplate>
<PagerStyle Height="20px" />
</asp:GridView>
翻页代码:
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
protected void GridView1_DataBound(object sender, EventArgs e)
{
DropDownList pageList;
pageList = (DropDownList)GridView1.TopPagerRow.Cells[0].FindControl("DropDownList1");
for (int i = 1; i <= GridView1.PageCount; i++)
{
ListItem li = new ListItem(i.ToString()+" / "+GridView1.PageCount.ToString());
if (i == GridView1.PageIndex + 1) li.Selected = true;
pageList.Items.Add(li);
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownList pageList = (DropDownList)GridView1.TopPagerRow.FindControl("DropDownList1");
GridView1.PageIndex = pageList.SelectedIndex;
}