private string GetSelectStatement(int startIndex, int count, TableFilter filter, TableSort sort)
{
// SELECT TOP {count} * FROM
// (SELECT TOP {?} * FROM {table} ORDER BY {pk} ASC)
// ORDER BY {pk} DESC
int total = this.GetRowCount(filter);
StringBuilder select = new StringBuilder();
if (total <= count)
{
select.Append("SELECT ");
filter.WriteColumnsProjection(select);
select.Append(" FROM ");
select.Append(this.QuotedName);
if (filter.IsRowFiltered)
{
select.Append(" WHERE (");
filter.WriteWhereStatement(select);
select.Append(")");
}
if (sort != null && sort.IsSorted)
{
select.Append(" ORDER BY ");
sort.WriteOrderBy(select);
}
}
else
{
select.Append("SELECT TOP ");
select.Append(count);
select.Append(" * FROM (SELECT TOP ");
select.Append(total - startIndex);
select.Append(" ");
filter.WriteColumnsProjection(select);
select.Append(" FROM ");
select.Append(this.QuotedName);
if (filter.IsRowFiltered)
{
select.Append(" WHERE (");
filter.WriteWhereStatement(select);
select.Append(")");
}
select.Append(" ORDER BY ");
select.Append(Columns[0].QuotedName);
select.Append(" DESC) ORDER BY ");
select.Append(sort);
select.Append(" ASC");
}
return(select.ToString());
}
private void viewSqlToolStripButton_Click(object sender, EventArgs e)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
filter.WriteColumnsProjection(sql);
sql.AppendLine();
sql.Append("FROM ");
sql.Append(table.QuotedName);
if (filter.IsRowFiltered)
{
sql.AppendLine();
sql.Append("WHERE ");
filter.WriteWhereStatement(sql);
}
if (IsSorted)
{
sql.AppendLine();
sql.Append("ORDER BY ");
sort.WriteOrderBy(sql);
}
CommandDocument doc = new CommandDocument(table.Database);
doc.Text = String.Format(Resources.QueryDocumentTitle, table.Database.Name);
doc.CommandText = sql.ToString();
doc.Show(this.DockPanel);
}
public override DataTable GetData(int startIndex, int count, TableFilter filter, TableSort sort)
{
var sql = new StringBuilder();
sql.Append("SELECT ");
filter.WriteColumnsProjection(sql);
sql.Append(" FROM ");
sql.Append(QuotedName);
if (filter != null && filter.IsRowFiltered)
{
sql.Append(" WHERE ");
filter.WriteWhereStatement(sql);
}
if (sort != null && sort.IsSorted)
{
sql.Append(" ORDER BY ");
sort.WriteOrderBy(sql);
}
sql.Append($" ROWS {startIndex + 1} TO {startIndex + count}");
var data = new DataTable(Name);
using (var connectionScope = Database.CreateConnectionScope())
using (var select = Connection.CreateCommand()) {
select.CommandText = sql.ToString();
using (var reader = select.ExecuteReader()) {
data.Load(reader);
}
}
return(data);
}
public override DataTable GetData(int startIndex, int count, TableFilter filter, TableSort sort)
{
var data = new DataTable(this.Name);
var text = new StringBuilder();
text.Append("SELECT ");
filter.WriteColumnsProjection(text);
text.Append(" FROM ");
text.Append(this.QuotedName);
if (filter.IsRowFiltered)
{
text.Append(" WHERE ");
filter.WriteWhereStatement(text);
}
if (sort != null && sort.IsSorted)
{
text.Append(" ORDER BY ");
sort.WriteOrderBy(text);
}
text.AppendFormat(" LIMIT {0}, {1}", startIndex, count);
using (var select = this.Connection.CreateCommand()) {
select.CommandText = text.ToString();
using (this.Database.CreateConnectionScope()) {
using (var adapter = (MySqlDataAdapter)this.Database.CreateAdapter(this, filter)) {
adapter.SelectCommand = select;
adapter.Fill(data);
}
}
}
return(data);
}
private string GetSelectTopStatement(int count, TableFilter filter, TableSort sort)
{
var sql = new StringBuilder();
sql.Append($"SELECT TOP {count} ");
filter.WriteColumnsProjection(sql);
sql.Append($" FROM {QuotedName} ");
if (count > 0)
{
if (filter != null && filter.IsRowFiltered)
{
sql.Append(" WHERE ");
filter.WriteWhereStatement(sql);
}
sql.Append(" ORDER BY ");
if (sort != null && sort.IsSorted)
{
sort.WriteOrderBy(sql);
}
else
{
WriteDefaultOrderBy(sql);
}
}
return(sql.ToString());
}
private string GetSelectOffsetFetchStatement(int startIndex, int count, TableFilter filter, TableSort sort)
{
var sql = new StringBuilder();
sql.Append("SELECT ");
filter.WriteColumnsProjection(sql);
sql.Append(" FROM ");
sql.Append(QuotedName);
if (filter.IsRowFiltered)
{
sql.Append(" WHERE ");
filter.WriteWhereStatement(sql);
}
sql.Append(" ORDER BY ");
if (sort != null && sort.IsSorted)
{
sort.WriteOrderBy(sql);
}
else
{
WriteDefaultOrderBy(sql);
}
sql.Append($" OFFSET {startIndex} ROWS FETCH NEXT {count} ROWS ONLY");
return(sql.ToString());
}
public override string GenerateAlterStatement()
{
var statement = new StringBuilder();
statement.AppendLine("BEGIN;");
if (IsView)
{
statement.Append("DROP VIEW ");
statement.Append(QuotedName);
statement.AppendLine(";");
statement.Append(GenerateCreateStatement());
statement.AppendLine(";");
}
else
{
statement.Append("ALTER TABLE ");
statement.Append(QuotedName);
statement.Append(" RENAME TO ");
statement.AppendLine("[" + Name + "_AlterTableTemp];");
statement.Append(GenerateCreateStatement());
statement.AppendLine(";");
statement.AppendLine();
statement.Append("INSERT INTO ");
statement.AppendLine(QuotedName);
statement.Append(" (");
var filter = new TableFilter(this);
filter.WriteColumnsProjection(statement);
statement.AppendLine(")");
statement.Append("SELECT ");
filter.WriteColumnsProjection(statement);
statement.AppendLine();
statement.AppendLine("FROM [" + Name + "_AlterTableTemp];");
statement.AppendLine();
statement.AppendLine("DROP TABLE [" + Name + "_AlterTableTemp];");
}
if (Triggers.Count > 0)
{
statement.AppendLine("/* RESTORE TRIGGERS */");
foreach (var trigger in Triggers)
{
statement.Append(trigger.GenerateCreateStatement());
statement.AppendLine(";");
}
}
statement.AppendLine("END;");
return(statement.ToString());
}
protected virtual DataTable GetDataSecuencial(int startIndex, int count, TableFilter filter, TableSort sort)
{
var sql = new StringBuilder();
sql.Append("SELECT ");
filter.WriteColumnsProjection(sql);
sql.Append(" FROM ");
sql.Append(QuotedName);
if (filter != null && filter.IsRowFiltered)
{
sql.Append(" WHERE ");
filter.WriteWhereStatement(sql);
}
if (sort != null && sort.IsSorted)
{
sql.Append(" ORDER BY ");
sort.WriteOrderBy(sql);
}
using (var select = Connection.CreateCommand()) {
select.CommandText = sql.ToString();
using (Database.CreateConnectionScope()) {
using (var reader = select.ExecuteReader(CommandBehavior.SequentialAccess)) {
var data = new DataTable(Name);
data.BeginLoadData();
for (var i = 0; i < reader.FieldCount; i++)
{
data.Columns.Add(new DataColumn(reader.GetName(i), reader.GetFieldType(i)));
}
while (startIndex > 0 && reader.Read())
{
startIndex--;
}
if (reader.Read())
{
do
{
var row = data.NewRow();
for (var i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i];
}
data.Rows.Add(row);
count--;
} while (reader.Read() && count > 0);
}
data.EndLoadData();
data.AcceptChanges();
return(data);
}
}
}
}
private string GetPagingWithKeyStatement(int startIndex, int count, TableFilter filter, string[] primaryKeyColumns)
{
var total = GetRowCount(filter);
var select = new StringBuilder();
if (startIndex == 0 || count == 0 || total <= count)
{
select.AppendFormat("SELECT TOP {0} ", count);
filter.WriteColumnsProjection(select);
select.Append(" FROM ");
select.Append(QuotedName);
if (filter.IsRowFiltered)
{
select.Append(" WHERE (");
filter.WriteWhereStatement(select);
select.Append(")");
}
}
else
{
// SELECT TOP {count} * FROM
// (SELECT TOP {total - startIndex} * FROM {table} ORDER BY {pk} DESC)
// ORDER BY {pk} ASC
select.Append("SELECT TOP ");
select.Append(count);
select.Append(" * FROM (SELECT TOP ");
select.Append(Math.Max(total - startIndex, 0));
select.Append(" ");
filter.WriteColumnsProjection(select);
select.Append(" FROM ");
select.Append(QuotedName);
if (filter.IsRowFiltered)
{
select.Append(" WHERE (");
filter.WriteWhereStatement(select);
select.Append(")");
}
select.Append(" ORDER BY ");
select.Append(string.Join(", ", primaryKeyColumns.Select(i => i + " DESC").ToArray()));
select.Append(")");
}
select.Append(" ORDER BY ");
select.Append(string.Join(", ", primaryKeyColumns.Select(i => i + " ASC").ToArray()));
return(select.ToString());
}
public override DbDataAdapter CreateAdapter(Table table, TableFilter filter)
{
if (!(table is SQLiteTable))
{
throw new ArgumentException("Table must be of type SQLiteTable", "table");
}
var adapter = new SQLiteDataAdapter(table.GetBaseSelectCommandText(filter), Connection);
if (!table.IsReadOnly)
{
var builder = new SQLiteCommandBuilder(adapter)
{
ConflictOption = ConflictOption.OverwriteChanges
};
SQLiteCommand updateCommand = null;
try {
updateCommand = builder.GetUpdateCommand();
} catch (InvalidOperationException) {
var selectSql = new StringBuilder();
selectSql.Append("SELECT RowId, ");
filter.WriteColumnsProjection(selectSql);
selectSql.Append(" FROM ");
selectSql.Append(table.QuotedName);
adapter = new SQLiteDataAdapter(selectSql.ToString(), Connection);
builder = new SQLiteCommandBuilder(adapter);
updateCommand = builder.GetUpdateCommand();
}
var insertCommand = builder.GetInsertCommand();
foreach (var column in table.Columns)
{
if (column.IsIdentity)
{
insertCommand.CommandText = insertCommand.CommandText + "; SELECT @RowId = last_insert_rowid()";
insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
var parameter = new SQLiteParameter {
ParameterName = "@RowId",
Direction = ParameterDirection.Output,
SourceColumn = column.Name,
SourceVersion = DataRowVersion.Current,
Value = DBNull.Value
};
insertCommand.Parameters.Add(parameter);
break;
}
}
adapter.UpdateCommand = updateCommand;
adapter.InsertCommand = insertCommand;
adapter.DeleteCommand = builder.GetDeleteCommand();
}
return(adapter);
}
public override DataTable GetData(int startIndex, int count, TableFilter filter, TableSort sort)
{
var sql = new StringBuilder();
sql.Append("SELECT ");
if (!IsView && !HasPrimaryKey)
{
sql.Append("RowId, ");
}
if (filter == null)
{
sql.Append('*');
}
else
{
filter.WriteColumnsProjection(sql);
}
sql.Append(" FROM ");
sql.Append(QuotedName);
if (filter != null && filter.IsRowFiltered)
{
sql.Append(" WHERE ");
filter.WriteWhereStatement(sql);
}
if (sort != null && sort.IsSorted)
{
sql.Append(" ORDER BY ");
sort.WriteOrderBy(sql);
}
sql.AppendFormat(" LIMIT {0}, {1}", startIndex, count);
var data = new DataTable(Name);
using (var select = Connection.CreateCommand()) {
select.CommandText = sql.ToString();
using (Database.CreateConnectionScope()) {
using (var adapter = (SQLiteDataAdapter)Database.CreateAdapter(this, filter)) {
adapter.SelectCommand = select;
adapter.Fill(data);
}
}
}
return(data);
}
public override string GetBaseSelectCommandText(TableFilter filter, bool excludeWhere)
{
var select = new StringBuilder();
select.Append("SELECT ");
filter.WriteColumnsProjection(select);
select.Append(" FROM ");
select.Append(QuotedName);
if (filter != null && filter.IsRowFiltered && !excludeWhere)
{
select.Append(" WHERE ");
filter.WriteWhereStatement(select);
}
return(select.ToString());
}
private string GetSelectStatement(int startIndex, int count, TableFilter filter, TableSort sort)
{
var sql = new StringBuilder();
sql.Append("SELECT ");
if (startIndex == 0 || count == 0)
{
sql.AppendFormat("TOP {0} ", count);
}
filter.WriteColumnsProjection(sql);
sql.Append(" FROM ");
sql.Append(QuotedName);
if (filter.IsRowFiltered)
{
sql.Append(" WHERE ");
filter.WriteWhereStatement(sql);
}
sql.Append(" ORDER BY ");
if (sort != null && sort.IsSorted)
{
sort.WriteOrderBy(sql);
}
else
{
var columns = new List <string>();
foreach (var c in Columns)
{
if (c.InPrimaryKey)
{
columns.Add(c.QuotedName);
}
}
if (columns.Count == 0)
{
columns.Add(Columns[0].QuotedName);
}
sql.Append(string.Join(", ", columns.ToArray()));
}
if (startIndex > 0 && count > 0)
{
sql.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", startIndex, count);
}
return(sql.ToString());
}
private string GetSelectStatement(int startIndex, int count, TableFilter filter, TableSort sort)
{
var select = new StringBuilder();
select.Append("WITH Ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY ");
if (sort != null && sort.IsSorted)
{
sort.WriteOrderBy(select);
}
else
{
// TODO: Project Primary Columns
select.Append(Columns[0].QuotedName);
}
select.Append(") AS [Row_Number()], * ");
select.Append(" FROM [");
select.Append(this.schema);
select.Append("].[");
select.Append(Name);
select.Append(']');
if (filter.IsRowFiltered)
{
select.Append(" WHERE (");
filter.WriteWhereStatement(select);
select.Append(")");
}
select.Append(") SELECT ");
filter.WriteColumnsProjection(select);
select.Append(" FROM Ordered WHERE (([Row_Number()] BETWEEN ");
select.Append(startIndex + 1);
select.Append(" AND ");
select.Append(startIndex + count);
select.Append(")");
select.Append(")");
return(select.ToString());
}
public override string GetBaseSelectCommandText(TableFilter filter)
{
var select = new StringBuilder();
select.Append("SELECT ");
if (!this.IsView && !this.HasPrimaryKey)
{
select.Append("RowId, ");
}
filter.WriteColumnsProjection(select);
select.Append(" FROM ");
select.Append(this.QuotedName);
if (filter.IsRowFiltered)
{
select.Append(" WHERE ");
filter.WriteWhereStatement(select);
}
return(select.ToString());
}
private string GetSelectRowNumberStatement(int startIndex, int count, TableFilter filter, TableSort sort)
{
var sql = new StringBuilder();
sql.Append("WITH Ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY ");
if (sort != null && sort.IsSorted)
{
sort.WriteOrderBy(sql);
}
else
{
WriteDefaultOrderBy(sql);
}
sql.Append(") AS [Row_Number()], * ");
sql.Append(" FROM ");
sql.Append(QuotedName);
if (filter.IsRowFiltered)
{
sql.Append(" WHERE (");
filter.WriteWhereStatement(sql);
sql.Append(")");
}
sql.Append(") SELECT ");
filter.WriteColumnsProjection(sql);
sql.Append(" FROM Ordered WHERE (([Row_Number()] BETWEEN ");
sql.Append(startIndex + 1);
sql.Append(" AND ");
sql.Append(startIndex + count);
sql.Append(")");
sql.Append(")");
return(sql.ToString());
}
