Dapper sql in

最新推荐文章于 2024-09-26 09:45:50 发布

【Help】

最新推荐文章于 2024-09-26 09:45:50 发布

阅读量941

点赞数

分类专栏： Dapper

Dapper 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

应用场景：

使用 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....)，这样就重名了，所以找了半天原因，原来是这么个回事，修改后程序运行正常