偷看 Dapper 產生的 SQL 指令與參數型別

2024-01-16 08:53 PM

這是調查 Dapper 參數茶包的副產品，檢查底層對映參數型別的工具函式。

遇到一個 Dapper 問題，懷疑與匿名參數物件屬性對映的 OracleParameter 型別有關。而 Oracle 不像 MSSQL 有 Profiler 可以偷看實體指令，我便想從程式端突破，設法在執行時取得參數資訊。原本我寄望於現成程式庫，但找到最接近的解決方案是 MiniProfiler.Integrations ，預設只支援 SQL Server / MySQL，如果要用在 Oracle，得寫幾行程式為 OracleConnection 實作 IDbConnectionFactory：

public class OracleDbConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;

    public OracleDbConnectionFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public DbConnection CreateConnection()
    {
        return new OracleConnection(_connectionString);
    }
}

是有成功印出 SQL 指令跟參數，它的參數資訊為 Dictionary<string, object> 型別，只看得到參數名稱跟參數值，無法得知 OracleParameter.OracleDbType：

懶得再繼續試其他程式庫，不如動手自己寫吧。我追進 Dapper 原始碼，發現 IDbCommand 的參數會透過 CacheInfo.ParamReader(IDbCommand, object?) 產生，只是 CacheInfo 的相關型別、方法、建構式都屬內部 API，不開放外部呼叫。所幸，有 Reflection 在手，即使標成 internal、private 也攔不住我們，層層解開，終於一窺底層樣貌：

SimulateDapperDbParameters() 會模擬 Dapper 執行 IDbCommand 的過程，將參數一一對映成 OracleParameter 或 SqlParameter (若需支援其他資料庫可自行增加)，實測 Oracle 及 SQL 可抓到實際的參數物件型別，還順便驗證 FixOdpNetDbTypeStringMapping 確實可修正將 string 對映到 NVarChar2，證明有抓取到正確結果。

範例程式碼如下，有需要的同學請自取修改利用：(注意：GetConstructors() 取建構式部分我偷懶寫死 [1] 取陣列第二個 Overloading，而 Dapper 新舊版的參數數量及型別可能不同，若不相容請自行調整)

using Dapper;
using Oracle.ManagedDataAccess.Client;
using StackExchange.Profiling.Internal;
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Transactions;

namespace NetFxConsole
{
    internal class Program
    {
        // TODO: encrypt the connection string and store it in the app.config
        private const string csOracle =
            "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SqlDB)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)));User Id=LAB;Password=****;";
        private const string csMsSql = "Data Source=SqlDB;Initial Catalog=LAB;User Id=LAB;Password=****;";

        static object GetSqlMapperParamReader(CommandDefinition cmd, IDbConnection cnn, object paramArg)
        {
            var sqlMapperType = typeof(SqlMapper);
            var sqlMapperIdentityType = sqlMapperType.Assembly.GetType("Dapper.SqlMapper+Identity");
            // TODO: hard-coded here, array index and parameters may differ in different versions of Dapper
            var identity = sqlMapperIdentityType.GetConstructors(
                BindingFlags.Instance | BindingFlags.NonPublic)[1].Invoke(new object[]
                {
                    cmd.CommandText, cmd.CommandType, string.Empty, typeof(string), paramArg.GetType(), null, 0
                });
            dynamic cacheInfo = sqlMapperType.GetMethod("GetCacheInfo", BindingFlags.Static | BindingFlags.NonPublic)
                .Invoke(null, new object[] { identity, paramArg, false });
            return cacheInfo.GetType().GetProperty("ParamReader").GetValue(cacheInfo, null);
        }

        public static void SimulateDapperDbParameters(string commandText, IDbConnection cnn, object paramArg)
        {
            var command = new CommandDefinition(commandText, paramArg);
            var paramReader = GetSqlMapperParamReader(command, cnn, paramArg);
            var mi = command.GetType().GetMethod("SetupCommand", BindingFlags.Instance | BindingFlags.NonPublic);
            var cmd = mi.Invoke(command, new object[] { cnn, paramReader });
            switch (cmd)
            {
                case OracleCommand oc:
                    Console.WriteLine($"CommandText: \x1b[33m{oc.CommandText}\x1b[0m");
                    foreach (OracleParameter p in oc.Parameters)
                    {
                        Console.WriteLine($" * \u001b[36m{p.ParameterName,-8}\u001b[0m / {p.DbType,-8} / \u001b[35m{$"{p.OracleDbType}({p.Size})",-16} \u001b[0m = {p.Value}");
                    }
                    break;
                case SqlCommand sc:
                    Console.WriteLine($"CommandText: \x1b[33m{sc.CommandText}\x1b[0m");
                    foreach (SqlParameter p in sc.Parameters)
                    {
                        Console.WriteLine($" * \u001b[36m{p.ParameterName,-8}\u001b[0m / {p.DbType,-8} / \u001b[35m{$"{p.SqlDbType}({p.Size})",-16}\u001b[0m = {p.Value}");
                    }
                    break;
                default:
                    throw new NotSupportedException();
            }
        }
        static void Main(string[] args)
        {
            var sql = "INSERT INTO SAMPLE VALUES (:name, :regDate, :score)";
            var paramObject = new
            {
                name = "Jeffrey",
                regDate = new DateTime(2012, 12, 21),
                score = 32767
            };
            using (var cn = new OracleConnection(csOracle))
            {
                SimulateDapperDbParameters(sql, cn, paramObject);
                FixOdpNetDbTypeStringMapping();
                SimulateDapperDbParameters(sql, cn, paramObject);
            }
            using (var cn = new SqlConnection(csMsSql))
            {
                sql = sql.Replace(":", "@");
                SimulateDapperDbParameters(sql, cn, paramObject);
            }
            Console.ReadLine();
        }
        // https://blog.darkthread.net/blog/dapper-odpnet-unicode-issue/
        static void FixOdpNetDbTypeStringMapping()
        {
            Assembly asm = typeof(OracleConnection).Assembly;
            Type tOraDb_DbTypeTable = asm.GetType("Oracle.ManagedDataAccess.Client.OraDb_DbTypeTable");
            var fldDbTypeMapping = tOraDb_DbTypeTable.GetField("dbTypeToOracleDbTypeMapping",
                BindingFlags.Static | BindingFlags.NonPublic);
            int[] mappings = (int[])fldDbTypeMapping.GetValue(null);
            mappings[(int)System.Data.DbType.String] = (int)OracleDbType.NVarchar2;
            fldDbTypeMapping.SetValue(null, mappings);
        }
    }
}

補充顯示資料時用到兩則小技巧：

偷看 Dapper 產生的 SQL 指令與參數型別

Comments

Post a comment