DatakeyNames和datakey

  1. 为什么不使用GridView的默认分页功能
    首先要说说为什么不用GridView的默认的分页功能,GridView控件并非真正知道如何获得一个新页面,它只是请求绑定的数据源控件返回适合规定页面的行,分页最终是由数据源控件完成。当我们使用SqlDataSource或使用以上的代码处理分页时。每次这个页面被请求或者回发时,所有和这个SELECT语句匹配的记录都被读取并存储到一个内部的DataSet中,但只显示适合当前页面大小的记录数。也就是说有可能使用Select语句返回1000000条记录,而每次回发只显示10条记录。如果启用了SqlDataSource上的缓存,通过把EnableCaching设置为true,则情况会更好一些。在这种情况下,我们只须访问一次数据库服务器,整个数据集只加载一次,并在指定的期限内存储在ASP.NET缓存中。只要数据保持缓存状态,显示任何页面将无须再次访问数据库服务器。然而,可能有大量数据存储在内存中,换而言之,Web服务器的压力大大的增加了。因此,如果要使用SqlDataSource来获取较小的数据时,GridView内建的自动分页可能足够高效了,但对于大数据量来说是不合适的。
  2. 分页的四种存储过程(分页+排序的版本请参考Blog里其他文章)
    在大多数情况下我们使用存储过程来进行分页,今天有空总结了一下使用存储过程对GridView进行分页的4种写法(分别是使用Top关键字,临时表,临时表变量和SQL
    Server 2005 新加的Row_Number()函数)
    后续的文章中还将涉及GridView控件使用ObjectDataSource自定义分页 +
    排序,Repeater控件自定义分页 + 排序,有兴趣的朋友可以参考。 复制代码 代码如下: if exists(select 1 from
    sys.objects where name = GetProductsCount and type = P) drop proc
    GetProductsCount go CREATE PROCEDURE GetProductsCount as select
    count(*) from products go –1.使用Top if exists(select 1 from
    sys.objects where name = GetProductsByPage and type = P) drop proc
    GetProductsByPage go CREATE PROCEDURE GetProductsByPage @PageNumber int,
    @PageSize int AS declare @sql nvarchar(4000) set @sql = select top +
    Convert(varchar, @PageSize) + * from products where productid not in
    (select top + Convert(varchar, (@PageNumber – 1) * @PageSize) +
    productid from products) exec sp_executesql @sql go –exec
    GetProductsByPage 1, 10 –exec GetProductsByPage 5, 10 –2.使用临时表 if
    exists(select 1 from sys.objects where name = GetProductsByPage and type
    = P) drop proc GetProductsByPage go CREATE PROCEDURE GetProductsByPage
    @PageNumber int, @PageSize int AS — 创建临时表 CREATE TABLE
    #TempProducts ( ID int IDENTITY PRIMARY KEY, ProductID int, ProductName
    varchar(40) , SupplierID int, CategoryID int, QuantityPerUnit
    nvarchar(20), UnitPrice money, UnitsInStock smallint, UnitsOnOrder
    smallint, ReorderLevel smallint, Discontinued bit ) — 填充临时表 INSERT
    INTO #TempProducts (ProductID, ProductName, SupplierID, CategoryID,
    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
    Discontinued) SELECT ProductID, ProductName, SupplierID, CategoryID,
    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
    Discontinued FROM Products DECLARE @FromID int DECLARE @ToID int SET
    @FromID = ((@PageNumber – 1) * @PageSize) + 1 SET @ToID = @PageNumber
    * @PageSize SELECT ProductID, ProductName, SupplierID, CategoryID,
    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
    Discontinued FROM #TempProducts WHERE ID = @FromID AND ID = @ToID go
    –exec GetProductsByPage 1, 10 –exec GetProductsByPage 5, 10
    –3.使用表变量 /*
    为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里就和一个row
    index(通过IDENTITY列)关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SET
    ROWCOUNT用来限制放到table变量里的记录的数量. 当SET
    ROWCOUNT的值指定为PageNumber *
    PageSize时,这个方法的效率取决于被请求的页数.对于比较前面的页来说–
    比如开始几页的数据– 这种方法非常有效.
    但是对接近尾部的页来说,这种方法的效率和默认分页时差不多 */ if
    exists(select 1 from sys.objects where name = GetProductsByPage and type
    = P) drop proc GetProductsByPage go CREATE PROCEDURE GetProductsByPage
    @PageNumber int, @PageSize int AS DECLARE @TempProducts TABLE ( ID int
    IDENTITY, productid int ) DECLARE @maxRows int SET @maxRows =
    @PageNumber * @PageSize –在返回指定的行数之后停止处理查询 SET ROWCOUNT
    @maxRows INSERT INTO @TempProducts (productid) SELECT productid FROM
    products ORDER BY productid SET ROWCOUNT @PageSize SELECT p.* FROM
    @TempProducts t INNER JOIN products p ON t.productid = p.productid WHERE
    ID (@PageNumber – 1) * @PageSize SET ROWCOUNT 0 GO –exec
    GetProductsByPage 1, 10 –exec GetProductsByPage 5, 10
    –4.使用row_number函数 –SQL Server
    2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关
    这个等级可以用来作为每条记录的row index. if exists(select 1 from
    sys.objects where name = GetProductsByPage and type = P) drop proc
    GetProductsByPage go CREATE PROCEDURE GetProductsByPage @PageNumber int,
    @PageSize int AS select ProductID, ProductName, SupplierID, CategoryID,
    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
    Discontinued from (select row_number() Over (order by productid) as
    row,ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued from
    products) as ProductsWithRowNumber where row between (@PageNumber – 1)
    * @PageSize + 1 and @PageNumber * @PageSize go –exec
    GetProductsByPage 1, 10 –exec GetProductsByPage 5, 10 3.
    在GridView中的应用 复制代码 代码如下: %@
    Page Language=”C#” AutoEventWireup=”true”
    CodeFile=”GridViewPaging.aspx.cs” Inherits=”GridViewPaging” % !DOCTYPE
    html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “” html xmlns=””
    head runat=”server” titlePaging/title /head body form runat=”server” div
    asp:LinkButton runat=”server” CommandName=”First”
    OnCommand=”lbtnPage_Command”|/asp:LinkButton asp:LinkButton
    runat=”server” CommandName=”Previous”
    OnCommand=”lbtnPage_Command”/asp:LinkButton asp:Label runat=”server” /
    asp:LinkButton runat=”server” CommandName=”Next”
    OnCommand=”lbtnPage_Command”/asp:LinkButton asp:LinkButton
    runat=”server” CommandName=”Last”
    OnCommand=”lbtnPage_Command”|/asp:LinkButton 转到第asp:DropDownList
    ID=”dropPage” runat=”server” AutoPostBack=”True”
    OnSelectedIndexChanged=”dropPage_SelectedIndexChanged”/asp:DropDownList页
    asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”
    DataKeyNames=”ProductID” DataSourceID=”SqlDataSource1″ Columns
    asp:BoundField DataField=”ProductID” HeaderText=”ProductID”
    InsertVisible=”False” ReadOnly=”True” / asp:BoundField
    DataField=”ProductName” HeaderText=”ProductName” / asp:BoundField
    DataField=”SupplierID” HeaderText=”SupplierID” / asp:BoundField
    DataField=”CategoryID” HeaderText=”CategoryID” / asp:BoundField
    DataField=”QuantityPerUnit” HeaderText=”QuantityPerUnit” /
    asp:BoundField DataField=”UnitPrice” HeaderText=”UnitPrice” /
    asp:BoundField DataField=”UnitsInStock” HeaderText=”UnitsInStock” /
    asp:BoundField DataField=”UnitsOnOrder” HeaderText=”UnitsOnOrder” /
    asp:BoundField DataField=”ReorderLevel” HeaderText=”ReorderLevel” /
    asp:CheckBoxField DataField=”Discontinued” HeaderText=”Discontinued” /
    /Columns /asp:GridView asp:SqlDataSource ID=”SqlDataSource1″
    runat=”server” ConnectionString=”Data Source=.\sqlexpress;Initial
    Catalog=Northwind;Integrated Security=True”
    ProviderName=”System.Data.SqlClient” SelectCommand=”GetProductsByPage”
    SelectCommandType=”StoredProcedure”
    OnSelecting=”SqlDataSource1_Selecting”
    OnSelected=”SqlDataSource1_Selected” SelectParameters asp:Parameter
    Name=”PageNumber” Type=”Int32″ / asp:Parameter Name=”PageSize”
    Type=”Int32″ / /SelectParameters /asp:SqlDataSource /div /form /body
    /html 复制代码 代码如下: %@ Page
    Language=”C#” AutoEventWireup=”true” CodeFile=”GridViewPaging.aspx.cs”
    Inherits=”GridViewPaging” % !DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0
    Transitional//EN” “” html xmlns=”” head runat=”server” titlePaging/title
    /head body form runat=”server” div asp:LinkButton runat=”server”
    CommandName=”First” OnCommand=”lbtnPage_Command”|/asp:LinkButton
    asp:LinkButton runat=”server” CommandName=”Previous”
    OnCommand=”lbtnPage_Command”/asp:LinkButton asp:Label runat=”server” /
    asp:LinkButton runat=”server” CommandName=”Next”
    OnCommand=”lbtnPage_Command”/asp:LinkButton asp:LinkButton
    runat=”server” CommandName=”Last”
    OnCommand=”lbtnPage_Command”|/asp:LinkButton 转到第asp:DropDownList
    ID=”dropPage” runat=”server” AutoPostBack=”True”
    OnSelectedIndexChanged=”dropPage_SelectedIndexChanged”/asp:DropDownList页
    asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”
    DataKeyNames=”ProductID” DataSourceID=”SqlDataSource1″ Columns
    asp:BoundField DataField=”ProductID” HeaderText=”ProductID”
    InsertVisible=”False” ReadOnly=”True” / asp:BoundField
    DataField=”ProductName” HeaderText=”ProductName” / asp:BoundField
    DataField=”SupplierID” HeaderText=”SupplierID” / asp:BoundField
    DataField=”CategoryID” HeaderText=”CategoryID” / asp:BoundField
    DataField=”QuantityPerUnit” HeaderText=”QuantityPerUnit” /
    asp:BoundField DataField=”UnitPrice” HeaderText=”UnitPrice” /
    asp:BoundField DataField=”UnitsInStock” HeaderText=”UnitsInStock” /
    asp:BoundField DataField=”UnitsOnOrder” HeaderText=”UnitsOnOrder” /
    asp:BoundField DataField=”ReorderLevel” HeaderText=”ReorderLevel” /
    asp:CheckBoxField DataField=”Discontinued” HeaderText=”Discontinued” /
    /Columns /asp:GridView asp:SqlDataSource ID=”SqlDataSource1″
    runat=”server” ConnectionString=”Data Source=.\sqlexpress;Initial
    Catalog=Northwind;Integrated Security=True”
    ProviderName=”System.Data.SqlClient” SelectCommand=”GetProductsByPage”
    SelectCommandType=”StoredProcedure”
    OnSelecting=”SqlDataSource1_Selecting”
    OnSelected=”SqlDataSource1_Selected” SelectParameters asp:Parameter
    Name=”PageNumber” Type=”Int32″ / asp:Parameter Name=”PageSize”
    Type=”Int32″ / /SelectParameters /asp:SqlDataSource /div /form /body
    /html 复制代码 代码如下: using System;
    using System.Data; using System.Configuration; using System.Collections;
    using System.Web; using System.Web.Security; using System.Web.UI; using
    System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls; using System.Data.SqlClient; public
    partial class GridViewPaging : System.Web.UI.Page {
    //每页显示的最多记录的条数 private int pageSize = 10; //当前页号 private
    int currentPageNumber; //显示数据的总条数 private static int rowCount;
    //总页数 private static int pageCount; protected void Page_Load(object
    sender, EventArgs e) { if (!IsPostBack) { SqlConnection cn = new
    SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“NorthwindConnectionString”].ConnectionString);
    SqlCommand cmd = new SqlCommand(“GetProductsCount”, cn); cmd.CommandType
    = CommandType.StoredProcedure; cn.Open(); rowCount =
    (int)cmd.ExecuteScalar(); cn.Close(); pageCount = (rowCount – 1) /
    pageSize + 1; currentPageNumber = 1; ViewState[“currentPageNumber”] =
    currentPageNumber; lbtnPrevious.Enabled = false; lbtnFirst.Enabled =
    false; for (int i = 1; i = pageCount; i++) { dropPage.Items.Add(new
    ListItem(i.ToString(), i.ToString())); } dropPage.SelectedValue =
    dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); } } protected
    void SqlDataSource1_Selecting(object sender,
    SqlDataSourceSelectingEventArgs e) {
    SqlDataSource1.SelectParameters[“PageNumber”].DefaultValue =
    currentPageNumber.ToString();
    SqlDataSource1.SelectParameters[“PageSize”].DefaultValue =
    pageSize.ToString(); } protected void SqlDataSource1_Selected(object
    sender, SqlDataSourceStatusEventArgs e) { lblMessage.Text = “共找到” +
    rowCount + “条记录, 当前第” + currentPageNumber + “/” + pageCount +
    “页”; } protected void lbtnPage_Command(object sender, CommandEventArgs
    e) { switch (e.CommandName) { case “First”: currentPageNumber = 1;
    break; case “Previous”: currentPageNumber =
    (int)ViewState[“currentPageNumber”] – 1 1 ?
    (int)ViewState[“currentPageNumber”] – 1 : 1; break; case “Next”:
    currentPageNumber = (int)ViewState[“currentPageNumber”] + 1 pageCount
    ? (int)ViewState[“currentPageNumber”] + 1 : pageCount; break; case
    “Last”: currentPageNumber = pageCount; break; } dropPage.SelectedValue =
    dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
    ViewState[“currentPageNumber”] = currentPageNumber;
    SetButton(currentPageNumber);
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); } private void
    SetButton(int currentPageNumber) { lbtnFirst.Enabled = currentPageNumber
    != 1; lbtnPrevious.Enabled = currentPageNumber != 1; lbtnNext.Enabled =
    currentPageNumber != pageCount; lbtnLast.Enabled = currentPageNumber !=
    pageCount; } protected void dropPage_SelectedIndexChanged(object
    sender, EventArgs e) { currentPageNumber =
    int.Parse(dropPage.SelectedValue); ViewState[“currentPageNumber”] =
    currentPageNumber; SetButton(currentPageNumber);
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); } } [/code]
    using System; using System.Data; using System.Configuration; using
    System.Collections; using System.Web; using System.Web.Security; using
    System.Web.UI; using System.Web.UI.WebControls; using
    System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls;
    using System.Data.SqlClient; public partial class GridViewPaging :
    System.Web.UI.Page { //每页显示的最多记录的条数 private int pageSize =
    10; //当前页号 private int currentPageNumber; //显示数据的总条数 private
    static int rowCount; //总页数 private static int pageCount; protected
    void Page_Load(object sender, EventArgs e) { if (!IsPostBack) {
    SqlConnection cn = new
    SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“NorthwindConnectionString”].ConnectionString);
    SqlCommand cmd = new SqlCommand(“GetProductsCount”, cn); cmd.CommandType
    = CommandType.StoredProcedure; cn.Open(); rowCount =
    (int)cmd.ExecuteScalar(); cn.Close(); pageCount = (rowCount – 1) /
    pageSize + 1; currentPageNumber = 1; ViewState[“currentPageNumber”] =
    currentPageNumber; lbtnPrevious.Enabled = false; lbtnFirst.Enabled =
    false; for (int i = 1; i = pageCount; i++) { dropPage.Items.Add(new
    ListItem(i.ToString(), i.ToString())); } dropPage.SelectedValue =
    dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); } } protected
    void SqlDataSource1_Selecting(object sender,
    SqlDataSourceSelectingEventArgs e) {
    SqlDataSource1.SelectParameters[“PageNumber”].DefaultValue =
    currentPageNumber.ToString();
    SqlDataSource1.SelectParameters[“PageSize”].DefaultValue =
    pageSize.ToString(); } protected void SqlDataSource1_Selected(object
    sender, SqlDataSourceStatusEventArgs e) { lblMessage.Text = “共找到” +
    rowCount + “条记录, 当前第” + currentPageNumber + “/” + pageCount +
    “页”; } protected void lbtnPage_Command(object sender, CommandEventArgs
    e) { switch (e.CommandName) { case “First”: currentPageNumber = 1;
    break; case “Previous”: currentPageNumber =
    (int)ViewState[“currentPageNumber”] – 1 1 ?
    (int)ViewState[“currentPageNumber”] – 1 : 1; break; case “Next”:
    currentPageNumber = (int)ViewState[“currentPageNumber”] + 1 pageCount
    ? (int)ViewState[“currentPageNumber”] + 1 : pageCount; break; case
    “Last”: currentPageNumber = pageCount; break; } dropPage.SelectedValue =
    dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
    ViewState[“currentPageNumber”] = currentPageNumber;
    SetButton(currentPageNumber);
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); } private void
    SetButton(int currentPageNumber) { lbtnFirst.Enabled = currentPageNumber
    != 1; lbtnPrevious.Enabled = currentPageNumber != 1; lbtnNext.Enabled =
    currentPageNumber != pageCount; lbtnLast.Enabled = currentPageNumber !=
    pageCount; } protected void dropPage_SelectedIndexChanged(object
    sender, EventArgs e) { currentPageNumber =
    int.Parse(dropPage.SelectedValue); ViewState[“currentPageNumber”] =
    currentPageNumber; SetButton(currentPageNumber);
    SqlDataSource1.Select(DataSourceSelectArguments.Empty); } } [/code]
    4.分页效果图:

DataKey
类用于表示数据绑定控件中某个记录的主键。记录的主键可以由数据源中的一个或多个字段组成。尽管
DataKey 类不是集合,但它可以存储多个键字段值。当调用 DataKey
类的某个构造函数

时,将填充键字段值。可以通过以下方法从 DataKey 对象中检索键字段值:

使用 DataKey.Item(Int32) 属性检索 DataKey 对象中特定索引位置的键字段值。

使用 DataKey.Item(String) 属性检索特定字段的键字段值。

使用 Value 属性检索 DataKey 对象中索引 0
位置的键字段值。当主键只包含一个字段时,此属性常用作检索记录键值的快捷方式。

使用 Values 属性创建可用于循环访问键字段值的 IOrderedDictionary 对象。

通常,当设置了数据绑定控件的 DataKeyNames 属性时,控件自动生成 DataKey
对象。DataKey 对象包含 DataKeyNames 属性中指定的一个或多个键字段的值。
一次显示一个记录的数据绑定控件(如 DetailsView 或 FormView)通常在它的
DataKey 属性中存储所显示的当前记录的 DataKey 对象。
一次显示多个记录的数据绑定控件(如 GridView) 通常在DataKeys
属性中存储DataKeyArray集合,该集合存储它的每个记录的 DataKey 对象。

正常情况下,DataKeys 属性用于检索 GridView 控件中特定数据行的 DataKey
对象。但是,如果您只需要检索当前选中行的 DataKey 对象,则可以简单地使用
SelectedDataKey 属性作为一种快捷方式。 这和从 DataKeys
集合检索位于指定索引(由 SelectedIndex 属性指定)位置的 DataKey
对象一样。还可以使用 SelectedValue 属性直接检索当前选中行的数据键值。

下面的代码示例演示如何使用 SelectedDataKey 属性来确定 GridView
控件中选中行的数据键值。

 

图片 1

图片 2代码

<%@ Page language=”C#” %>

<script runat=”server”>
  void CustomersGridView_SelectedIndexChanged(Object sender, EventArgs e)  
  {        
    // Display the primary key value of the selected row.
    Message.Text = “The primary key value of the selected row is ” +
      CustomersGridView.SelectedDataKey.Value.ToString() + “.”;    
  }
</script>

<html>
  <body>
    <form runat=”server”>        
      <h3>GridView SelectedDataKey Example</h3>            
      <asp:label id=”Message”
        forecolor=”Red”
        runat=”server”/>                
      <br/><br/>
      <asp:gridview id=”CustomersGridView” 
        datasourceid=”CustomersSource” 
        allowpaging=”true”
        autogeneratecolumns=”true”
        autogenerateselectbutton=”true”    
        datakeynames=”CustomerID”
        onselectedindexchanged=”CustomersGridView_SelectedIndexChanged”   
        runat=”server”>                
        <selectedrowstyle backcolor=”LightBlue”
          forecolor=”DarkBlue”/>               
      </asp:gridview>
            
      <!– This example uses Microsoft SQL Server and connects  –>
      <!– to the Northwind sample database. Use an ASP.NET     –>
      <!– expression to retrieve the connection string value   –>
      <!– from the Web.config file.                            –>
      <asp:sqldatasource id=”CustomersSource”
        selectcommand=”Select [CustomerID], [CompanyName], [Address], [City], [PostalCode], [Country] From [Customers]”
        connectionstring=”<%$ ConnectionStrings:NorthWindConnectionString%>” 
        runat=”server”/>            
    </form></body></html>

图片 3