/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
SQLiteConnectionFlags flags = cnn.Flags;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(dbtypeName, flags);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
/// <summary>
/// Perform the bind operation for an individual parameter
/// </summary>
/// <param name="index">The index of the parameter to bind</param>
/// <param name="param">The parameter we're binding</param>
private void BindParameter(int index, SQLiteParameter param)
{
if (param == null)
{
throw new SQLiteException("Insufficient parameters supplied to the command");
}
if (HelperMethods.HasFlags(
_flags, SQLiteConnectionFlags.UseConnectionBindValueCallbacks))
{
bool complete;
InvokeBindValueCallback(index, param, out complete);
if (complete)
{
return;
}
}
object obj = param.Value;
DbType objType = param.DbType;
if ((obj != null) && (objType == DbType.Object))
{
objType = SQLiteConvert.TypeToDbType(obj.GetType());
}
if (SQLite3.ForceLogPrepare() || HelperMethods.LogPreBind(_flags))
{
IntPtr handle = _sqlite_stmt;
SQLiteLog.LogMessage(HelperMethods.StringFormat(
CultureInfo.CurrentCulture,
"Binding statement {0} paramter #{1} with database type {2} and raw value {{{3}}}...",
handle, index, objType, obj));
}
if ((obj == null) || Convert.IsDBNull(obj))
{
_sql.Bind_Null(this, _flags, index);
return;
}
CultureInfo invariantCultureInfo = CultureInfo.InvariantCulture;
bool invariantText = HelperMethods.HasFlags(
_flags, SQLiteConnectionFlags.BindInvariantText);
CultureInfo cultureInfo = CultureInfo.CurrentCulture;
if (HelperMethods.HasFlags(
_flags, SQLiteConnectionFlags.ConvertInvariantText))
{
cultureInfo = invariantCultureInfo;
}
if (HelperMethods.HasFlags(
_flags, SQLiteConnectionFlags.BindAllAsText))
{
if (obj is DateTime)
{
_sql.Bind_DateTime(this, _flags, index, (DateTime)obj);
}
else
{
_sql.Bind_Text(this, _flags, index, invariantText ?
SQLiteConvert.ToStringWithProvider(obj, invariantCultureInfo) :
SQLiteConvert.ToStringWithProvider(obj, cultureInfo));
}
return;
}
bool invariantDecimal = HelperMethods.HasFlags(
_flags, SQLiteConnectionFlags.BindInvariantDecimal);
if (HelperMethods.HasFlags(
_flags, SQLiteConnectionFlags.BindDecimalAsText))
{
if (obj is Decimal)
{
_sql.Bind_Text(this, _flags, index, invariantText || invariantDecimal ?
SQLiteConvert.ToStringWithProvider(obj, invariantCultureInfo) :
SQLiteConvert.ToStringWithProvider(obj, cultureInfo));
return;
}
}
switch (objType)
{
case DbType.Date:
case DbType.Time:
case DbType.DateTime:
//
// NOTE: The old method (commented below) does not honor the selected date format
// for the connection.
// _sql.Bind_DateTime(this, index, Convert.ToDateTime(obj, cultureInfo));
_sql.Bind_DateTime(this, _flags, index, (obj is string) ?
_sql.ToDateTime((string)obj) : Convert.ToDateTime(obj, cultureInfo));
break;
case DbType.Boolean:
_sql.Bind_Boolean(this, _flags, index, SQLiteConvert.ToBoolean(obj, cultureInfo, true));
break;
case DbType.SByte:
_sql.Bind_Int32(this, _flags, index, Convert.ToSByte(obj, cultureInfo));
break;
case DbType.Int16:
_sql.Bind_Int32(this, _flags, index, Convert.ToInt16(obj, cultureInfo));
break;
case DbType.Int32:
_sql.Bind_Int32(this, _flags, index, Convert.ToInt32(obj, cultureInfo));
break;
case DbType.Int64:
_sql.Bind_Int64(this, _flags, index, Convert.ToInt64(obj, cultureInfo));
break;
case DbType.Byte:
_sql.Bind_UInt32(this, _flags, index, Convert.ToByte(obj, cultureInfo));
break;
case DbType.UInt16:
_sql.Bind_UInt32(this, _flags, index, Convert.ToUInt16(obj, cultureInfo));
break;
case DbType.UInt32:
_sql.Bind_UInt32(this, _flags, index, Convert.ToUInt32(obj, cultureInfo));
break;
case DbType.UInt64:
_sql.Bind_UInt64(this, _flags, index, Convert.ToUInt64(obj, cultureInfo));
break;
case DbType.Single:
case DbType.Double:
case DbType.Currency:
_sql.Bind_Double(this, _flags, index, Convert.ToDouble(obj, cultureInfo));
break;
case DbType.Binary:
_sql.Bind_Blob(this, _flags, index, (byte[])obj);
break;
case DbType.Guid:
if (_command.Connection._binaryGuid == true)
{
_sql.Bind_Blob(this, _flags, index, ((Guid)obj).ToByteArray());
}
else
{
_sql.Bind_Text(this, _flags, index, invariantText ?
SQLiteConvert.ToStringWithProvider(obj, invariantCultureInfo) :
SQLiteConvert.ToStringWithProvider(obj, cultureInfo));
}
break;
case DbType.Decimal: // Dont store decimal as double ... loses precision
_sql.Bind_Text(this, _flags, index, invariantText || invariantDecimal ?
SQLiteConvert.ToStringWithProvider(Convert.ToDecimal(obj, cultureInfo), invariantCultureInfo) :
SQLiteConvert.ToStringWithProvider(Convert.ToDecimal(obj, cultureInfo), cultureInfo));
break;
default:
_sql.Bind_Text(this, _flags, index, invariantText ?
SQLiteConvert.ToStringWithProvider(obj, invariantCultureInfo) :
SQLiteConvert.ToStringWithProvider(obj, cultureInfo));
break;
}
}
/// <summary>
/// Perform the bind operation for an individual parameter
/// </summary>
/// <param name="index">The index of the parameter to bind</param>
/// <param name="param">The parameter we're binding</param>
private void BindParameter(int index, SQLiteParameter param)
{
if (param == null)
{
throw new SQLiteException((int)SQLiteErrorCode.Error, "Insufficient parameters supplied to the command");
}
object obj = param.Value;
DbType objType = param.DbType;
if (Convert.IsDBNull(obj) || obj == null)
{
_sql.Bind_Null(this, index);
return;
}
if (objType == DbType.Object)
{
objType = SQLiteConvert.TypeToDbType(obj.GetType());
}
switch (objType)
{
case DbType.Date:
case DbType.Time:
case DbType.DateTime:
_sql.Bind_DateTime(this, index, Convert.ToDateTime(obj, CultureInfo.CurrentCulture));
break;
case DbType.Int64:
case DbType.UInt64:
_sql.Bind_Int64(this, index, Convert.ToInt64(obj, CultureInfo.CurrentCulture));
break;
case DbType.Boolean:
case DbType.Int16:
case DbType.Int32:
case DbType.UInt16:
case DbType.UInt32:
case DbType.SByte:
case DbType.Byte:
_sql.Bind_Int32(this, index, Convert.ToInt32(obj, CultureInfo.CurrentCulture));
break;
case DbType.Single:
case DbType.Double:
case DbType.Currency:
case DbType.Decimal:
_sql.Bind_Double(this, index, Convert.ToDouble(obj, CultureInfo.CurrentCulture));
break;
case DbType.Binary:
_sql.Bind_Blob(this, index, (byte[])obj);
break;
case DbType.Guid:
_sql.Bind_Blob(this, index, ((Guid)obj).ToByteArray());
break;
default:
_sql.Bind_Text(this, index, obj.ToString());
break;
}
}
/// <summary>
/// Perform the bind operation for an individual parameter
/// </summary>
/// <param name="index">The index of the parameter to bind</param>
/// <param name="param">The parameter we're binding</param>
private void BindParameter(int index, SQLiteParameter param)
{
if (param == null)
{
throw new SQLiteException("Insufficient parameters supplied to the command");
}
object obj = param.Value;
DbType objType = param.DbType;
if ((obj != null) && (objType == DbType.Object))
{
objType = SQLiteConvert.TypeToDbType(obj.GetType());
}
if ((_flags & SQLiteConnectionFlags.LogPreBind) == SQLiteConnectionFlags.LogPreBind)
{
IntPtr handle = _sqlite_stmt;
SQLiteLog.LogMessage(String.Format(
"Binding statement {0} paramter #{1} with database type {2} and raw value {{{3}}}...",
handle, index, objType, obj));
}
if ((obj == null) || Convert.IsDBNull(obj))
{
_sql.Bind_Null(this, _flags, index);
return;
}
if ((_flags & SQLiteConnectionFlags.BindAllAsText) == SQLiteConnectionFlags.BindAllAsText)
{
if (obj is DateTime)
{
_sql.Bind_DateTime(this, _flags, index, (DateTime)obj);
}
else
{
_sql.Bind_Text(this, _flags, index, obj.ToString());
}
return;
}
switch (objType)
{
case DbType.Date:
case DbType.Time:
case DbType.DateTime:
//
// NOTE: The old method (commented below) does not honor the selected date format
// for the connection.
// _sql.Bind_DateTime(this, index, Convert.ToDateTime(obj, CultureInfo.CurrentCulture));
_sql.Bind_DateTime(this, _flags, index, (obj is string) ?
_sql.ToDateTime((string)obj) : Convert.ToDateTime(obj, CultureInfo.CurrentCulture));
break;
case DbType.Boolean:
_sql.Bind_Int32(this, _flags, index, ToBoolean(obj, CultureInfo.CurrentCulture) ? 1 : 0);
break;
case DbType.SByte:
_sql.Bind_Int32(this, _flags, index, Convert.ToSByte(obj, CultureInfo.CurrentCulture));
break;
case DbType.Int16:
_sql.Bind_Int32(this, _flags, index, Convert.ToInt16(obj, CultureInfo.CurrentCulture));
break;
case DbType.Int32:
_sql.Bind_Int32(this, _flags, index, Convert.ToInt32(obj, CultureInfo.CurrentCulture));
break;
case DbType.Int64:
_sql.Bind_Int64(this, _flags, index, Convert.ToInt64(obj, CultureInfo.CurrentCulture));
break;
case DbType.Byte:
_sql.Bind_UInt32(this, _flags, index, Convert.ToByte(obj, CultureInfo.CurrentCulture));
break;
case DbType.UInt16:
_sql.Bind_UInt32(this, _flags, index, Convert.ToUInt16(obj, CultureInfo.CurrentCulture));
break;
case DbType.UInt32:
_sql.Bind_UInt32(this, _flags, index, Convert.ToUInt32(obj, CultureInfo.CurrentCulture));
break;
case DbType.UInt64:
_sql.Bind_UInt64(this, _flags, index, Convert.ToUInt64(obj, CultureInfo.CurrentCulture));
break;
case DbType.Single:
case DbType.Double:
case DbType.Currency:
//case DbType.Decimal: // Dont store decimal as double ... loses precision
_sql.Bind_Double(this, _flags, index, Convert.ToDouble(obj, CultureInfo.CurrentCulture));
break;
case DbType.Binary:
_sql.Bind_Blob(this, _flags, index, (byte[])obj);
break;
case DbType.Guid:
if (_command.Connection._binaryGuid == true)
{
_sql.Bind_Blob(this, _flags, index, ((Guid)obj).ToByteArray());
}
else
{
_sql.Bind_Text(this, _flags, index, obj.ToString());
}
break;
case DbType.Decimal: // Dont store decimal as double ... loses precision
_sql.Bind_Text(this, _flags, index, Convert.ToDecimal(obj, CultureInfo.CurrentCulture).ToString(CultureInfo.InvariantCulture));
break;
default:
_sql.Bind_Text(this, _flags, index, obj.ToString());
break;
}
}
private void BindParameter(int index, SQLiteParameter param)
{
if (param == null)
{
throw new SQLiteException(1, "Insufficient parameters supplied to the command");
}
object obj2 = param.Value;
DbType dbType = param.DbType;
if (Convert.IsDBNull(obj2) || (obj2 == null))
{
this._sql.Bind_Null(this, index);
}
else
{
if (dbType == DbType.Object)
{
dbType = SQLiteConvert.TypeToDbType(obj2.GetType());
}
switch (dbType)
{
case DbType.Binary:
this._sql.Bind_Blob(this, index, (byte[])obj2);
return;
case DbType.Byte:
case DbType.Boolean:
case DbType.Int16:
case DbType.Int32:
case DbType.SByte:
case DbType.UInt16:
case DbType.UInt32:
this._sql.Bind_Int32(this, index, Convert.ToInt32(obj2, CultureInfo.CurrentCulture));
return;
case DbType.Currency:
case DbType.Double:
case DbType.Single:
this._sql.Bind_Double(this, index, Convert.ToDouble(obj2, CultureInfo.CurrentCulture));
return;
case DbType.Date:
case DbType.DateTime:
case DbType.Time:
this._sql.Bind_DateTime(this, index, Convert.ToDateTime(obj2, CultureInfo.CurrentCulture));
return;
case DbType.Decimal:
this._sql.Bind_Text(this, index, Convert.ToDecimal(obj2, CultureInfo.CurrentCulture).ToString(CultureInfo.InvariantCulture));
return;
case DbType.Guid:
if (!this._command.Connection._binaryGuid)
{
this._sql.Bind_Text(this, index, obj2.ToString());
return;
}
this._sql.Bind_Blob(this, index, ((Guid)obj2).ToByteArray());
return;
case DbType.Int64:
case DbType.UInt64:
this._sql.Bind_Int64(this, index, Convert.ToInt64(obj2, CultureInfo.CurrentCulture));
return;
}
this._sql.Bind_Text(this, index, obj2.ToString());
}
}
