// 获取 String 类型的 SQL 片断
protected override string GetSqlValueByString(object value, object dbType, int?size = null)
{
bool unicode = DbTypeUtils.IsUnicode(dbType);
string result = this.EscapeQuote(value.ToString(), unicode, true);
return(result);
}
// 获取 DateTimeOffset 类型的 SQL 片断
protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?precision)
{
// 默认精度6
string format = "yyyy-MM-dd HH:mm:ss.ffffff";
if (DbTypeUtils.IsDateTimeOffset(dbType))
{
string pad = string.Empty;
if (precision != null && precision.Value > 0)
{
pad = "f".PadLeft(precision.Value > 7 ? 7 : precision.Value, 'f');
}
if (!string.IsNullOrEmpty(pad))
{
format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", pad);
}
}
string date = ((DateTimeOffset)value).DateTime.ToString(format);
string span = ((DateTimeOffset)value).Offset.ToString(@"hh");
span = string.Format("{0}{1}", ((DateTimeOffset)value).Offset.Hours >= 0 ? '+' : '-', span);
string result = string.Format("(TIMESTAMPTZ '{0}{1}')", date, span);
return(result);
// Npgsql 的显示都是以本地时区显示的？###
}
/// <summary>
/// 获取 DatetTime 类型的 SQL 片断
/// </summary>
protected override string GetSqlValueByDateTime(object value, object dbType, int?precision)
{
// 默认精度6
string format = "yyyy-MM-dd HH:mm:ss";
bool isTimestamp = false;
if (DbTypeUtils.IsDateTime(dbType) || DbTypeUtils.IsDateTime2(dbType))
{
format = "yyyy-MM-dd HH:mm:ss.ffffff";
isTimestamp = true;
string pad = string.Empty;
if (precision != null && precision.Value > 0)
{
pad = "f".PadLeft(precision.Value > 7 ? 7 : precision.Value, 'f');
}
if (!string.IsNullOrEmpty(pad))
{
format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", pad);
}
}
string result = this.EscapeQuote(((DateTime)value).ToString(format), false, false);
result = isTimestamp
? string.Format("TO_TIMESTAMP({0},'yyyy-mm-dd hh24:mi:ss.ff')", result)
: string.Format("TO_DATE({0},'yyyy-mm-dd hh24:mi:ss')", result);
return(result);
}
// 获取 DatetTime 类型的 SQL 片断（包括DateTime和TimeStamp)
protected override string GetSqlValueByDateTime(object value, object dbType, int?precision)
{
// 默认精度为0
string format = "yyyy-MM-dd HH:mm:ss";
if (DbTypeUtils.IsDate(dbType))
{
format = "yyyy-MM-dd";
}
else if (DbTypeUtils.IsDateTime(dbType) || DbTypeUtils.IsDateTime2(dbType))
{
string pad = string.Empty;
if (precision != null && precision.Value > 0)
{
pad = "f".PadLeft(precision.Value > 6 ? 6 : precision.Value, 'f');
}
if (!string.IsNullOrEmpty(pad))
{
format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", pad);
}
}
string result = this.EscapeQuote(((DateTime)value).ToString(format), false, false);
return(result);
}
// 获取 DateTimeOffset 类型的 SQL 片断
protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?precision)
{
DbTypeUtils.IsDateTimeOffset(dbType);
return(null);
//string format = "yyyy-MM-dd HH:mm:ss.ffffff";
//_OracleDbType dbTypeInfo = _OracleDbType.Create(dbType);
//if (dbTypeInfo != null && dbTypeInfo.IsDateTimeOffset)
//{
// string pad = string.Empty;
// if (precision != null && precision.Value > 0) pad = "f".PadLeft(precision.Value > 7 ? 7 : precision.Value, 'f');
// if (!string.IsNullOrEmpty(pad)) format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", pad);
//}
//string date = ((DateTimeOffset)value).DateTime.ToString(format);
//string span = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm");
//span = string.Format("{0}{1}", ((DateTimeOffset)value).Offset.Hours >= 0 ? '+' : '-', span);
//string result = string.Format("(TIMESTAMP '{0}{1}')", date, span);
//return result;
//c#中向oracle中操作timestamp 必须间接的转timestamp 进行操作：
//SELECT * FROM T_TABLE WHERE createDate Between TO_TIMESTAMP('2015-09-15','yyyy-mm-dd hh24:mi:ss') AND TO_TIMESTAMP('2015-09-25','yyyy-mm-dd hh24:mi:ss')
///或者:
//SELECT * FROM T_TABLE WHERE createDate Between TO_TIMESTAMP_TZ('2013-12-09','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') AND TO_TIMESTAMP_TZ('2015-12-09','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
}
// 获取 Time 类型的 SQL 片断
protected override string GetSqlValueByTime(object value, object dbType, int?precision)
{
// https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-FD8C41B7-8CDC-4D02-8E6B-5250416BC17D
// throw new NotImplementedException("Oracle [Time] must map to .NET [DateTime] type.");
DbTypeUtils.IsTime(dbType);
return(null);
}
/// <summary>
/// 设置命令参数对象的 DbType属性
/// </summary>
/// <param name="parameter">命令参数对象</param>
/// <param name="dbType">DbType属性</param>
public static void SetDbType(this NpgsqlParameter parameter, object dbType)
{
if (dbType != null)
{
if (dbType is DbType)
{
parameter.DbType = (DbType)dbType;
}
else if (dbType is NpgsqlDbType)
{
parameter.NpgsqlDbType = (NpgsqlDbType)dbType;
}
else
{
DbTypeUtils.ThrowException(dbType);
}
}
}
/// <summary>
/// 是否日期+时间类型
/// </summary>
public static bool IsDateTime(object dbType)
{
if (dbType == null)
{
return(false);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.DateTime);
}
else if (dbType is NpgsqlDbType)
{
return(((NpgsqlDbType)dbType) == NpgsqlDbType.Timestamp);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
}
/// <summary>
/// 是否日期类型
/// </summary>
public static bool IsDate(object dbType)
{
if (dbType == null)
{
return(false);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.Date);
}
else if (dbType is SqlDbType)
{
return(((SqlDbType)dbType) == SqlDbType.Date);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
}
/// <summary>
/// 是否日期+时间+精度类型
/// </summary>
public static bool IsDateTime2(object dbType)
{
if (dbType == null)
{
return(false);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.DateTime2);
}
else if (dbType is OracleDbType)
{
return(((OracleDbType)dbType) == OracleDbType.TimeStamp);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
}
/// <summary>
/// 检查是否Unicode数据类型
/// </summary>
public static bool IsUnicode(object dbType)
{
if (dbType == null)
{
return(true);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.String || ((DbType)dbType) == DbType.StringFixedLength);
}
else if (dbType is OracleDbType)
{
return(((OracleDbType)dbType) == OracleDbType.NVarchar2 || ((OracleDbType)dbType) == OracleDbType.NChar || ((OracleDbType)dbType) == OracleDbType.NClob);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
}
/// <summary>
/// 是否时间类型
/// </summary>
public static bool IsTime(object dbType)
{
if (dbType == null)
{
return(false);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.Time);
}
else if (dbType is MySqlDbType)
{
return(((MySqlDbType)dbType) == MySqlDbType.Time);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
}
/// <summary>
/// 检查是否Unicode数据类型
/// </summary>
public static bool IsUnicode(object dbType)
{
// 默认加 N
if (dbType == null)
{
return(true);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.String || ((DbType)dbType) == DbType.StringFixedLength);
}
else if (dbType is SqlDbType)
{
return(((SqlDbType)dbType) == SqlDbType.NVarChar || ((SqlDbType)dbType) == SqlDbType.NChar || ((SqlDbType)dbType) == SqlDbType.NText);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
}
/// <summary>
/// 是否日期+时间+精度+时区类型
/// </summary>
public static bool IsDateTimeOffset(object dbType)
{
#if netcore
if (dbType == null)
{
return(false);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.DateTimeOffset);
}
else if (dbType is NpgsqlDbType)
{
return(((NpgsqlDbType)dbType) == NpgsqlDbType.TimestampTz);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
#endif
#if !netcore
if (dbType == null)
{
return(false);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.DateTimeOffset);
}
else if (dbType is NpgsqlDbType)
{
return(((NpgsqlDbType)dbType) == NpgsqlDbType.TimestampTZ);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
#endif
}
// 获取 Time 类型的 SQL 片断
protected override string GetSqlValueByTime(object value, object dbType, int?precision)
{
// 默认精度6
string format = @"hh\:mm\:ss\.ffffff";
if (DbTypeUtils.IsTime(dbType))
{
string pad = string.Empty;
if (precision != null && precision.Value > 0)
{
pad = "f".PadLeft(precision.Value > 6 ? 6 : precision.Value, 'f');
}
if (!string.IsNullOrEmpty(pad))
{
format = string.Format(@"hh\:mm\:ss\.{0}", pad);
}
}
string result = this.EscapeQuote(((TimeSpan)value).ToString(format), false, false);
return(result);
}
/// <summary>
/// 是否时间类型
/// </summary>
public static bool IsTime(object dbType)
{
#if netcore
if (dbType == null)
{
return(false);
}
else if (dbType is DbType)
{
return(((DbType)dbType) == DbType.Time);
}
else if (dbType is NpgsqlDbType)
{
return(((NpgsqlDbType)dbType) == NpgsqlDbType.Time);
}
else
{
return(DbTypeUtils.ThrowException(dbType));
}
#endif
#if !netcore
throw new NotSupportedException("Npgsql does not support Time DbType.");
#endif
}
// 获取 DateTimeOffset 类型的 SQL 片断
protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?precision = null)
{
DbTypeUtils.IsDateTimeOffset(dbType);
return(null);
}
/// <summary>
/// 是否日期+时间+精度类型
/// </summary>
public static bool IsDateTime2(object dbType)
{
return(DbTypeUtils.IsDateTime(dbType));
}