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>
/// 取各种数据库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");
}
}
/// <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>
/// 获取数据库参数的前缀
/// </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("@");
}
}
/// <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>
/// 返回时间加减的表达式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);
}