您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> 關於.NET

當 ADO.NET 遇上 dynamic：動態封裝讓資料存取更優雅

編輯：關於.NET

前言：兩種風格的碰撞

ADO.NET：規矩、嚴謹、專注資料存取，猶如《美麗心靈》裡的納什教授。
dynamic：不按牌理出牌、無所不能、與 Lambda 是冤家，宛如《未來戰士》T-1000 液態金屬。
當兩者相遇，ADO.NET 丟掉 DataSet 包袱，無需實體類即可跨層傳遞查詢結果，甚至像呼叫普通方法一樣呼叫預存程序——這就是本文要展示的「動態 Command」魔法。

傳統 ADO.NET 範例回顧

1. 執行 SQL 查詢

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "select Top 10 * from Orders";
    cmd.CommandType = CommandType.Text;
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
            Console.WriteLine("OrderID: {0}, OrderDate: {1}",
                reader.GetInt32(reader.GetOrdinal("OrderID")),
                reader.GetDateTime(reader.GetOrdinal("OrderDate")));
    }
}

2. 呼叫預存程序

cmd.CommandText = "CustOrdersOrders";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("CustomerID", "ALFKI"));

dynamic 重構後的極致簡潔

using (dynamic cmd = conn.CreateDynamicCommand())
{
    // 1. 直接查詢
    foreach (dynamic order in cmd("select Top 10 * from Orders"))
        Console.WriteLine("OrderID: {0}, OrderDate: {1}", order.OrderID, order.OrderDate);

    // 2. 參數化查詢 (Named Arguments)
    foreach (dynamic order in cmd("select * from Orders where CustomerID = @CustomerID", CustomerID: "ALFKI"))
        Console.WriteLine("OrderID: {0}, OrderDate: {1}", order.OrderID, order.OrderDate);

    // 3. 呼叫預存程序 (Method Syntax)
    foreach (dynamic order in cmd.CustOrdersOrders(CustomerID: "ALFKI"))
        Console.WriteLine("OrderID: {0}, OrderDate: {1}", order.OrderID, order.OrderDate);
}

一個 dynamic cmd 同時支援「委托呼叫語法」與「成員呼叫語法」，這背後靠的是 DynamicObject 重寫 TryInvoke 與 TryInvokeMember。

核心實現：DynamicCommand + DynamicEntity

1. 擴充方法入口

public static class Extensions
{
    public static DynamicCommand CreateDynamicCommand(this DbConnection conn)
        => new DynamicCommand(conn);
}

2. DynamicCommand : DynamicObject, IDisposable


public class DynamicCommand : DynamicObject, IDisposable
{
    public DbConnection Connection { get; set; }

    public DynamicCommand(DbConnection conn) => Connection = conn;

    // 處理 cmd("sql", param1: val1, ...)
    public override bool TryInvoke(InvokeBinder binder, object[] args, out object result)
    {
        if (args.Length == 0) throw new ArgumentException("args must has value");
        result = Execute(args[0].ToString(), CommandType.Text,
                         binder.CallInfo.ArgumentNames, args.Skip(1).ToArray());
        return true;
    }

    // 處理 cmd.StoredProcName(param1: val1, ...)
    public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
    {
        if (binder.CallInfo.ArgumentNames.Count != binder.CallInfo.ArgumentCount)
            throw new ArgumentException("All parameters must be named");
        result = Execute(binder.Name, CommandType.StoredProcedure,
                         binder.CallInfo.ArgumentNames, args);
        return true;
    }

    private object Execute(string cmdText, CommandType type, IEnumerable names, object[] args)
    {
        bool manageConn = (Connection.State == ConnectionState.Closed);
        if (manageConn) Connection.Open();
        try
        {
            using (var cmd = Connection.CreateCommand())
            {
                cmd.CommandType = type;
                cmd.CommandText = cmdText;
                for (int i = 0; i < args.Length; i++)
                {
                    var p = cmd.CreateParameter();
                    p.ParameterName = "@" + names.ElementAt(i);
                    p.Value = args[i] ?? DBNull.Value;
                    cmd.Parameters.Add(p);
                }
                return ExecuteList(cmd);
            }
        }
        finally { if (manageConn) Connection.Close(); }
    }

    private static IEnumerable ExecuteList(DbCommand cmd)
    {
        var list = new List();
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var entity = new DynamicEntity();
                for (int i = 0; i < reader.FieldCount; i++)
                    entity.SetMember(reader.GetName(i), reader.GetValue(i));
                list.Add(entity);
            }
        }
        return list;
    }

    public void Dispose() { /* 若自建連接則關閉 */ }
}

3. DynamicEntity : DynamicObject —— 動態屬性包裝


internal class DynamicEntity : DynamicObject
{
    private readonly Dictionary _values
        = new Dictionary(StringComparer.OrdinalIgnoreCase);

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        if (_values.TryGetValue(binder.Name, out result)) return true;
        throw new MissingMemberException($"Property '{binder.Name}' does not exist");
    }

    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        SetMember(binder.Name, value);
        return true;
    }

    public override IEnumerable GetDynamicMemberNames() => _values.Keys;

    internal void SetMember(string name, object value)
        => _values[name] = (value is DBNull) ? null : value;
}

原理剖析：dynamic 的本質

用 Reflector 反編譯即可發現：dynamic 在 IL 層面不存在，它是編譯器將 dynamic 轉為 object 並插入 CallSite 快取的語法糖。正如魯迅所云：「世界上本沒有 dynamic，只是微軟對委托封裝得太牛了，也便有了 dynamic。」

適用場景與注意事項

優勢	風險/限制
無需定義實體類，快速原型開發	編譯期無型別檢查，拼錯屬性名只能運行時發現
預存程序呼叫如同本地方法	不支援 Lambda 運算式 (無法建構 Expression Tree)
跨層傳遞匿名結果集	大量反射呼叫有效能開銷，高頻熱路徑慎用