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()); }