0

ADO.NET裡的Select()提供部分類似SQL的查詢功能，例如: 簡化版的LIKE(%只能放前後，不能擺中間)，但有些基本的SQL語法還是做不到的，例如: DISTINCT。

微軟曾針對ADO.NET 1.1提出過解決方案KB，但所謂的DataSetHelper，複雜度看來跟自己DIY沒差多少，只差在是MS提出的官方範例。所幸，在ADO.NET 2.0裡，DataView.ToTable()終於將DISTINCT的功能歸為內建，我們可以直接匯出一個只包含指定欄位的DataTable，而且可以決定是否DISTINCT。

廢話不多說，請看示範:

static void testDistinct()
{
    DataTable t = new DataTable();
    t.Columns.Add("Category", typeof(string));
    t.Columns.Add("Product", typeof(string));
    t.Columns.Add("Version", typeof(string));
    t.Rows.Add("OS", "Windows", "2003");
    t.Rows.Add("OS", "Windows", "Vista");
    t.Rows.Add("Application", "Office", "XP");
    t.Rows.Add("Application", "Office", "2003");
    t.Rows.Add("Application", "Project", "2003");
    t.Rows.Add("Database", "SQL", "2000");
    t.Rows.Add("Database", "SQL", "2005");
    t.Rows.Add("Database", "SQL", "2000"); //Duplicated
    Console.WriteLine("Original Rows Count = "
        + t.Rows.Count.ToString());
    Console.WriteLine("DISTINCT Category Rows Count = " + t.DefaultView
        .ToTable(true, "Category").Rows.Count.ToString());
    Console.WriteLine("DISTINCT Category, Product Rows Count = " + t.DefaultView
        .ToTable(true, "Category", "Product").Rows.Count.ToString());
    Console.WriteLine("DISTINCT Category, Product, Version Rows Count = " + t.DefaultView
        .ToTable(true, "Category", "Product", "Version").Rows.Count.ToString());
}

執行結果如下:

Original Rows Count = 8
DISTINCT Category Rows Count = 3
DISTINCT Category, Product Rows Count = 4
DISTINCT Category, Product, Version Rows Count = 7

那LINQ呢?

LINQ有個Distionct()函數可以套用，但比起DataView.ToTable()沒省什麼功夫。當然，這種小把戲並非LINQ精華所在，日後再找實例介紹LINQ的"特異功能"。

//...省略...
    t.Rows.Add("Database", "SQL", "2005");
    t.Rows.Add("Database", "SQL", "2000"); //Duplicated
 
    var rows = from row in t.AsEnumerable()
               select new
               {
                   Category = row.Field<string>("Category"),
                   Product = row.Field<string>("Product")
               };
    Console.WriteLine("Orig RowCount=" + (rows).Count());
    Console.WriteLine("Distinct RowCount=" + (rows).Distinct().Count());

Comments

Be the first to post a comment

Post a comment