Esempio n. 1
0
        public static WhereStatement Copy(WhereStatement statement)
        {
            WhereStatement result       = new WhereStatement();
            int            currentLevel = 0;

            foreach (List <WhereClause> level in statement)
            {
                currentLevel++;
                result.Add(new List <WhereClause>());
                foreach (WhereClause clause in statement[currentLevel - 1])
                {
                    WhereClause clauseCopy = new WhereClause(clause.FieldName, clause.ComparisonOperator, clause.Value);
                    foreach (WhereClause.SubClause subClause in clause.SubClauses)
                    {
                        WhereClause.SubClause subClauseCopy = new WhereClause.SubClause(subClause.LogicOperator, subClause.ComparisonOperator, subClause.Value);
                        clauseCopy.SubClauses.Add(subClauseCopy);
                    }
                    result[currentLevel - 1].Add(clauseCopy);
                }
            }
            return(result);
        }
Esempio n. 2
0
        /// <summary>
        /// This static method combines 2 where statements with eachother to form a new statement, 两个 WhereStatement 联合
        /// </summary>
        /// <param name="statement1"></param>
        /// <param name="statement2"></param>
        /// <returns></returns>
        public static WhereStatement CombineStatements(WhereStatement statement1, WhereStatement statement2)
        {
            // statement1: {Level1}((Age<15 OR Age>=20) AND (strEmail LIKE 'e%') OR {Level2}(Age BETWEEN 15 AND 20))
            // Statement2: {Level1}((Name = 'Peter'))
            // Return statement: {Level1}((Age<15 or Age>=20) AND (strEmail like 'e%') AND (Name = 'Peter'))

            // Make a copy of statement1
            WhereStatement result = WhereStatement.Copy(statement1);

            // Add all clauses of statement2 to result
            for (int i = 0; i < statement2.ClauseLevels; i++) // for each clause level in statement2
            {
                List <WhereClause> level = statement2[i];
                foreach (WhereClause clause in level)             // for each clause in level i
                {
                    for (int j = 0; j < result.ClauseLevels; j++) // for each level in result, add the clause
                    {
                        result.AddWhereClauseToLevel(clause, j);
                    }
                }
            }

            return(result);
        }
Esempio n. 3
0
        /// <summary>
        /// 创建 查询语句
        /// </summary>
        /// <returns>Returns a string containing the query, or a DbCommand containing a command with parameters</returns>
        private string BuildQuery(bool buildCommand, out List <DbParameter> dbParameters)
        {
            List <DbParameter> parameters = new List <DbParameter>();
            string             Query      = "SELECT ";

            // Output Distinct
            if (_distinct)
            {
                Query += "DISTINCT ";
            }

            // Output Top clause
            if (!(_topClause.Quantity == 100 & _topClause.Unit == TopUnit.Percent))
            {
                Query += "TOP " + _topClause.Quantity;
                if (_topClause.Unit == TopUnit.Percent)
                {
                    Query += " PERCENT";
                }
                Query += " ";
            }

            // Output column names
            if (_selectedColumns.Count == 0)
            {
                if (_fromTables.Count == 1)
                {
                    Query += _fromTables[0] + "."; // By default only select * from the table that was selected. If there are any joins, it is the responsibility of the user to select the needed columns.
                }
                Query += "*";
            }
            else
            {
                foreach (string ColumnName in _selectedColumns)
                {
                    Query += ColumnName + ',';
                }
                Query  = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
                Query += ' ';
            }
            // Output table names
            if (_fromTables.Count > 0)
            {
                Query += " FROM ";
                foreach (string TableName in _fromTables)
                {
                    Query += TableName + ',';
                }
                Query  = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
                Query += ' ';
            }

            // Output joins
            if (_joins.Count > 0)
            {
                foreach (JoinClause Clause in _joins)
                {
                    string JoinString = "";
                    switch (Clause.JoinType)
                    {
                    case JoinType.InnerJoin: JoinString = "INNER JOIN"; break;

                    case JoinType.OuterJoin: JoinString = "OUTER JOIN"; break;

                    case JoinType.LeftJoin: JoinString = "LEFT JOIN"; break;

                    case JoinType.RightJoin: JoinString = "RIGHT JOIN"; break;
                    }
                    JoinString += " " + Clause.ToTable + " ON ";
                    JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable + '.' + Clause.FromColumn, Clause.ComparisonOperator, new SqlLiteral(Clause.ToTable + '.' + Clause.ToColumn));
                    Query      += JoinString + ' ';
                }
            }

            // Output where statement
            if (_whereStatement.ClauseLevels > 0)
            {
                if (buildCommand)
                {
                    Query += " WHERE " + _whereStatement.BuildWhereStatement(true, out parameters);
                    this._parameters.AddRange(parameters);
                }
                else
                {
                    Query += " WHERE " + _whereStatement.BuildWhereStatement();
                }
            }

            // Output GroupBy statement
            if (_groupByColumns.Count > 0)
            {
                Query += " GROUP BY ";
                foreach (string Column in _groupByColumns)
                {
                    Query += Column + ',';
                }
                Query  = Query.TrimEnd(',');
                Query += ' ';
            }

            // Output having statement
            if (_havingStatement.ClauseLevels > 0)
            {
                // Check if a Group By Clause was set
                if (_groupByColumns.Count == 0)
                {
                    throw new Exception("Having statement was set without Group By");
                }
                if (buildCommand)
                {
                    Query += " HAVING " + _havingStatement.BuildWhereStatement(true, out parameters);
                    this._parameters.AddRange(parameters);
                }
                else
                {
                    Query += " HAVING " + _havingStatement.BuildWhereStatement();
                }
            }

            // Output OrderBy statement
            Query += _orderByStatement.BuildOrderByStatement();

            // Return the build command
            dbParameters = this._parameters;
            return(Query);
        }
Esempio n. 4
0
        protected WhereStatement _havingStatement    = null;                                // HAVING 从句

        public SelectQueryBuilder(IDbHelper dbHelper)
            : base(dbHelper)
        {
            _havingStatement = new WhereStatement(dbHelper);// HAVING 从句
        }
Esempio n. 5
0
        /// <summary>
        /// Builds the select query
        /// </summary>
        /// <returns>Returns a string containing the query, or a DbCommand containing a command with parameters</returns>
        private object BuildQuery(bool buildCommand)
        {
            if (buildCommand && _dbProviderFactory == null)
            {
                throw new Exception("Cannot build a command when the Db Factory hasn't been specified. Call SetDbProviderFactory first.");
            }

            DbCommand command = null;

            if (buildCommand)
            {
                command = _dbProviderFactory.CreateCommand();
            }

            string Query = "SELECT ";

            // Output Distinct
            if (_distinct)
            {
                Query += "DISTINCT ";
            }

            // Output Top clause
            if (!(_topClause.Quantity == 100 & _topClause.Unit == TopUnit.Percent))
            {
                Query += "TOP " + _topClause.Quantity;
                if (_topClause.Unit == TopUnit.Percent)
                {
                    Query += " PERCENT";
                }
                Query += " ";
            }

            // Output column names
            if (_selectedColumns.Count == 0)
            {
                if (_selectedTables.Count == 1)
                {
                    Query += _selectedTables[0] + "."; // By default only select * from the table that was selected. If there are any joins, it is the responsibility of the user to select the needed columns.
                }
                Query += "*";
            }
            else
            {
                foreach (string ColumnName in _selectedColumns)
                {
                    Query += ColumnName + ',';
                }
                Query  = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
                Query += ' ';
            }
            // Output table names
            if (_selectedTables.Count > 0)
            {
                Query += " FROM ";
                foreach (string TableName in _selectedTables)
                {
                    Query += TableName + ',';
                }
                Query  = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
                Query += ' ';
            }

            // Output joins
            if (_joins.Count > 0)
            {
                foreach (JoinClause Clause in _joins)
                {
                    string JoinString = "";
                    switch (Clause.JoinType)
                    {
                    case JoinType.InnerJoin: JoinString = "INNER JOIN"; break;

                    case JoinType.OuterJoin: JoinString = "OUTER JOIN"; break;

                    case JoinType.LeftJoin: JoinString = "LEFT JOIN"; break;

                    case JoinType.RightJoin: JoinString = "RIGHT JOIN"; break;
                    }
                    JoinString += " " + Clause.ToTable + " ON ";
                    JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable + '.' + Clause.FromColumn, Clause.ComparisonOperator, new SqlLiteral(Clause.ToTable + '.' + Clause.ToColumn));
                    Query      += JoinString + ' ';
                }
            }

            // Output where statement
            if (_whereStatement.ClauseLevels > 0)
            {
                if (buildCommand)
                {
                    Query += " WHERE " + _whereStatement.BuildWhereStatement(true, ref command);
                }
                else
                {
                    Query += " WHERE " + _whereStatement.BuildWhereStatement();
                }
            }

            // Output GroupBy statement
            if (_groupByColumns.Count > 0)
            {
                Query += " GROUP BY ";
                foreach (string Column in _groupByColumns)
                {
                    Query += Column + ',';
                }
                Query  = Query.TrimEnd(',');
                Query += ' ';
            }

            // Output having statement
            if (_havingStatement.ClauseLevels > 0)
            {
                // Check if a Group By Clause was set
                if (_groupByColumns.Count == 0)
                {
                    throw new Exception("Having statement was set without Group By");
                }
                if (buildCommand)
                {
                    Query += " HAVING " + _havingStatement.BuildWhereStatement(true, ref command);
                }
                else
                {
                    Query += " HAVING " + _havingStatement.BuildWhereStatement();
                }
            }

            // Output OrderBy statement
            if (_orderByStatement.Count > 0)
            {
                Query += " ORDER BY ";
                foreach (OrderByClause Clause in _orderByStatement)
                {
                    string OrderByClause = "";
                    switch (Clause.SortOrder)
                    {
                    case Sorting.Ascending:
                        OrderByClause = Clause.FieldName + " ASC"; break;

                    case Sorting.Descending:
                        OrderByClause = Clause.FieldName + " DESC"; break;
                    }
                    Query += OrderByClause + ',';
                }
                Query  = Query.TrimEnd(','); // Trim de last AND inserted by foreach loop
                Query += ' ';
            }

            if (buildCommand)
            {
                // Return the build command
                command.CommandText = Query;
                return(command);
            }
            else
            {
                // Return the built query
                return(Query);
            }
        }