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 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); }
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); }
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()); }
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 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 int GetRowCount(TableFilter filter) { int count = -1; var sql = new StringBuilder(); sql.Append("SELECT COUNT(*) FROM "); sql.Append(this.QuotedName); if (filter != null && filter.IsRowFiltered) { sql.Append(" WHERE "); filter.WriteWhereStatement(sql); } using (var command = this.Connection.CreateCommand()) { command.CommandText = sql.ToString(); using (this.Database.CreateConnectionScope()) { count = Convert.ToInt32(command.ExecuteScalar()); } } return(count); }
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()); }