Dapper Query 手札

the query sample with Dapper. 紀錄 Dapper Query 七種應用狀況。

紀錄 Dapper Query 七種應用狀況。

一、無參數 Dapper Query

using (SqlConnection conn = DBHelper.CONNSEC.Open())
{
    // 無參數 Dapper Query 
    var dataList = conn.Query<SecConnectionPool>("SELECT * FROM SecConnectionPool ");
}

二、固定參數 Dapper Query

using (SqlConnection conn = DBHelper.CONNSEC.Open())
{       
    string sql = "SELECT * FROM SecConnectionPool WHERE ConnectionID like @ConnectionID ";
    // Dapper Query 參數可以是 anonymous type
    var dataList = conn.Query<SecConnectionPool>(sql, new { ConnectionID = "CONN%" });
}

三、動態參數 Dapper Query

using (SqlConnection conn = DBHelper.CONNSEC.Open())
{
    DynamicParameters param = new DynamicParameters(); // Dapper 動態參數
    StringBuilder sql = new StringBuilder(); 
    sql.Append(@"SELECT * FROM SecConnectionPool WHERE 1=1 ");
    
    // 依條件動態加入查詢參數
    if (!String.IsNullOrWhiteSpace(myFiled))
    {
       sql.Append("AND MyFiled = @myFiled");
       param.Add("myFiled", myFiled);
    }
    
    // 依條件動態加入查詢參數 LIKE
    if (!String.IsNullOrWhiteSpace(connectionID ))
    {
       sql.Append("AND ConnectionID like @connectionID ");
       param.Add("connectionID ", $"%{connectionID }%");
    }
    
    // 依條件動態加入查詢參數 勾選與否 checked?:Y/N,
    if (args.isEnable == "Y")
      sql.Append("AND IsEnable = 'Y' ");
    else if(args.isEnable == "N")
      sql.Append("AND IsEnable != 'Y' ");
        
    // 依條件動態加入查詢參數 日期區間 bgn <= x < end
    if (args.dateBgn.HasValue)
    {
      // 若有起日 則 大於等於 起日
      sql.Append("AND UpdDtm >= @bgnDate ");
      param.Add("@bgnDate", args.dateBgn.Value);
    }
    if (args.dateEnd.HasValue) // 訖日
    {
      // 若有訖日 則 小於 訖日隔天
      sql.Append("AND UpdDtm < @endDate ");
      param.Add("@endDate", args.dateEnd.Value.AddDays(1));
    }
                
    // 依條件動態加入查詢參數 IN
    if (argList != null && argList.Count > 0)
    {
        // 動態生成查詢變數串列
        var nameList = Enumerable.Range(1, argList.Count).Select(i => $"@MyField{i}");
        // 組成 IN 查詢條件字句        
        sql.Append("AND MyField IN (" + String.Join(",", nameList) + ") ");           
        // 一一填入查詢變數串列
        Enumerable.Zip(nameList, argList).ToList().ForEach(c => {
            param.Add(c.First, c.Second);
        });
    }
       
    // Dapper Query
    var dataList = conn.Query<SecConnectionPool>(sql.ToString(), param)
                       .AsList();
}

四、從 SqlCommand 轉成 Dapper CommandDefinition 再執行

using (SqlConnection conn = DBHelper.CONNSEC.Open())
{
    // 模擬先拿到 SqlCommand 物件
    SqlCommand cmd = new SqlCommand();
    StringBuilder sql = new StringBuilder();
    sql.Append(@"SELECT * FROM SecConnectionPool WHERE 1=1 ");

    // 依條件動態加入查詢參數
    if (!String.IsNullOrWhiteSpace(connectionID ))
    {
        sql.Append("ConnectionID like @connectionID ");
        cmd.Parameters.AddWithValue("@connectionID ", $"%{connectionID }%");
    }

    cmd.CommandText = sql.ToString(); // 組織好的SQL

    //# 把 SqlCommand 轉成 Dapper CommandDefinition 再執行
    var dataList = conn.Query<SecConnectionPool>(cmd.AsDapperCommand());
}

public static class SqlCommandExtensions
{
    /// <summary>
    /// cast SqlCommand as Dapper CommandDefinition
    /// </summary>
    public static CommandDefinition AsDapperCommand(this SqlCommand sql)
    {
        if (sql.Parameters.Count > 0)
        {
            DynamicParameters args = new DynamicParameters();
            foreach (SqlParameter p in sql.Parameters) {
                args.Add(p.ParameterName, p.Value);
            }

            return new CommandDefinition(sql.CommandText, args);
        }
        else
        {
            return new CommandDefinition(sql.CommandText);
        }
    }
}

五、GetEx:取代 Dapper.Contrib 之 Get 指令無法多 P-Key 取值的狀況

強烈建議：未來全新的 Data Table 的P-Key只設定一個欄位。 若是主檔明細檔關聯，主檔P-Key一個欄位應該問題不大，明細檔的P-Key建議用一個純粹的序號(identity)來做，再加一個指向主檔的Foreign-Key。其中要注意的是明細檔的P-Key只是用來 access 不可做為其他 table 的Foreign-Key。要設定用另一組Unique-Key比如：(main_no + item_no) 組合做為其他 table 的Foreign-Key。此明細檔的P-Key與商業邏輯必需完全無關只是一個用來存取的序號。

public static class MyDapperExtensions
{
  /// <summary>
  /// 取代 Dapper.Contrib 之 Get 指令無法多 P-Key 取值的狀況
  /// </summary>
  public static TTable GetEx<TTable>(this SqlConnection conn, object args, SqlTransaction txn = null)
  {
    // 依 Property 動態加入 P-Key 查詢條件
    List<String> conds = new List<string>();
    foreach (PropertyInfo pi in args.GetType().GetProperties())
    {
    	conds.Add($"{pi.Name} = @{pi.Name} ");
    }
    
    String tableName = typeof(TTable).Name;
    StringBuilder sql = new StringBuilder($@"SELECT TOP 1 * FROM {tableName} WHERE {String.Join("AND ", conds)}; ");
    var info = conn.Query<TTable>(sql.ToString(), args, txn).First();
    return info;
  }
}

GetEx 範例

/// ## GetEx 範例
//# 開始DB交易
using (var conn = DBHelper.CONNSEC.Open())
using (var txn = conn.BeginTransaction())
{
  // 取得現在的值
  var info = conn.GetEx<SRSM001>(new
  {
    idno = formData.idno,          // p-key 1
    acct_yymm = formData.acct_yymm // p-key 2
  }, txn);
  
  // 填入更新的值
  info.name1 = formData.name1;
  ...
  
  conn.Update<SRSM001>(info, txn);
  txn.Commit();
}

六、LoadEx:載入多筆資料。與GetEx相比可以取回多筆資料。

/// <summary>
/// 載入多筆資料。與GetEx相比可以取回多筆資料。
/// </summary>
public static IEnumerable<TTable> LoadEx<TTable>(this SqlConnection conn, object args, SqlTransaction txn = null)
{
  // 依 Property 動態加入 P-Key 查詢條件
  List<String> conds = new List<string>();
  foreach (PropertyInfo pi in args.GetType().GetProperties())
  {
  	conds.Add($"{pi.Name} = @{pi.Name} ");
  }
  
  String tableName = typeof(TTable).Name;
  StringBuilder sql = new StringBuilder($@"SELECT * FROM {tableName} WHERE {String.Join("AND ", conds)}; ");
  var dataList = conn.Query<TTable>(sql.ToString(), args, txn).AsList();
  return dataList;
}

LoadEx 範例：取得主檔下的多筆明細

/// ## LoadEx 範例：取得主檔下的多筆明細
using (var conn = DBHelper.CONNDB.Open())
{
  // ......
  // 取得次要資訊：附件清單
  var attachList = conn.LoadEx<M_TCSP_COURSE_ATTACH>(new { CourseID = model.CourseID });
  // ......
}

七、QueryMultiple:多項目查詢，如：主檔明細檔一回合就查詢出來。

參考：Dapper - Result Multi-Result

/// <summary>
/// 取出表單(有主檔、明細檔)
/// </summary>
public static TraineeAppForm Get(int AppSN)
{
  StringBuilder sql = new StringBuilder();
  // 主檔 & 代碼名稱對照檔
  sql.Append(@"SELECT M.*, C.CourseName
    FROM M_TCSP_TA_TRAINEE M (NOLOCK) 
    LEFT JOIN M_TCSP_COURSE C (NOLOCK) ON M.CourseID = C.CourseID 
    WHERE M.AppSN = @AppSN; ");
  // 明細檔
  sql.Append("SELECT itemSn = SN, * FROM M_TCSP_TA_TRAINEE_ATTACH N (NOLOCK) WHERE AppSN = @AppSN; ");
  
  using (var conn = DBHelper.CONNDB.Open())
  using (var multi = conn.QueryMultiple(sql.ToString(), new { AppSN }))
  {
    dynamic m = multi.ReadFirstOrDefault(); //<--- 依次取出第一份查詢結果。
    TraineeAppForm info = null;
    if (m != null)
    {
      // 主檔 & transform
      info = new TraineeAppForm
      {
        AppSN = m.AppSN,
        CompanyNo = m.CompanyNo,
        TraineeName = m.TraineeName,
        TraineePhone = m.TraineePhone,
        TraineeEmail = m.TraineeEmail,
        ApplyCourse = new CodeNameModel {
          code = m.CourseID,  // 課程代碼
          name = m.CourseName // 課程名稱
        },
        Remark = m.Remark,
        Status = m.Status,
        ApvClass = m.ApvClass
      };
      
      // detail & 主接mapping
      info.attachList = multi.Read<TraineeAppFormAttach>().AsList(); //<--- 依次取出第二份查詢結果。
    }
    
    return info;
  }
}