示例#1
0
        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);
        }
示例#2
0
        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);
        }
示例#3
0
        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;
        }
示例#4
0
        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);
        }
示例#5
0
        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;
        }
示例#6
0
        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);
        }
示例#7
0
        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);
        }
示例#8
0
        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);
        }
示例#9
0
        /// <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);
        }
示例#10
0
        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);
        }
示例#11
0
        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;
        }
示例#12
0
        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);
        }
示例#13
0
        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;
        }
示例#14
0
        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);
        }
示例#15
0
        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);
        }
示例#16
0
        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);
        }
示例#17
0
        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);
        }
示例#18
0
        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);
        }
示例#19
0
        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);
        }
示例#20
0
        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;
        }
示例#21
0
        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;
        }
示例#22
0
 /// <summary>
 /// The tgComparison class is dynamically created by your
 /// BusinessEntity's DynamicQuery mechanism.
 /// </summary>
 public tgComparison(tgDynamicQuerySerializable query)
 {
     this.data.Query = query;
 }
示例#23
0
        /// <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;
        }
示例#24
0
        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;
        }
示例#25
0
        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);
        }
示例#26
0
        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;
        }
示例#27
0
        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;
        }
示例#28
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgSetOperation(tgDynamicQuerySerializable query)
 {
     this.Query = query;
 }
示例#29
0
        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];
        }
示例#30
0
        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);
        }
示例#31
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgJoinItem(tgDynamicQuerySerializable parentQuery)
 {
     this.parentQuery = parentQuery;
 }
示例#32
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgSetOperation(tgDynamicQuerySerializable query)
 {
     this.Query = query;
 }
示例#33
0
        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;
        }
示例#34
0
        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);
        }
示例#35
0
        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;
        }
示例#36
0
        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);
        }
示例#37
0
        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;
        }
示例#38
0
 /// <summary>
 /// The tgComparison class is dynamically created by your
 /// BusinessEntity's DynamicQuery mechanism.
 /// </summary>
 public tgComparison(tgDynamicQuerySerializable query)
 {
     this.data.Query = query;
 }
示例#39
0
        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;
        }
示例#40
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgCase(tgDynamicQuerySerializable query)
 {
     this.data.Query = query;
 }
示例#41
0
        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);
        }
示例#42
0
        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;
        }
示例#43
0
        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;
        }
示例#44
0
        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;
        }
示例#45
0
 /// <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;
 }
示例#46
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgCase(tgDynamicQuerySerializable query, tgQueryItem queryItem)
 {
     this.data.Query = query;
     this.data.QueryItem = queryItem;
 }
示例#47
0
        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;
        }
示例#48
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgJoinItem(tgDynamicQuerySerializable parentQuery)
 {
     this.parentQuery = parentQuery;
 }
示例#49
0
        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;
        }
示例#50
0
        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);
        }
示例#51
0
        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;
        }
示例#52
0
        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);
        }
示例#53
0
        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;
        }
示例#54
0
        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);
        }
示例#55
0
        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;
        }
示例#56
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgCase(tgDynamicQuerySerializable query)
 {
     this.data.Query = query;
 }
示例#57
0
        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;
        }
示例#58
0
        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);
        }
示例#59
0
        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);
        }
示例#60
0
 /// <summary>
 /// The Constructor
 /// </summary>
 public tgCase(tgDynamicQuerySerializable query, tgQueryItem queryItem)
 {
     this.data.Query     = query;
     this.data.QueryItem = queryItem;
 }