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;
}
}Last updated