2024-07-25

根据驱动 获取数据类型 注意 Remark

select CONST_PRE_PRICE/(1+ifnull(ORDER_PRICE_RATE,0)) from pm_project_const_month

 /// <summary>
 /// 获取数据类型
 /// </summary>
 /// <param name="dbConnectionString"></param>
 /// <returns></returns>
 /// <exception cref="NotImplementedException"></exception>
 public static DatabaseType GetDatabaseType(this DbConnectionString dbConnectionString)
 {
       var connConfig = dbConnectionString;
   switch (connConfig.ProviderName)
   {
       case "System.Data.SqlClient":
           return DatabaseType.SqlServer;
       case "Oracle.ManagedDataAccess.Client":
           return DatabaseType.Oracle;
       case "MySql.Data.MySqlClient":
           return DatabaseType.MySql;
       case "System.Data.SQLite":
           return DatabaseType.SQLite;
       case "IBM.Data.Informix.11.1.0":
           return DatabaseType.Sinoregal;
       case "Npgsql":
           return DatabaseType.PostgreSQL;
       case "Microsoft.Data.Sqlite":
           return DatabaseType.SQLite;
       case "lcpi.data.oledb":
           return DatabaseType.OleDb;
       case "Kdbndp_V9":
           return DatabaseType.Kingbase;
       case "Net.IBM.Data.Db2":
           return DatabaseType.DB2;
       case "FirebirdSql.Data.FirebirdClient":
           return DatabaseType.Firebird;
       case "dmdbms.DmProvider":
           if (dbConnectionString.Remark?.Contains("Oracle") == true)
           {
               return DatabaseType.DamengOracle;
           }
           return DatabaseType.Dameng;
       case "OceanBase":
           if (dbConnectionString.Remark?.Contains("Oracle") == true)
           {
               return DatabaseType.OceanBaseOracle;
           }
           return DatabaseType.OceanBase;
       default:
           throw new NotImplementedException("未找到数据提供器，请检查" + connConfig.Name + "连接串的providerName属性。");
   }
 }

数据库mysql空关键字替换为其它数据库

namespace RG3.DO.Abstractions.Utilities
{
    /// <summary> 
    ///   数据库差异 工具类   20240725  cbg
    /// </summary>
    public partial class DbSqlUtil
    {
        #region  设置 SQL 参数替换  
        /// <summary>
        /// 执行mysql ifnull 替换为 实际应用的sql语句
        /// </summary>
        public static string SetNullSqlConvert(string sql, DatabaseType databaseType, bool isOracle = false)
        {
            StringBuilder sb = new StringBuilder(sql);
            switch (databaseType)
            {
                case DatabaseType.Dameng:
                    if (isOracle)
                    {
                        sb.Replace("IFNULL(", "NVL(");
                        sb.Replace("ifnull(", "NVL(");
                    }
                    break;
                case DatabaseType.SqlServer:
                    sb.Replace("IFNULL(", "ISNULL(");
                    sb.Replace("ifnull(", "ISNULL(");
                    break;
                case DatabaseType.Oracle:
                    sb.Replace("IFNULL(", "NVL(");
                    sb.Replace("ifnull(", "NVL(");
                    break;
                case DatabaseType.SQLite:
                    sb.Replace("IFNULL(", "COALESCE(");
                    sb.Replace("ifnull(", "COALESCE(");
                    break;
                case DatabaseType.Sinoregal:
                    //使用 IFNULL

                    break;
                case DatabaseType.Informix:
                    sb.Replace("IFNULL(", "COALESCE(");
                    sb.Replace("ifnull(", "COALESCE(");

                    break;
                case DatabaseType.DB2:
                    sb.Replace("IFNULL(", "COALESCE(");
                    sb.Replace("ifnull(", "COALESCE(");
                    break;
                case DatabaseType.PostgreSQL:
                    sb.Replace("IFNULL(", "COALESCE(");
                    sb.Replace("ifnull(", "COALESCE(");
                    break;
                case DatabaseType.Firebird:
                    sb.Replace("IFNULL(", "COALESCE(");
                    sb.Replace("ifnull(", "COALESCE(");
                    break;

                case DatabaseType.Kingbase:
                    //使用 IFNULL

                    break;

                case DatabaseType.OleDb:
                    //未知

                    break;

                case DatabaseType.TideDB:
                    //未知

                    break;

                case DatabaseType.OceanBase:
                    if (isOracle)
                    {
                        sb.Replace("IFNULL(", "NVL(");
                        sb.Replace("ifnull(", "NVL(");
                    }
                    break;
            }
            return sb.ToString();
        }
        #endregion
    }
}