public static string GetSqlType(Type clrType, bool storeDateTimeAsTicks = true, int?maxStringLength = null)
{
if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32) || clrType == typeof(UInt32) || clrType == typeof(Int64))
{
return("integer");
}
else if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal))
{
return("float");
}
else if (clrType == typeof(String) || clrType == typeof(StringBuilder) || clrType == typeof(Uri) || clrType == typeof(UriBuilder))
{
if (maxStringLength.HasValue)
{
return("varchar(" + maxStringLength.Value + ")");
}
return("varchar");
}
else if (clrType == typeof(TimeSpan))
{
return("bigint");
}
else if (clrType == typeof(DateTime))
{
return(storeDateTimeAsTicks ? "bigint" : "datetime");
}
else if (clrType == typeof(DateTimeOffset))
{
return("bigint");
}
else if (clrType == typeof(byte[]))
{
return("blob");
}
else if (clrType == typeof(Guid))
{
return("varchar(36)");
}
else
{
var enumInfo = EnumCache.GetInfo(clrType);
if (enumInfo.IsEnum)
{
if (enumInfo.StoreAsText)
{
return("varchar");
}
else
{
return("integer");
}
}
throw new NotSupportedException("Cannot store type: " + clrType);
}
}
private object ReadCol(Sqlite3Statement stmt, int index, ColType type, Type clrType, object defaultValue)
{
if (type == ColType.Null)
{
return(defaultValue);
}
else
{
var clrTypeInfo = clrType.GetTypeInfo();
if (clrType == typeof(String))
{
return(SQLite3.ColumnStringUTF8(stmt, index));
}
else if (clrType == typeof(Int32))
{
return((int)SQLite3.ColumnInt32(stmt, index));
}
else if (clrType == typeof(Boolean))
{
return(SQLite3.ColumnInt32(stmt, index) == 1);
}
else if (clrType == typeof(double))
{
return(SQLite3.ColumnDouble(stmt, index));
}
else if (clrType == typeof(float))
{
return((float)SQLite3.ColumnDouble(stmt, index));
}
else if (clrType == typeof(TimeSpan))
{
return(new TimeSpan(SQLite3.ColumnInt64(stmt, index)));
}
else if (clrType == typeof(DateTime))
{
if (Connection.StoreDateTimeAsTicks)
{
return(new DateTime(SQLite3.ColumnInt64(stmt, index)));
}
else
{
var text = SQLite3.ColumnStringUTF8(stmt, index);
DateTime resultDate;
if (!DateTime.TryParseExact(text, DateTimeExactStoreFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out resultDate))
{
resultDate = DateTime.Parse(text);
}
return(resultDate);
}
}
else if (clrType == typeof(DateTimeOffset))
{
var value = SQLite3.ColumnInt64(stmt, index);
// if the date goes outside the bounds of a valid date time, we need to consider the date as corrupted by
// either the disk or some other process, and in that case the date cannot be trusted. With this in mind
// we are going to use the default value if the date has been corrupted.
if (value < DateTimeOffset.MinValue.Ticks || value > DateTimeOffset.MaxValue.Ticks)
{
return(defaultValue);
}
return(new DateTimeOffset(value, TimeSpan.Zero));
}
else if (clrTypeInfo.IsEnum)
{
if (type == ColType.Text)
{
var value = SQLite3.ColumnStringUTF8(stmt, index);
return(Enum.Parse(clrType, value, true));
}
else
{
var value = SQLite3.ColumnInt32(stmt, index);
var enumCache = EnumCache.GetInfo(clrType);
return(enumCache.GetEnumFromInt32Value(value));
}
}
else if (clrType == typeof(Int64))
{
return(SQLite3.ColumnInt64(stmt, index));
}
else if (clrType == typeof(UInt32))
{
return((uint)SQLite3.ColumnInt64(stmt, index));
}
else if (clrType == typeof(decimal))
{
return((decimal)SQLite3.ColumnDouble(stmt, index));
}
else if (clrType == typeof(Byte))
{
return((byte)SQLite3.ColumnInt32(stmt, index));
}
else if (clrType == typeof(UInt16))
{
return((ushort)SQLite3.ColumnInt32(stmt, index));
}
else if (clrType == typeof(Int16))
{
return((short)SQLite3.ColumnInt32(stmt, index));
}
else if (clrType == typeof(sbyte))
{
return((sbyte)SQLite3.ColumnInt32(stmt, index));
}
else if (clrType == typeof(byte[]))
{
return(SQLite3.ColumnByteArray(stmt, index));
}
else if (clrType == typeof(Guid))
{
var text = SQLite3.ColumnStringUTF8(stmt, index);
return(new Guid(text));
}
else if (clrType == typeof(Uri))
{
var text = SQLite3.ColumnStringUTF8(stmt, index);
return(new Uri(text));
}
else if (clrType == typeof(StringBuilder))
{
var text = SQLite3.ColumnStringUTF8(stmt, index);
return(new StringBuilder(text));
}
else if (clrType == typeof(UriBuilder))
{
var text = SQLite3.ColumnStringUTF8(stmt, index);
return(new UriBuilder(text));
}
else
{
throw new NotSupportedException("Don't know how to read " + clrType);
}
}
}
private static void BindParameter(Sqlite3Statement stmt, int index, object value, bool storeDateTimeAsTicks)
{
if (value == null)
{
SQLite3.BindNull(stmt, index);
}
else
{
if (value is Int32)
{
SQLite3.BindInt32(stmt, index, (int)value);
}
else if (value is String)
{
SQLite3.BindStringUTF8(stmt, index, (string)value, -1, NegativePointer);
}
else if (value is Byte || value is UInt16 || value is SByte || value is Int16)
{
SQLite3.BindInt32(stmt, index, Convert.ToInt32(value));
}
else if (value is Boolean)
{
SQLite3.BindInt32(stmt, index, (bool)value ? 1 : 0);
}
else if (value is UInt32 || value is Int64)
{
SQLite3.BindInt64(stmt, index, Convert.ToInt64(value));
}
else if (value is Single || value is Double || value is Decimal)
{
SQLite3.BindDouble(stmt, index, Convert.ToDouble(value));
}
else if (value is TimeSpan)
{
SQLite3.BindInt64(stmt, index, ((TimeSpan)value).Ticks);
}
else if (value is DateTime)
{
if (storeDateTimeAsTicks)
{
SQLite3.BindInt64(stmt, index, ((DateTime)value).Ticks);
}
else
{
SQLite3.BindStringUTF8(stmt, index, ((DateTime)value).ToString(DateTimeExactStoreFormat, System.Globalization.CultureInfo.InvariantCulture), -1, NegativePointer);
}
}
else if (value is DateTimeOffset)
{
SQLite3.BindInt64(stmt, index, ((DateTimeOffset)value).UtcTicks);
}
else if (value is byte[])
{
SQLite3.BindBlob(stmt, index, (byte[])value, ((byte[])value).Length, NegativePointer);
}
else if (value is Guid)
{
SQLite3.BindStringUTF8(stmt, index, ((Guid)value).ToString(), 72, NegativePointer);
}
else if (value is Uri)
{
SQLite3.BindStringUTF8(stmt, index, ((Uri)value).ToString(), -1, NegativePointer);
}
else if (value is StringBuilder)
{
SQLite3.BindStringUTF8(stmt, index, ((StringBuilder)value).ToString(), -1, NegativePointer);
}
else if (value is UriBuilder)
{
SQLite3.BindStringUTF8(stmt, index, ((UriBuilder)value).ToString(), -1, NegativePointer);
}
else
{
// Now we could possibly get an enum, retrieve cached info
var valueType = value.GetType();
var enumInfo = EnumCache.GetInfo(valueType);
if (enumInfo.IsEnum)
{
var enumIntValue = Convert.ToInt32(value);
if (enumInfo.StoreAsText)
{
SQLite3.BindStringUTF8(stmt, index, enumInfo.GetEnumFromInt32Value(enumIntValue).ToString(), -1, NegativePointer);
}
else
{
SQLite3.BindInt32(stmt, index, enumIntValue);
}
}
else
{
var type = value.GetType();
throw new NotSupportedException("Cannot store type: " + type);
}
}
}
}
