public static string DbDelimiter(this DbConnType dbConnType, string TableOrFieldName, bool checkSymExists = false)
{
if (dbConnType == DbConnType.MSSQL)
{
if (checkSymExists)
{
return(TableOrFieldName.StartsWith("[") ? TableOrFieldName : $"[{TableOrFieldName}]");
}
else
{
return($"[{TableOrFieldName}]");
}
}
else if (dbConnType.IsMySQL())
{
if (checkSymExists)
{
return(TableOrFieldName.StartsWith("`") ? TableOrFieldName : $"`{TableOrFieldName}`");
}
else
{
return($"`{TableOrFieldName}`");
}
}
return(TableOrFieldName);
}
/// <summary>
/// 取各种数据库获取当前时间的函数
/// </summary>
/// <param name="connType"></param>
/// <returns></returns>
public static string GetCurrentTimeFuncName(this DbConnType connType)
{
if (connType.IsOracle())
{
return("current_date");
}
else if (connType == DbConnType.MSSQL)
{
return("getdate()");
}
else if (connType.IsMySQL())
{
return("now()");
}
else if (connType == DbConnType.POSTGRESQL)
{
return("localtimestamp");
}
else if (connType == DbConnType.SQLITE)
{
return("datetime('now')");
}
else
{
return("date()");
}
}
/// <summary>
/// 取各种数据库ISNULL的函数
/// </summary>
/// <param name="connType"></param>
/// <returns></returns>
public static string GetIsNullFuncName(this DbConnType connType)
{
if (connType.IsOracle())
{
return("NVL");
}
else if (connType == DbConnType.MSSQL)
{
return("ISNULL");
}
else if (connType.IsMySQL())
{
return("IFNULL");
}
else if (connType == DbConnType.POSTGRESQL)
{
return("NULLIF");
}
else if (connType == DbConnType.SQLITE)
{
return("IFNULL");
}
else
{
return("ISNULL");
}
}
public DateTime GetTime(DbConnType connType, string ConnectionString)
{
using (DbContext conn = new DbContext(ConnectionString, connType))
{
return(conn.Now());
}
}
/// <summary>
/// 获取更新的语句
/// </summary>
/// <param name="tableName"></param>
/// <param name="Columns"></param>
/// <param name="Keys"></param>
/// <param name="connType"></param>
/// <returns></returns>
public static string GetUpdateSqlStr(this DbConnType connType, string tableName, string[] Columns, string[] Keys)
{
StringBuilder strbld = new StringBuilder(512);
strbld.AppendFormat("Update {0} set ", connType.DbDelimiter(tableName));
for (int i = 0, count = Columns.Length; i < count; i++)
{
string fieldName = Columns[i];
if (Keys.Contains(fieldName))
{
continue;
}
string parName = connType.TreatParaName(fieldName);
strbld.AppendFormat("{0}={1},", connType.DbDelimiter(fieldName), parName);
}
strbld.Remove(strbld.Length - 1, 1);
strbld.Append(" where ");
for (int i = 0, count = Keys.Length; i < count; i++)
{
if (0 != i)
{
strbld.Append(" and ");
}
string fieldName = Keys[i];
string parName = connType.TreatParaName(fieldName);
strbld.AppendFormat("{0}={1}", connType.DbDelimiter(fieldName), parName);
}
return(strbld.ToString());
}
private static DbProviderFactory GetFactoryDirect(DbConnType dbConnType)
{
if (dbConnType == DbConnType.MSSQL) //ok
{
return(System.Data.SqlClient.SqlClientFactory.Instance); //ok
}
else if (dbConnType == DbConnType.MYSQL) //ok
{
return(GetFactory("MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data")); //ok
}
//return GetFactory("MySql.Data.MySqlClient.MySqlClientFactory,MySqlConnector");
else if (dbConnType == DbConnType.ODPNET) //ok
{
return(GetFactory("Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess"));
}
else if (dbConnType == DbConnType.ORACLE) //ok
{
return(GetFactory("System.Data.OracleClient.OracleClientFactory,System.Data.OracleClient"));
}
else if (dbConnType == DbConnType.SQLITE) // ok
{
return(GetFactory("System.Data.SQLite.SQLiteFactory, System.Data.SQLite"));
}
else if (dbConnType == DbConnType.POSTGRESQL) //ok
{
return(GetFactory("Npgsql.NpgsqlFactory,Npgsql"));
}
else
{
throw new NotSupportedException(dbConnType.ToString());
}
}
/// <summary>
/// parName ====> @parName
/// </summary>
/// <param name="dbConnType"></param>
/// <param name="paraNames"></param>
/// <returns></returns>
public static IEnumerable <string> TreatParaNames(this DbConnType dbConnType, params string[] paraNames)
{
foreach (string par in paraNames)
{
yield return(TreatParaName(dbConnType, par));
}
}
private static object GetDataFieldValue(object obj, DbConnType connType)
{
if (SmartCrudHelper.IsNullOrDBNull(obj))
{
return(null); // DBNull.Value; //dapper下不可使用 DBNull.Value
}
else
{
if (connType == DbConnType.ODPNET)
{
Type t = obj.GetType();
if (t == GuidType)
{
return(obj?.ToString().ToUpper());
}
else if (t == Bool)
{
return((byte)(((bool)obj) ? 1 : 0));
}
else if (t == SmartCrudHelper.SByte)
{
return(Convert.ToByte(obj));
}
}
return(obj);
}
}
/// <summary>
/// QPVideoAnchorDB数据库操作
/// </summary>
/// <returns></returns>
public static SqlSugarClient GetSqlSugarDB(DbConnType type)
{
var strConnectionString = "";
switch (type)
{
case DbConnType.QPAgentAnchorDB:
strConnectionString = ConfigurationManager.AppSettings["QPAgentAnchorDB"];
break;
case DbConnType.QPVideoAnchorDB:
strConnectionString = ConfigurationManager.AppSettings["QPVideoAnchorDB"];
break;
case DbConnType.QPAnchorRecordDB:
strConnectionString = ConfigurationManager.AppSettings["QPAnchorRecordDB"];
break;
case DbConnType.MovieDB:
strConnectionString = ConfigurationManager.AppSettings["MovieRecordDb"];
break;
case DbConnType.AnchorPersonaliseDb:
strConnectionString = ConfigurationManager.AppSettings["AnchorPersonaliseDb"];
break;
default:
strConnectionString = ConfigurationManager.AppSettings["QPVideoAnchorDB"];
break;
}
ICacheService cacheService = null;
if (_RedisCache)
{
cacheService = new RedisCache(_RedisHost, _RedisPort);
}
else
{
cacheService = new HttpRuntimeCache();
}
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = strConnectionString,
DbType = DbType.SqlServer,
InitKeyType = InitKeyType.Attribute,//InitKeyType 是读取主键和自增列信息的方式
IsAutoCloseConnection = true,
ConfigureExternalServices = new ConfigureExternalServices()
{
SqlFuncServices = ExtMethods.GetExpMethods,
DataInfoCacheService = cacheService
}
});
return(db);
}
/// <summary>
/// 把字段串转换成各数据库连接类型对应的形式,such as @Name,@Id,@Code
/// </summary>
/// <param name="Columns"></param>
/// <param name="connType"></param>
/// <returns></returns>
public static string GetParaStringViaColumns(this DbConnType connType, string[] Columns)
{
StringBuilder strbld = new StringBuilder(512);
for (int i = 0, count = Columns.Length; i < count; i++)
{
strbld.AppendFormat("{0},", connType.TreatParaName(Columns[i]));
}
return(strbld.ToString().TrimEnd(','));
}
/// <summary>
/// parName ====> @parName
/// </summary>
/// <param name="dbConnType"></param>
/// <param name="paraName"></param>
/// <returns></returns>
public static string TreatParaName(this DbConnType dbConnType, string paraName)
{
if (dbParPrefix.Contains(paraName[0])) //存在前缀
{
return($"{dbConnType.GetConnTypePrefixChar()}{paraName.TrimParaName()}");
}
else
{
return($"{dbConnType.GetConnTypePrefixChar()}{paraName}");
}
}
public static DbProviderFactory GetFactory(DbConnType dbConnType)
{
DbProviderFactory result = null;
string str = dbConnType.ToString();
if (dic.TryGetValue(str, out result))
{
return(result);
}
result = GetFactoryDirect(dbConnType);
dic.TryAdd(str, result);
return(result);
}
/// <summary>
/// 处理连接串,参数支持Diction<string,object>,DbParameter[] , new{},useBrackets=true则是使用这样的参数 #{Para1},否则就是使用 #Para1#
/// </summary>
/// <param name="dbConnType"></param>
/// <param name="oriSql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static string TransSQL(this DbConnType dbConnType, string oriSql, object param, bool useBrackets = false)
{
string sql = oriSql;
int countofChar = oriSql.Count(c => c == '#');
if (2 <= countofChar)
{
sql = sql.Replace(CURRENT_TIME, GetCurrentTimeFuncName(dbConnType)).Replace(IS_NULL, GetIsNullFuncName(dbConnType));
if (null != param)
{
IEnumerable <DbParameter> dbpars = param as IEnumerable <DbParameter>;
if (null != dbpars)
{
foreach (var par in dbpars)
{
string naturePara = par.ParameterName.TrimParaName();
string parNameInstead = "";
if (!useBrackets)
{
parNameInstead = string.Format("#{0}#", naturePara);
}
else
{
parNameInstead = "#{" + naturePara + "}";
}
sql = sql.Replace(parNameInstead, dbConnType.TreatParaName(naturePara));
}
}
else
{
IDictionary <string, object> dic = SmartCrudHelper.AsDictionary(param);
foreach (var par in dic)
{
string parNameInstead = "";
if (!useBrackets)
{
parNameInstead = string.Format("#{0}#", par.Key);
}
else
{
parNameInstead = "#{" + par.Key + "}";
}
sql = sql.Replace(parNameInstead, dbConnType.TreatParaName(par.Key));
}
}
}
}
return(sql);
}
/// <summary>
/// 获取连字符号
/// </summary>
/// <param name="connType"></param>
/// <returns></returns>
public static string GetStringConcat(this DbConnType connType, string fieldOrVar1, string fieldOrVar2)
{
if (connType.IsOracle() || connType == DbConnType.POSTGRESQL || connType == DbConnType.SQLITE)
{
return(string.Format("{0} || {1}", fieldOrVar1, fieldOrVar2));
}
else if (connType.IsMySQL())
{
return(string.Format("CONCAT({0},{1})", fieldOrVar1, fieldOrVar2));
}
else
{
return(string.Format("{0} + {1}", fieldOrVar1, fieldOrVar2));
}
}
/// <summary>
/// 获取数据库参数的前缀
/// </summary>
/// <param name="connType"></param>
/// <returns></returns>
private static string GetConnTypePrefixChar(this DbConnType connType)
{
if (connType.IsOracle() || connType == DbConnType.POSTGRESQL)
{
return(":");
}
else if (connType.IsMySQL())
{
return("@");
}
else
{
return("@");
}
}
public DbContext(string connectString, DbConnType dbConnType)
{
SqlBuilder.LoadTypeHandler();
this.DbType = dbConnType;
if (this.DbType == DbConnType.MSSQL)
{
if (connectString.IndexOf("MultipleActiveResultSets", StringComparison.OrdinalIgnoreCase) < 0)
{
connectString += ";MultipleActiveResultSets=true;";
}
}
dbProvider = DatabaseProvider.GetFactory(dbConnType);
this.Db = dbProvider.CreateConnection();
this.Db.ConnectionString = connectString;
this.Db.Open();
}
public static string DbDelimiter(this string TableOrFieldName, DbConnType dbConnType, bool checkSymExists = false) => dbConnType.DbDelimiter(TableOrFieldName, checkSymExists);
public static bool IsMySQL(this DbConnType dbConnType) => dbConnType == DbConnType.MYSQL;
/// <summary>
/// 返回时间加减的表达式1:DAY 2:HOUR 3:MINUTE 4:SECOND
/// </summary>
/// <param name="connType"></param>
/// <param name="dateType"></param>
/// <param name="Value"></param>
/// <returns></returns>
public static string GetDateAddString(this DbConnType connType, DATEPART dateType, int Value)
{
if (dateType == DATEPART.MS)
{
throw new NotSupportedException("DATEPART.MS");
}
string sym = "+";
if (0 > Value)//减
{
Value *= -1;
sym = "-";
}
string result = "";
switch (dateType)
{
case DATEPART.DAY: //天数
if (connType.IsMySQL())
{
result = $"{sym} INTERVAL {Value} DAY";
}
else if (connType == DbConnType.POSTGRESQL)
{
result = $"{sym} interval '{Value} days'";
}
else
{
result = $"{sym}{Value}";
}
break;
case DATEPART.HOUR: //小时
if (connType.IsMySQL())
{
result = $"{sym} INTERVAL {Value} HOUR";
}
else if (connType == DbConnType.POSTGRESQL)
{
result = $"{sym} interval '{Value} hours'";
}
else
{
result = $"{sym}{Value}/24.0";
}
break;
case DATEPART.MINUTE: //分时
if (connType.IsMySQL())
{
result = $"{sym} INTERVAL {Value} MINUTE";
}
else if (connType == DbConnType.POSTGRESQL)
{
result = $"{sym} interval '{Value} minutes'";
}
else
{
result = $"{sym}{Value}/1440.0";
}
break;
case DATEPART.SECOND:
if (connType.IsMySQL())
{
result = $"{sym} INTERVAL {Value} seconds";
}
else if (connType == DbConnType.POSTGRESQL)
{
result = $"{sym} interval '{Value} seconds'";
}
else
{
result = $"{sym}{Value}/86400.0";
}
break;
}
return(result);
}
public static bool IsOracle(this DbConnType dbConnType) => dbConnType == DbConnType.ORACLE || dbConnType == DbConnType.ODPNET;
