// 增加一个参数
protected override IDbDataParameter AddParameter(object value, ResolveToken token,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
if (value is bool)
{
value = ((bool)value) ? 1 : 0;
}
else if (value is Guid)
{
value = ((Guid)value).ToByteArray();
dbType = OracleDbType.Raw;
}
else if (value is DateTimeOffset)
{
var dto = (DateTimeOffset)value;
var zone = (dto.Offset < TimeSpan.Zero ? "-" : "+") + dto.Offset.ToString("hh\\:mm");
value = new OracleTimeStampTZ(dto.DateTime, zone);
}
// 补充 DbType
OracleParameter parameter = (OracleParameter)base.AddParameter(value, token, dbType, size, precision, scale, direction);
parameter.DbType(dbType);
return(parameter);
}
/// <summary>
/// Cria um parâmetro para a consulta.
/// </summary>
/// <param name="name">Nome do parâmetro.</param>
/// <param name="value">Valor do parâmetro.</param>
/// <returns>Objeto do tipo <see cref="GDAParameter"/>.</returns>
protected override GDAParameter CreateParameter(string name, object value)
{
if (value != null && value.GetType() == typeof(DateTimeOffset))
{
var date = (DateTimeOffset)value;
#if DEVART
value = new Devart.Data.Oracle.OracleTimeStamp(date.DateTime, date.Offset.ToString());
#else
value = new OracleTimeStampTZ(date.DateTime, date.Offset.ToString());
#endif
GDAParameter parameter = new GDAParameter(name, value);
parameter.DbType = DbType.DateTime;
return(parameter);
}
else if (value is Guid)
{
return(new GDAParameter(name, ((Guid)value).ToString()));
}
else
{
if (value != null && value.GetType().IsEnum)
{
value = Convert.ChangeType(value, Enum.GetUnderlyingType(value.GetType()));
}
return(new GDAParameter(name, value));
}
}
public static DateTime? ToNullableDateTime(OracleTimeStampTZ value)
{
if (value.IsNull)
return null;
if (value.TimeZone == "UTC" || value.TimeZone == "+00:00")
return DateTime.SpecifyKind(value.Value, DateTimeKind.Utc);
var utc = value.Value.Subtract(value.GetTimeZoneOffset());
return DateTime.SpecifyKind(utc, DateTimeKind.Utc).ToLocalTime();
}
public OracleTimestampWithTimeZone(OracleTimeStampTZ timeStamp)
{
_value = timeStamp;
if (!timeStamp.IsNull)
{
_dateTime = new OracleDateTime(timeStamp.Year, timeStamp.Month, timeStamp.Day, timeStamp.Hour, timeStamp.Minute, timeStamp.Second);
}
}
/// <summary>
///
/// </summary>
/// <param name="format"></param>
/// <param name="date"></param>
/// <param name="dateTime"></param>
/// <returns></returns>
protected internal bool ConvertToDateTimeFromOracleInternal(OracleDateTimeFormat format, object date, out DateTime dateTime)
{
dateTime = DateTime.MinValue;
if (date == null)
{
return(false);
}
try
{
switch (format)
{
case OracleDateTimeFormat.ORACLE_DATE:
var oracleDate = new OracleDate((string)date);
if (oracleDate != OracleDate.Null)
{
dateTime = oracleDate.Value;
}
break;
case OracleDateTimeFormat.ORACLE_TIMESTAMP:
var oracleTSDate = new OracleTimeStamp((string)date);
if (oracleTSDate != OracleTimeStamp.Null)
{
dateTime = oracleTSDate.Value;
}
break;
case OracleDateTimeFormat.ORACLE_TIMESTAMP_TZ:
var oracleTSZDate = new OracleTimeStampTZ((string)date);
if (oracleTSZDate != OracleTimeStampTZ.Null)
{
dateTime = oracleTSZDate.Value;
}
break;
case OracleDateTimeFormat.ORACLE_TIMESTAMP_LTZ:
var oracleLTSZDate = new OracleTimeStampLTZ((string)date);
if (oracleLTSZDate != OracleTimeStampLTZ.Null)
{
dateTime = oracleLTSZDate.Value;
}
break;
default:
throw new ApplicationException("Invalid format specified");
}
}
catch (Exception)
{
return(false);
}
return(true);
}
public static DateTime?ToNullableDateTime(OracleTimeStampTZ value)
{
if (value.IsNull)
{
return(null);
}
if (value.TimeZone == "UTC" || value.TimeZone == "+00:00")
{
return(DateTime.SpecifyKind(value.Value, DateTimeKind.Utc));
}
var utc = value.Value.Subtract(value.GetTimeZoneOffset());
return(DateTime.SpecifyKind(utc, DateTimeKind.Utc).ToLocalTime());
}
private void button7_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection();
_connObj.ConnectionString = _connstring;
_connObj.Open();
OracleGlobalization info = OracleGlobalization.GetClientInfo();
info.Territory = "Hong Kong";
info.TimeZone = "Asia/Hong_Kong";
OracleGlobalization.SetThreadInfo(info);
_connObj.SetSessionInfo(info);
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "SELECT LaunchDate AT LOCAL LaunchDateLocal FROM Products";
OracleDataReader _reader = _cmdObj.ExecuteReader();
if (_reader.HasRows)
{
if (_reader.Read())
{
OracleTimeStampTZ _launchDate = _reader.GetOracleTimeStampTZ
(_reader.GetOrdinal("LaunchDateLocal"));
MessageBox.Show(_launchDate.ToString());
}
}
_reader.Dispose();
_cmdObj.Dispose();
_connObj.Dispose();
_reader.Close();
_connObj.Close();
_reader = null;
_connObj = null;
_cmdObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public OracleTimeStampTzField(OracleTimeStampTZ value)
{
_value = value;
}
private object GetValue(OracleDecimal number, OracleDate date, OracleTimeStamp timestamp, OracleTimeStampTZ timestampWithTimezone, OracleTimeStampLTZ timestampWithLocalTimeZone, OracleIntervalYM yearToMonth, OracleIntervalDS dayToSecond)
{
if (!LimitValuesAvailable)
{
return(null);
}
if (_valueType == typeof(OracleDecimal))
{
return(number.IsNull ? null : new OracleNumber(number));
}
if (_valueType == typeof(OracleDate))
{
return(date.IsNull ? null : new OracleDateTime(date));
}
if (_valueType == typeof(OracleTimeStamp))
{
return(timestamp.IsNull ? null : new OracleTimestamp(timestamp));
}
if (_valueType == typeof(OracleTimeStampTZ))
{
return(timestampWithTimezone.IsNull ? null : new OracleTimestampWithTimeZone(timestampWithTimezone));
}
if (_valueType == typeof(OracleTimeStampLTZ))
{
return(timestampWithLocalTimeZone.IsNull ? null : new OracleTimestampWithLocalTimeZone(timestampWithLocalTimeZone));
}
if (_valueType == typeof(OracleIntervalYM))
{
return(yearToMonth.IsNull ? null : new OracleIntervalYearToMonth(yearToMonth));
}
if (_valueType == typeof(OracleIntervalDS))
{
return(dayToSecond.IsNull ? null : new OracleIntervalDayToSecond(dayToSecond));
}
return(null);
}
public static void TextWrite(this TextWriter writer, OracleTimeStampTZ ts1)
{
writer.WriteLine($"\"{ts1.Year:D4}-{ts1.Month:D2}-{ts1.Day:D2} {ts1.Hour:D2}:{ts1.Minute:D2}:{ts1.Second:D2}.{ts1.Nanosecond} {ts1.TimeZone}\"");
}
public static DateTimeOffset ToDateTimeOffset(this OracleTimeStampTZ value)
{
return(new DateTimeOffset(value.Value, value.GetTimeZoneOffset()));
}
internal static bool IsEqual(OracleTimeStampTZ?val1, OracleTimeStampTZ?val2) =>
val1.Value.IsNull != val2.Value.IsNull
? false
: (val1.Value.IsNull && val2.Value.IsNull) ||
OracleTimeStampTZ.SetPrecision(val1.Value, OracleTimeStampPrecisionCompare).Equals(OracleTimeStampTZ.SetPrecision(val2.Value, OracleTimeStampPrecisionCompare));
private object GetValue(OracleDecimal number, OracleDate date, OracleTimeStamp timestamp, OracleTimeStampTZ timestampWithTimezone, OracleTimeStampLTZ timestampWithLocalTimeZone, OracleIntervalYM yearToMonth, OracleIntervalDS dayToSecond)
{
if (!LimitValuesAvailable)
{
return null;
}
if (_valueType == typeof(OracleDecimal))
{
return number.IsNull ? null : new OracleNumber(number);
}
if (_valueType == typeof(OracleDate))
{
return date.IsNull ? null : new OracleDateTime(date);
}
if (_valueType == typeof(OracleTimeStamp))
{
return timestamp.IsNull ? null : new OracleTimestamp(timestamp);
}
if (_valueType == typeof(OracleTimeStampTZ))
{
return timestampWithTimezone.IsNull ? null : new OracleTimestampWithTimeZone(timestampWithTimezone);
}
if (_valueType == typeof(OracleTimeStampLTZ))
{
return timestampWithLocalTimeZone.IsNull ? null : new OracleTimestampWithLocalTimeZone(timestampWithLocalTimeZone);
}
if (_valueType == typeof(OracleIntervalYM))
{
return yearToMonth.IsNull ? null : new OracleIntervalYearToMonth(yearToMonth);
}
if (_valueType == typeof(OracleIntervalDS))
{
return dayToSecond.IsNull ? null : new OracleIntervalDayToSecond(dayToSecond);
}
return null;
}
