应用场景:
使用 sql的 Case When Then 批量更新某张表,底层数据库用到了Dapper
代码示例:
public int UpdateClientReceivedResult(Dictionary<string, string> dict) { var dynamicSqlParam = new DynamicParameters(); var sbSet = new StringBuilder(256); sbSet.Append(" (CASE SmsFlowID "); int idx = 0; foreach (var kv in dict) { sbSet.Append(string.Format(" WHEN @SmsFlowID{0} THEN @ClientReceivedResult{0} ", idx)); dynamicSqlParam.Add(string.Format("SmsFlowID{0}", idx), kv.Key, null, null, null); dynamicSqlParam.Add(string.Format("ClientReceivedResult{0}", idx), kv.Value, null, null, null); idx += 1; } sbSet.Append(" ELSE ClientReceivedResult END) "); var sbWhere = new StringBuilder(256); sbWhere.Append(" AND SmsFlowID in @ids "); dynamicSqlParam.Add("ids", dict.Keys.ToArray(), null, null, null); string sql = string.Format("UPDATE [SmsSendResult] SET [ClientReceivedResult]={0} WHERE 1=1 {1}", sbSet, sbWhere); int affectNum = -1; using (var conn = new SqlConnection(SmsDBConnString)) { affectNum = conn.Execute(sql, dynamicSqlParam); } return affectNum; }
最终生成的sql:
UPDATE [SmsSendResult] SET [ClientReceivedResult]= (CASE SmsFlowID WHEN @SmsFlowID0 THEN @ClientReceivedResult0 WHEN @SmsFlowID1 THEN @ClientReceivedResult1 WHEN @SmsFlowID2 THEN @ClientReceivedResult2 WHEN @SmsFlowID3 THEN @ClientReceivedResult3 WHEN @SmsFlowID4 THEN @ClientReceivedResult4 WHEN @SmsFlowID5 THEN @ClientReceivedResult5 WHEN @SmsFlowID6 THEN @ClientReceivedResult6 WHEN @SmsFlowID7 THEN @ClientReceivedResult7 ELSE ClientReceivedResult END) WHERE 1=1 AND SmsFlowID in @SmsFlowID
代码说明:
这个方法的作用是,根据传进来的Dict<SmsFlowID,ClientReceivedResult>批量更新SmsSendResult对应的值,为了限定更新的条数,需要用到where in操作,in后面的参数名必须要注意一下,尽量不和别的参数名字有重复,我之前sbWhere是这么写的:
var sbWhere = new StringBuilder(256); sbWhere.Append(" AND SmsFlowID in @SmsFlowID "); dynamicSqlParam.Add("SmsFlowID", dict.Keys.ToArray(), null, null, null);
结果运行报异常:说什么已存在相同参数名SmsFlowID1。。。找了半天,原来是in的参数名字的问题!Dapper对于 where col in @arg 会将动态参数的数组类型的参数自动转化为 where col in (@arg1,@arg2....),这样就重名了,所以找了半天原因,原来是这么个回事,修改后程序运行正常