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(esDataRequest request, esDynamicQuerySerializable query) { IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; esProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as esProviderSpecificMetadata; string name = String.Empty; string schema = iQuery.Schema ?? request.Schema ?? providerMetadata.Schema; if (schema != null) { name += Delimiters.TableOpen + schema + Delimiters.TableClose + "."; } name += Delimiters.TableOpen; if (query.es.QuerySource != null) { name += query.es.QuerySource; } else { name += providerMetadata.Destination; } name += Delimiters.TableClose; return(name); }
protected static string GetFromStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { 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(request, iQuery.InternalFromQuery, cmd, ref pindex); sql += ")"; if (iSubQuery.SubQueryAlias != " ") { sql += " AS " + iSubQuery.SubQueryAlias; } iSubQuery.IsInSubQuery = false; } return(sql); }
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) { name += Delimiters.TableOpen + catalog + 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 GetSetOperationStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { 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(request, setOperation.Query, cmd, ref pindex); } } 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 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); }
private void PerformAutoLogic(esDataSourceSelectEventArgs e) { esDynamicQuery query = e.Query != null ? e.Query : e.Collection.es.Query; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (this.autoPaging) { query.es.PageNumber = e.PageNumber; query.es.PageSize = e.PageSize; } if (this.autoSorting) { if (e.SortItems != null) { foreach (esDataSourceSortItem sortItem in e.SortItems) { esColumnMetadata col = e.Collection.es.Meta.Columns.FindByPropertyName(sortItem.Property); if (col != null) { query.OrderBy(col.Name, sortItem.Direction); } else if (sortItem.Property[0] == '<') { query.OrderBy(sortItem.Property, sortItem.Direction); } } } else { if (this.AutoPaging) { List <esColumnMetadata> pks = e.Collection.es.Meta.Columns.PrimaryKeys; if (pks != null) { foreach (esColumnMetadata pk in pks) { query.OrderBy(pk.Name, esOrderByDirection.Ascending); } } } } } if (this.autoSorting || this.AutoPaging) { if (e.Query != null) { IEntityCollection iColl = e.Collection as IEntityCollection; iColl.HookupQuery(query); } query.Load(); } }
public void EmptyAliasUsesColumnName() { aggTestColl.Query.Select ( aggTestColl.Query.Salary.Sum() ); Assert.IsTrue(aggTestColl.Query.Load()); IDynamicQuerySerializableInternal iQuery = aggTestColl.Query as IDynamicQuerySerializableInternal; Assert.AreEqual("Salary", iQuery.InternalSelectColumns[0].Column.Alias); //Assert.AreEqual("Salary", aggTestColl.Query.Salary.Sum().Alias); }
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 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, 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); }
/// <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); }
private static string GetSubquerySearchCondition(esDynamicQuerySerializable query) { string searchCondition = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; switch (iQuery.SubquerySearchCondition) { case esSubquerySearchCondition.All: searchCondition = "ALL"; break; case esSubquerySearchCondition.Any: searchCondition = "ANY"; break; case esSubquerySearchCondition.Some: searchCondition = "SOME"; break; } return(searchCondition); }
protected static string BuildQuery(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex) { bool paging = false; if (query.es.PageNumber.HasValue && query.es.PageSize.HasValue) { paging = true; } IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; string select = GetSelectStatement(request, query, cmd, ref pindex); string from = GetFromStatement(request, query, cmd, ref pindex); string join = GetJoinStatement(request, query, cmd, ref pindex); string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex); string groupBy = GetGroupByStatement(request, query, cmd, ref pindex); string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex); string orderBy = GetOrderByStatement(request, query, cmd, ref pindex); string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex); 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; } 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); }
/// <summary> /// This initializes the esDataRequest for the query. /// </summary> /// <param name="request">The request to populate.</param> protected void PopulateRequest(esDataRequest request) { IMetadata meta = this.Meta; esConnection conn = this.es2.Connection; esProviderSpecificMetadata providerMetadata = meta.GetProviderMetadata(conn.ProviderMetadataKey); IDynamicQuerySerializableInternal iQuery = this as IDynamicQuerySerializableInternal; if ((this.queries != null && this.queries.Count > 0) || iQuery.InternalSetOperations != null) { AssignProviderMetadata(this, new List <esDynamicQuerySerializable>()); } string catalog = conn.Catalog; string schema = conn.Schema; iData.Catalog = catalog; iData.Schema = schema; iData.DataID = meta.DataID; iData.ProviderMetadata = providerMetadata; iData.Columns = meta.Columns; request.ConnectionString = conn.ConnectionString; request.CommandTimeout = conn.CommandTimeout; request.QueryType = esQueryType.DynamicQuery; request.DynamicQuery = this; request.DataID = meta.DataID; request.ProviderMetadata = providerMetadata; request.Catalog = catalog; request.Schema = schema; request.Columns = meta.Columns; if (this.m_selectAll) { _selectAll(); } if (es.QuerySource == null || es.QuerySource.Length == 0) { es.QuerySource = providerMetadata.Source; } }
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); }
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); }
protected static string GetJoinStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { 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 "; sql += GetComparisonStatement(request, query, joinData.WhereItems, String.Empty, cmd, ref pindex); } } 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 BuildQuery(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; string select = GetSelectStatement(request, query, cmd, ref pindex); string from = GetFromStatement(request, query, cmd, ref pindex); string join = GetJoinStatement(request, query, cmd, ref pindex); string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex); string groupBy = GetGroupByStatement(request, query, cmd, ref pindex); string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex); string orderBy = GetOrderByStatement(request, query, cmd, ref pindex); string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex); string sql = String.Empty; sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy; return(sql); }
protected static string GetColumnName(esColumnItem column) { if (column.Query == null || column.Query.es.JoinAlias == " ") { return(Delimiters.ColumnOpen + column.Name + Delimiters.ColumnClose); } else { IDynamicQuerySerializableInternal iQuery = column.Query as IDynamicQuerySerializableInternal; if (iQuery.IsInSubQuery) { return(column.Query.es.JoinAlias + "." + Delimiters.ColumnOpen + column.Name + Delimiters.ColumnClose); } else { string alias = iQuery.SubQueryAlias == string.Empty ? iQuery.JoinAlias : iQuery.SubQueryAlias; return(alias + "." + Delimiters.ColumnOpen + column.Name + Delimiters.ColumnClose); } } }
public esCase When(esComparison comparison) { this.WhenItem = new esExpressionOrComparison(); this.WhenItem.Comparisons = new List <esComparison>(); if (comparison != null) { if (comparison.data.WhereExpression != null) { foreach (esComparison exp in comparison.data.WhereExpression) { esDynamicQuerySerializable q = exp.Value as esDynamicQuerySerializable; if (q != null) { IDynamicQuerySerializableInternal iQ = q as IDynamicQuerySerializableInternal; iQ.HookupProviderMetadata(q); } } this.WhenItem.Comparisons.AddRange(comparison.data.WhereExpression); } else { this.WhenItem.Comparisons.Add(comparison); } esDynamicQuerySerializable query = comparison.Value as esDynamicQuerySerializable; if (query != null) { IDynamicQuerySerializableInternal iQ = query as IDynamicQuerySerializableInternal; iQ.HookupProviderMetadata(query); } } return(this); }
protected static string GetGroupByStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { 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(groupBy.Expression, false, false); } comma = ","; } if (query.es.WithRollup) { sql += " WITH ROLLUP"; } } return(sql); }
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 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); }
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); }
/// <summary> /// /// </summary> private void AssignProviderMetadata(esDynamicQuerySerializable query, List <esDynamicQuerySerializable> beenThere) { if (beenThere.Contains(query)) { return; } beenThere.Add(query); esDynamicQuery theQuery = query as esDynamicQuery; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (theQuery != null) { esConnection conn = theQuery.es2.Connection; if (iQuery.ProviderMetadata == null) { esProviderSpecificMetadata providerMetadata = theQuery.Meta.GetProviderMetadata(conn.ProviderMetadataKey); iQuery.DataID = theQuery.Meta.DataID; iQuery.Columns = theQuery.Meta.Columns; iQuery.ProviderMetadata = providerMetadata; } iQuery.Catalog = conn.Catalog; iQuery.Schema = conn.Schema; } // This code is for proxies as they are unable to work with column and provider metadata // until serialized back to the server if (iQuery.SelectAll) { foreach (esColumnMetadata col in (esColumnMetadataCollection)iQuery.Columns) { esQueryItem item = new esQueryItem(this, col.Name, col.esType); query.Select(item); } } else { List <esQueryItem> columns = iQuery.SelectAllExcept; if (columns != null) { foreach (esColumnMetadata col in (esColumnMetadataCollection)iQuery.Columns) { bool found = false; for (int i = 0; i < columns.Count; i++) { if (col.Name == (string)columns[i]) { found = true; break; } } if (found) { continue; } esExpression item = new esQueryItem(this, col.Name, col.esType); query.Select(item); } } } foreach (esDynamicQuerySerializable subQuery in iQuery.queries.Values) { AssignProviderMetadata(subQuery, beenThere); } if (iQuery.InternalSetOperations != null) { foreach (esSetOperation setOperation in iQuery.InternalSetOperations) { AssignProviderMetadata(setOperation.Query, beenThere); } } }