C# DataSet 進階技巧：SelectDistinct 實現欄位去重與資料篩選

需求場景

從 DataTable 擷取某欄位的不重複值，建立新 DataTable 供下拉選單、報表分組、主從關聯使用。ADO.NET 無內建 SELECT DISTINCT，需自行實作。

DataSetHelper 類別設計

using System;
using System.Data;

public class DataSetHelper
{
    public DataSet DataSet { get; private set; }
    
    public DataSetHelper(DataSet ds = null)
    {
        DataSet = ds;
    }
    
    /// 

    /// 比較兩值是否相等，正確處理 DBNull.Value
    /// 
    private bool ColumnEqual(object a, object b)
    {
        if (a == DBNull.Value && b == DBNull.Value) return true;
        if (a == DBNull.Value || b == DBNull.Value) return false;
        return a.Equals(b);
    }
    
    /// 

    /// 從來源 DataTable 指定欄位建立去重 DataTable
    /// 
    /// 新資料表名稱
    /// 來源資料表
    /// 去重欄位名稱
    /// 去重後的 DataTable
    public DataTable SelectDistinct(string tableName, DataTable sourceTable, string fieldName)
    {
        if (sourceTable == null) throw new ArgumentNullException(nameof(sourceTable));
        if (!sourceTable.Columns.Contains(fieldName))
            throw new ArgumentException($"欄位 '{fieldName}' 不存在");
        
        DataTable distinctTable = new DataTable(tableName);
        distinctTable.Columns.Add(fieldName, sourceTable.Columns[fieldName].DataType);
        
        // 使用 Select 排序後遍歷，相鄰比較去重
        object lastValue = null;
        foreach (DataRow row in sourceTable.Select("", fieldName))
        {
            object currentValue = row[fieldName];
            if (lastValue == null || !ColumnEqual(lastValue, currentValue))
            {
                lastValue = currentValue;
                distinctTable.Rows.Add(new object[] { currentValue });
            }
        }
        
        // 選擇性加入 DataSet
        if (DataSet != null)
            DataSet.Tables.Add(distinctTable);
        
        return distinctTable;
    }
}

完整測試程式

using System;
using System.Data;
using System.Windows.Forms;

public class DistinctDemoForm : Form
{
    private DataGridView grid;
    private Button btnDistinct;
    private DataSet ds;
    private DataSetHelper helper;
    
    public DistinctDemoForm()
    {
        grid = new DataGridView { Dock = DockStyle.Fill, ReadOnly = true };
        btnDistinct = new Button { Text = "取得不重複員工", Dock = DockStyle.Bottom, Height = 40 };
        btnDistinct.Click += BtnDistinct_Click;
        
        Controls.Add(grid);
        Controls.Add(btnDistinct);
        Text = "DataTable Distinct Demo";
        ClientSize = new Size(600, 400);
        
        InitializeData();
    }
    
    private void InitializeData()
    {
        ds = new DataSet();
        helper = new DataSetHelper(ds);
        
        // 建立訂單資料表
        DataTable orders = new DataTable("Orders");
        orders.Columns.Add("EmployeeID", typeof(string));
        orders.Columns.Add("OrderID", typeof(int));
        orders.Columns.Add("Amount", typeof(decimal));
        
        // 測試資料：多筆重複員工
        orders.Rows.Add("Sam", 5, 25.00m);
        orders.Rows.Add("Tom", 7, 50.00m);
        orders.Rows.Add("Sue", 9, 11.00m);
        orders.Rows.Add("Tom", 12, 7.00m);
        orders.Rows.Add("Sam", 14, 512.00m);
        orders.Rows.Add("Sue", 15, 17.00m);
        orders.Rows.Add("Sue", 22, 2.50m);
        orders.Rows.Add("Tom", 24, 3.00m);
        orders.Rows.Add("Tom", 33, 78.75m);
        
        ds.Tables.Add(orders);
        grid.DataSource = orders;
    }
    
    private void BtnDistinct_Click(object sender, EventArgs e)
    {
        // 只能點一次，避免重複加入同名表
        btnDistinct.Enabled = false;
        
        DataTable distinctEmployees = helper.SelectDistinct(
            "DistinctEmployees", ds.Tables["Orders"], "EmployeeID");
        
        grid.DataSource = distinctEmployees;
        Text = $"不重複員工數：{distinctEmployees.Rows.Count}";
    }
    
    [STAThread]
    static void Main() => Application.Run(new DistinctDemoForm());
}

進階擴充：多欄位去重

public DataTable SelectDistinct(string tableName, DataTable sourceTable, params string[] fieldNames)
{
    DataTable distinctTable = new DataTable(tableName);
    foreach (string fn in fieldNames)
        distinctTable.Columns.Add(fn, sourceTable.Columns[fn].DataType);
    
    // 建立複合鍵比較
    var seenKeys = new HashSet();
    
    foreach (DataRow row in sourceTable.Select("", string.Join(",", fieldNames)))
    {
        object[] keyValues = new object[fieldNames.Length];
        for (int i = 0; i < fieldNames.Length; i++)
            keyValues[i] = row[fieldNames[i]];
        
        string compositeKey = string.Join("|", keyValues.Select(v => 
            v == DBNull.Value ? "NULL" : v.ToString()));
        
        if (seenKeys.Add(compositeKey))  // Add 回傳 true 表示新鍵
            distinctTable.Rows.Add(keyValues);
    }
    
    if (DataSet != null) DataSet.Tables.Add(distinctTable);
    return distinctTable;
}

替代方案比較

DataView 內建做法 (.NET 2.0+)

// 最簡單的一行去重
DataTable distinct = sourceTable.DefaultView.ToTable(true, "EmployeeID");
// true = distinct, 後接欄位名稱陣列
DataTable distinctMulti = sourceTable.DefaultView.ToTable(true, "EmployeeID", "Department");

本章小結

• SelectDistinct 核心：排序 + 相鄰比較 + DBNull.Value 正確處理
• DataSetHelper 封裝邏輯，可選擇性加入 DataSet 管理
• 現代專案優先使用 DataView.ToTable(true, ...) 或 LINQ
• 多欄位去重需建立複合鍵，HashSet 效能優於雙重迴圈
• 大量資料去重建議在資料庫層面 (SELECT DISTINCT) 完成