[ASP.net WebForm] 顯示GridView撈出的筆數 (效能+短碼小技巧)

[ASP.net WebForm] 顯示GridView撈出的筆數 (效能+短碼小技巧)

 

過去我的做法可能在按鈕的Click事件裡這麼寫:



  if (dv!=null)
  {
    int number = dv.Table.Rows.Count;//抓出DataView的總筆數   
    Label1.Text = "件数: " + number;
  }
  else
  {
    Label1.Text = "件数: 0";
  }


這邊紀錄一下直接利用SQL Server的@@ROWCOUNT寫法,不用再讓SqlDataSource去Select (這樣會讓SqlDataSource再送出一次查詢)



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Label ID="Label1" runat="server" /><!--件数の表示-->
    <asp:TextBox ID="TextBox1" runat="server" />
    <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
    <asp:DropDownList ID="DropDownList1" runat="server">
        <asp:ListItem Value="-1">all</asp:ListItem>
        <asp:ListItem Value="2000">H12</asp:ListItem>
        <asp:ListItem Value="2005">H17</asp:ListItem>
    </asp:DropDownList>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="工事台帳ID" HeaderText="工事台帳ID" SortExpression="工事台帳ID" />
            <asp:BoundField DataField="費目" HeaderText="費目" SortExpression="費目" />
            <asp:BoundField DataField="工事名称" HeaderText="工事名称" SortExpression="工事名称" />
            <asp:BoundField DataField="施行年度ID" HeaderText="施行年度ID" SortExpression="施行年度ID" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommand="SELECT DISTINCT [工事台帳ID], [費目], [工事名称], [施行年度ID] FROM [工事台帳] 
                       WHERE (@施行年度ID = '-1' or [施行年度ID] = @施行年度ID) AND (@工事名称 = '' or [工事名称] LIKE '%' + @工事名称 + '%');
                       SELECT @NumberOfRecords = @@ROWCOUNT"
        OnSelected="SqlDataSource1_Selected">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="施行年度ID" PropertyName="SelectedValue"
                Type="String" />
            <asp:ControlParameter ControlID="TextBox1" Name="工事名称" PropertyName="Text" ConvertEmptyStringToNull="false"
                Type="String" />
            <asp:Parameter Name="NumberOfRecords" Type="Int32" Direction="Output" />
        </SelectParameters>
    </asp:SqlDataSource>
    </form>
</body>
</html>


using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class Default5 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {

    }



    //同じSelectCommandのクエリはSelectedイベントに入らない.
    protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
    {
        int number = (int)e.Command.Parameters["@NumberOfRecords"].Value;
        Label1.Text = "件数: " + number.ToString();

    }
}

直接在送出第一次查詢時,SQL Server就做掉,真是上了一課

原文出處:http://social.msdn.microsoft.com/Forums/ja-JP/vwdexpressja/thread/9c20fac4-2de4-403f-8046-df6ca720071f