public WhereForm(SelectStatement selectStatement, WhereColumn whereColumn) { _selectStatement = selectStatement; _whereColumn = whereColumn; InitializeComponent(); columns.ValueMember = "Alias"; columns.DisplayMember = "Alias"; columns.DataSource = _selectStatement.SelectColumns.Where(item => (item.ColumnType & ColumnType.Where) == ColumnType.Where).ToList(); if (whereColumn != null) { columns.Enabled = false; columns.SelectedValue = _selectStatement.SelectColumns.Single(item => item.Alias == whereColumn.ColumnDef.Name); operators.SelectedItem = whereColumn.Operator; SelectColumn selectColumn = (SelectColumn)columns.SelectedItem; if (selectColumn != null) { if (selectColumn.OptionsSelectStatement != null) { options.SelectedValue = whereColumn.Value1; } else { value1.Text = whereColumn.Value1; value2.Text = whereColumn.Value2; } } } }
private void columns_SelectedIndexChanged(object sender, EventArgs e) { SelectColumn selectColumn = (SelectColumn)columns.SelectedItem; if (selectColumn != null) { if (selectColumn.OptionsSelectStatement != null) { value1.Visible = false; options.Visible = true; value2.Enabled = false; operators.DataSource = new[] { Operator.IsEqualTo }; operators.SelectedItem = Operator.IsEqualTo; options.ValueMember = "Id"; options.DisplayMember = "Text"; DataTable dataTable = selectColumn.OptionsSelectStatement.Execute().Tables[0]; options.DataSource = dataTable.Rows.Cast <DataRow>().Select(item => new ComboBoxItem { Id = item[0].ToString(), Text = item.ItemArray.Skip(1).Select(item2 => item2.ToString()).Aggregate((a, b) => a + " - " + b) }).ToList(); } else { operators.DataSource = new List <object>(Enum.GetValues(typeof(Operator)).Cast <object>()); value1.Visible = true; options.Visible = false; value2.Enabled = false; } } }
protected override SqlStatement PrepareStatement(IRequest context) { var tableName = context.Access().ResolveTableName(TableName); if (!context.Access().TableExists(tableName)) { throw new ObjectNotFoundException(tableName); } var queryExp = new SqlQueryExpression(new SelectColumn[] { SelectColumn.Glob("*") }); queryExp.FromClause.AddTable(tableName.FullName); queryExp.WhereExpression = WhereExpression; var queryInfo = new QueryInfo(context, queryExp); if (Limit > 0) { queryInfo.Limit = new QueryLimit(Limit); } var queryPlan = context.Query.Context.QueryPlanner().PlanQuery(queryInfo); return(new Prepared(tableName, queryPlan)); }
IStatement IPreparableStatement.Prepare(IRequest request) { var tableName = request.Query.ResolveTableName(TableName); if (!request.Query.TableExists(tableName)) { throw new ObjectNotFoundException(tableName); } var queryExp = new SqlQueryExpression(new SelectColumn[] { SelectColumn.Glob("*") }); queryExp.FromClause.AddTable(tableName.FullName); queryExp.WhereExpression = WhereExpression; var queryInfo = new QueryInfo(request, queryExp); if (Limit > 0) { queryInfo.Limit = new QueryLimit(Limit); } var queryPlan = request.Query.Context.QueryPlanner().PlanQuery(queryInfo); return(new Prepared(tableName, queryPlan)); }
private bool IsIdentitySelect(TableSelectExpression expression) { if (expression.Columns.Count != 1) { return(false); } if (expression.From == null) { return(false); } if (expression.From.AllTables.Count != 1) { return(false); } SelectColumn column = expression.Columns[0]; if (column.Alias == null) { return(false); } if (column.Alias.Name != "IDENTITY") { return(false); } return(true); }
public string BuildSelect(SelectColumn columns, Filter filter, Sorting sorting, Pagination pagination, out object parameters) { Contract.RequiresNotNull(columns, "columns != null"); Contract.RequiresNotNull(filter, "filter != null"); Contract.RequiresNotNull(sorting, "sorting != null"); Contract.RequiresNotNull(pagination, "paggination != null"); var where = BuildWhere(filter, out parameters); var selectColumns = BuildColumns(columns); var sort = BuildSorting(sorting); var page = BuildPagging(pagination); var tableAlias = Dialect.TableAlias(TableMap.Table); var builder = new StringBuilder($"select {selectColumns} from {tableAlias}"); if (!string.IsNullOrWhiteSpace(@where)) { builder.Append($" where {@where}"); } if (!string.IsNullOrWhiteSpace(sort)) { builder.Append($" order by {sort}"); } if (!string.IsNullOrWhiteSpace(page)) { builder.Append(page); } return(builder.ToString()); }
public string BuildSelect(SelectColumn columns, Filter filter, out object parameters) { Contract.RequiresNotNull(columns, "columns != null"); Contract.RequiresNotNull(filter, "filter != null"); return(BuildSelect(columns, filter, new Sorting(), out parameters)); }
public void RemoveUnusedColumns_SupportQuickSearch_WithCount_Ensure_FieldExpression_NotReplaced() { SelectColumn col1 = new SelectColumn { Expression = new ResourceDataColumn() }; SelectColumn col2 = new SelectColumn { Expression = new ResourceDataColumn() }; StructuredQuery query = new StructuredQuery(); query.SelectColumns.Add(col1); query.SelectColumns.Add(col2); ClientAggregate agg = new ClientAggregate(); agg.AggregatedColumns.Add(new ReportAggregateField { AggregateMethod = AggregateMethod.Count }); StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg, true); Assert.That(result.SelectColumns.Count, Is.EqualTo(2)); Assert.That(result.SelectColumns[0].Expression, Is.TypeOf <ResourceDataColumn>()); Assert.That(result.SelectColumns[1].Expression, Is.TypeOf <ResourceDataColumn>()); Assert.IsTrue(result.SelectColumns[0].IsHidden); Assert.IsTrue(result.SelectColumns[1].IsHidden); }
private static int MakeupFunctions(QuerySelectColumnSet columnSet, IList <Expression> extraAggregateFunctions, out Expression[] defFunList, out string[] defFunNames) { // Make up the functions list, List <SelectColumn> functionsList = columnSet.FunctionColumns; int fsz = functionsList.Count; ArrayList completeFunList = new ArrayList(); for (int i = 0; i < fsz; ++i) { SelectColumn scol = functionsList[i]; completeFunList.Add(scol.Expression); completeFunList.Add(scol.InternalName.Name); } for (int i = 0; i < extraAggregateFunctions.Count; ++i) { completeFunList.Add(extraAggregateFunctions[i]); completeFunList.Add("HAVINGAG_" + (i + 1)); } int fsz2 = completeFunList.Count / 2; defFunList = new Expression[fsz2]; defFunNames = new string[fsz2]; for (int i = 0; i < fsz2; ++i) { defFunList[i] = (Expression)completeFunList[i * 2]; defFunNames[i] = (string)completeFunList[(i * 2) + 1]; } return(fsz); }
private static IQueryPlanNode EvaluateSingle(QuerySelectColumnSet columnSet) { if (columnSet.AggregateCount > 0) { throw new ApplicationException("Invalid use of aggregate function in select with no FROM clause"); } // Make up the lists List <SelectColumn> selectedColumns = columnSet.SelectedColumns; int colCount = selectedColumns.Count; var colNames = new string[colCount]; var expList = new Expression[colCount]; var subsetVars = new ObjectName[colCount]; var aliases1 = new ObjectName[colCount]; for (int i = 0; i < colCount; ++i) { SelectColumn scol = selectedColumns[i]; expList[i] = scol.Expression; colNames[i] = scol.InternalName.Name; subsetVars[i] = scol.InternalName.Clone(); aliases1[i] = scol.Alias.Clone(); } return(new SubsetNode(new CreateFunctionsNode(new SingleRowTableNode(), expList, colNames), subsetVars, aliases1)); }
private void selects_SelectedIndexChanged(object sender, EventArgs e) { if (selects.SelectedItems.Count == 1) { _selectColumn = ((SelectColumnListViewItem)selects.SelectedItems[0]).SelectColumn; } }
public static int DeleteFrom(this IQuery context, ObjectName tableName, SqlExpression expression, int limit) { if (expression is SqlQueryExpression) { return(context.DeleteFrom(tableName, (SqlQueryExpression)expression, limit)); } var table = context.GetMutableTable(tableName); if (table == null) { throw new ObjectNotFoundException(tableName); } var queryExpression = new SqlQueryExpression(new List <SelectColumn> { SelectColumn.Glob("*") }); queryExpression.FromClause.AddTable(tableName.Name); queryExpression.WhereExpression = expression; var planExpression = queryExpression.Evaluate(context, null); var plan = (SqlQueryObject)((SqlConstantExpression)planExpression).Value.Value; var deleteSet = plan.QueryPlan.Evaluate(context); return(context.DeleteFrom(tableName, deleteSet, limit)); }
/// <summary> /// Patch up the client aggregate with counts _if_ rollup grand totals or sub totals are requested and there is no count included in the client aggregate. /// </summary> /// <param name="clientAggregate"></param> /// <param name="query"></param> public static void EnsureShowTotalsHasCount(StructuredQuery query, ClientAggregate clientAggregate) { // if ((rollupGrandTotals || rollupSubTotals) && clientAggregate.GroupedColumns.Any()) //<< remove commented code and delete the follow_if_ when the builder sets these flags! if (clientAggregate.GroupedColumns.Count > 0) { ReportGroupField reportGroupField = clientAggregate.GroupedColumns.First( ); // Check to see if the first column group contains a count in the aggregate if (clientAggregate.AggregatedColumns.FirstOrDefault(ac => ac.AggregateMethod == AggregateMethod.Count) == null) { // Inject a count aggregate for a column that is _not_ a group column SelectColumn column = query.SelectColumns.FirstOrDefault(sc => clientAggregate.GroupedColumns.All(gc => gc.ReportColumnId != sc.ColumnId)); if (column != null) { clientAggregate.AggregatedColumns.Add(new ReportAggregateField { AggregateMethod = AggregateMethod.Count, IncludedCount = true, ReportColumnId = column.ColumnId, ReportColumnEntityId = column.EntityId, ShowGrandTotals = reportGroupField.ShowGrandTotals, ShowRowCounts = reportGroupField.ShowRowCounts, ShowOptionLabel = reportGroupField.ShowOptionLabel, ShowRowLabels = reportGroupField.ShowRowLabels, ShowSubTotals = reportGroupField.ShowSubTotals }); } } } }
private IQueryPlanNode EvaluateToSingle(PreparedQuerySelectColumns columns) { if (columns.AggregateCount > 0) { throw new InvalidOperationException("Invalid use of aggregate function in select with no FROM clause"); } // Make up the lists var selectedColumns = columns.SelectedColumns.ToList(); int colCount = selectedColumns.Count; var colNames = new string[colCount]; var expList = new SqlExpression[colCount]; var subsetVars = new ObjectName[colCount]; var aliases1 = new ObjectName[colCount]; for (int i = 0; i < colCount; ++i) { SelectColumn scol = selectedColumns[i]; expList[i] = scol.Expression; colNames[i] = scol.InternalName.Name; subsetVars[i] = scol.InternalName; aliases1[i] = scol.ResolvedName; } return(new SubsetNode(new CreateFunctionsNode(new SingleRowTableNode(), expList, colNames), subsetVars, aliases1)); }
private static IEnumerable <ByColumn> ResolveOrderByRefs(QuerySelectColumnSet columnSet, IEnumerable <ByColumn> orderBy) { // Resolve any numerical references in the ORDER BY list (eg. // '1' will be a reference to column 1. var result = new List <ByColumn>(); if (orderBy != null) { List <SelectColumn> preparedColSet = columnSet.SelectedColumns; foreach (ByColumn col in orderBy) { var byColumn = col; Expression exp = col.Expression; if (exp is ConstantExpression) { Number bnum = ((ConstantExpression)exp).Value.ToNumber(); if (bnum.Scale == 0) { int colRef = bnum.ToInt32() - 1; if (colRef >= 0 && colRef < preparedColSet.Count) { SelectColumn scol = preparedColSet[colRef]; byColumn = new ByColumn(scol.Expression, byColumn.Ascending); } } } result.Add(byColumn); } } return(result.AsReadOnly()); }
private void ParseColumns(IEnumerable <string[]> selectionWithColumnPath) { foreach (var selectionColumnPath in selectionWithColumnPath) { SelectColumn parentColumn = new RootColumn(_baseType); for (var depth = 0; depth < selectionColumnPath.Length; depth++) { if (IsGreaterThenMaxDepth(depth)) { break; } var columnName = selectionColumnPath[depth]; var property = parentColumn.Type.GetTypeOrGenericType().GetProperties().FirstOrDefault(x => x.Name.ToLowerInvariant() == columnName.ToLowerInvariant()); if (property == null || IsGreaterThanMaxDepth(property, depth)) { break; } var key = string.Join(".", selectionColumnPath.Take(depth + 1)).ToLowerInvariant(); var currentColumn = _allSelectColumns.FirstOrDefault(all => all.Key == key); if (currentColumn != null) { parentColumn = currentColumn; } else { // pass non selectable & unselectable properties if (IsNotSelectableProperty(key) || IsUnselectableProperty(key) || IsAnIndexerProperty(property)) { continue; } var column = new SelectColumn(columnName, key, property.PropertyType); if (_rawSelection.Contains(column.Key + ".*", StringComparer.OrdinalIgnoreCase)) { column.InclusionType = SelectInclusingType.IncludeAllProperties; ProcessInclusingType(column); } else if (property.PropertyType.IsCustomObjectType() && _rawSelection.Contains(column.Key, StringComparer.OrdinalIgnoreCase)) { column.InclusionType = SelectInclusingType.IncludeBaseProperties; ProcessInclusingType(column); } _allSelectColumns.Add(column); if (depth == 0) { _selectColumns.Add(column); } column.ParentColumn = parentColumn; parentColumn.SubColumns.Add(column); parentColumn = column; } } } }
public static SelectColumn BuildColumn(PlSqlParser.SelectedElementContext context) { string alias = null; if (context.column_alias() != null && !context.column_alias().IsEmpty) { alias = Name.Simple(context.column_alias()); } SelectColumn column; if (context.expression() != null && !context.expression().IsEmpty) { column = new SelectColumn(Expression.Build(context.expression()), alias); } else if (context.selectedColumn() != null && !context.selectedColumn().IsEmpty) { bool glob = context.selectedColumn().glob != null; ObjectName name = Name.Select(context.selectedColumn().objectName(), glob); var exp = SqlExpression.Reference(name); column = new SelectColumn(exp, alias); } else { throw new ParseCanceledException(); } return(column); }
/// <summary> /// Applies the sort order. /// </summary> /// <param name="structuredQuery">The structured query.</param> /// <param name="reportSortOrders">The report sort orders.</param> private static void ApplySortOrder(StructuredQuery structuredQuery, IEnumerable <ReportSortOrder> reportSortOrders) { // Check that all sort orders are valid, that is the column ID GUIDs are in the report _and_ the Order is an // orderBy direction _or_ empty. OrderByDirection orderByDirection = OrderByDirection.Ascending; IEnumerable <ReportSortOrder> sortOrders = reportSortOrders as IList <ReportSortOrder> ?? reportSortOrders.ToList(); if (!sortOrders.All(col => structuredQuery.SelectColumns.Any(sc => sc.EntityId.ToString(CultureInfo.InvariantCulture) == col.ColumnId)) || !sortOrders.All(col => Enum.TryParse(col.Order, out orderByDirection) || col.Order == string.Empty)) { throw new ArgumentOutOfRangeException("reportSortOrders"); } // Clear any existing sort order clauses that may be saved with the report structuredQuery.OrderBy.Clear(); foreach (ReportSortOrder sortOrder in sortOrders.Where(sortOrder => Enum.TryParse(sortOrder.Order, out orderByDirection))) { long sortEntityColumnId; if (!long.TryParse(sortOrder.ColumnId, out sortEntityColumnId)) { continue; } SelectColumn sortColumn = structuredQuery.SelectColumns.FirstOrDefault(sc => sc.EntityId == sortEntityColumnId); if (sortColumn != null && sortColumn.Expression != null && sortColumn.Expression.ExpressionId != Guid.Empty && sortColumn.ColumnId != Guid.Empty) { structuredQuery.OrderBy.Add(new OrderByItem { Direction = orderByDirection, Expression = new ColumnReference { EntityId = sortEntityColumnId, ExpressionId = sortColumn.Expression.ExpressionId, ColumnId = sortColumn.ColumnId } }); } } }
protected override SqlStatement PrepareStatement(IRequest context) { var tableName = context.Access().ResolveTableName(TableName); if (!context.Access().TableExists(tableName)) { throw new ObjectNotFoundException(tableName); } var queryExpression = new SqlQueryExpression(new[] { SelectColumn.Glob("*") }); queryExpression.FromClause.AddTable(tableName.FullName); queryExpression.WhereExpression = WherExpression; var queryFrom = QueryExpressionFrom.Create(context, queryExpression); var queryPlan = context.Query.Context.QueryPlanner().PlanQuery(new QueryInfo(context, queryExpression)); var columns = new List <SqlAssignExpression>(); foreach (var assignment in Assignments) { var columnName = ObjectName.Parse(assignment.ColumnName); var refName = queryFrom.ResolveReference(columnName); var expression = assignment.Expression.Prepare(queryFrom.ExpressionPreparer); var assign = SqlExpression.Assign(SqlExpression.Reference(refName), expression); columns.Add(assign); } return(new Prepared(tableName, queryPlan, columns.ToArray(), Limit)); }
public void RemoveUnusedColumns_WithCount_Ensure_AggregateExpressionWithGrouping_Removed( ) { ResourceEntity resNode = new ResourceEntity( ); AggregateEntity aggNode = new AggregateEntity { GroupedEntity = resNode }; aggNode.GroupBy.Add(new ResourceDataColumn( )); SelectColumn col1 = new SelectColumn { Expression = new ResourceDataColumn( ) }; SelectColumn col2 = new SelectColumn { Expression = new AggregateExpression { NodeId = aggNode.NodeId } }; StructuredQuery query = new StructuredQuery( ); query.RootEntity = aggNode; query.SelectColumns.Add(col1); query.SelectColumns.Add(col2); ClientAggregate agg = new ClientAggregate( ); agg.AggregatedColumns.Add(new ReportAggregateField { AggregateMethod = AggregateMethod.Count }); StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg); Assert.That(result.SelectColumns.Count, Is.EqualTo(2)); Assert.That(result.SelectColumns [0].Expression, Is.TypeOf <IdExpression>( )); Assert.That(result.SelectColumns [1].Expression, Is.TypeOf <AggregateExpression>( )); }
public ColumnCollection GetColumns() { ColumnCollection columns = new ColumnCollection(); ISqlScript sqlScript = SqlParserService.Parse(_select); if (sqlScript.Statements != null && sqlScript.Statements.Count > 0) { ISqlStatement statement = sqlScript.Statements[0]; if (statement is ISelectStatement) { ISelectStatement selectStatement = statement as ISelectStatement; if (selectStatement.QueryExpression != null && selectStatement.QueryExpression is ISelectExpression) { ISelectExpression selectExpression = selectStatement.QueryExpression as ISelectExpression; for (int i = 0; i < selectExpression.SelectList.Count; i++) { SelectColumn selectColumn = selectExpression.SelectList[i]; Column column = _table[selectColumn.Name]; if (column != null) { columns.Add(column); } } } } } return(columns); }
public SelectColumnListViewItem(string text, SelectColumn selectColumn) { SelectColumn = selectColumn; Text = text; SubItems.Add(""); SubItems.Add(""); RefreshText(); }
public string BuildSelect(SelectColumn columns, Filter filter, Sorting sorting, out object parameters) { Contract.RequiresNotNull(columns, "columns != null"); Contract.RequiresNotNull(filter, "filter != null"); Contract.RequiresNotNull(sorting, "sorting != null"); return(BuildSelect(columns, filter, sorting, new Pagination(), out parameters)); }
/// <summary> /// Renders a sinle select column /// </summary> protected virtual void SelectColumn(StringBuilder builder, SelectColumn col) { Expression(builder, col.Expression); if (col.ColumnAlias != null) { builder.Append(" "); Identifier(builder, col.ColumnAlias); } }
/// <summary> /// Builds the addition order column dictionary. /// </summary> /// <remarks> /// Lifted from the report info service /// </remarks> /// <param name="query">The query.</param> /// <param name="clientAggregate">The client aggregate.</param> /// <returns>System.Collections.Generic.Dictionary{System.Guid,EDC.ReadiNow.Metadata.Query.Structured.SelectColumn}.</returns> private Dictionary <Guid, SelectColumn> BuildAdditionOrderColumnDictionary(StructuredQuery query, ClientAggregate clientAggregate) { Dictionary <Guid, SelectColumn> additionalOrderColumns = new Dictionary <Guid, SelectColumn>(); foreach (ReportAggregateField reportAggregateField in clientAggregate.AggregatedColumns) { if (reportAggregateField.AggregateMethod == AggregateMethod.Max || reportAggregateField.AggregateMethod == AggregateMethod.Min) { SelectColumn currentSelectColumn = query.SelectColumns.FirstOrDefault(sc => sc.ColumnId == reportAggregateField.ReportColumnId); if (currentSelectColumn != null && currentSelectColumn.Expression is ResourceExpression && ((ResourceExpression)currentSelectColumn.Expression).CastType is ChoiceRelationshipType) { //add choice field column's order column in additional order column dictionary. if (!additionalOrderColumns.ContainsKey(currentSelectColumn.ColumnId)) { SelectColumn orderColumn = new SelectColumn { ColumnId = Guid.NewGuid(), ColumnName = "EnumOrder", DisplayName = "EnumOrder", Expression = new ResourceDataColumn { NodeId = ((EntityExpression)currentSelectColumn.Expression).NodeId, FieldId = new Model.EntityRef("core:enumOrder") } }; query.SelectColumns.Add(orderColumn); additionalOrderColumns.Add(currentSelectColumn.ColumnId, orderColumn); } } else if (currentSelectColumn != null && currentSelectColumn.Expression is AggregateExpression && ((AggregateExpression)currentSelectColumn.Expression).Expression is ResourceExpression && ((ResourceExpression)(((AggregateExpression)currentSelectColumn.Expression).Expression)).CastType is ChoiceRelationshipType) { //add aggregated choice field column's order column in additional order column dictionary. if (!additionalOrderColumns.ContainsKey(currentSelectColumn.ColumnId)) { SelectColumn orderColumn = new SelectColumn { ColumnId = Guid.NewGuid(), ColumnName = "EnumOrder", DisplayName = "EnumOrder", Expression = new AggregateExpression { NodeId = ((AggregateExpression)currentSelectColumn.Expression).NodeId, Expression = currentSelectColumn.Expression } }; additionalOrderColumns.Add(currentSelectColumn.ColumnId, orderColumn); } } } } return(additionalOrderColumns); }
/// <summary> /// Adds a field to be included in the select statement of the query. /// </summary> public void SelectField(string fieldId, SelectColumnAggregate?aggregate = null, SelectColumnFormat?format = null) { var column = new SelectColumn { FieldId = fieldId, Aggregate = aggregate, Format = format }; Raw.Select.Add(column); }
/// <summary> /// Renders a row count SELECT statement. /// </summary> /// <param name="query">Query definition to count rows for</param> /// <returns>Generated SQL statement</returns> /// <remarks> /// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate. /// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method. /// </remarks> public override string RenderRowCount(SelectQuery query) { string baseSql = RenderSelect(query, false); SelectQuery countQuery = new SelectQuery(); SelectColumn col = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count); countQuery.Columns.Add(col); countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t"); return(RenderSelect(countQuery)); }
private void ckbSelected_Loaded(object sender, RoutedEventArgs e) { var element = sender as CheckBox; if (!(element.DataContext is MarkObject)) { var data = ((System.Windows.Controls.DataGridCell)(((System.Windows.Controls.Primitives.ToggleButton)(sender)).Parent)).DataContext; element.DataContext = SelectColumn.GetMarkObject(data); element.IsChecked = SelectColumn.GetMarkObject(data).Selected; } }
public async Task <int> SaveAsync(TableMap tableMap, SelectColumn columns, object value) { var builder = SqlBuilder.Save(tableMap); var sql = builder.Build(columns, value, out object parameters); using (var connection = _connectionFactory.CreateConnection()) { var result = await connection.ExecuteScalarAsync(sql, parameters); return(Convert.ToInt32(result)); } }
public async Task <IEnumerable <TEntity> > SelectAllAsync(TableMap tableMap, SelectColumn columns, Filter filter) { var builder = SqlBuilder.Select(tableMap); var sql = builder.BuildSelect(columns, filter, out object parameters); using (var connection = _connectionFactory.CreateConnection()) { var result = await connection.QueryAsync <dynamic>(sql, parameters); return(_entityFactory.BuildMultiple(result)); } }
/// <summary> /// Renders a SELECT statement /// </summary> /// <param name="query">Query definition</param> /// <returns>Generated SQL statement</returns> public override string RenderSelect(SelectQuery query) { if (query.Top > -1 && query.OrderByTerms.Count > 0) { string baseSql = RenderSelect(query, -1); SelectQuery countQuery = new SelectQuery(); SelectColumn col = new SelectColumn("*"); countQuery.Columns.Add(col); countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t"); return RenderSelect(countQuery, query.Top); } else { return RenderSelect(query, query.Top); } }
/* void RenderFromPhrase(StringBuilder builder, FromClause fromClause) { this.From(builder); this.FromTerm(builder, fromClause.BaseTable); foreach(Join join in fromClause.Joins) { builder.AppendFormat(" {0} join ", join.Type.ToString().ToLower()); this.FromTerm(builder, join.RightTable); if (join.Type != JoinType.Cross) { builder.AppendFormat(" on "); this.QualifiedIdentifier(builder, join.LeftTable.RefName, join.LeftField); builder.AppendFormat(" = "); this.QualifiedIdentifier(builder, join.RightTable.RefName, join.RightField); } } } */ /// <summary> /// Renders a row count SELECT statement. /// </summary> /// <param name="query">Query definition to count rows for</param> /// <returns>Generated SQL statement</returns> /// <remarks> /// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate. /// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method. /// </remarks> public override string RenderRowCount(SelectQuery query) { string baseSql = RenderSelect(query); SelectQuery countQuery = new SelectQuery(); SelectColumn col = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count); countQuery.Columns.Add(col); countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t"); return RenderSelect(countQuery); }
/// <summary> /// Prepares the given SelectColumn by fully qualifying the expression and /// allocating it correctly within this context. /// </summary> /// <param name="col"></param> /// <param name="context"></param> private SelectColumn PrepareSelectColumn(SelectColumn col, IQueryContext context) { // Check to see if we have any Select statements in the // Expression. This is necessary, because we can't have a // sub-select evaluated during list table downloading. if (col.Expression.HasSubQuery()) throw new ApplicationException("Sub-command not allowed in column list."); // First fully qualify the select expression var exp = col.Expression.Prepare(fromSet.ExpressionQualifier); col = new SelectColumn(exp, col.Alias); // If the expression isn't a simple variable, then add to // function list. ObjectName v = exp.AsVariable(); if (v == null) { // This means we have a complex expression. ++runningFunNumber; string aggStr = runningFunNumber.ToString(); // If this is an aggregate column then add to aggregate count. if (col.Expression.HasAggregateFunction(context)) { ++AggregateCount; // Add '_A' code to end of internal name of column to signify this is // an aggregate column aggStr += "_A"; } // If this is a constant column then add to constant cound. else if (exp.IsConstant()) { ConstantCount = ConstantCount + 1; } else { // Must be an expression with variable's embedded ( eg. // (part_id + 3) * 4, (id * value_of_part), etc ) } if (col.Alias == null) { col = new SelectColumn(col.Expression, new ObjectName(col.Expression.ToString())); } col.InternalName = new ObjectName(FunctionTableName, aggStr); FunctionColumns.Add(col); } else { // Not a complex expression if (col.Alias == null) { col = new SelectColumn(col.Expression, v); } else { col = new SelectColumn(col.Expression, col.Alias); } col.InternalName = v; } return col; }
private Field SelectAggregate(string functionName, params SqlExpression[] args) { var column = new SelectColumn(SqlExpression.FunctionCall(functionName, args)); var query = new SqlQueryExpression(new[] { column }); query.FromClause.AddTable("APP.test_table"); var result = AdminQuery.Select(query); var row = result.FirstOrDefault(); if (row == null) throw new InvalidOperationException(); return row.GetValue(0); }
public static SelectColumn Deserialize(BinaryReader reader) { var exp = SqlExpression.Deserialize(reader); string alias = null; var hasAlias = reader.ReadByte() == 1; if (hasAlias) alias = reader.ReadString(); var column = new SelectColumn(exp, alias); var hasInternalName = reader.ReadByte() == 1; if (hasInternalName) column.InternalName = ObjectName.Deserialize(reader); var hasResolvedName = reader.ReadByte() == 1; if (hasResolvedName) column.ResolvedName = ObjectName.Deserialize(reader); return column; }
public static void Serialize(SelectColumn column, BinaryWriter writer) { SqlExpression.Serialize(column.Expression, writer); var hasAlias = !String.IsNullOrEmpty(column.Alias); if (!hasAlias) { writer.Write((byte)0); } else { writer.Write((byte)1); writer.Write(column.Alias); } bool hasInternalName = column.InternalName != null; if (hasInternalName) { writer.Write((byte)1); ObjectName.Serialize(column.InternalName, writer); } else { writer.Write((byte)0); } bool hasResolvedName = column.ResolvedName != null; if (hasResolvedName) { writer.Write((byte) 1); ObjectName.Serialize(column.ResolvedName, writer); } else { writer.Write((byte)0); } }
/// <summary> /// Adds all the columns from the given IFromTableSource object. /// </summary> /// <param name="table"></param> private void AddAllFromTable(IFromTableSource table) { // Select all the tables ObjectName[] vars = table.AllColumns; foreach (ObjectName v in vars) { // Make up the SelectColumn Expression e = Expression.Variable(v); SelectColumn ncol = new SelectColumn(e, v); ncol.InternalName = v; // Add to the list of columns selected SelectSingleColumn(ncol); } }
/// <summary> /// Renders a row count SELECT statement. /// </summary> /// <param name="query">Query definition to count rows for</param> /// <returns>Generated SQL statement</returns> /// <remarks> /// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate. /// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method. /// </remarks> public override string RenderRowCount(SelectQuery query) { SelectQuery queryAddition = query.Clone(); queryAddition.Columns.Clear(); SelectColumn col = new SelectColumn("*", null, "", SqlAggregationFunction.Count); queryAddition.Columns.Add(col); queryAddition.OrderByTerms.Clear(); return RenderSelect(queryAddition); }
/// <summary> /// Adds a single SelectColumn to the list of output columns /// from the command. /// </summary> /// <param name="col"></param> /// <remarks> /// Note that at this point the the information in the given /// SelectColumn may not be completely qualified. /// </remarks> public void SelectSingleColumn(SelectColumn col) { SelectedColumns.Add(col); }