internal bool Contains(string tableName, WhereStatement where)
{
// Build the SQL query
List <SQLiteParameter> parameters;
string sql = String.Format("SELECT EXISTS(SELECT 1 FROM {0} WHERE {1} LIMIT 1);",
Context.QuoteIdentifier(tableName),
where.BuildStatement(out parameters)
);
// Execute the command
using (SQLiteCommand command = Context.CreateCommand(sql))
{
command.Parameters.AddRange(parameters.ToArray());
return(Context.ExecuteScalar <int>(command) == 1);
}
}
/// <summary>
/// Creates an index with the specified name on the specified table
/// </summary>
/// <param name="name"></param>
/// <param name="table"></param>
/// <param name="cols"></param>
/// <param name="options"></param>
/// <param name="where"></param>
public void CreateIndex(string name, string table, IndexedColumn[] cols, IndexCreationOptions options, WhereStatement where = null)
{
// -----------------------------------------
// Begin the SQL generation
// -----------------------------------------
StringBuilder sql = new StringBuilder("CREATE ", 256);
sql.AppendIf(options.HasFlag(IndexCreationOptions.Unique), "UNIQUE ");
sql.Append("INDEX ");
sql.AppendIf(options.HasFlag(IndexCreationOptions.IfNotExists), "IF NOT EXISTS ");
// Append index name
sql.Append($"{name} ON ");
sql.Append(QuoteIdentifier(table, this.IdentifierQuoteMode, this.IdentifierQuoteKind));
sql.Append("(");
// Append columns
int i = cols.Length;
foreach (var col in cols)
{
--i;
sql.Append(QuoteIdentifier(col.Name, this.IdentifierQuoteMode, this.IdentifierQuoteKind));
sql.AppendIf(col.Collate != Collation.Default, $" COLLATE {col.Collate.ToString().ToUpperInvariant()}");
sql.AppendIf(col.SortOrder == Sorting.Descending, " DESC");
sql.AppendIf(i > 0, ", ");
}
// Close
sql.Append(")");
// Add where if we have one
if (where != null)
{
sql.Append(" WHERE ");
sql.Append(where.BuildStatement());
}
// -----------------------------------------
// Execute the command on the database
// -----------------------------------------
using (SQLiteCommand command = CreateCommand(sql.ToString()))
{
command.ExecuteNonQuery();
}
}
