C# Linq語法(二)

續上篇 ---> 上篇參考

上一篇利用Linq撈出XML語法後
接著是更進一步的應用 join  GroupJoin及綜合用法
.

圖1

圖2

圖3

程式碼：

圖1

private void button4_Click(object sender, EventArgs e)
{
    XDocument doc1 = XDocument.Load(@"C:\資料庫檔\中文北風XML\產品.xml");
    XDocument doc2 = XDocument.Load(@"C:\資料庫檔\中文北風XML\產品類別.xml");
    /*
        * 說明：在產品.xml中有產品類別這個欄位，但是這個欄位是用產品類別.xml的代碼表示
        *       要使這2個表關連後顯示「產品類別」中的代碼類別為何。
        *       
        * 實作如下：
    var result = from xx in doc1.Descendants("產品")
                    join yy in doc2.Descendants("產品類別") 
                    on xx.Element("類別編號").Value equals yy.Element("類別編號").Value
                    select new
                    {
                        Id = xx.Element("產品編號").Value,
                        Name = xx.Element("產品名稱").Value,
                        TypeName = yy.Element("類別名稱").Value,
                        Price = xx.Element("單價").Value,
                        Unit = xx.Element("單位數量").Value,
                        Stock = xx.Element("庫存量").Value
                    };
            
    // join on 「產品」中類別編號的值 equal 「產品類別」中類別編號的值
    */

    //上列注解可用下方的語法，結果相同，但更為簡潔
    var result = (from xx in doc1.Descendants("產品")
                    select xx).Join(
                    doc2.Descendants("產品類別"),
                    y => y.Element("類別編號").Value,
                    x => x.Element("類別編號").Value,
                    (x, y) => new
                    {
                        Id = x.Element("產品編號").Value,
                        Name = x.Element("產品名稱").Value,
                        TypeName = y.Element("類別名稱").Value,
                        Price = x.Element("單價").Value,
                        Unit = x.Element("單位數量").Value,
                        Stock = x.Element("庫存量").Value
                    }
                    );
            
            
    StringBuilder sb = new StringBuilder();
    foreach (var a in result)
        sb.Append(a.Id).Append("\t")
            .Append(a.Name).Append("\t")
            .Append(a.TypeName).Append("\t")
            .Append(a.Price).Append("\t")
            .Append(a.Unit).Append("\t")
            .Append(a.Stock).Append("\t")
            .Append("\r\n");

    textBox1.Text = sb.ToString();

    dataGridView1.DataSource = result.ToList();
}

圖2

private void button5_Click(object sender, EventArgs e)
{
    XDocument doc1 = XDocument.Load(@"C:\資料庫檔\中文北風XML\訂單主檔.xml");
    XDocument doc2 = XDocument.Load(@"C:\資料庫檔\中文北風XML\員工.xml");
    XDocument doc3 = XDocument.Load(@"C:\資料庫檔\中文北風XML\客戶.xml");
    XDocument doc4 = XDocument.Load(@"C:\資料庫檔\中文北風XML\送貨方式.xml");

    //訂單主檔.xml中有許多欄與其他表關連，可以利用Linq語法join出的結果，再繼續join

    var result = from xx in doc1.Descendants("訂單主檔")
                    join yy in doc4.Descendants("送貨方式")
                    on xx.Element("送貨方式").Value equals yy.Element("送貨編號").Value
                    select new
                    {
                        Id = xx.Element("訂單號碼").Value,
                        Cid = xx.Element("客戶編號").Value,
                        Eid = xx.Element("員工編號").Value,
                        Date = xx.Element("訂單日期").Value,
                        ShipType = yy.Element("方式").Value

                    };
    var result2 = (from zz in result.ToList()
                    join aa in doc2.Descendants("員工")
                    on zz.Eid equals aa.Element("員工編號").Value
                    select new
                    {
                        Id = zz.Id,
                        Cid = zz.Cid,
                        EmployeeName = aa.Element("姓名").Value,
                        Date = zz.Date,
                        ShipType = zz.ShipType
                    }).Join(
                    //產生出的結果，再join 客戶.xml
                        doc3.Descendants("客戶"),
                        x => x.Cid,
                        y => y.Element("客戶編號").Value,
                        (x, y) => new
                        {
                            OrderId = x.Id,
                            CompanyName = y.Element("公司名稱").Value,
                            Customer = y.Element("連絡人").Value,
                            EmployeeName = x.EmployeeName,
                            Date = DateTime.Parse(x.Date),
                            ShipType = x.ShipType
                        }
                    );
                        

    StringBuilder sb = new StringBuilder();
    foreach (var a in result2)
        sb.Append(a.OrderId).Append("\t")
            .Append(a.CompanyName).Append("\t")
            .Append(a.Customer).Append("\t")
            .Append(a.EmployeeName).Append("\t")
            .Append(a.Date).Append("\t")
            .Append(a.ShipType).Append("\t")
            .Append("\r\n");

    textBox1.Text = sb.ToString();

    dataGridView1.DataSource = result2.ToList();
}

圖3

private void button6_Click(object sender, EventArgs e)
{
    SqlConnection cnn1 = new SqlConnection("server=localhost;database=中文北風;UID=SQLUser;PWD=1234");
    SqlCommand cmd1 = new SqlCommand("SELECT 產品編號,產品,類別編號,單價 FROM 產品資料", cnn1);

    OleDbConnection cnn2 = new OleDbConnection();
    cnn2.ConnectionString = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\資料庫檔\北風A.mdb";
    OleDbCommand cmd2 = new OleDbCommand("SELECT 類別編號,類別名稱 FROM 產品類別", cnn2);           

    DataTable t1 = new DataTable();
    DataTable t2 = new DataTable();

    cnn1.Open();
    SqlDataReader mydr1 = cmd1.ExecuteReader();
    t1.Load(mydr1);
    mydr1.Close();
    cmd1.Dispose();
    cnn1.Close();

    cnn2.Open();
    OleDbDataReader mydr2 = cmd2.ExecuteReader();
    t2.Load(mydr2);
    mydr2.Close();
    cmd2.Dispose();
    cnn2.Close();
    /*
        * 說明：由SQLServer中抓出「產品資料」，再由Access中抓出「產品類別」
        *       將2張表join後group by 產品類別，算出各 產品類別 的平均為何
        * 
    var tempResult = from xx in t1.AsEnumerable()
                    join yy in t2.AsEnumerable()
                    on xx.Field("類別編號") equals yy.Field("類別編號")
                    select new
                    {
                        類別名稱 = yy.Field("類別名稱"),
                        價錢=xx.Field("單價")
                    };
    var result = from zz in tempResult.ToList()
                    group zz by zz.類別名稱 into gg
                    select new
                    {
                        類別名 = gg.Key,
                        平均售價 = gg.Average(a => a.價錢),
                        數量 = gg.Count()
                    };
    */

    var result = (from xx in t2.AsEnumerable()
                    select new
                    {
                        類別編號 = xx.Field("類別編號"),
                        類別名 = xx.Field("類別名稱")

                    }).GroupJoin(
                    t1.AsEnumerable(),
                    x => x.類別編號,
                    y => y.Field("類別編號"),
                    (a, b) => new
                    {
                        類別名 = a.類別名,
                        平均售價 = b.Average(c=>c.Field("單價")),
                        數量 = b.Count()
                    }
                    );
            

    dataGridView1.DataSource = result.ToList();
}