/// <summary> /// 获取 Time 类型的 SQL 片断 /// </summary> /// <param name="value">值</param> /// <param name="dbType">数据类型</param> /// <param name="scale">小数位</param> /// <returns></returns> protected override string GetSqlValueByTime(object value, object dbType, int?scale) { // SQLSERVER 的Time类型范围:00:00:00.0000000 到 23:59:59.9999999 // https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/time-transact-sql?view=sql-server-2017 // 默认精度为7 string format = @"hh\:mm\:ss\.fffffff"; if (DbTypeUtils.IsTime(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 7 ? 7 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format(@"hh\:mm\:ss\.{0}", s); } } string result = this.EscapeQuote(((TimeSpan)value).ToString(format), false, false); return(result); }
// 获取 DateTimeOffset 类型的 SQL 片断 protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale) { // 默认精度为6 string format = "yyyy-MM-dd HH:mm:ss.ffffff"; if (DbTypeUtils.IsDateTimeOffset(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 7 ? 7 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", s); } } string myDateTime = ((DateTimeOffset)value).DateTime.ToString(format); string myOffset = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); myOffset = string.Format("{0}{1}", ((DateTimeOffset)value).Offset < TimeSpan.Zero ? '-' : '+', myOffset); string result = string.Format("TO_TIMESTAMP_TZ('{0} {1}','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm')", myDateTime, myOffset); return(result); }
// 获取 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); }
/// <summary> /// 获取 DateTimeOffset 类型的 SQL 片断 /// </summary> /// <param name="value">值</param> /// <param name="dbType">数据类型</param> /// <param name="scale">小数位</param> /// <returns></returns> protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale) { // 默认精度为7 string format = "yyyy-MM-dd HH:mm:ss.fffffff"; if (DbTypeUtils.IsDateTimeOffset(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 7 ? 7 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", s); } } string myDateTime = this.EscapeQuote(((DateTimeOffset)value).DateTime.ToString(format), false, false); string myOffset = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); myOffset = string.Format("{0}{1}", ((DateTimeOffset)value).Offset < TimeSpan.Zero ? '-' : '+', myOffset); myOffset = this.EscapeQuote(myOffset, false, false); string result = string.Format("TODATETIMEOFFSET({0},{1})", myDateTime, myOffset); return(result); }
// 获取 Time 类型的 SQL 片断 protected override string GetSqlValueByTime(object value, object dbType, int?scale) { // 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 TimeSpan ts = (TimeSpan)value; // 默认精度为6 string format = @"hh\:mm\:ss\.ffffff"; if (DbTypeUtils.IsTime(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 7 ? 7 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format(@"hh\:mm\:ss\.{0}", s); } } string result = ts.ToString(format); result = string.Format("{0} {1}", ts.Days, result); result = this.EscapeQuote(result, false, false); result = string.Format("TO_DSINTERVAL({0})", result); return(result); }
// 获取 DatetTime 类型的 SQL 片断 protected override string GetSqlValueByDateTime(object value, object dbType, int?scale) { // 默认精度6 string format = "yyyy-MM-dd HH:mm:ss.ffffff"; if (DbTypeUtils.IsDate(dbType)) { format = "yyyy-MM-dd"; } else if (DbTypeUtils.IsDateTime(dbType) || DbTypeUtils.IsDateTime2(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 6 ? 6 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", s); } } string date = ((DateTime)value).ToString(format); string result = string.Format("'{0}'::TIMESTAMP", date); return(result); }
/// <summary> /// 获取 DatetTime 类型的 SQL 片断 /// </summary> /// <param name="value">值</param> /// <param name="dbType">数据类型</param> /// <param name="scale">小数位</param> /// <returns></returns> protected override string GetSqlValueByDateTime(object value, object dbType, int?scale) { // 默认精度为0 string format = "yyyy-MM-dd HH:mm:ss"; if (DbTypeUtils.IsDate(dbType)) { format = "yyyy-MM-dd"; } else if (DbTypeUtils.IsDateTime(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 6 ? 6 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", s); } } string result = this.EscapeQuote(((DateTime)value).ToString(format), false, false); return(result); }
// 获取 DateTimeOffset 类型的 SQL 片断 protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale) { // 默认精度6 string format = "yyyy-MM-dd HH:mm:ss.ffffff"; if (DbTypeUtils.IsDateTimeOffset(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 6 ? 6 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", s); } } string myDateTime = ((DateTimeOffset)value).DateTime.ToString(format); string myOffset = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); myOffset = string.Format("{0}{1}", ((DateTimeOffset)value).Offset < TimeSpan.Zero ? '-' : '+', myOffset); string result = string.Format("'{0}{1}'::TIMESTAMPTZ ", myDateTime, myOffset); return(result); // Npgsql 的显示都是以本地时区显示的?### }
// 获取 DatetTime 类型的 SQL 片断 protected override string GetSqlValueByDateTime(object value, object dbType, int?precision) { // 默认精度为3 string format = "yyyy-MM-dd HH:mm:ss.fff"; if (DbTypeUtils.IsDate(dbType)) { format = "yyyy-MM-dd"; } else if (DbTypeUtils.IsDateTime(dbType)) { format = "yyyy-MM-dd HH:mm:ss.fff"; } else if (DbTypeUtils.IsDateTime2(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); } else { format = "yyyy-MM-dd HH:mm:ss.fffffff"; } } string result = this.EscapeQuote(((DateTime)value).ToString(format), false, false); return(result); }
/// <summary> /// 获取 Time 类型的 SQL 片断 /// </summary> /// <param name="value">值</param> /// <param name="dbType">数据类型</param> /// <param name="scale">小数位</param> /// <returns></returns> protected override string GetSqlValueByTime(object value, object dbType, int?scale) { // the range is '-838:59:59.000000' to '838:59:59.000000' new TimeSpan(-34, -22, -59, -59)~new TimeSpan(34, 22, 59, 59); // https://dev.mysql.com/doc/refman/8.0/en/time.html TimeSpan ts = (TimeSpan)value; int hours = (int)ts.TotalHours; // 默认精度为7 string format = @"mm\:ss\.ffffff"; if (DbTypeUtils.IsTime(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 6 ? 6 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format(@"mm\:ss\.{0}", s); } } string result = ts.ToString(format); result = string.Format("{0}:{1}", hours, result); result = this.EscapeQuote(result, false, false); return(result); }
// 获取 DatetTime 类型的 SQL 片断 protected override string GetSqlValueByDateTime(object value, object dbType, int?scale) { DateTime date = (DateTime)value; // 默认精度6 string format = "yyyy-MM-dd HH:mm:ss.ffffff"; if (DbTypeUtils.IsDate(dbType)) { format = "yyyy-MM-dd"; } else if (DbTypeUtils.IsDateTime(dbType) || DbTypeUtils.IsDateTime2(dbType)) { string s = string.Empty; format = "yyyy-MM-dd HH:mm:ss.ffffff"; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 7 ? 7 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format("yyyy-MM-dd HH:mm:ss.{0}", s); } } string result = this.EscapeQuote(((DateTime)value).ToString(format), false, false); result = string.Format("TO_TIMESTAMP({0},'yyyy-mm-dd hh24:mi:ss.ff')", result); return(result); }
// 获取 DateTimeOffset 类型的 SQL 片断 protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?precision) { // 默认精度为7 string format = "yyyy-MM-dd HH:mm:ss.fffffff"; 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 = this.EscapeQuote(((DateTimeOffset)value).DateTime.ToString(format), false, false); string span = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); span = string.Format("{0}{1}", ((DateTimeOffset)value).Offset.Hours >= 0 ? '+' : '-', span); span = this.EscapeQuote(span, false, false); string result = string.Format("TODATETIMEOFFSET({0},{1})", date, span); return(result); }
/// <summary> /// 访问 IsNullOrEmpty 方法 /// </summary> /// <param name="m">方法表达式</param> protected override Expression VisitIsNullOrEmpty(MethodCallExpression m) { _builder.Append("ISNULL("); _visitor.Visit(m.Arguments[0]); _builder.Append(","); bool isUnicode = DbTypeUtils.IsUnicode(_visitedMark.Current); string empty = isUnicode ? "N''" : "''"; _builder.Append(empty); _builder.Append(") = "); _builder.Append(empty); return(m); }
/// <summary> /// 设置命令参数对象的 DbType属性 /// </summary> /// <param name="parameter">命令参数对象</param> /// <param name="dbType">DbType属性</param> public static void DbType(this SQLiteParameter parameter, object dbType) { if (dbType != null) { if (dbType is DbType) { parameter.DbType = (DbType)dbType; } else { DbTypeUtils.ThrowException(dbType); } } }
/// <summary> /// 访问 string.Contains 方法 /// </summary> /// <param name="m">方法表达式</param> protected override Expression VisitStringContains(MethodCallExpression m) { // https://www.cnblogs.com/yangmingyu/p/6928209.html // 对于其他的特殊字符:'^', '-', ']' 因为它们本身在包含在 '[]' 中使用,所以需要用另外的方式来转义,于是就引入了 like 中的 escape 子句,另外值得注意的是:escape 可以转义所有的特殊字符。 // EF 的 Like 不用参数化... _visitor.Visit(m.Object); if (this.NotOperands != null && this.NotOperands.Contains(m)) { _builder.Append(" NOT"); } _builder.Append(" LIKE "); if (m.Arguments[0].CanEvaluate()) { ColumnAttribute column = null; bool isUnicode = DbTypeUtils.IsUnicode(_visitedMark.Current, out column); string value = _dbValue.GetSqlValue(m.Arguments[0].Evaluate().Value, _builder.Token, column); if (!_builder.Parameterized && value != null) { value = value.TrimStart('N').Trim('\''); } if (_builder.Parameterized) { _builder.Append("("); _builder.Append("'%' + "); _builder.Append(value); _builder.Append(" + '%'"); _builder.Append(")"); } else { if (isUnicode) { _builder.Append('N'); } _builder.Append("'%"); _builder.Append(value); _builder.Append("%'"); } } else { _builder.Append("('%' + "); _visitor.Visit(m.Arguments[0]); _builder.Append(" + '%')"); } return(m); }
/// <summary> /// 是否日期+时间+精度类型 /// </summary> /// <param name="dbType">DbType属性</param> public static bool IsDateTime2(object dbType) { if (dbType == null) { return(false); } else if (dbType is DbType) { return(((DbType)dbType) == System.Data.DbType.DateTime2); } else { return(DbTypeUtils.ThrowException(dbType)); } }
/// <summary> /// 访问 EndWidth 方法 /// </summary> /// <param name="m">方法表达式</param> protected override Expression VisitEndsWith(MethodCallExpression m) { if (m != null) { _visitor.Visit(m.Object); if (this.NotOperands != null && this.NotOperands.Contains(m)) { _builder.Append(" NOT"); } _builder.Append(" LIKE "); if (m.Arguments[0].CanEvaluate()) { ColumnAttribute column = null; bool isUnicode = DbTypeUtils.IsUnicode(_visitedMark.Current, out column); string value = _dbValue.GetSqlValue(m.Arguments[0].Evaluate().Value, _builder.Token, column); if (!_builder.Parameterized && value != null) { value = value.TrimStart('N').Trim('\''); } if (_builder.Parameterized) { _builder.Append("('%' || "); _builder.Append(value); _builder.Append(")"); } else { if (isUnicode) { _builder.Append('N'); } _builder.Append("'%"); _builder.Append(value); _builder.Append("'"); } } else { _builder.Append("('%' || "); _visitor.Visit(m.Arguments[0]); _builder.Append(")"); } } return(m); }
/// <summary> /// 检查指定的数据类型是否为 Unicode /// </summary> /// <param name="dbType">数据类型 <see cref="System.Data.DbType"/> </param> /// <returns></returns> public static bool IsUnicode(object dbType) { if (dbType == null) { return(true); } else if (dbType is DbType) { return(((DbType)dbType) == System.Data.DbType.String || ((DbType)dbType) == System.Data.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 (dbType == null) { return(false); } else if (dbType is DbType) { return(((DbType)dbType) == System.Data.DbType.DateTimeOffset); } else if (dbType is OracleDbType) { return(((OracleDbType)dbType) == OracleDbType.TimeStampTZ);// || ((OracleDbType)dbType) == OracleDbType.TimeStampLTZ; } 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) == System.Data.DbType.Time); } else if (dbType is OracleDbType) { return(((OracleDbType)dbType) == OracleDbType.IntervalDS); } else { return(DbTypeUtils.ThrowException(dbType)); } }
/// <summary> /// 是否日期+时间+精度+时区类型 /// </summary> /// <param name="dbType">DbType属性</param> public static bool IsDateTimeOffset(object dbType) { #if netcore if (dbType == null) { return(false); } else if (dbType is DbType) { return(((DbType)dbType) == System.Data.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) == System.Data.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) { // 默认精度为7 string format = @"hh\:mm\:ss\.fffffff"; if (DbTypeUtils.IsTime(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(@"hh\:mm\:ss\.{0}", pad); } } string result = this.EscapeQuote(((TimeSpan)value).ToString(format), false, false); return(result); }
// 获取 Time 类型的 SQL 片断 protected override string GetSqlValueByTime(object value, object dbType, int?scale) { // 默认精度6 string format = @"hh\:mm\:ss\.ffffff"; if (DbTypeUtils.IsTime(dbType)) { string s = string.Empty; if (scale != null && scale.Value > 0) { s = string.Empty.PadLeft(scale.Value > 6 ? 6 : scale.Value, 'f'); } if (!string.IsNullOrEmpty(s)) { format = string.Format(@"hh\:mm\:ss\.{0}", s); } } string date = ((TimeSpan)value).ToString(format); string result = string.Format("'{0}'::TIME", date); return(result); }
/// <summary> /// 设置命令参数对象的 DbType属性 /// </summary> /// <param name="parameter">命令参数对象</param> /// <param name="dbType">DbType属性</param> public static void DbType(this SqlParameter parameter, object dbType) { if (dbType != null) { if (dbType is DbType) { parameter.DbType = (DbType)dbType; } else if (dbType is SqlDbType) { parameter.SqlDbType = (SqlDbType)dbType; } else { DbTypeUtils.ThrowException(dbType); } } else if (parameter.Value != null && parameter.Value is DateTime && dbType == null) { // 如果 DateTime 没有指定 DbType,则需要转为 DateTime2才能保持原有的精度 parameter.DbType = System.Data.DbType.DateTime2; } }
/// <summary> /// 检查字段或属性成员声明的 DbType 是否为 Unicode 数据类型 /// </summary> /// <param name="m">将要检查的字段或属性成员</param> /// <param name="column">字段或属性成员显示声明的列特性</param> /// <returns></returns> public static bool IsUnicode(MemberVisitedStack.VisitedMember m, out ColumnAttribute column) { column = m != null?TypeUtils.GetColumnAttribute(m.Member, m.ReflectedType) : null; return(DbTypeUtils.IsUnicode(column == null ? null : column.DbType)); }
/// <summary> /// 检查字段或属性成员声明的 DbType 是否为 Unicode 数据类型 /// </summary> /// <param name="m">将要检查的字段或属性成员</param> /// <returns></returns> public static bool IsUnicode(MemberVisitedStack.VisitedMember m) { ColumnAttribute column = null; return(DbTypeUtils.IsUnicode(m, out column)); }
/// <summary> /// 是否日期+时间+精度类型 /// </summary> public static bool IsDateTime2(object dbType) { return(DbTypeUtils.IsDateTime(dbType)); }
/// <summary> /// 获取 DateTimeOffset 类型的 SQL 片断 /// </summary> /// <param name="value">值</param> /// <param name="dbType">数据类型</param> /// <param name="scale">小数位</param> /// <returns></returns> protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale = null) { DbTypeUtils.IsDateTimeOffset(dbType); return(null); }
/// <summary> /// 判断指定类型是否是unicode /// </summary> protected override bool IsUnicode(object dbType) { return(DbTypeUtils.IsUnicode(dbType)); }
/// <summary> /// 访问 ToString 方法 /// </summary> protected override Expression VisitToStringImpl(Expression node) { // => a.ID.ToString() // 字符串不进行转换 if (node == null || node.Type == typeof(string)) { return(_visitor.Visit(node)); } ColumnAttribute column = null; bool isUnicode = _provider.DbValue.IsUnicode(_visitedMark.Current, out column); bool isBytes = node.Type == typeof(byte[]); bool isDate = node.Type == typeof(DateTime) || node.Type == typeof(DateTime?) || node.Type == typeof(TimeSpan) || node.Type == typeof(TimeSpan?) || node.Type == typeof(DateTimeOffset) || node.Type == typeof(DateTimeOffset?); if (!isBytes) { if (isUnicode) { _builder.Append("TO_NCHAR("); } else { _builder.Append("TO_CHAR("); } } // 其它类型转字符串 if (isDate) { _visitor.Visit(node); string format = string.Empty; ColumnAttribute c = _provider.DbValue.GetColumnAttribute(_visitedMark.Current); if (c != null && DbTypeUtils.IsDate(c.DbType)) { format = "yyyy-mm-dd"; } else if (c != null && (DbTypeUtils.IsDateTime(c.DbType) || DbTypeUtils.IsDateTime2(c.DbType))) { format = "yyyy-mm-dd hh24:mi:ss.ff"; } else if (c != null && DbTypeUtils.IsDateTimeOffset(c.DbType)) { format = "yyyy-mm-dd hh24:mi:ss.ff tzh:tzm"; } // 没有显式指定数据类型,则根据表达式的类型来判断 if (string.IsNullOrEmpty(format)) { if (node.Type == typeof(DateTime) || node.Type == typeof(DateTime?)) { format = "yyyy-mm-dd hh24:mi:ss.ff"; } else if (node.Type == typeof(DateTimeOffset) || node.Type == typeof(DateTimeOffset?)) { format = "yyyy-mm-dd hh24:mi:ss.ff tzh:tzm"; } } if (!string.IsNullOrEmpty(format)) { _builder.Append(",'"); _builder.Append(format); _builder.Append("'"); } } else if (isBytes) { _builder.Append("RTRIM(DBMS_LOB.SUBSTR("); _visitor.Visit(node); _builder.Append(')'); } else if (node.Type == typeof(Guid)) { _builder.Append("REGEXP_REPLACE(REGEXP_REPLACE("); _visitor.Visit(node); _builder.Append(@",'(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'),'(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})','\4\3\2\1-\6\5-\8\7\9')"); } else { _visitor.Visit(node); } _builder.Append(')'); return(node); }