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

  • 相关阅读:
    【转】【SEE】基于SSE指令集的程序设计简介
    【转】【Asp.Net】asp.net服务器控件创建
    ControlTemplate in WPF ——ScrollBar
    ControlTemplate in WPF —— Menu
    ControlTemplate in WPF —— Expander
    ControlTemplate in WPF —— TreeView
    ControlTemplate in WPF —— ListBox
    ControlTemplate in WPF —— ComboBox
    ControlTemplate in WPF —— TextBox
    ControlTemplate in WPF —— RadioButton
  • 原文地址:https://www.cnblogs.com/guyun/p/6142088.html
Copyright © 2020-2023  润新知