private static void AppendSelectAndDistinctKeyWords(SelectStatement select, StringBuilder output) { // SELECT. output.Append("SELECT "); if (select.Distinct) output.Append("DISTINCT "); }
internal static void TryDetermineSubQueryDataType(SelectStatement query, out Type dataType, out DbType dbType) { // Automatically determine data type if possible. if (query.SelectList.Count == 1) { SelectItem firstItem = query.SelectList[0]; if (firstItem.ItemType == SqlItemType.Column) { IDbColumn field = (IDbColumn)firstItem.Item; dataType = field.DataType; dbType = field.DbType; } else if (firstItem.ItemType == SqlItemType.Function) { Function function = (Function)firstItem.Item; dataType = function.DataType; dbType = function.DbType; } else { dataType = typeof(object); dbType = DbType.Object; } } else { dataType = typeof(object); dbType = DbType.Object; } }
private static void AppendSelectList(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters) { // Select list. if (select.SelectList != null && select.SelectList.Count > 0) select.SelectList.Render(dbms, output, parameters); else output.Append("*"); }
private static void AppendFirstExpression(SelectStatement select, StringBuilder output) { if (select.Top > 0) { output.Append("FIRST "); output.Append(select.Top.ToString(CultureInfo.InvariantCulture)); output.Append(" "); } }
private static void AppendWhereClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters) { // WHERE. if (select.Where != null && !select.Where.IsEmpty) { output.Append(" WHERE "); select.Where.Render(dbms, output, parameters); } }
private static void AppendLimitExpression(SelectStatement select, StringBuilder output) { // LIMIT comes last - after ORDER BY. if (select.Top > 0) { output.Append(" LIMIT "); output.Append(select.Top.ToString(CultureInfo.InvariantCulture)); output.Append(" "); } }
public void Render(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters) { AppendSelectAndDistinctKeyWords(select, output); AppendFirstExpression(select, output); AppendSelectList(select, dbms, output, parameters); AppendFromClause(select, dbms, output); AppendWhereClause(select, dbms, output, parameters); AppendGroupByClause(select, dbms, output, parameters); AppendHavingClause(select, dbms, output, parameters); AppendOrderByClause(select, dbms, output, parameters); }
private static void AppendFromClause(SelectStatement select, DbmsType dbms, StringBuilder output) { // FROM. output.Append(" "); if (select.Relations != null) { select.Relations.RenderFromClause(select.FromTable, dbms, output); } else { // If no relations have been specified. output.Append("FROM "); select.FromTable.RenderTableName(dbms, output); // AS may not be used between table name and alias because Oracle doesn't that syntax. if (select.FromTable.Alias != select.FromTable.TableName) { output.Append(" "); select.FromTable.RenderAlias(dbms, output); } } }
/// <summary> /// Creates a <see cref="ComparePredicate"/>.<seealso cref="ComparisonOperator"/> /// </summary> /// <param name="subQuery"><see cref="SelectStatement"/> that returns a single value.</param> /// <param name="comparisonOperator"><see cref="ComparisonOperator"/>. Eg: ">=".</param> /// <param name="function">Function.</param> /// <param name="negate">Specifies whether the predicate is negated (NOT).</param> /// <returns>ComparePredicate.</returns> /// <remarks>Creates a predicate that compares the value retrieved by the query to a value computed by the /// function (see <see cref="AggregateFunction"/>, <see cref="Function"/>).</remarks> public static ComparePredicate Compare(SelectStatement subQuery, string comparisonOperator, Function function, bool negate) { if (subQuery == null || comparisonOperator == null || function == null) throw new ArgumentException(Messages.PredicateFactory_ObjReferenceIsNull); return new ComparePredicate(new PredicateItem(subQuery), comparisonOperator, new PredicateItem(function), negate); }
private void InitQuery(SelectStatement query) { this.Item = query; this.ItemType = SqlItemType.SubQuery; this.Alias = query.Alias; Type dataType; DbType dbType; SqlItemUtil.TryDetermineSubQueryDataType(query, out dataType, out dbType); this.DataType = dataType; this.DbType = dbType; }
/// <summary> /// Creates an item that contains a query. Assigns alias if query specifies one. /// If a query contains a single select item (field or function) than a data type will automatically be determined. /// </summary> /// <param name="query">Query.</param> public SelectItem(SelectStatement query) { InitQuery(query); }
private void InitQuery(SelectStatement query) { this.item = query; this.itemType = SqlItemType.SubQuery; SqlItemUtil.TryDetermineSubQueryDataType(query, out this.dataType, out this.dbType); this.alias = query.Alias; }
/// <summary> /// Creates a new item from the given query. /// </summary> internal PredicateItem(SelectStatement query) { InitQuery(query); }
protected void DbFetchProductCategoryChildren() { ProductCategoryMeta itemMeta = new ProductCategoryMeta(); SelectStatement select = new SelectStatement(itemMeta); select.Where.Add(itemMeta.FK_ParentProductCategoryID.ChildForeignKey[0], this.GetPrimaryKeyValue()[0]); this.ProductCategoryChildren = (EntityCollection<ProductCategoryEntity, ProductCategoryMeta>)select.Execute(); SetOldProductCategoryChildrenIDs(); }
private static void AppendOrderByClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters) { // ORDER BY. if (select.OrderBy != null && select.OrderBy.Count > 0) { output.Append(" "); select.OrderBy.Render(dbms, output, parameters); } }
protected void DbFetchSalesOrderHeaderChildren2() { SalesOrderHeaderMeta itemMeta = new SalesOrderHeaderMeta(); SelectStatement select = new SelectStatement(itemMeta); select.Where.Add(itemMeta.FK_ShipToAddressID.ChildForeignKey[0], this.GetPrimaryKeyValue()[0]); this.SalesOrderHeaderChildren2 = (EntityCollection<SalesOrderHeaderEntity, SalesOrderHeaderMeta>)select.Execute(); SetOldSalesOrderHeaderChildren2IDs(); }
private static void AppendHavingClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters) { // HAVING. if (select.Having != null && !select.Having.IsEmpty) { output.Append(" HAVING "); select.Having.Render(dbms, output, parameters); } }
/// <summary> /// Creates a <see cref="ComparePredicate"/>.<seealso cref="ComparisonOperator"/> /// </summary> /// <param name="field">Database field.</param> /// <param name="comparisonOperator"><see cref="ComparisonOperator"/>. Eg: ">=".</param> /// <param name="subQuery"><see cref="SelectStatement"/> that returns a single value.</param> /// <param name="negate">Specifies whether the predicate is negated (NOT).</param> /// <returns>ComparePredicate.</returns> /// <remarks>Creates a predicate that compares a field to a value retrieved by subquery.</remarks> public static ComparePredicate Compare(IDbColumn field, string comparisonOperator, SelectStatement subQuery, bool negate) { if (field == null || comparisonOperator == null || subQuery == null) throw new ArgumentException(Messages.PredicateFactory_ObjReferenceIsNull); return new ComparePredicate(new PredicateItem(field, SqlItemType.Column), comparisonOperator, new PredicateItem(subQuery, SqlItemType.SubQuery), negate); }
/// <summary> /// Creates a <see cref="ComparePredicate"/>.<seealso cref="ComparisonOperator"/> /// </summary> /// <param name="leftQuery">Left hand side <see cref="SelectStatement"/> that returns a single value.</param> /// <param name="comparisonOperator"><see cref="ComparisonOperator"/>. Eg: ">=".</param> /// <param name="rightQuery">Right hand side <see cref="SelectStatement"/> that returns a single value.</param> /// <param name="negate">Specifies whether the predicate is negated (NOT).</param> /// <returns>ComparePredicate.</returns> /// <remarks>Creates a predicate that compares the value retrieved by one query to a value retrieved by another query.</remarks> public static ComparePredicate Compare(SelectStatement leftQuery, string comparisonOperator, SelectStatement rightQuery, bool negate) { if (leftQuery == null || comparisonOperator == null || rightQuery == null) throw new ArgumentException(Messages.PredicateFactory_ObjReferenceIsNull); return new ComparePredicate(new PredicateItem(leftQuery), comparisonOperator, new PredicateItem(rightQuery), negate); }
/// <summary> /// Creates a <see cref="ComparePredicate"/>.<seealso cref="ComparisonOperator"/> /// </summary> /// <param name="subQuery"><see cref="SelectStatement"/> that returns a single value.</param> /// <param name="comparisonOperator"><see cref="ComparisonOperator"/>. Eg: ">=".</param> /// <param name="value">Value. <b>Null</b> is not allowed.</param> /// <param name="negate">Specifies whether the predicate is negated (NOT).</param> /// <returns>ComparePredicate.</returns> /// <remarks>Creates a predicate that compares the value retrieved by subquery to a .NET value. /// String, Char[], Boolean, Char, Byte, Int16, Int32, Int64, Single, Double, Decimal, DateTime etc.</remarks> public static ComparePredicate Compare(SelectStatement subQuery, string comparisonOperator, object value, bool negate) { if (subQuery == null || comparisonOperator == null || value == null) throw new ArgumentException(Messages.PredicateFactory_ObjReferenceIsNull); DbType dbType = DetermineDbType(subQuery.SelectList[0]); int size = DetermineSize(subQuery.SelectList[0]); return new ComparePredicate(new PredicateItem(subQuery), comparisonOperator, new PredicateItem(value, dbType, size, "par"), negate); }
private static void AppendGroupByClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters) { // GROUP BY. if (select.GroupBy != null && select.GroupBy.Fields.Count > 0) { output.Append(" "); select.GroupBy.Render(dbms, output, parameters); } }
/// <summary> /// Creates an <see cref="IsNullPredicate"/>. /// </summary> /// <param name="subQuery"><see cref="SelectStatement"/> that returns a single value.</param> /// <returns>IsNullPredicate.</returns> /// <remarks>Creates a predicate that checks whether the value retrieved the by the query is <b>null</b>.</remarks> public static IsNullPredicate IsNull(SelectStatement subQuery) { if (subQuery == null) throw new ArgumentException(Messages.PredicateFactory_ObjReferenceIsNull); return new IsNullPredicate(new PredicateItem(subQuery), false); }
protected void DbFetchCustomerAddressChildren() { CustomerAddressMeta itemMeta = new CustomerAddressMeta(); SelectStatement select = new SelectStatement(itemMeta); select.Where.Add(itemMeta.FK_AddressID.ChildForeignKey[0], this.GetPrimaryKeyValue()[0]); this.CustomerAddressChildren = (EntityCollection<CustomerAddressEntity, CustomerAddressMeta>)select.Execute(); SetOldCustomerAddressChildrenIDs(); }
/// <summary> /// Creates an <see cref="InPredicate"/>. /// </summary> /// <param name="field">Database field.</param> /// <param name="subQuery"><see cref="SelectStatement"/> that selects values from a single column.</param> /// <param name="negate">Specifies whether the predicate is negated (NOT IN).</param> /// <returns>InPredicate.</returns> /// <remarks>Creates an IN predicate that determines if a field matches any value in a subquery.</remarks> public static InPredicate In(IDbColumn field, SelectStatement subQuery, bool negate) { if (field == null || subQuery == null) throw new ArgumentException(Messages.PredicateFactory_ObjReferenceIsNull); PredicateItem[] itemValues = new PredicateItem[] { new PredicateItem(subQuery, SqlItemType.SubQuery) }; return new InPredicate(new PredicateItem(field, SqlItemType.Column), itemValues, negate); }
/// <summary> /// Creates an <see cref="ExistsPredicate"/> that checks if a given query returns any rows. /// </summary> /// <param name="subQuery"><see cref="SelectStatement"/>.</param> /// <param name="negate">Specifies whether the predicate is negated (NOT EXISTS).</param> /// <returns>ExistsPredicate.</returns> public static ExistsPredicate Exists(SelectStatement subQuery, bool negate) { if (subQuery == null) throw new ArgumentException(Messages.PredicateFactory_ObjReferenceIsNull); return new ExistsPredicate(new PredicateItem(subQuery, SqlItemType.SubQuery), negate); }
protected void DbFetchSalesOrderDetailChildren() { SalesOrderDetailMeta itemMeta = new SalesOrderDetailMeta(); SelectStatement select = new SelectStatement(itemMeta); select.Where.Add(itemMeta.FK_SalesOrderID.ChildForeignKey[0], this.GetPrimaryKeyValue()[0]); this.SalesOrderDetailChildren = (EntityCollection<SalesOrderDetailEntity, SalesOrderDetailMeta>)select.Execute(); SetOldSalesOrderDetailChildrenIDs(); }