static public string CreateFullName(tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
tgMetadata meta = query;
string name = String.Empty;
string catalog = meta.Catalog ?? null;
string schema = meta.Schema ?? null;
if (catalog != null && schema != null)
{
name += Delimiters.TableOpen + catalog + Delimiters.TableClose + ".";
}
if (schema != null)
{
name += Delimiters.TableOpen + schema + Delimiters.TableClose + ".";
}
name += Delimiters.TableOpen;
if (query.tg.QuerySource != null)
{
name += query.tg.QuerySource;
}
else
{
name += meta.Destination;
}
name += Delimiters.TableClose;
return(name);
}
static public string CreateFullName(tgDataRequest request, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
tgProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as tgProviderSpecificMetadata;
string name = String.Empty;
string catalog = iQuery.Catalog ?? request.Catalog ?? providerMetadata.Catalog;
string schema = iQuery.Schema ?? request.Schema ?? providerMetadata.Schema;
if (catalog != null && schema != null)
{
name += Delimiters.TableOpen + catalog + Delimiters.TableClose + ".";
}
if (schema != null)
{
name += Delimiters.TableOpen + schema + Delimiters.TableClose + ".";
}
name += Delimiters.TableOpen;
if (query.tg.QuerySource != null)
{
name += query.tg.QuerySource;
}
else
{
name += providerMetadata.Destination;
}
name += Delimiters.TableClose;
return(name);
}
protected static string ApplyWhereSubOperations(StandardProviderParameters std, tgDynamicQuerySerializable query, tgComparison.tgComparisonData comparisonData)
{
string sql = string.Empty;
if (comparisonData.HasExpression)
{
sql += GetMathmaticalExpressionColumn(std, query, comparisonData.Expression);
if (comparisonData.SubOperators != null && comparisonData.SubOperators.Count > 0)
{
sql = BuildSubOperationsSql(std, sql, comparisonData.SubOperators);
}
return sql;
}
string delimitedColumnName = GetColumnName(comparisonData.Column);
if (comparisonData.SubOperators != null)
{
sql = BuildSubOperationsSql(std, delimitedColumnName, comparisonData.SubOperators);
}
else
{
sql = delimitedColumnName;
}
return sql;
}
protected static string GetFromStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string sql = String.Empty;
if (iQuery.InternalFromQuery == null)
{
sql = Shared.CreateFullName(std.request, query);
if (iQuery.JoinAlias != " ")
{
sql += " " + iQuery.JoinAlias;
}
}
else
{
IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal;
iSubQuery.IsInSubQuery = true;
sql += "(";
sql += BuildQuery(std, iQuery.InternalFromQuery);
sql += ")";
if (iSubQuery.SubQueryAlias != " ")
{
sql += " AS " + iSubQuery.SubQueryAlias;
}
iSubQuery.IsInSubQuery = false;
}
return(sql);
}
static public string CreateFullName(tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
tgMetadata meta = query;
string name = String.Empty;
string catalog = meta.Catalog ?? null;
string schema = meta.Schema ?? null;
if (catalog != null && schema != null)
{
name += Delimiters.TableOpen + catalog + Delimiters.TableClose + ".";
}
if (schema != null)
{
name += Delimiters.TableOpen + schema + Delimiters.TableClose + ".";
}
name += Delimiters.TableOpen;
if (query.tg.QuerySource != null)
name += query.tg.QuerySource;
else
name += meta.Destination;
name += Delimiters.TableClose;
return name;
}
protected static string BuildQuery(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
bool paging = false;
if (query.tg.PageNumber.HasValue && query.tg.PageSize.HasValue)
{
paging = true;
}
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = String.Empty;
if (paging)
{
int begRow = ((query.tg.PageNumber.Value - 1) * query.tg.PageSize.Value) + 1;
int endRow = begRow + (query.tg.PageSize.Value - 1);
// The WITH statement
sql += "WITH [withStatement] AS (";
sql += "SELECT " + select + ", ROW_NUMBER() OVER(" + orderBy + ") AS ESRN ";
sql += "FROM " + from + join + where + groupBy + ") ";
sql += "SELECT * FROM [withStatement] ";
sql += "WHERE ESRN BETWEEN " + begRow + " AND " + endRow;
sql += " ORDER BY ESRN ASC";
}
else
{
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
}
if (iQuery.Skip.HasValue || iQuery.Take.HasValue)
{
if (iQuery.Skip.HasValue)
{
sql += " OFFSET " + iQuery.Skip.ToString() + " ROWS ";
}
if (iQuery.Take.HasValue)
{
sql += " FETCH NEXT " + iQuery.Take.ToString() + " ROWS ONLY ";
}
}
return(sql);
}
protected static string BuildQuery(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
bool paging = false;
if (query.tg.PageNumber.HasValue && query.tg.PageSize.HasValue)
{
paging = true;
}
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = String.Empty;
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
if (paging)
{
int begRow = ((query.tg.PageNumber.Value - 1) * query.tg.PageSize.Value);
sql += " LIMIT " + query.tg.PageSize.ToString();
sql += " OFFSET " + begRow.ToString() + " ";
}
else if (query.tg.Top >= 0)
{
sql += " LIMIT " + query.tg.Top.ToString() + " ";
}
else if (iQuery.Skip.HasValue || iQuery.Take.HasValue)
{
if (iQuery.Take.HasValue)
{
sql += " LIMIT " + iQuery.Take.ToString() + " ";
}
if (iQuery.Skip.HasValue)
{
sql += " OFFSET " + iQuery.Skip.ToString() + " ";
}
}
return(sql);
}
protected static string GetOrderByStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalOrderByItems != null)
{
sql += " ORDER BY ";
foreach (tgOrderByItem orderByItem in iQuery.InternalOrderByItems)
{
bool literal = false;
sql += comma;
string columnName = orderByItem.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
if (orderByItem.Direction == tgOrderByDirection.Unassigned)
{
literal = true; // They must provide the DESC/ASC in the literal string
}
}
else
{
sql += GetExpressionColumn(std, query, orderByItem.Expression, false, false);
}
if (!literal)
{
if (orderByItem.Direction == tgOrderByDirection.Ascending)
{
sql += " ASC";
}
else
{
sql += " DESC";
}
}
comma = ",";
}
}
return(sql);
}
/// <summary>
/// Used to describe the "where" conditions of the join itself
/// </summary>
/// <param name="items"></param>
/// <returns></returns>
public tgDynamicQuerySerializable On(params object[] items)
{
if (this.data.WhereItems == null)
{
this.data.WhereItems = new List <tgComparison>();
}
foreach (object item in items)
{
tgComparison wi = item as tgComparison;
if (wi != null)
{
if (wi.data.WhereExpression != null)
{
foreach (tgComparison exp in wi.data.WhereExpression)
{
tgDynamicQuerySerializable q = exp.Value as tgDynamicQuerySerializable;
if (q != null)
{
IDynamicQuerySerializableInternal iQ = q as IDynamicQuerySerializableInternal;
iQ.HookupProviderMetadata(q);
}
}
this.data.WhereItems.AddRange(wi.data.WhereExpression);
}
else
{
this.data.WhereItems.Add(wi);
}
tgDynamicQuerySerializable query = wi.Value as tgDynamicQuerySerializable;
if (query != null)
{
IDynamicQuerySerializableInternal iQ = query as IDynamicQuerySerializableInternal;
iQ.HookupProviderMetadata(query);
}
}
else
{
throw new Exception("Unsupported Join Syntax");
}
}
return(this.parentQuery);
}
protected static string GetJoinStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (tgJoinItem joinItem in iQuery.InternalJoinItems)
{
tgJoinItem.tgJoinItemData joinData = (tgJoinItem.tgJoinItemData)joinItem;
switch (joinData.JoinType)
{
case tgJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case tgJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case tgJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case tgJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal;
sql += Shared.CreateFullName(std.request, joinData.Query);
sql += " " + iSubQuery.JoinAlias;
if (query.tg.WithNoLock == true)
{
sql += " WITH (NOLOCK)";
}
sql += " ON ";
sql += GetComparisonStatement(std, query, joinData.WhereItems, String.Empty);
}
}
return(sql);
}
protected static string ApplyWhereSubOperations(StandardProviderParameters std, tgDynamicQuerySerializable query, tgComparison.tgComparisonData comparisonData)
{
string sql = string.Empty;
string delimitedColumnName = GetColumnName(comparisonData.Column);
if (comparisonData.SubOperators != null)
{
sql = BuildSubOperationsSql(std, delimitedColumnName, comparisonData.SubOperators);
}
else
{
sql = delimitedColumnName;
}
return sql;
}
protected static string GetFromStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string sql = String.Empty;
int joinCount = 0;
if (iQuery.InternalJoinItems != null && iQuery.InternalJoinItems.Count > 0)
{
joinCount = iQuery.InternalJoinItems.Count;
for (int i = 0; i < joinCount; i++)
{
sql += "(";
}
}
if (iQuery.InternalFromQuery == null)
{
sql += Shared.CreateFullName(query);
if (iQuery.JoinAlias != " ")
{
sql += " " + iQuery.JoinAlias;
}
}
else
{
IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal;
iSubQuery.IsInSubQuery = true;
sql += "(";
sql += BuildQuery(std, iQuery.InternalFromQuery);
sql += ")";
if (iSubQuery.SubQueryAlias != " ")
{
sql += " AS " + iSubQuery.SubQueryAlias;
}
iSubQuery.IsInSubQuery = false;
}
return(sql);
}
protected static string BuildQuery(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
return sql;
}
private static string GetSubquerySearchCondition(tgDynamicQuerySerializable query)
{
string searchCondition = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
switch (iQuery.SubquerySearchCondition)
{
case tgSubquerySearchCondition.All: searchCondition = "ALL"; break;
case tgSubquerySearchCondition.Any: searchCondition = "ANY"; break;
case tgSubquerySearchCondition.Some: searchCondition = "SOME"; break;
}
return(searchCondition);
}
protected static string BuildQuery(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
return(sql);
}
protected static string GetGroupByStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalGroupByItems != null)
{
sql += " GROUP BY ";
if (query.tg.WithRollup)
{
sql += " ROLLUP(";
}
foreach (tgGroupByItem groupBy in iQuery.InternalGroupByItems)
{
sql += comma;
string columnName = groupBy.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
}
else
{
sql += GetExpressionColumn(std, query, groupBy.Expression, false, false);
}
comma = ",";
}
if (query.tg.WithRollup)
{
sql += ")";
}
}
return(sql);
}
static public string CreateFullName(tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
tgProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as tgProviderSpecificMetadata;
string name = String.Empty;
name += Delimiters.TableOpen;
if (query.tg.QuerySource != null)
{
name += query.tg.QuerySource;
}
else
{
name += providerMetadata.Destination;
}
name += Delimiters.TableClose;
return(name);
}
protected static string GetExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgExpression expression, bool inExpression, bool useAlias)
{
string sql = String.Empty;
if (expression.CaseWhen != null)
{
return(GetCaseWhenThenEnd(std, query, expression.CaseWhen));
}
if (expression.HasMathmaticalExpression)
{
sql += GetMathmaticalExpressionColumn(std, query, expression.MathmaticalExpression);
}
else
{
sql += GetColumnName(expression.Column);
}
if (expression.SubOperators != null)
{
if (expression.Column.Distinct)
{
sql = BuildSubOperationsSql(std, "DISTINCT " + sql, expression.SubOperators);
}
else
{
sql = BuildSubOperationsSql(std, sql, expression.SubOperators);
}
}
if (!inExpression && useAlias)
{
if (expression.SubOperators != null || expression.Column.HasAlias)
{
sql += " AS " + Delimiters.StringOpen + expression.Column.Alias + Delimiters.StringClose;
}
}
return(sql);
}
public tgCase When(tgComparison comparison)
{
this.WhenItem = new tgExpressionOrComparison();
this.WhenItem.Comparisons = new List <tgComparison>();
if (comparison != null)
{
if (comparison.data.WhereExpression != null)
{
foreach (tgComparison exp in comparison.data.WhereExpression)
{
tgDynamicQuerySerializable q = exp.Value as tgDynamicQuerySerializable;
if (q != null)
{
IDynamicQuerySerializableInternal iQ = q as IDynamicQuerySerializableInternal;
iQ.HookupProviderMetadata(q);
}
}
this.WhenItem.Comparisons.AddRange(comparison.data.WhereExpression);
}
else
{
this.WhenItem.Comparisons.Add(comparison);
}
tgDynamicQuerySerializable query = comparison.Value as tgDynamicQuerySerializable;
if (query != null)
{
IDynamicQuerySerializableInternal iQ = query as IDynamicQuerySerializableInternal;
iQ.HookupProviderMetadata(query);
}
}
return(this);
}
protected static string GetSetOperationStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalSetOperations != null)
{
foreach (tgSetOperation setOperation in iQuery.InternalSetOperations)
{
switch (setOperation.SetOperationType)
{
case tgSetOperationType.Union: sql += " UNION "; break;
case tgSetOperationType.UnionAll: sql += " UNION ALL "; break;
case tgSetOperationType.Intersect: sql += " INTERSECT "; break;
case tgSetOperationType.Except: sql += " EXCEPT "; break;
}
sql += BuildQuery(std, setOperation.Query);
}
}
return sql;
}
protected static string GetCaseWhenThenEnd(StandardProviderParameters std, tgDynamicQuerySerializable query, tgCase caseWhenThen)
{
string sql = string.Empty;
Tiraggo.DynamicQuery.tgCase.tgSimpleCaseData caseStatement = caseWhenThen;
tgColumnItem column = caseStatement.QueryItem;
sql += "CASE ";
List<tgComparison> list = new List<tgComparison>();
foreach (Tiraggo.DynamicQuery.tgCase.tgSimpleCaseData.tgCaseClause caseClause in caseStatement.Cases)
{
sql += " WHEN ";
if (!caseClause.When.IsExpression)
{
sql += GetComparisonStatement(std, query, caseClause.When.Comparisons, string.Empty);
}
else
{
if (!caseClause.When.Expression.IsLiteralValue)
{
sql += GetExpressionColumn(std, query, caseClause.When.Expression, false, true);
}
else
{
if (caseClause.When.Expression.LiteralValue is string)
{
sql += Delimiters.StringOpen + caseClause.When.Expression.LiteralValue + Delimiters.StringClose;
}
else
{
sql += Convert.ToString(caseClause.When.Expression.LiteralValue);
}
}
}
sql += " THEN ";
if (!caseClause.Then.IsLiteralValue)
{
sql += GetExpressionColumn(std, query, caseClause.Then, false, true);
}
else
{
if (caseClause.Then.LiteralValue is string)
{
sql += Delimiters.StringOpen + caseClause.Then.LiteralValue + Delimiters.StringClose;
}
else
{
sql += Convert.ToString(caseClause.Then.LiteralValue);
}
}
}
if (caseStatement.Else != null)
{
sql += " ELSE ";
if (!caseStatement.Else.IsLiteralValue)
{
sql += GetExpressionColumn(std, query, caseStatement.Else, false, true);
}
else
{
if (caseStatement.Else.LiteralValue is string)
{
sql += Delimiters.StringOpen + caseStatement.Else.LiteralValue + Delimiters.StringClose;
}
else
{
sql += Convert.ToString(caseStatement.Else.LiteralValue);
}
}
}
sql += " END ";
sql += " AS " + Delimiters.ColumnOpen + column.Alias + Delimiters.ColumnClose;
return sql;
}
/// <summary>
/// The tgComparison class is dynamically created by your
/// BusinessEntity's DynamicQuery mechanism.
/// </summary>
public tgComparison(tgDynamicQuerySerializable query)
{
this.data.Query = query;
}
/// <summary>
/// Comparison ensuring that this column is NOT IN a list of values.
/// </summary>
/// <param name="value">The Query to provide the matching values for the NOT IN comparison</param>
/// <returns></returns>
public tgComparison NotIn(tgDynamicQuerySerializable subQuery)
{
tgComparison comparison = new tgComparison(this.query);
comparison.Operand = tgComparisonOperand.NotIn;
comparison.data.Column = this.Column;
comparison.SubOperators = this.SubOperators;
comparison.Value = subQuery;
this.query.AddQueryToList(subQuery);
return comparison;
}
public static string CreateFullName(tgDataRequest request, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
tgProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as tgProviderSpecificMetadata;
string name = String.Empty;
string catalog = iQuery.Catalog ?? request.Catalog ?? providerMetadata.Catalog;
string schema = iQuery.Schema ?? request.Schema ?? providerMetadata.Schema;
if (catalog != null && schema != null)
{
name += Delimiters.TableOpen + catalog + Delimiters.TableClose + ".";
}
if (schema != null)
{
name += Delimiters.TableOpen + schema + Delimiters.TableClose + ".";
}
name += Delimiters.TableOpen;
if (query.tg.QuerySource != null)
name += query.tg.QuerySource;
else
name += providerMetadata.Destination;
name += Delimiters.TableClose;
return name;
}
protected static string GetMathmaticalExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgMathmaticalExpression mathmaticalExpression)
{
bool isConcat = false;
string sql = "(";
if (mathmaticalExpression.ItemFirst)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, false);
sql += esArithmeticOperatorToString(mathmaticalExpression, out isConcat);
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, false);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
}
else
{
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
sql += esArithmeticOperatorToString(mathmaticalExpression, out isConcat);
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, false);
}
sql += ")";
if (isConcat)
{
sql = "CONCAT(" + sql.Substring(1, sql.Length - 2) + ")";
}
return(sql);
}
protected static string BuildQuery(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
bool paging = false;
if (query.tg.PageNumber.HasValue && query.tg.PageSize.HasValue)
paging = true;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = String.Empty;
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
if (paging)
{
int begRow = ((query.tg.PageNumber.Value - 1) * query.tg.PageSize.Value);
sql += " LIMIT " + query.tg.PageSize.ToString();
sql += " OFFSET " + begRow.ToString() + " ";
}
else if (query.tg.Top >= 0)
{
sql += " LIMIT " + query.tg.Top.ToString() + " ";
}
else if (iQuery.Skip.HasValue || iQuery.Take.HasValue)
{
if (iQuery.Take.HasValue)
{
sql += " LIMIT " + iQuery.Take.ToString() + " ";
}
if (iQuery.Skip.HasValue)
{
sql += " OFFSET " + iQuery.Skip.ToString() + " ";
}
}
return sql;
}
protected static string GetJoinStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (tgJoinItem joinItem in iQuery.InternalJoinItems)
{
tgJoinItem.tgJoinItemData joinData = (tgJoinItem.tgJoinItemData)joinItem;
switch (joinData.JoinType)
{
case tgJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case tgJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case tgJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case tgJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal;
sql += Shared.CreateFullName((tgProviderSpecificMetadata)iSubQuery.ProviderMetadata);
sql += " " + iSubQuery.JoinAlias + " ON ";
sql += GetComparisonStatement(std, query, joinData.WhereItems, String.Empty);
}
}
return sql;
}
/// <summary>
/// The Constructor
/// </summary>
public tgSetOperation(tgDynamicQuerySerializable query)
{
this.Query = query;
}
static public Dictionary<string, SqlParameter> GetParameters(tgDynamicQuerySerializable query)
{
tgMetadata meta = null;
lock (parameterCache)
{
meta = query;
if (!parameterCache.ContainsKey(meta.DataID))
{
// The Parameters for this Table haven't been cached yet, this is a one time operation
Dictionary<string, SqlParameter> types = new Dictionary<string, SqlParameter>();
SqlParameter param1;
foreach (tgColumnMetadata col in meta.Columns)
{
tgTypeMap typeMap = meta.GetTypeMap(col.PropertyName);
if (typeMap != null)
{
string nativeType = typeMap.NativeType;
SqlDbType dbType = Cache.NativeTypeToDbType(nativeType);
param1 = new SqlParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.PropertyName);
param1.SourceColumn = col.Name;
switch (dbType)
{
case SqlDbType.BigInt:
case SqlDbType.Decimal:
case SqlDbType.Float:
case SqlDbType.Int:
case SqlDbType.Money:
case SqlDbType.Real:
case SqlDbType.SmallMoney:
case SqlDbType.TinyInt:
case SqlDbType.SmallInt:
param1.Size = (int)col.CharacterMaxLength;
param1.Precision = (byte)col.NumericPrecision;
param1.Scale = (byte)col.NumericScale;
break;
case SqlDbType.DateTime:
param1.Precision = 23;
param1.Scale = 3;
break;
case SqlDbType.SmallDateTime:
param1.Precision = 16;
break;
case SqlDbType.Udt:
SetUdtTypeNameToAvoidMonoError(param1, typeMap);
break;
}
types[col.Name] = param1;
}
}
parameterCache[meta.DataID] = types;
}
}
return parameterCache[meta.DataID];
}
protected static string GetMathmaticalExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgMathmaticalExpression mathmaticalExpression)
{
string sql = "(";
if (mathmaticalExpression.ItemFirst)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
switch (mathmaticalExpression.Operator)
{
case tgArithmeticOperator.Add: sql += "+"; break;
case tgArithmeticOperator.Subtract: sql += "-"; break;
case tgArithmeticOperator.Multiply: sql += "*"; break;
case tgArithmeticOperator.Divide: sql += "/"; break;
case tgArithmeticOperator.Modulo: sql += "%"; break;
}
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
switch (mathmaticalExpression.LiteralType)
{
case tgSystemType.String:
sql += Delimiters.StringOpen + (string)mathmaticalExpression.Literal + Delimiters.StringClose;
break;
case tgSystemType.DateTime:
sql += Delimiters.StringOpen + ((DateTime)(mathmaticalExpression.Literal)).ToShortDateString() + Delimiters.StringClose;
break;
default:
sql += Convert.ToString(mathmaticalExpression.Literal);
break;
}
}
}
else
{
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
switch (mathmaticalExpression.LiteralType)
{
case tgSystemType.String:
sql += Delimiters.StringOpen + (string)mathmaticalExpression.Literal + Delimiters.StringClose;
break;
case tgSystemType.DateTime:
sql += Delimiters.StringOpen + ((DateTime)(mathmaticalExpression.Literal)).ToShortDateString() + Delimiters.StringClose;
break;
default:
sql += Convert.ToString(mathmaticalExpression.Literal);
break;
}
}
switch (mathmaticalExpression.Operator)
{
case tgArithmeticOperator.Add: sql += "+"; break;
case tgArithmeticOperator.Subtract: sql += "-"; break;
case tgArithmeticOperator.Multiply: sql += "*"; break;
case tgArithmeticOperator.Divide: sql += "/"; break;
case tgArithmeticOperator.Modulo: sql += "%"; break;
}
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
}
sql += ")";
return(sql);
}
/// <summary>
/// The Constructor
/// </summary>
public tgJoinItem(tgDynamicQuerySerializable parentQuery)
{
this.parentQuery = parentQuery;
}
/// <summary>
/// The Constructor
/// </summary>
public tgSetOperation(tgDynamicQuerySerializable query)
{
this.Query = query;
}
protected static string GetMathmaticalExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgMathmaticalExpression mathmaticalExpression)
{
bool isConcat = false;
string sql = "(";
if (mathmaticalExpression.ItemFirst)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, false);
sql += esArithmeticOperatorToString(mathmaticalExpression, out isConcat);
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, false);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
}
else
{
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
sql += esArithmeticOperatorToString(mathmaticalExpression, out isConcat);
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, false);
}
sql += ")";
if (isConcat)
{
sql = "CONCAT(" + sql.Substring(1, sql.Length - 2) + ")";
}
return sql;
}
protected static string GetMathmaticalExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgMathmaticalExpression mathmaticalExpression)
{
bool isMod = false;
bool needsRounding = false;
string sql = "(";
if (mathmaticalExpression.ItemFirst)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, false);
sql += esArithmeticOperatorToString(mathmaticalExpression, out isMod, out needsRounding);
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, false);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
}
else
{
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
sql += esArithmeticOperatorToString(mathmaticalExpression, out isMod, out needsRounding);
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, false);
}
sql += ")";
if (isMod)
{
sql = "MOD(" + sql.Replace("(", String.Empty).Replace(")", String.Empty) + ")";
}
if (needsRounding)
{
sql = "ROUND(" + sql + ", 10)";
}
return(sql);
}
protected static string GetFromStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string sql = String.Empty;
if (iQuery.InternalFromQuery == null)
{
sql = Shared.CreateFullName(query);
if (iQuery.JoinAlias != " ")
{
sql += " " + iQuery.JoinAlias;
}
}
else
{
IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal;
iSubQuery.IsInSubQuery = true;
sql += "(";
sql += BuildQuery(std, iQuery.InternalFromQuery);
sql += ")";
if (iSubQuery.SubQueryAlias != " ")
{
sql += " AS " + iSubQuery.SubQueryAlias;
}
iSubQuery.IsInSubQuery = false;
}
return sql;
}
protected static string BuildQuery(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
bool selectAll = (iQuery.InternalSelectColumns == null && !query.tg.CountAll);
bool paging = false;
if (query.tg.PageNumber.HasValue && query.tg.PageSize.HasValue)
{
paging = true;
}
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = String.Empty;
if (paging)
{
int begRow = ((query.tg.PageNumber.Value - 1) * query.tg.PageSize.Value) + 1;
int endRow = begRow + (query.tg.PageSize.Value - 1);
// The WITH statement
sql += "WITH \"withStatement\" AS (";
if (selectAll)
{
sql += "SELECT " +
Delimiters.TableOpen + query.tg.QuerySource + Delimiters.TableClose
+ ".*, ROW_NUMBER() OVER(" + orderBy + ") AS ESRN ";
}
else
{
sql += "SELECT " + select + ", ROW_NUMBER() OVER(" + orderBy + ") AS ESRN ";
}
sql += "FROM " + from + join + where + groupBy + ") ";
// The actual select
if (selectAll || join.Length > 0 || groupBy.Length > 0 || query.tg.Distinct)
{
sql += "SELECT " +
Delimiters.TableOpen + "withStatement" + Delimiters.TableClose
+ ".* FROM \"withStatement\" ";
}
else
{
sql += "SELECT " + select + " FROM \"withStatement\" ";
}
sql += "WHERE ESRN BETWEEN " + begRow + " AND " + endRow;
sql += " ORDER BY ESRN ASC";
}
else
{
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
}
return(sql);
}
protected static string GetOrderByStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalOrderByItems != null)
{
sql += " ORDER BY ";
foreach (tgOrderByItem orderByItem in iQuery.InternalOrderByItems)
{
bool literal = false;
sql += comma;
string columnName = orderByItem.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
if (orderByItem.Direction == tgOrderByDirection.Unassigned)
{
literal = true; // They must provide the DESC/ASC in the literal string
}
}
else
{
sql += GetExpressionColumn(std, query, orderByItem.Expression, false, false);
}
if (!literal)
{
if (orderByItem.Direction == tgOrderByDirection.Ascending)
sql += " ASC";
else
sql += " DESC";
}
comma = ",";
}
}
return sql;
}
/// <summary>
/// The tgComparison class is dynamically created by your
/// BusinessEntity's DynamicQuery mechanism.
/// </summary>
public tgComparison(tgDynamicQuerySerializable query)
{
this.data.Query = query;
}
protected static string GetJoinStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (tgJoinItem joinItem in iQuery.InternalJoinItems)
{
tgJoinItem.tgJoinItemData joinData = (tgJoinItem.tgJoinItemData)joinItem;
switch (joinData.JoinType)
{
case tgJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case tgJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case tgJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case tgJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal;
sql += Shared.CreateFullName((tgProviderSpecificMetadata)iSubQuery.ProviderMetadata);
sql += " " + iSubQuery.JoinAlias + " ON ";
foreach (tgComparison comparisonItem in joinData.WhereItems)
{
tgComparison.tgComparisonData comparisonData = (tgComparison.tgComparisonData)comparisonItem;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == tgParenthesis.Open)
sql += "(";
else
sql += ")";
continue;
}
if (comparisonData.IsConjunction)
{
if (comparisonData.Conjunction == tgConjunction.And)
sql += " AND ";
else
sql += " OR ";
continue;
}
sql += GetColumnName(comparisonData.Column);
switch (comparisonData.Operand)
{
case tgComparisonOperand.Equal:
sql += " = ";
break;
case tgComparisonOperand.NotEqual:
sql += " <> ";
break;
case tgComparisonOperand.GreaterThan:
sql += " > ";
break;
case tgComparisonOperand.LessThan:
sql += " < ";
break;
case tgComparisonOperand.LessThanOrEqual:
sql += " <= ";
break;
case tgComparisonOperand.GreaterThanOrEqual:
sql += " >= ";
break;
case tgComparisonOperand.IsNull:
sql += " IS NULL ";
break;
case tgComparisonOperand.IsNotNull:
sql += " IS NOT NULL ";
break;
case tgComparisonOperand.In:
sql += " IN ";
break;
case tgComparisonOperand.NotIn:
sql += " NOT IN ";
break;
}
if (comparisonData.ComparisonColumn.Name != null)
{
sql += GetColumnName(comparisonData.ComparisonColumn);
}
else
{
tgDynamicQuerySerializable subQuery = comparisonData.Value as tgDynamicQuerySerializable;
if (subQuery != null)
{
sql += GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ")";
}
else if (comparisonData.Value != null)
{
// Handle Literals
sql += comparisonData.Value.ToString();
}
}
}
sql += ")";
}
}
return sql;
}
/// <summary>
/// The Constructor
/// </summary>
public tgCase(tgDynamicQuerySerializable query)
{
this.data.Query = query;
}
protected static string ApplyWhereSubOperations(StandardProviderParameters std, tgDynamicQuerySerializable query, tgComparison.tgComparisonData comparisonData)
{
string sql = string.Empty;
if (comparisonData.HasExpression)
{
sql += GetMathmaticalExpressionColumn(std, query, comparisonData.Expression);
if (comparisonData.SubOperators != null && comparisonData.SubOperators.Count > 0)
{
sql = BuildSubOperationsSql(std, sql, comparisonData.SubOperators);
}
return(sql);
}
string delimitedColumnName = GetColumnName(comparisonData.Column);
if (comparisonData.SubOperators != null)
{
sql = BuildSubOperationsSql(std, delimitedColumnName, comparisonData.SubOperators);
}
else
{
sql = delimitedColumnName;
}
return(sql);
}
protected static string BuildQuery(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
bool paging = false;
if (query.tg.PageNumber.HasValue && query.tg.PageSize.HasValue)
paging = true;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = String.Empty;
if (paging)
{
int begRow = ((query.tg.PageNumber.Value - 1) * query.tg.PageSize.Value) + 1;
int endRow = begRow + (query.tg.PageSize.Value - 1);
// The WITH statement
sql += "WITH [withStatement] AS (";
sql += "SELECT " + select + ", ROW_NUMBER() OVER(" + orderBy + ") AS ESRN ";
sql += "FROM " + from + join + where + groupBy + ") ";
// The actual select
if (join.Length > 0)
sql += "SELECT * FROM [withStatement] ";
else
sql += "SELECT " + select + " FROM [withStatement] ";
sql += "WHERE ESRN BETWEEN " + begRow + " AND " + endRow;
}
else
{
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
}
return sql;
}
public static string CreateFullName(tgDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
tgProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as tgProviderSpecificMetadata;
string name = String.Empty;
name += Delimiters.TableOpen;
if (query.tg.QuerySource != null)
name += query.tg.QuerySource;
else
name += providerMetadata.Destination;
name += Delimiters.TableClose;
return name;
}
protected static string GetMathmaticalExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgMathmaticalExpression mathmaticalExpression)
{
string sql = "(";
if (mathmaticalExpression.ItemFirst)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
switch (mathmaticalExpression.Operator)
{
case tgArithmeticOperator.Add: sql += "+"; break;
case tgArithmeticOperator.Subtract: sql += "-"; break;
case tgArithmeticOperator.Multiply: sql += "*"; break;
case tgArithmeticOperator.Divide: sql += "/"; break;
case tgArithmeticOperator.Modulo: sql += "%"; break;
}
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
switch (mathmaticalExpression.LiteralType)
{
case tgSystemType.String:
sql += Delimiters.StringOpen + (string)mathmaticalExpression.Literal + Delimiters.StringClose;
break;
case tgSystemType.DateTime:
sql += Delimiters.StringOpen + ((DateTime)(mathmaticalExpression.Literal)).ToShortDateString() + Delimiters.StringClose;
break;
default:
sql += Convert.ToString(mathmaticalExpression.Literal);
break;
}
}
}
else
{
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
switch (mathmaticalExpression.LiteralType)
{
case tgSystemType.String:
sql += Delimiters.StringOpen + (string)mathmaticalExpression.Literal + Delimiters.StringClose;
break;
case tgSystemType.DateTime:
sql += Delimiters.StringOpen + ((DateTime)(mathmaticalExpression.Literal)).ToShortDateString() + Delimiters.StringClose;
break;
default:
sql += Convert.ToString(mathmaticalExpression.Literal);
break;
}
}
switch (mathmaticalExpression.Operator)
{
case tgArithmeticOperator.Add: sql += "+"; break;
case tgArithmeticOperator.Subtract: sql += "-"; break;
case tgArithmeticOperator.Multiply: sql += "*"; break;
case tgArithmeticOperator.Divide: sql += "/"; break;
case tgArithmeticOperator.Modulo: sql += "%"; break;
}
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
}
sql += ")";
return sql;
}
/// <summary>
/// The tgQueryItem class is dynamically created by your
/// BusinessEntity's DynamicQuery mechanism.
/// </summary>
/// <param name="query">The esDynamicQueryTransport passed in via DynamicQuery</param>
/// <param name="columnName">The columnName passed in via DynamicQuery</param>
/// <param name="datatype">The tgSystemType</param>
public tgQueryItem(tgDynamicQuerySerializable query, string columnName, tgSystemType datatype)
{
this.query = query;
this.Column.Query = query;
this.Column.Name = columnName;
this.Column.Query.tg.JoinAlias = query.tg.JoinAlias;
this.Column.Datatype = datatype;
}
/// <summary>
/// The Constructor
/// </summary>
public tgCase(tgDynamicQuerySerializable query, tgQueryItem queryItem)
{
this.data.Query = query;
this.data.QueryItem = queryItem;
}
private static string GetSubquerySearchCondition(tgDynamicQuerySerializable query)
{
string searchCondition = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
switch (iQuery.SubquerySearchCondition)
{
case tgSubquerySearchCondition.All: searchCondition = "ALL"; break;
case tgSubquerySearchCondition.Any: searchCondition = "ANY"; break;
case tgSubquerySearchCondition.Some: searchCondition = "SOME"; break;
}
return searchCondition;
}
/// <summary>
/// The Constructor
/// </summary>
public tgJoinItem(tgDynamicQuerySerializable parentQuery)
{
this.parentQuery = parentQuery;
}
protected static string GetComparisonStatement(StandardProviderParameters std, tgDynamicQuerySerializable query, List<tgComparison> items, string prefix)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
string compareTo = String.Empty;
foreach (tgComparison comparisonItem in items)
{
tgComparison.tgComparisonData comparisonData = (tgComparison.tgComparisonData)comparisonItem;
tgDynamicQuerySerializable subQuery = null;
bool requiresParam = true;
bool needsStringParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == tgParenthesis.Open)
sql += "(";
else
sql += ")";
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case tgConjunction.And: sql += " AND "; break;
case tgConjunction.Or: sql += " OR "; break;
case tgConjunction.AndNot: sql += " AND NOT "; break;
case tgConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary<string, VistaDBParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (tgProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (tgColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as tgDynamicQuerySerializable;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case tgComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case tgComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case tgComparisonOperand.Equal:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo;
else
sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo;
else
sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo;
else
sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo;
else
sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo;
else
sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo;
else
sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) +
", " + compareTo + ")";
needsStringParameter = true;
break;
case tgComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL";
requiresParam = false;
break;
case tgComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case tgComparisonOperand.In:
case tgComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(std, query, comparisonData);
sql += (comparisonData.Operand == tgComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == tgComparisonOperand.In)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN (";
}
foreach (object oin in comparisonData.Values)
{
string str = oin as string;
if (str != null)
{
// STRING
sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose;
comma = ",";
}
else if (null != oin as System.Collections.IEnumerable)
{
// LIST OR COLLECTION OF SOME SORT
System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable;
if (enumer != null)
{
System.Collections.IEnumerator iter = enumer.GetEnumerator();
while (iter.MoveNext())
{
object o = iter.Current;
string soin = o as string;
if (soin != null)
sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose;
else
sql += comma + Convert.ToString(o);
comma = ",";
}
}
}
else
{
// NON STRING OR LIST
sql += comma + Convert.ToString(oin);
comma = ",";
}
}
sql += ")";
requiresParam = false;
}
}
break;
case tgComparisonOperand.Between:
VistaDBCommand sqlCommand = std.cmd as VistaDBCommand;
sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
sqlCommand.Parameters.Add(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
sql += " AND " + compareTo;
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
VistaDBParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.ParameterName = compareTo;
p.Value = comparisonData.Value;
if (needsStringParameter)
{
p.DbType = DbType.String;
}
}
else
{
p = new VistaDBParameter(compareTo, comparisonData.Value);
}
std.cmd.Parameters.Add(p);
}
}
}
return sql;
}
protected static string GetSelectStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
bool selectAll = true;
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
sql += String.Empty;
if (query.tg.Distinct)
{
sql += " DISTINCT ";
}
if (iQuery.InternalSelectColumns != null)
{
selectAll = false;
foreach (tgExpression expressionItem in iQuery.InternalSelectColumns)
{
if (expressionItem.Query != null)
{
IDynamicQuerySerializableInternal iSubQuery = expressionItem.Query as IDynamicQuerySerializableInternal;
sql += comma;
if (iSubQuery.SubQueryAlias == string.Empty)
{
sql += iSubQuery.JoinAlias + ".*";
}
else
{
iSubQuery.IsInSubQuery = true;
sql += " (" + BuildQuery(std, expressionItem.Query as tgDynamicQuerySerializable) + ") AS " + iSubQuery.SubQueryAlias;
iSubQuery.IsInSubQuery = false;
}
comma = ",";
}
else
{
sql += comma;
string columnName = expressionItem.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
}
else
{
sql += GetExpressionColumn(std, query, expressionItem, false, true);
}
comma = ",";
}
}
sql += " ";
}
if (query.tg.CountAll)
{
selectAll = false;
sql += comma;
sql += "COUNT(*)";
if (query.tg.CountAllAlias != null)
{
// Need DBMS string delimiter here
sql += " AS " + Delimiters.StringOpen + query.tg.CountAllAlias + Delimiters.StringClose;
}
}
if (selectAll)
{
sql += "*";
}
return(sql);
}
protected static string GetExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgExpression expression, bool inExpression, bool useAlias)
{
string sql = String.Empty;
if (expression.CaseWhen != null)
{
return GetCaseWhenThenEnd(std, query, expression.CaseWhen);
}
if (expression.HasMathmaticalExpression)
{
sql += GetMathmaticalExpressionColumn(std, query, expression.MathmaticalExpression);
}
else
{
sql += GetColumnName(expression.Column);
}
if (expression.SubOperators != null)
{
if (expression.Column.Distinct)
{
sql = BuildSubOperationsSql(std, "DISTINCT " + sql, expression.SubOperators);
}
else
{
sql = BuildSubOperationsSql(std, sql, expression.SubOperators);
}
}
if (!inExpression && useAlias)
{
if (expression.SubOperators != null || expression.Column.HasAlias)
{
sql += " AS " + Delimiters.StringOpen + expression.Column.Alias + Delimiters.StringClose;
}
}
return sql;
}
protected static string GetComparisonStatement(StandardProviderParameters std, tgDynamicQuerySerializable query, List <tgComparison> items, string prefix)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
string compareTo = String.Empty;
foreach (tgComparison comparisonItem in items)
{
tgComparison.tgComparisonData comparisonData = (tgComparison.tgComparisonData)comparisonItem;
tgDynamicQuerySerializable subQuery = null;
bool requiresParam = true;
bool needsStringParameter = false;
std.needsStringParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == tgParenthesis.Open)
{
sql += "(";
}
else
{
sql += ")";
}
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case tgConjunction.And: sql += " AND "; break;
case tgConjunction.Or: sql += " OR "; break;
case tgConjunction.AndNot: sql += " AND NOT "; break;
case tgConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary <string, MySqlParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (tgProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (tgColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
if (comparisonData.Column.Name[0] == '<')
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
}
else
{
sql += comparisonData.Column.Name;
}
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as tgDynamicQuerySerializable;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case tgComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case tgComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case tgComparisonOperand.Equal:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo;
}
else
{
sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo;
}
else
{
sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo;
}
else
{
sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo;
}
else
{
sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo;
}
else
{
sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo;
}
else
{
sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case tgComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) +
", " + compareTo + ")";
needsStringParameter = true;
break;
case tgComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL";
requiresParam = false;
break;
case tgComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case tgComparisonOperand.In:
case tgComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(std, query, comparisonData);
sql += (comparisonData.Operand == tgComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == tgComparisonOperand.In)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN (";
}
foreach (object oin in comparisonData.Values)
{
string str = oin as string;
if (str != null)
{
// STRING
sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose;
comma = ",";
}
else if (null != oin as System.Collections.IEnumerable)
{
// LIST OR COLLECTION OF SOME SORT
System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable;
if (enumer != null)
{
System.Collections.IEnumerator iter = enumer.GetEnumerator();
while (iter.MoveNext())
{
object o = iter.Current;
string soin = o as string;
if (soin != null)
{
sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose;
}
else
{
sql += comma + Convert.ToString(o);
}
comma = ",";
}
}
}
else
{
// NON STRING OR LIST
sql += comma + Convert.ToString(oin);
comma = ",";
}
}
sql += ")";
requiresParam = false;
}
}
break;
case tgComparisonOperand.Between:
MySqlCommand sqlCommand = std.cmd as MySqlCommand;
sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
sql += " AND " + compareTo;
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
MySqlParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.ParameterName = compareTo;
p.Value = comparisonData.Value;
if (needsStringParameter)
{
p.DbType = DbType.String;
}
else if (std.needsIntegerParameter)
{
p.DbType = DbType.Int32;
}
}
else
{
p = new MySqlParameter(compareTo, comparisonData.Value);
}
std.cmd.Parameters.Add(p);
}
}
}
return(sql);
}
protected static string GetGroupByStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalGroupByItems != null)
{
sql += " GROUP BY ";
foreach (tgGroupByItem groupBy in iQuery.InternalGroupByItems)
{
sql += comma;
string columnName = groupBy.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
sql += columnName.Substring(1, columnName.Length - 2);
else
sql += GetExpressionColumn(std, query, groupBy.Expression, false, false);
comma = ",";
}
if (query.tg.WithRollup)
{
sql += " WITH ROLLUP";
}
}
return sql;
}
protected static string GetSetOperationStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalSetOperations != null)
{
foreach (tgSetOperation setOperation in iQuery.InternalSetOperations)
{
switch (setOperation.SetOperationType)
{
case tgSetOperationType.Union: sql += " UNION "; break;
case tgSetOperationType.UnionAll: sql += " UNION ALL "; break;
case tgSetOperationType.Intersect: sql += " INTERSECT "; break;
case tgSetOperationType.Except: sql += " EXCEPT "; break;
}
sql += BuildQuery(std, setOperation.Query);
}
}
return(sql);
}
protected static string GetMathmaticalExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgMathmaticalExpression mathmaticalExpression)
{
string sql = "(";
if (mathmaticalExpression.ItemFirst)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
sql += esArithmeticOperatorToString(mathmaticalExpression.Operator);
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
}
else
{
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
sql += GetMathmaticalExpressionLiteralType(std, mathmaticalExpression);
}
sql += esArithmeticOperatorToString(mathmaticalExpression.Operator);
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
}
sql += ")";
return sql;
}
/// <summary>
/// The Constructor
/// </summary>
public tgCase(tgDynamicQuerySerializable query)
{
this.data.Query = query;
}
protected static string GetSelectStatement(StandardProviderParameters std, tgDynamicQuerySerializable query)
{
string sql = String.Empty;
string comma = String.Empty;
bool selectAll = true;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (query.tg.Distinct) sql += " DISTINCT ";
if (query.tg.Top >= 0) sql += " TOP " + query.tg.Top.ToString() + " ";
if (iQuery.InternalSelectColumns != null)
{
selectAll = false;
foreach (tgExpression expressionItem in iQuery.InternalSelectColumns)
{
if (expressionItem.Query != null)
{
IDynamicQuerySerializableInternal iSubQuery = expressionItem.Query as IDynamicQuerySerializableInternal;
sql += comma;
if (iSubQuery.SubQueryAlias == string.Empty)
{
sql += iSubQuery.JoinAlias + ".*";
}
else
{
iSubQuery.IsInSubQuery = true;
sql += " (" + BuildQuery(std, expressionItem.Query as tgDynamicQuerySerializable) + ") AS " + iSubQuery.SubQueryAlias;
iSubQuery.IsInSubQuery = false;
}
comma = ",";
}
else
{
sql += comma;
string columnName = expressionItem.Column.Name;
if (columnName != null && columnName[0] == '<')
sql += columnName.Substring(1, columnName.Length - 2);
else
sql += GetExpressionColumn(std, query, expressionItem, false, true);
comma = ",";
}
}
sql += " ";
}
if (query.tg.CountAll)
{
selectAll = false;
sql += comma;
sql += "COUNT(*)";
if (query.tg.CountAllAlias != null)
{
// Need DBMS string delimiter here
sql += " AS " + Delimiters.StringOpen + query.tg.CountAllAlias + Delimiters.StringClose;
}
}
if (selectAll)
{
sql += "*";
}
return sql;
}
protected static string GetCaseWhenThenEnd(StandardProviderParameters std, tgDynamicQuerySerializable query, tgCase caseWhenThen)
{
string sql = string.Empty;
Tiraggo.DynamicQuery.tgCase.tgSimpleCaseData caseStatement = caseWhenThen;
tgColumnItem column = caseStatement.QueryItem;
sql += "CASE ";
List <tgComparison> list = new List <tgComparison>();
foreach (Tiraggo.DynamicQuery.tgCase.tgSimpleCaseData.tgCaseClause caseClause in caseStatement.Cases)
{
sql += " WHEN ";
if (!caseClause.When.IsExpression)
{
sql += GetComparisonStatement(std, query, caseClause.When.Comparisons, string.Empty);
}
else
{
if (!caseClause.When.Expression.IsLiteralValue)
{
sql += GetExpressionColumn(std, query, caseClause.When.Expression, false, true);
}
else
{
if (caseClause.When.Expression.LiteralValue is string)
{
sql += Delimiters.StringOpen + caseClause.When.Expression.LiteralValue + Delimiters.StringClose;
}
else
{
sql += Convert.ToString(caseClause.When.Expression.LiteralValue);
}
}
}
sql += " THEN ";
if (!caseClause.Then.IsLiteralValue)
{
sql += GetExpressionColumn(std, query, caseClause.Then, false, true);
}
else
{
if (caseClause.Then.LiteralValue is string)
{
sql += Delimiters.StringOpen + caseClause.Then.LiteralValue + Delimiters.StringClose;
}
else
{
sql += Convert.ToString(caseClause.Then.LiteralValue);
}
}
}
if (caseStatement.Else != null)
{
sql += " ELSE ";
if (!caseStatement.Else.IsLiteralValue)
{
sql += GetExpressionColumn(std, query, caseStatement.Else, false, true);
}
else
{
if (caseStatement.Else.LiteralValue is string)
{
sql += Delimiters.StringOpen + caseStatement.Else.LiteralValue + Delimiters.StringClose;
}
else
{
sql += Convert.ToString(caseStatement.Else.LiteralValue);
}
}
}
sql += " END ";
if (column.HasAlias)
{
sql += " AS " + Delimiters.AliasOpen + column.Alias + Delimiters.AliasClose;
}
else
{
sql += " AS " + Delimiters.ColumnOpen + column.Alias + Delimiters.ColumnClose;
}
return(sql);
}
protected static string GetMathmaticalExpressionColumn(StandardProviderParameters std, tgDynamicQuerySerializable query, tgMathmaticalExpression mathmaticalExpression)
{
string sql = "(";
if (mathmaticalExpression.ItemFirst)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
sql += esArithmeticOperatorToString(mathmaticalExpression);
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
sql += GetMathmaticalExpressionLiteralType(mathmaticalExpression);
}
}
else
{
if (mathmaticalExpression.SelectItem2 != null)
{
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true);
}
else
{
sql += GetMathmaticalExpressionLiteralType(mathmaticalExpression);
}
sql += esArithmeticOperatorToString(mathmaticalExpression);
sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true);
}
sql += ")";
return(sql);
}
/// <summary>
/// The Constructor
/// </summary>
public tgCase(tgDynamicQuerySerializable query, tgQueryItem queryItem)
{
this.data.Query = query;
this.data.QueryItem = queryItem;
}