• Dapper sql in


    应用场景:

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

  • 相关阅读:
    新手入门贴之基于 python 语言的接口自动化 demo 小实战
    记新人从 excel 文件中读取字典数据踩的一个坑
    接口自动化 之 unittest+ddt+openpyxl 综合
    接口自动化之unittest+ddt
    接口自动化之unittest初探
    python之类与对象(5)
    python之类与对象(4)
    python之类与对象(3)
    python之类与对象(2)
    传智播客JavaWeb day01 快捷键、XML
  • 原文地址:https://www.cnblogs.com/guyun/p/6142088.html
Copyright © 2020-2023  润新知