[ADO.NET] 彙總函數練習

練習 ADO.NET 彙總函數的使用 ~~

namespace AggDeno
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        DataTable dtAgg = new DataTable();
        DataTable dtDataTable = new DataTable();
        private void Form1_Load(object sender, EventArgs e)
        {
            dtFill();
        }

        private void dtFill()
        {
            dtAgg.Columns.Add("Alias", typeof(string));
            dtAgg.Columns.Add("Data",typeof(decimal));
            dtAgg.Rows.Add("EG-2A",369);
            dtAgg.Rows.Add("EG-2A",344);
            dtAgg.Rows.Add("IA1-1X", 322.88);
            dtAgg.Rows.Add("IA1-1X", 0.335);
            dtAgg.Rows.Add("IQ1-4X", 0.225);
            dtAgg.Rows.Add("IQ1-4X", 3.6);
            dtAgg.Rows.Add("IQ1-4X", 222.5);
            dgvData.DataSource = dtAgg;

            // Clone() 複製原 DataTable Schema
            // Copy()  複製原 DataTable Schema 和 Data
            dtDataTable = dtAgg.Clone();
            dgvDataTable.DataSource = dtDataTable;
        }

        private void btnADO_Click(object sender, EventArgs e)
        {
            dtDataTable.Rows.Clear();

            // 利用 ToTable() 找出 Alias 唯一值
            DataTable dtLoop = dtAgg.DefaultView.ToTable(true, "Alias");

            string filter = string.Empty;
            string Alias = string.Empty;
            foreach (DataRow dr in dtLoop.Rows)
            {
                Alias = dr["Alias"].ToString();

                DataRow drNew = dtDataTable.NewRow();
                drNew["Alias"] = Alias;
                filter = string.Format("Alias = '{0}'", Alias);
                drNew["Data"] = dtAgg.Compute("AVG(Data)", filter);
                dtDataTable.Rows.Add(drNew);
            }
        }

        private void btnDataTableLINQ_Click(object sender, EventArgs e)
        {
            dtDataTable.Rows.Clear();

            IEnumerable<string> loop = dtAgg.AsEnumerable().Select(Row => Row.Field<string>("Alias")).Distinct();

            foreach (string Alias in loop)
            {
                decimal avg = dtAgg.AsEnumerable().Where(dr => dr.Field<string>("Alias") == Alias).Average(dr => dr.Field<decimal>("Data"));

                DataRow drNew = dtDataTable.NewRow();
                drNew["Alias"] = Alias;
                drNew["Data"] = avg;
                dtDataTable.Rows.Add(drNew);
            }
        }

        private void btnLINQ_Click(object sender, EventArgs e)
        {

            var result = dtAgg.AsEnumerable().GroupBy(datarow => datarow.Field<string>("Alias")).Select(g => new { Alias = g.Key , AVG = g.Average(a => a.Field<decimal>("Data"))}).ToList();

            dgvLINQ.DataSource = result;
        }
    }
}

延伸閱讀
• [LINQ] SUM
• [LINQ] GroupBy

參考資料
• 查詢運算式語法範例：彙總運算子