[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就做掉,真是上了一課