protected static string BuildQuery(StandardProviderParameters std, esDynamicQuerySerializable query) { IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; bool selectAll = (iQuery.InternalSelectColumns == null && !query.es.CountAll); bool paging = false; if (query.es.PageNumber.HasValue && query.es.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.es.PageNumber.Value - 1) * query.es.PageSize.Value) + 1; int endRow = begRow + (query.es.PageSize.Value - 1); // The WITH statement sql += "WITH \"withStatement\" AS ("; if (selectAll) { sql += "SELECT " + Delimiters.TableOpen + query.es.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.es.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 GetFromStatement(StandardProviderParameters std, esDynamicQuerySerializable 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, esDynamicQuerySerializable query) { bool paging = false; if (query.es.PageNumber.HasValue && query.es.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.es.PageNumber.Value - 1) * query.es.PageSize.Value) + 1; int endRow = begRow + (query.es.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; }
public static SqlCommand PrepareCommand(esDataRequest request) { StandardProviderParameters std = new StandardProviderParameters(); std.cmd = new SqlCommand(); std.pindex = NextParamIndex(std.cmd); std.request = request; string sql = BuildQuery(std, request.DynamicQuery); std.cmd.CommandText = sql; return (SqlCommand)std.cmd; }
protected static string BuildQuery(StandardProviderParameters std, esDynamicQuerySerializable query) { bool paging = false; if (query.es.PageNumber.HasValue && query.es.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.es.PageNumber.Value - 1) * query.es.PageSize.Value); sql += " LIMIT " + query.es.PageSize.ToString(); sql += " OFFSET " + begRow.ToString() + " "; } else if (query.es.Top >= 0) { sql += " LIMIT " + query.es.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 BuildQuery(StandardProviderParameters std, esDynamicQuerySerializable 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 GetOrderByStatement(StandardProviderParameters std, esDynamicQuerySerializable query) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalOrderByItems != null) { sql += " ORDER BY "; foreach (esOrderByItem 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 == esOrderByDirection.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 == esOrderByDirection.Ascending) sql += " ASC"; else sql += " DESC"; } comma = ","; } } return sql; }
protected static string ApplyWhereSubOperations(StandardProviderParameters std, esDynamicQuerySerializable query, esComparison.esComparisonData 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 GetMathmaticalExpressionColumn(StandardProviderParameters std, esDynamicQuerySerializable query, esMathmaticalExpression 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; }
protected static string GetExpressionColumn(StandardProviderParameters std, esDynamicQuerySerializable query, esExpression 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 GetMathmaticalExpressionColumn(StandardProviderParameters std, esDynamicQuerySerializable query, esMathmaticalExpression mathmaticalExpression) { string sql = "("; if (mathmaticalExpression.ItemFirst) { sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true); switch (mathmaticalExpression.Operator) { case esArithmeticOperator.Add: sql += "+"; break; case esArithmeticOperator.Subtract: sql += "-"; break; case esArithmeticOperator.Multiply: sql += "*"; break; case esArithmeticOperator.Divide: sql += "/"; break; case esArithmeticOperator.Modulo: sql += "%"; break; } if (mathmaticalExpression.SelectItem2 != null) { sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem2, true, true); } else { switch (mathmaticalExpression.LiteralType) { case esSystemType.String: sql += Delimiters.StringOpen + (string)mathmaticalExpression.Literal + Delimiters.StringClose; break; case esSystemType.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 esSystemType.String: sql += Delimiters.StringOpen + (string)mathmaticalExpression.Literal + Delimiters.StringClose; break; case esSystemType.DateTime: sql += Delimiters.StringOpen + ((DateTime)(mathmaticalExpression.Literal)).ToShortDateString() + Delimiters.StringClose; break; default: sql += Convert.ToString(mathmaticalExpression.Literal); break; } } switch (mathmaticalExpression.Operator) { case esArithmeticOperator.Add: sql += "+"; break; case esArithmeticOperator.Subtract: sql += "-"; break; case esArithmeticOperator.Multiply: sql += "*"; break; case esArithmeticOperator.Divide: sql += "/"; break; case esArithmeticOperator.Modulo: sql += "%"; break; } sql += GetExpressionColumn(std, query, mathmaticalExpression.SelectItem1, true, true); } sql += ")"; return sql; }
protected static string ApplyWhereSubOperations(StandardProviderParameters std, esDynamicQuerySerializable query, esComparison.esComparisonData 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 GetMathmaticalExpressionColumn(StandardProviderParameters std, esDynamicQuerySerializable query, esMathmaticalExpression 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, esDynamicQuerySerializable query, esMathmaticalExpression 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 BuildSubOperationsSql(StandardProviderParameters std, string columnName, List<esQuerySubOperator> subOperators) { string sql = string.Empty; subOperators.Reverse(); Stack<object> stack = new Stack<object>(); if (subOperators != null) { foreach (esQuerySubOperator op in subOperators) { switch (op.SubOperator) { case esQuerySubOperatorType.ToLower: sql += "LCase("; stack.Push(")"); break; case esQuerySubOperatorType.ToUpper: sql += "UCase("; stack.Push(")"); break; case esQuerySubOperatorType.LTrim: sql += "LTrim("; stack.Push(")"); break; case esQuerySubOperatorType.RTrim: sql += "RTrim("; stack.Push(")"); break; case esQuerySubOperatorType.Trim: sql += "Trim("; stack.Push(")"); break; case esQuerySubOperatorType.SubString: sql += "Mid("; stack.Push(")"); stack.Push(op.Parameters["length"]); stack.Push(","); if (op.Parameters.ContainsKey("start")) { stack.Push(op.Parameters["start"]); stack.Push(","); } else { // They didn't pass in start so we start // at the beginning stack.Push(1); stack.Push(","); } break; case esQuerySubOperatorType.Coalesce: sql += "Coalesce("; stack.Push(")"); stack.Push(op.Parameters["expressions"]); stack.Push(","); break; case esQuerySubOperatorType.Date: sql += "DATEADD(\"d\", 0, DATEDIFF(\"d\", 0,"; stack.Push("))"); break; case esQuerySubOperatorType.Length: sql += "Len("; stack.Push(")"); break; case esQuerySubOperatorType.Round: sql += "Round("; stack.Push(")"); stack.Push(op.Parameters["SignificantDigits"]); stack.Push(","); break; case esQuerySubOperatorType.DatePart: sql += "DatePart(\""; sql += op.Parameters["DatePart"]; sql += "\","; stack.Push(")"); break; case esQuerySubOperatorType.Avg: sql += "Round(Avg("; stack.Push("), 10)"); break; case esQuerySubOperatorType.Count: sql += "Count("; stack.Push(")"); break; case esQuerySubOperatorType.Max: sql += "Max("; stack.Push(")"); break; case esQuerySubOperatorType.Min: sql += "Min("; stack.Push(")"); break; case esQuerySubOperatorType.StdDev: sql += "Round(StDev("; stack.Push("), 10)"); break; case esQuerySubOperatorType.Sum: sql += "Sum("; stack.Push(")"); break; case esQuerySubOperatorType.Var: sql += "Round(Var("; stack.Push("), 10)"); break; case esQuerySubOperatorType.Cast: sql += "Cast("; stack.Push(")"); if (op.Parameters.Count > 1) { stack.Push(")"); if (op.Parameters.Count == 2) { stack.Push(op.Parameters["length"].ToString()); } else { stack.Push(op.Parameters["scale"].ToString()); stack.Push(","); stack.Push(op.Parameters["precision"].ToString()); } stack.Push("("); } stack.Push(GetCastSql((esCastType)op.Parameters["esCastType"])); stack.Push(" AS "); break; } } sql += columnName; while (stack.Count > 0) { sql += stack.Pop().ToString(); } } return sql; }
protected static string GetGroupByStatement(StandardProviderParameters std, esDynamicQuerySerializable query) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalGroupByItems != null) { sql += " GROUP BY "; foreach (esGroupByItem 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.es.WithRollup) { sql += " WITH ROLLUP"; } } return sql; }
protected static string GetSetOperationStatement(StandardProviderParameters std, esDynamicQuerySerializable query) { string sql = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalSetOperations != null) { foreach (esSetOperation setOperation in iQuery.InternalSetOperations) { switch (setOperation.SetOperationType) { case esSetOperationType.Union: sql += " UNION "; break; case esSetOperationType.UnionAll: sql += " UNION ALL "; break; case esSetOperationType.Intersect: sql += " INTERSECT "; break; case esSetOperationType.Except: sql += " EXCEPT "; break; } sql += BuildQuery(std, setOperation.Query); } } return sql; }
protected static string GetSelectStatement(StandardProviderParameters std, esDynamicQuerySerializable query) { string sql = String.Empty; string comma = String.Empty; bool selectAll = true; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (query.es.Distinct) sql += " DISTINCT "; if (query.es.Top >= 0) sql += " TOP " + query.es.Top.ToString() + " "; if (iQuery.InternalSelectColumns != null) { selectAll = false; foreach (esExpression 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 esDynamicQuerySerializable) + ") 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.es.CountAll) { selectAll = false; sql += comma; sql += "COUNT(*)"; if (query.es.CountAllAlias != null) { // Need DBMS string delimiter here sql += " AS " + Delimiters.StringOpen + query.es.CountAllAlias + Delimiters.StringClose; } } if (selectAll) { sql += "*"; } return sql; }
protected static string GetCaseWhenThenEnd(StandardProviderParameters std, esDynamicQuerySerializable query, esCase caseWhenThen) { string sql = string.Empty; EntitySpaces.DynamicQuery.esCase.esSimpleCaseData caseStatement = caseWhenThen; esColumnItem column = caseStatement.QueryItem; sql += Delimiters.ColumnOpen + column.Alias + Delimiters.ColumnClose + " = "; sql += "CASE "; List<esComparison> list = new List<esComparison>(); foreach (EntitySpaces.DynamicQuery.esCase.esSimpleCaseData.esCaseClause 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 "; return sql; }
protected static string GetJoinStatement(StandardProviderParameters std, esDynamicQuerySerializable query) { string sql = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalJoinItems != null) { foreach (esJoinItem joinItem in iQuery.InternalJoinItems) { esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem; switch (joinData.JoinType) { case esJoinType.InnerJoin: sql += " INNER JOIN "; break; case esJoinType.LeftJoin: sql += " LEFT JOIN "; break; case esJoinType.RightJoin: sql += " RIGHT JOIN "; break; case esJoinType.FullJoin: sql += " FULL JOIN "; break; } IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal; sql += Shared.CreateFullName(std.request, joinData.Query); sql += " " + iSubQuery.JoinAlias; if (query.es.WithNoLock == true) { sql += " WITH (NOLOCK)"; } sql += " ON "; sql += GetComparisonStatement(std, query, joinData.WhereItems, String.Empty); } } return sql; }
protected static string GetMathmaticalExpressionLiteralType(StandardProviderParameters std, esMathmaticalExpression mathmaticalExpression) { switch (mathmaticalExpression.LiteralType) { case esSystemType.String: return Delimiters.StringOpen + (string)mathmaticalExpression.Literal + Delimiters.StringClose; case esSystemType.DateTime: return Delimiters.StringOpen + ((DateTime)(mathmaticalExpression.Literal)).ToShortDateString() + Delimiters.StringClose; default: return Convert.ToString(mathmaticalExpression.Literal); } }
protected static string GetComparisonStatement(StandardProviderParameters std, esDynamicQuerySerializable query, List<esComparison> items, string prefix) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; DbType paramType = DbType.String; string compareTo = String.Empty; foreach (esComparison comparisonItem in items) { paramType = DbType.String; esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; std.needsIntegerParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) sql += "("; else sql += ")"; continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case esConjunction.And: sql += " AND "; break; case esConjunction.Or: sql += " OR "; break; case esConjunction.AndNot: sql += " AND NOT "; break; case esConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary<string, SqlParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)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 esDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)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 esComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case esComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case esComparisonOperand.Equal: if(comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo; else sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData); break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo; else sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData); break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo; else sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData); break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo; else sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData); break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo; else sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData); break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo; else sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData); break; case esComparisonOperand.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 esComparisonOperand.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 esComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; paramType = DbType.AnsiStringFixedLength; needsStringParameter = true; break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL"; requiresParam = false; break; case esComparisonOperand.In: case esComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(std, query, comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.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 esComparisonOperand.Between: SqlCommand sqlCommand = std.cmd as SqlCommand; 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; esColumnMetadataCollection columns = (esColumnMetadataCollection)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) { SqlParameter 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 = paramType; } else if (std.needsIntegerParameter) { p.DbType = DbType.Int32; } } else { p = new SqlParameter(compareTo, comparisonData.Value); } std.cmd.Parameters.Add(p); } } } return sql; }
protected static string BuildSubOperationsSql(StandardProviderParameters std, string columnName, List<esQuerySubOperator> subOperators) { string sql = string.Empty; subOperators.Reverse(); Stack<object> stack = new Stack<object>(); if (subOperators != null) { foreach (esQuerySubOperator op in subOperators) { switch (op.SubOperator) { case esQuerySubOperatorType.ToLower: sql += "LOWER("; stack.Push(")"); break; case esQuerySubOperatorType.ToUpper: sql += "UPPER("; stack.Push(")"); break; case esQuerySubOperatorType.LTrim: sql += "LTRIM("; stack.Push(")"); break; case esQuerySubOperatorType.RTrim: sql += "RTRIM("; stack.Push(")"); break; case esQuerySubOperatorType.Trim: sql += "LTRIM(RTRIM("; stack.Push("))"); break; case esQuerySubOperatorType.SubString: sql += "SUBSTRING("; stack.Push(")"); stack.Push(op.Parameters["length"]); stack.Push(","); if (op.Parameters.ContainsKey("start")) { stack.Push(op.Parameters["start"]); stack.Push(","); } else { // They didn't pass in start so we start // at the beginning stack.Push(1); stack.Push(","); } break; case esQuerySubOperatorType.Coalesce: sql += "COALESCE("; stack.Push(")"); stack.Push(op.Parameters["expressions"]); stack.Push(","); break; case esQuerySubOperatorType.Date: sql += "DATEADD(dd, 0, DATEDIFF(dd, 0,"; stack.Push("))"); break; case esQuerySubOperatorType.Length: sql += "LEN("; stack.Push(")"); break; case esQuerySubOperatorType.Round: sql += "ROUND("; stack.Push(")"); stack.Push(op.Parameters["SignificantDigits"]); stack.Push(","); break; case esQuerySubOperatorType.DatePart: std.needsIntegerParameter = true; sql += "DATEPART("; sql += op.Parameters["DatePart"]; sql += ","; stack.Push(")"); break; case esQuerySubOperatorType.Avg: sql += "AVG("; stack.Push(")"); break; case esQuerySubOperatorType.Count: sql += "COUNT("; stack.Push(")"); break; case esQuerySubOperatorType.Max: sql += "MAX("; stack.Push(")"); break; case esQuerySubOperatorType.Min: sql += "MIN("; stack.Push(")"); break; case esQuerySubOperatorType.StdDev: sql += "STDEV("; stack.Push(")"); break; case esQuerySubOperatorType.Sum: sql += "SUM("; stack.Push(")"); break; case esQuerySubOperatorType.Var: sql += "VAR("; stack.Push(")"); break; case esQuerySubOperatorType.Cast: sql += "CAST("; stack.Push(")"); if(op.Parameters.Count > 1) { stack.Push(")"); if (op.Parameters.Count == 2) { stack.Push(op.Parameters["length"].ToString()); } else { stack.Push(op.Parameters["scale"].ToString()); stack.Push(","); stack.Push(op.Parameters["precision"].ToString()); } stack.Push("("); } stack.Push(GetCastSql((esCastType)op.Parameters["esCastType"])); stack.Push(" AS "); break; } } sql += columnName; while (stack.Count > 0) { sql += stack.Pop().ToString(); } } return sql; }
protected static string GetJoinStatement(StandardProviderParameters std, esDynamicQuerySerializable query) { string sql = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalJoinItems != null) { foreach (esJoinItem joinItem in iQuery.InternalJoinItems) { esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem; switch (joinData.JoinType) { case esJoinType.InnerJoin: sql += " INNER JOIN "; break; case esJoinType.LeftJoin: sql += " LEFT JOIN "; break; case esJoinType.RightJoin: sql += " RIGHT JOIN "; break; case esJoinType.FullJoin: sql += " FULL JOIN "; break; } IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal; sql += Shared.CreateFullName((esProviderSpecificMetadata)iSubQuery.ProviderMetadata); sql += " " + iSubQuery.JoinAlias + " ON "; foreach (esComparison comparisonItem in joinData.WhereItems) { esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) sql += "("; else sql += ")"; continue; } if (comparisonData.IsConjunction) { if (comparisonData.Conjunction == esConjunction.And) sql += " AND "; else sql += " OR "; continue; } sql += GetColumnName(comparisonData.Column); switch (comparisonData.Operand) { case esComparisonOperand.Equal: sql += " = "; break; case esComparisonOperand.NotEqual: sql += " <> "; break; case esComparisonOperand.GreaterThan: sql += " > "; break; case esComparisonOperand.LessThan: sql += " < "; break; case esComparisonOperand.LessThanOrEqual: sql += " <= "; break; case esComparisonOperand.GreaterThanOrEqual: sql += " >= "; break; case esComparisonOperand.IsNull: sql += " IS NULL "; break; case esComparisonOperand.IsNotNull: sql += " IS NOT NULL "; break; case esComparisonOperand.In: sql += " IN "; break; case esComparisonOperand.NotIn: sql += " NOT IN "; break; } if (comparisonData.ComparisonColumn.Name != null) { sql += GetColumnName(comparisonData.ComparisonColumn); } else { esDynamicQuerySerializable subQuery = comparisonData.Value as esDynamicQuerySerializable; if (subQuery != null) { sql += GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ")"; } else if (comparisonData.Value != null) { // Handle Literals sql += comparisonData.Value.ToString(); } } } sql += ")"; } } return sql; }