关于UtilMethods.GetNativeSql的使用 返回

Falcon 发布于2025/1/17

悬赏：0 飞吻

收藏

热忱回答（2）

• fate sta VIP0

  2025/1/17

  这个自封装不太好写

  0 回复
• Falcon VIP0

  2025/1/17

  @fate sta：自己写了一个，但是感觉还是有很多不足，特别是GetParm方法需要对参数描述补充。

  希望官方或者有同样需要的人一起补充，完善代码。

  
  

      /// <summary>
      /// sql帮助方法
      /// </summary>
      public static class SqlHelper
      {
          /// <summary>
          /// 从UtilMethods.GetNativeSql(sql,parms)获取的字符串中获取sql和parms。这是UtilMethods.GetNativeSql(sql,parms)的逆方法
          /// </summary>
          /// <param name="str">UtilMethods.GetNativeSql(sql,parms)生成的字符串</param>
          /// <returns>包含sql和parms的元组</returns>
          public static (string sql, SugarParameter[] pars) GetSqlAndPars(string str) {
              /*
               * [Sql]:SELECT "OPTYPE","BUSINESSTYPE","MESSAGELOGID","ORGID","ORGNAME","REFERRALID","IDNO","REFERRALTYPENAME","REFERRALPOSNAME","REFERRALDEPTNAME","REFERRALPURPOSE","APPLYTIME","APPLYDOCTORID","APPLYDOCTORNAME","RECORDSTATUS","CREATETIME","ID" FROM "FDS_RMQ_REFERRAL"  WHERE (( "APPLYDOCTORID" = :applyDoctorId0 ) AND ( "CREATETIME" >(:MethodConst2+(1*:MethodConst3)) )) 
                  [Pars]:
                  [Name]::applyDoctorId0 [Value]:did [Type]:String   
                  [Name]::MethodConst2 [Value]:2025/1/17 10:45:33 [Type]:DateTime   
                  [Name]::MethodConst3 [Value]:-1 [Type]:Double   
               */
              var sql = "";
              var pars = new List<SugarParameter>();
              using var sr = new StringReader(str);
              string? line = sr.ReadLine();
              while(line != null) {
                  if(line.StartsWith("[Sql]:")) {
                      sql = GetSql(line);
                  }
                  if(line.StartsWith("[Name]:")) {
                      pars.Add(GetParm(line));
                  }
                  line = sr.ReadLine();
              }
              return (sql, pars.ToArray());
          }
          /// <summary>
          /// 解析sql语句
          /// </summary>
          /// <param name="str">sql参数行</param>
          /// <returns></returns>
          private static string GetSql(string str) => str.Substring(6);
          /// <summary>
          /// 解析一行参数
          /// </summary>
          /// <param name="str">参数行</param>
          /// <returns>执行参数</returns>
          private static SugarParameter GetParm(string str) {
              //[Name]::applyDoctorId0[Value]:did[Type]:String
              string? name = "", val = "";
              System.Data.DbType? type = null;
              foreach(var n in str.Split('[',StringSplitOptions.RemoveEmptyEntries)) {
                  if(n.StartsWith("Name]:")) {
                      name = n.Substring(6);
                  }
                  if(n.StartsWith("Value]:")) {
                      val = n.Substring(7);
                  }
                  if(n.StartsWith("Type]:")) {
                      type = Enum.Parse<System.Data.DbType>(n.Substring(6));
                  }
              }
              var sp = new SugarParameter(name,type == null ? val : GetParmValue(type.Value,val));
              if(type != null) {
                  sp.DbType = type.Value;
              }
              return sp;
          }
          private static object GetParmValue(System.Data.DbType type,string val) {
              var t = MapDbTypeToCSharpType(type);
              return val.ChangeType(t) ?? throw new NotSupportedException($"不支持的类型转换{t.FullName},值:{val}");
          }
          /// <summary>
          /// 将System.Data.DbType转换为c#对象类型
          /// </summary>
          /// <param name="dbType">数据库类型</param>
          /// <returns>c#对象类型</returns>
          /// <exception cref="NotSupportedException"></exception>
          private static Type MapDbTypeToCSharpType(System.Data.DbType dbType) {
              switch(dbType) {
                  case System.Data.DbType.AnsiString:
                  case System.Data.DbType.String:
                      return typeof(string);
                  case System.Data.DbType.Byte:
                      return typeof(byte);
                  case System.Data.DbType.Boolean:
                      return typeof(bool);
                  case System.Data.DbType.DateTime:
                      return typeof(DateTime);
                  case System.Data.DbType.Decimal:
                      return typeof(decimal);
                  case System.Data.DbType.Double:
                      return typeof(double);
                  case System.Data.DbType.Int16:
                      return typeof(short);
                  case System.Data.DbType.Int32:
                      return typeof(int);
                  case System.Data.DbType.Int64:
                      return typeof(long);
                  case System.Data.DbType.Guid:
                      return typeof(Guid);
                  default:
                      throw new NotSupportedException("未映射数据类型 " + dbType);
              }
          }
      }

  
  

  0 回复