internal KeyQuery(SQLiteConnection cnn, string database, string table, params string[] columns)
{
using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
{
_command = cnn.CreateCommand();
for (int n = 0; n < columns.Length; n++)
{
columns[n] = builder.QuoteIdentifier(columns[n]);
}
}
_command.CommandText = String.Format(CultureInfo.InvariantCulture, "SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", String.Join(",", columns), database, table);
_command.Parameters.AddWithValue(null, (long)0);
}
internal KeyQuery(SQLiteConnection cnn, string database, string table, params string[] columns)
{
using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
{
_command = cnn.CreateCommand();
for (int n = 0; n < columns.Length; n++)
{
columns[n] = builder.QuoteIdentifier(columns[n]);
}
}
_command.CommandText = UnsafeNativeMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", String.Join(",", columns), database, table);
_command.Parameters.AddWithValue(null, (long)0);
}
internal KeyQuery(SQLiteConnection cnn, string database, string table, params string[] columns)
{
using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
{
this._command = cnn.CreateCommand();
for (int i = 0; i < columns.Length; i++)
{
columns[i] = builder.QuoteIdentifier(columns[i]);
}
}
this._command.CommandText = string.Format("SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", string.Join(",", columns), database, table);
this._command.Parameters.AddWithValue(null, 0L);
}
/// <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;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(dbtypeName);
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>
/// 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;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(dbtypeName);
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);
}
}
}
