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