[ASP.NET]利用DataTable.Compute來使用SQL的aggregate functions

2008-11-25

25763
0
ASP.NET

摘要:[ASP.NET]利用DataTable.Compute來使用SQL的aggregate functions

最近同事問到DataTable可不可以使用max的方法來計算最大值...

去找了一下資料,果真有方法可以做到,就是利用DataTable的Compute方法

asp.net(c#)

DatatableCompute.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DatatableCompute.aspx.cs" Inherits="DatatableCompute" %> <!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>DatatableCompute</title> </head> <body> <form id="form1" runat="server"> <div> Max sales(Los Angeles)：<asp:Label ID="lblMax" runat="server" Text="lblMax"></asp:Label> <br /> Sum sales(All)：<asp:Label ID="lblSum" runat="server" Text="lblSum"></asp:Label><br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" /> <asp:BoundField DataField="sales" HeaderText="sales" SortExpression="sales" /> <asp:BoundField DataField="date" HeaderText="date" SortExpression="date" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [name], [sales], [date] FROM [Table5]"></asp:SqlDataSource> </div> </form> </body> </html>

DatatableCompute.aspx.cs

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; public partial class DatatableCompute : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataTable dt = new DataTable(); dt = ((DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable(); this.lblSum.Text = dt.Compute("sum(sales)", "").ToString();//加總Table裡所有sales的值 this.lblMax.Text = dt.Compute("max(sales)", "name='Los Angeles'").ToString();//取出name為'Los Angeles'的最大sales值 } }

執行結果：

參考網址：

http://programming.top54u.com/post/ASP-Net-DataTable-Compute-Column-Sum-using-C-sharp.aspx
http://msdn.microsoft.com/zh-tw/library/system.data.datatable.compute.aspx

回首頁