internal bool getBoolean(int columnIndex)
{
string val = getObjectInternal(columnIndex);
SFDataType sfDataType = sfResultSetMetaData.getColumnTypeByIndex(columnIndex);
return((bool)SFDataConverter.convertToCSharpVal(val, sfDataType, typeof(Boolean)));
}
private static DateTimeOffset ConvertToDateTimeOffset(string srcVal, SFDataType srcType)
{
switch (srcType)
{
case SFDataType.TIMESTAMP_TZ:
int spaceIndex = srcVal.IndexOf(' ');
if (spaceIndex == -1)
{
throw new SnowflakeDbException(SFError.INTERNAL_ERROR,
$"Invalid timestamp_tz value: {srcVal}");
}
else
{
Tuple <long, long> secAndNsecTZ = ExtractTimestamp(srcVal.Substring(0, spaceIndex));
int offset = Int32.Parse(srcVal.Substring(spaceIndex + 1, srcVal.Length - spaceIndex - 1));
TimeSpan offSetTimespan = new TimeSpan((offset - 1440) / 60, 0, 0);
return(new DateTimeOffset(UnixEpoch.Ticks +
(secAndNsecTZ.Item1 * 1000 * 1000 * 1000 + secAndNsecTZ.Item2) / 100, TimeSpan.Zero).ToOffset(offSetTimespan));
}
case SFDataType.TIMESTAMP_LTZ:
Tuple <long, long> secAndNsecLTZ = ExtractTimestamp(srcVal);
return(new DateTimeOffset(UnixEpoch.Ticks +
(secAndNsecLTZ.Item1 * 1000 * 1000 * 1000 + secAndNsecLTZ.Item2) / 100, TimeSpan.Zero).ToLocalTime());
default:
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcType, typeof(DateTimeOffset).ToString());
}
}
internal byte[] getBytes(int columnIndex)
{
string val = getObjectInternal(columnIndex);
SFDataType sfDataType = sfResultSetMetaData.getColumnTypeByIndex(columnIndex);
return((byte[])SFDataConverter.convertToCSharpVal(val, sfDataType, typeof(byte[])));
}
internal int getInt32(int columnIndex)
{
string val = getObjectInternal(columnIndex);
SFDataType sfDataType = sfResultSetMetaData.getColumnTypeByIndex(columnIndex);
return((Int32)SFDataConverter.convertToCSharpVal(val, sfDataType, typeof(Int32)));
}
private static DateTimeOffset ConvertToDateTimeOffset(UTF8Buffer srcVal, SFDataType srcType)
{
switch (srcType)
{
case SFDataType.TIMESTAMP_TZ:
int spaceIndex = Array.IndexOf <byte>(srcVal.Buffer, (byte)' ', srcVal.offset, srcVal.length);;
if (spaceIndex == -1)
{
throw new SnowflakeDbException(SFError.INTERNAL_ERROR,
$"Invalid timestamp_tz value: {srcVal}");
}
else
{
spaceIndex -= srcVal.offset;
Tuple <long, long> secAndNsecTZ = ExtractTimestamp(new UTF8Buffer(srcVal.Buffer, srcVal.offset, spaceIndex));
int offset = FastParser.FastParseInt32(srcVal.Buffer, srcVal.offset + spaceIndex + 1, srcVal.length - spaceIndex - 1);
TimeSpan offSetTimespan = new TimeSpan((offset - 1440) / 60, 0, 0);
return(new DateTimeOffset(UnixEpoch.Ticks +
(secAndNsecTZ.Item1 * 1000 * 1000 * 1000 + secAndNsecTZ.Item2) / 100, TimeSpan.Zero).ToOffset(offSetTimespan));
}
case SFDataType.TIMESTAMP_LTZ:
Tuple <long, long> secAndNsecLTZ = ExtractTimestamp(srcVal);
return(new DateTimeOffset(UnixEpoch.Ticks +
(secAndNsecLTZ.Item1 * 1000 * 1000 * 1000 + secAndNsecLTZ.Item2) / 100, TimeSpan.Zero).ToLocalTime());
default:
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcType, typeof(DateTimeOffset).ToString());
}
}
public SnowflakeDbParameter Add(string parameterName, SFDataType dataType)
{
SnowflakeDbParameter parameter = new SnowflakeDbParameter(parameterName, dataType);
parameterList.Add(parameter);
return(parameter);
}
internal static string csharpValToSfVal(SFDataType sfDataType, object srcVal)
{
switch (sfDataType)
{
case SFDataType.TIMESTAMP_LTZ:
if (srcVal.GetType() != typeof(DateTimeOffset))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), SFDataType.TIMESTAMP_LTZ.ToString());
}
else
{
return(((long)(((DateTimeOffset)srcVal).UtcTicks - UnixEpoch.Ticks) * 100).ToString());
}
case SFDataType.TIMESTAMP_TZ:
return(""); // ( (long)( ( (DateTime)srcVal ).Ticks - UnixEpoch.Ticks ) * 100 ).ToString();
case SFDataType.TIMESTAMP_NTZ:
return(((long)(((DateTime)srcVal).Ticks - UnixEpoch.Ticks) * 100).ToString());
//return $"{( (DateTime)srcVal ).ToString( "s" ).Replace( "T", " " )}";
default:
return(srcVal.ToString());
}
}
private Type GetNativeTypeForColumn(SFDataType sfType, ExecResponseRowType col)
{
switch (sfType)
{
case SFDataType.FIXED:
return(col.scale == 0 ? typeof(long) : typeof(decimal));
case SFDataType.REAL:
return(typeof(double));
case SFDataType.TEXT:
case SFDataType.VARIANT:
case SFDataType.OBJECT:
return(typeof(string));
case SFDataType.DATE:
case SFDataType.TIME:
case SFDataType.TIMESTAMP_NTZ:
return(typeof(DateTime));
case SFDataType.TIMESTAMP_LTZ:
case SFDataType.TIMESTAMP_TZ:
return(typeof(DateTimeOffset));
case SFDataType.BINARY:
return(typeof(byte[]));
case SFDataType.BOOLEAN:
return(typeof(bool));
default:
throw new SnowflakeDbException(SFError.INTERNAL_ERROR,
$"Unknow column type: {sfType}");
}
}
internal DateTimeOffset getDateTimeOffset(int columnIndex)
{
string val = getObjectInternal(columnIndex);
SFDataType sfDataType = sfResultSetMetaData.getColumnTypeByIndex(columnIndex);
return((DateTimeOffset)SFDataConverter.convertToCSharpVal(
val, sfDataType, typeof(DateTimeOffset)));
}
// Method with the same signature as before the performance work
// Used by unit tests only
internal static object ConvertToCSharpVal(string srcVal, SFDataType srcType, Type destType)
{
// Create an UTF8Buffer with an offset to get better testing
byte[] b1 = Encoding.UTF8.GetBytes(srcVal);
byte[] b2 = new byte[b1.Length + 100];
Array.Copy(b1, 0, b2, 100, b1.Length);
var v = new UTF8Buffer(b2, 100, b1.Length);
return(ConvertToCSharpVal(v, srcType, destType));
}
internal Type getCSharpTypeByIndex(int targetIndex)
{
if (targetIndex < 0 || targetIndex >= columnCount)
{
throw new SnowflakeDbException(SFError.COLUMN_INDEX_OUT_OF_BOUND, targetIndex);
}
SFDataType sfType = getColumnTypeByIndex(targetIndex);
return(GetNativeTypeForColumn(sfType, rowTypes[targetIndex]));
}
internal string getString(int columnIndex)
{
SFDataType sfDataType = sfResultSetMetaData.getColumnTypeByIndex(columnIndex);
switch (sfDataType)
{
case SFDataType.DATE:
return(SFDataConverter.toDateString(getDateTime(columnIndex),
sfResultSetMetaData.dateOutputFormat));
default:
return(getObjectInternal(columnIndex));
}
}
internal object ConvertToCSharpVal(string srcVal, SFDataType srcType, Type destType)
{
//Logger.DebugFmt("src value: {0}, srcType: {1}, destType: {2}", srcVal, srcType, destType);
if (srcVal == null)
{
return(DBNull.Value);
}
if (destType == typeof(short) ||
destType == typeof(int) ||
destType == typeof(long) ||
destType == typeof(Guid) ||
destType == typeof(double) ||
destType == typeof(float) ||
destType == typeof(decimal))
{
if (!typeConverters.ContainsKey(destType))
{
typeConverters.Add(destType, TypeDescriptor.GetConverter(destType));
}
return(typeConverters[destType].ConvertFrom(srcVal));
}
else if (destType == typeof(Boolean))
{
return(srcVal.Equals("1", StringComparison.OrdinalIgnoreCase) ||
srcVal.Equals("true", StringComparison.OrdinalIgnoreCase));
}
else if (destType == typeof(string))
{
return(srcVal);
}
else if (destType == typeof(DateTime))
{
return(ConvertToDateTime(srcVal, srcType));
}
else if (destType == typeof(DateTimeOffset))
{
return(ConvertToDateTimeOffset(srcVal, srcType));
}
else if (destType == typeof(byte[]))
{
return(srcType == SFDataType.BINARY ?
HexToBytes(srcVal) : Encoding.UTF8.GetBytes(srcVal));
}
else
{
throw new SnowflakeDbException(SFError.INTERNAL_ERROR, "Invalid destination type.");
}
}
internal static object ConvertToCSharpVal(string srcVal, SFDataType srcType, Type destType)
{
Logger.Debug($"src value: {srcVal}, srcType: {srcType}, destType: {destType}");
if (srcVal == null)
{
return(DBNull.Value);
}
if (destType == typeof(short) ||
destType == typeof(int) ||
destType == typeof(long) ||
destType == typeof(Guid) ||
destType == typeof(double) ||
destType == typeof(float) ||
destType == typeof(decimal))
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
var typeConverter = TypeDescriptor.GetConverter(destType);
return(typeConverter.ConvertFrom(srcVal));
}
else if (destType == typeof(Boolean))
{
return(srcVal.Equals("1", StringComparison.OrdinalIgnoreCase) ||
srcVal.Equals("true", StringComparison.OrdinalIgnoreCase));
}
else if (destType == typeof(string))
{
return(srcVal);
}
else if (destType == typeof(DateTime))
{
return(ConvertToDateTime(srcVal, srcType));
}
else if (destType == typeof(DateTimeOffset))
{
return(ConvertToDateTimeOffset(srcVal, srcType));
}
else if (destType == typeof(byte[]))
{
return(srcType == SFDataType.BINARY ?
HexToBytes(srcVal) : Encoding.UTF8.GetBytes(srcVal));
}
else
{
throw new SnowflakeDbException(SFError.INTERNAL_ERROR, "Invalid destination type.");
}
}
private static DateTime ConvertToDateTime(string srcVal, SFDataType srcType)
{
switch (srcType)
{
case SFDataType.DATE:
long srcValLong = Int64.Parse(srcVal);
return(UnixEpoch.AddDays(srcValLong));
case SFDataType.TIME:
case SFDataType.TIMESTAMP_NTZ:
Tuple <long, long> secAndNsec = ExtractTimestamp(srcVal);
return(UnixEpoch.AddTicks((long)(secAndNsec.Item1 * 1000 * 1000 * 1000 + secAndNsec.Item2) / 100));
default:
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal, srcType, typeof(DateTime));
}
}
private static DateTime ConvertToDateTime(UTF8Buffer srcVal, SFDataType srcType)
{
switch (srcType)
{
case SFDataType.DATE:
long srcValLong = FastParser.FastParseInt64(srcVal.Buffer, srcVal.offset, srcVal.length);
return(UnixEpoch.AddDays(srcValLong));
case SFDataType.TIME:
case SFDataType.TIMESTAMP_NTZ:
Tuple <long, long> secAndNsec = ExtractTimestamp(srcVal);
var tickDiff = secAndNsec.Item1 * 10000000L + secAndNsec.Item2 / 100L;
return(UnixEpoch.AddTicks(tickDiff));
default:
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal, srcType, typeof(DateTime));
}
}
internal static string csharpValToSfVal(SFDataType sfDataType, object srcVal)
{
switch (sfDataType)
{
case SFDataType.TIMESTAMP_LTZ:
if (srcVal.GetType() != typeof(DateTimeOffset))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), SFDataType.TIMESTAMP_LTZ.ToString());
}
else
{
return(((long)(((DateTimeOffset)srcVal).UtcTicks - UnixEpoch.Ticks) * 100).ToString());
}
default:
throw new NotImplementedException();
}
}
internal Type getCSharpTypeByIndex(int targetIndex)
{
if (targetIndex < 0 || targetIndex >= columnCount)
{
throw new SnowflakeDbException(SFError.COLUMN_INDEX_OUT_OF_BOUND, targetIndex);
}
SFDataType sfType = getColumnTypeByIndex(targetIndex);
switch (sfType)
{
case SFDataType.FIXED:
return(rowTypes[targetIndex].scale == 0 ? typeof(long) : typeof(decimal));
case SFDataType.REAL:
return(typeof(double));
case SFDataType.TEXT:
case SFDataType.VARIANT:
case SFDataType.OBJECT:
return(typeof(string));
case SFDataType.DATE:
case SFDataType.TIME:
case SFDataType.TIMESTAMP_NTZ:
return(typeof(DateTime));
case SFDataType.TIMESTAMP_LTZ:
case SFDataType.TIMESTAMP_TZ:
return(typeof(DateTimeOffset));
case SFDataType.BINARY:
return(typeof(byte));
case SFDataType.BOOLEAN:
return(typeof(bool));
default:
throw new SnowflakeDbException(SFError.INTERNAL_ERROR,
String.Format("Unknow column type: {0}", sfType));
}
}
static internal Object convertToCSharpVal(string srcVal, SFDataType srcType, Type destType)
{
logger.TraceFormat("src value: {0}, srcType: {1}, destType: {2}", srcVal, srcType, destType);
if (destType == typeof(Int16) ||
destType == typeof(Int32) ||
destType == typeof(Int64) ||
destType == typeof(Guid) ||
destType == typeof(Double) ||
destType == typeof(float) ||
destType == typeof(Decimal))
{
var typeConverter = TypeDescriptor.GetConverter(destType);
return(typeConverter.ConvertFrom(srcVal));
}
else if (destType == typeof(Boolean))
{
return(String.Compare(srcVal, "1", true) == 0 ||
String.Compare(srcVal, "true", true) == 0);
}
else if (destType == typeof(string))
{
return(srcVal);
}
else if (destType == typeof(DateTime))
{
return(convertToDateTime(srcVal, srcType));
}
else if (destType == typeof(DateTimeOffset))
{
return(convertToDateTimeOffset(srcVal, srcType));
}
else if (destType == typeof(byte[]))
{
return(srcType == SFDataType.BINARY ?
hexToBytes(srcVal) : Encoding.UTF8.GetBytes(srcVal));
}
else
{
throw new SnowflakeDbException(SFError.INTERNAL_ERROR, "Invalid destination type.");
}
}
private void testGetDateAndOrTime(string inputTimeStr, int?precision, SFDataType dataType)
{
// Can't use DateTime object as test case, must parse.
DateTime inputTime;
if (inputTimeStr == null)
{
inputTime = dataType == SFDataType.DATE ? DateTime.Today : DateTime.Now;
}
else
{
inputTime = DateTime.ParseExact(inputTimeStr, "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = $"create or replace table testGetDateAndOrTime(cola {dataType}{ (precision == null ? string.Empty : $"({precision})" )});";
internal static string csharpValToSfVal(SFDataType sfDataType, object srcVal)
{
string destVal = null;
if (srcVal != DBNull.Value)
{
switch (sfDataType)
{
case SFDataType.TIMESTAMP_LTZ:
if (srcVal.GetType() != typeof(DateTimeOffset))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), SFDataType.TIMESTAMP_LTZ.ToString());
}
else
{
destVal = ((long)(((DateTimeOffset)srcVal).UtcTicks - UnixEpoch.Ticks) * 100).ToString();
}
break;
case SFDataType.FIXED:
case SFDataType.BOOLEAN:
case SFDataType.REAL:
case SFDataType.TEXT:
destVal = string.Format(CultureInfo.InvariantCulture, "{0}", srcVal);
break;
case SFDataType.TIME:
if (srcVal.GetType() != typeof(DateTime))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), DbType.Time.ToString());
}
else
{
DateTime srcDt = ((DateTime)srcVal);
long nanoSinceMidNight = (long)(srcDt.Ticks - srcDt.Date.Ticks) * 100L;
destVal = nanoSinceMidNight.ToString();
}
break;
case SFDataType.DATE:
if (srcVal.GetType() != typeof(DateTime))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), DbType.Date.ToString());
}
else
{
DateTime dt = ((DateTime)srcVal).Date;
var ts = dt.Subtract(UnixEpoch);
long millis = (long)(ts.TotalMilliseconds);
destVal = millis.ToString();
}
break;
case SFDataType.TIMESTAMP_NTZ:
if (srcVal.GetType() != typeof(DateTime))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), DbType.DateTime.ToString());
}
else
{
DateTime srcDt = (DateTime)srcVal;
var diff = srcDt.Subtract(UnixEpoch);
var tickDiff = diff.Ticks;
destVal = $"{tickDiff}00"; // Cannot multiple tickDiff by 100 because long might overflow.
}
break;
case SFDataType.TIMESTAMP_TZ:
if (srcVal.GetType() != typeof(DateTimeOffset))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), DbType.DateTimeOffset.ToString());
}
else
{
DateTimeOffset dtOffset = (DateTimeOffset)srcVal;
destVal = String.Format("{0} {1}", (dtOffset.UtcTicks - UnixEpoch.Ticks) * 100L,
dtOffset.Offset.TotalMinutes + 1440);
}
break;
case SFDataType.BINARY:
if (srcVal.GetType() != typeof(byte[]))
{
throw new SnowflakeDbException(SFError.INVALID_DATA_CONVERSION, srcVal,
srcVal.GetType().ToString(), DbType.Binary.ToString());
}
else
{
destVal = BytesToHex((byte[])srcVal);
}
break;
default:
throw new SnowflakeDbException(
SFError.UNSUPPORTED_SNOWFLAKE_TYPE_FOR_PARAM, sfDataType.ToString());
}
}
return(destVal);
}
internal static object ConvertToCSharpVal(UTF8Buffer srcVal, SFDataType srcType, Type destType)
{
if (srcVal == null)
{
return(DBNull.Value);
}
try
{
// The most common conversions are checked first for maximum performance
if (destType == typeof(Int64))
{
return(FastParser.FastParseInt64(srcVal.Buffer, srcVal.offset, srcVal.length));
}
else if (destType == typeof(Int32))
{
return(FastParser.FastParseInt32(srcVal.Buffer, srcVal.offset, srcVal.length));
}
else if (destType == typeof(decimal))
{
return(FastParser.FastParseDecimal(srcVal.Buffer, srcVal.offset, srcVal.length));
}
else if (destType == typeof(string))
{
return(srcVal.ToString());
}
else if (destType == typeof(DateTime))
{
return(ConvertToDateTime(srcVal, srcType));
}
else if (destType == typeof(DateTimeOffset))
{
return(ConvertToDateTimeOffset(srcVal, srcType));
}
else if (destType == typeof(Boolean))
{
var val = srcVal.Buffer[srcVal.offset];
return(val == '1' || val == 't' || val == 'T');
}
else if (destType == typeof(byte[]))
{
return(srcType == SFDataType.BINARY ?
HexToBytes(srcVal.ToString()) : srcVal.GetBytes());
}
else if (destType == typeof(Int16))
{
// Use checked keyword to make sure we generate an OverflowException if conversion fails
int result = FastParser.FastParseInt32(srcVal.Buffer, srcVal.offset, srcVal.length);
return(checked ((Int16)result));
}
else if (destType == typeof(byte))
{
// Use checked keyword to make sure we generate an OverflowException if conversion fails
int result = FastParser.FastParseInt32(srcVal.Buffer, srcVal.offset, srcVal.length);
return(checked ((byte)result));
}
else if (destType == typeof(double))
{
return(Convert.ToDouble(srcVal.ToString(), CultureInfo.InvariantCulture));
}
else if (destType == typeof(float))
{
return(Convert.ToSingle(srcVal.ToString(), CultureInfo.InvariantCulture));
}
else if (destType == typeof(Guid))
{
return(new Guid(srcVal.ToString()));
}
else if (destType == typeof(char[]))
{
byte[] data = srcType == SFDataType.BINARY ?
HexToBytes(srcVal.ToString()) : srcVal.GetBytes();
return(Encoding.UTF8.GetString(data).ToCharArray());
}
else
{
throw new SnowflakeDbException(SFError.INTERNAL_ERROR, "Invalid destination type.");
}
}
catch (OverflowException)
{
throw new OverflowException($"Error converting '{srcVal} to {destType.Name}'. Use GetString() to handle very large values");
}
}
public SnowflakeDbParameter()
{
SFDataType = SFDataType.None;
OriginType = SFDataType.None;
}
public SnowflakeDbParameter(string ParameterName, SFDataType SFDataType)
{
this.ParameterName = ParameterName;
this.SFDataType = SFDataType;
OriginType = SFDataType;
}
public SnowflakeDbParameter(int ParameterIndex, SFDataType SFDataType)
{
this.ParameterName = ParameterIndex.ToString();
this.SFDataType = SFDataType;
}