示例#1
0
        public virtual void Union()
        {
            SqlUnion    union = new SqlUnion();
            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10"));
            query.FromClause.BaseTable = FromTerm.Table("products");

            union.Add(query);

            query = new SelectQuery();
            query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
            query.FromClause.BaseTable = FromTerm.Table("products");

            union.Add(query, DistinctModifier.All);

            query = new SelectQuery();
            query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
            query.FromClause.BaseTable = FromTerm.Table("products");

            union.Add(query, DistinctModifier.Distinct);

            string sql = Renderer.RenderUnion(union);

            Console.WriteLine(sql);
            RunSql(sql);
        }
示例#2
0
 /// <summary></summary>
 public Join(FromTerm leftTable, FromTerm rightTable, WhereClause conditions, JoinType type)
 {
     LeftTable  = leftTable;
     RightTable = rightTable;
     Conditions = conditions;
     Type       = type;
 }
示例#3
0
        /// <summary>
        /// Generates a unique reference to an attribute-value pair table
        /// </summary>
        /// <param name="tableName">The name of the attribute-value pair table</param>
        /// <returns>A new instance of the AVP table FromTerm</returns>
        public FromTerm GetNextAVPTableReference(string tableName)
        {
            FromTerm term = FromTerm.Table(tableName, "tb" + ++this.currentAttributeTableNumber, "[dbo]");

            this.attributeTables.Add(term);
            return(term);
        }
示例#4
0
        /// <summary>Описание конструкции SELECT ... FROM</summary>
        public static From Union(Union union, string alias)
        {
            From from = new From();

            from.Term = FromTerm.Union(Qb.GetQueryObject(union), alias);
            return(from);
        }
示例#5
0
        /// <summary>Описание конструкции SELECT ... FROM</summary>
        public static From SubQuery(string subQuery, string alias)
        {
            From from = new From();

            from.Term = FromTerm.SubQuery(subQuery, alias);
            return(from);
        }
示例#6
0
        public virtual void RawSql()
        {
            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10"));
            query.FromClause.BaseTable = FromTerm.Table("products");
            RenderSelect(query);
        }
示例#7
0
 /// <summary>Аналог конструкции SELECT ... FROM</summary>
 public Select From(string tableName)
 {
     if (this.Query.FromClause.BaseTable != null)
     {
         throw new InvalidQueryException(SR.Err_RepeatFrom);
     }
     this.Query.FromClause.BaseTable = FromTerm.Table(tableName);
     return(this);
 }
示例#8
0
        /// <summary>
        /// Creates a FromTerm which represents a sub-query.
        /// </summary>
        /// <param name="query">A SelectQuery instance representing the sub query</param>
        /// <param name="alias">term alias</param>
        /// <returns>A FromTerm which represents a sub-query.</returns>
        public static FromTerm SubQuery(SelectQuery query, string alias)
        {
            FromTerm term = new FromTerm();

            term.expr  = query;
            term.alias = alias;
            term.type  = FromTermType.SubQueryObj;
            return(term);
        }
        //[ExpectedException(typeof(InvalidQueryException))]
        public void PagingNoOrder()
        {
            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name"));
            query.FromClause.BaseTable = FromTerm.Table("customers");

            Assert.Throws <InvalidQueryException>(() => RenderPage(0, 2, 3, query));
        }
示例#10
0
        public virtual void SimpleSelect()
        {
            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name"));
            query.FromClause.BaseTable = FromTerm.Table("customers");

            RenderSelect(query);
        }
示例#11
0
        public virtual void Pivot1()
        {
            SelectQuery baseQuery = new SelectQuery();

            baseQuery.Columns.Add(new SelectColumn("*"));
            baseQuery.FromClause.BaseTable = FromTerm.Table("orders");

            PivotTable pivot = new PivotTable();

            SetupPivot(pivot);
            pivot.BaseQuery  = baseQuery;
            pivot.Function   = SqlAggregationFunction.Sum;
            pivot.ValueField = "quantaty";
            pivot.RowField   = "customerId";

            PivotColumn pivotCol    = new PivotColumn("date", SqlDataType.Date);
            TimePeriod  currentYear = TimePeriod.FromToday(TimePeriodType.Year);

            pivotCol.Values.Add(PivotColumnValue.CreateRange("PreviousYears", new Range(null, currentYear.Add(-1).PeriodStartDate)));
            pivotCol.Values.Add(PivotColumnValue.CreateRange("LastYear", new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
            pivotCol.Values.Add(PivotColumnValue.CreateRange("FollowingYears", new Range(currentYear.PeriodStartDate, null)));
            pivot.Columns.Add(pivotCol);

            pivotCol = new PivotColumn("productId", SqlDataType.Number);
            pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1));
            pivotCol.Values.Add(PivotColumnValue.CreateRange("product2", new Range(2, 3)));
            pivot.Columns.Add(pivotCol);

            SelectQuery pivotQuery = pivot.BuildPivotSql();
            DataTable   data       = Fill(pivotQuery);

            if (data == null)
            {
                return;
            }
            WriteTable(data);

            Console.WriteLine("Drill down");
            SelectQuery drillDownQuery = pivot.BuildDrillDownSql(SqlConstant.Number(1), "LastYear");

            data = Fill(drillDownQuery);
            if (data == null)
            {
                return;
            }
            WriteTable(data);

            Console.WriteLine("Drill down");
            drillDownQuery = pivot.BuildDrillDownSql(null, "LastYear");
            data           = Fill(drillDownQuery);
            if (data == null)
            {
                return;
            }
            WriteTable(data);
        }
示例#12
0
        public virtual void TableSpace()
        {
            SelectQuery query = new SelectQuery();

            query.TableSpace = "sqlom.dbo";
            query.Columns.Add(new SelectColumn("name"));
            query.FromClause.BaseTable = FromTerm.Table("customers", "t");

            RenderSelect(query);
        }
示例#13
0
        public virtual void Join4()
        {
            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("*"));
            query.FromClause.BaseTable = FromTerm.Table("customers");
            query.FromClause.Join(JoinType.Cross, query.FromClause.BaseTable, FromTerm.Table("products"));

            RenderSelect(query);
        }
示例#14
0
        /// <summary>
        /// Creates a FromTerm which represents a database table or view.
        /// </summary>
        /// <param name="tableName">Name of the table or view</param>
        /// <param name="alias">Alias of the FromTerm</param>
        /// <param name="ns1">First table namespace</param>
        /// <param name="ns2">Second table namespace</param>
        /// <returns>A FromTerm which represents a database table or view</returns>
        /// <remarks>Use the <paramref name="ns1"/> parameter to set table database and <paramref name="ns2"/> to set table owner.</remarks>
        public static FromTerm Table(string tableName, string alias, string ns1, string ns2)
        {
            FromTerm term = new FromTerm();

            term.expr  = tableName;
            term.alias = alias;
            term.type  = FromTermType.Table;
            term.ns1   = ns1;
            term.ns2   = ns2;
            return(term);
        }
示例#15
0
        public virtual void TableSpace2()
        {
            SelectQuery query = new SelectQuery();

            query.TableSpace = "foo.bar";
            query.Columns.Add(new SelectColumn("*"));
            query.FromClause.BaseTable = FromTerm.Table("customers", "t1", "sqlom", "dbo");
            query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, FromTerm.Table("customers", "t2", "dbo"), "customerId", "customerId");

            RenderSelect(query);
        }
示例#16
0
        /// <summary>
        /// Renders a row count SELECT statement.
        /// </summary>
        /// <param name="query">Query definition to count rows for</param>
        /// <returns>Generated SQL statement</returns>
        /// <remarks>
        /// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate.
        /// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method.
        /// </remarks>
        public override string RenderRowCount(SelectQuery query)
        {
            string baseSql = RenderSelect(query, false);

            SelectQuery  countQuery = new SelectQuery();
            SelectColumn col        = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count);

            countQuery.Columns.Add(col);
            countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
            return(RenderSelect(countQuery));
        }
示例#17
0
 /// <summary>
 /// Creates a SelectColumn with a column name, table, column alias and optional aggregation function
 /// </summary>
 /// <param name="columnName">Name of a column</param>
 /// <param name="table">The table this field belongs to</param>
 /// <param name="columnAlias">Alias of the column</param>
 /// <param name="function">Aggregation function to be applied to the column. Use SqlAggregationFunction.None to specify that no function should be applied.</param>
 public SelectColumn(string columnName, FromTerm table, string columnAlias, SqlAggregationFunction function)
 {
     if (function == SqlAggregationFunction.None)
     {
         expr = SqlExpression.Field(columnName, table);
     }
     else
     {
         expr = SqlExpression.Function(function, SqlExpression.Field(columnName, table));
     }
     this.alias = columnAlias;
 }
示例#18
0
        public virtual void Paging2()
        {
            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name"));
            query.FromClause.BaseTable = FromTerm.Table("customers");
            query.OrderByTerms.Add(new OrderByTerm("name", null, OrderByDirection.Descending));
            query.OrderByTerms.Add(new OrderByTerm("birthDate", null, OrderByDirection.Ascending));

            int count = RenderRowCount(query);

            RenderPage(1, 2, count, query);
        }
示例#19
0
        public virtual void Join3()
        {
            FromTerm tProducts = FromTerm.Table("products", "p");
            FromTerm tOrders   = FromTerm.Table("orders", "o");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tProducts));
            query.Columns.Add(new SelectColumn("date", tOrders));
            query.FromClause.BaseTable = tOrders;
            query.FromClause.Join(JoinType.Right, query.FromClause.BaseTable, tProducts, "productId", "productId");

            RenderSelect(query);
        }
示例#20
0
        public virtual void ComplicatedQuery()
        {
            FromTerm tCustomers = FromTerm.Table("customers", "c");
            FromTerm tProducts  = FromTerm.Table("products", "p");
            FromTerm tOrders    = FromTerm.Table("orders", "o");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers));
            query.Columns.Add(new SelectColumn(SqlExpression.IfNull(SqlExpression.Field("name", tCustomers), SqlExpression.Constant(SqlConstant.String("name"))), "notNull"));
            query.Columns.Add(new SelectColumn(SqlExpression.Null(), "nullValue"));
            query.Columns.Add(new SelectColumn("name", tProducts, "productName", SqlAggregationFunction.None));
            query.Columns.Add(new SelectColumn("price", tProducts));

            query.FromClause.BaseTable = tCustomers;
            query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
            query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");

            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.String("Dohe"), SqlExpression.Field("name", tCustomers), CompareOperator.NotEqual));
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("J%"), CompareOperator.Like));
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Date(DateTime.Now), SqlExpression.Field("date", tOrders), CompareOperator.Greater));
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.Greater));
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.GreaterOrEqual));
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(11.5), CompareOperator.LessOrEqual));
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.BitwiseAnd));

            WhereClause group = new WhereClause(WhereClauseRelationship.Or);

            group.Terms.Add(WhereTerm.CreateBetween(SqlExpression.Field("price", tProducts), SqlExpression.Number(1), SqlExpression.Number(10)));
            group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Nail", "Hamer", "Skrewdriver" })));
            group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), "select name from products"));
            group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Unkown" })));
            group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), "select name from products"));
            group.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("name", tProducts)));
            group.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field("name", tProducts)));
            group.Terms.Add(WhereTerm.CreateExists("select productId from products"));
            group.Terms.Add(WhereTerm.CreateNotExists("select productId from products"));

            query.WherePhrase.SubClauses.Add(group);

            query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending));
            query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending));

            query.Distinct = true;
            query.Top      = 10;

            RenderSelect(query);
        }
示例#21
0
        public virtual void Join2()
        {
            FromTerm tCustomers = FromTerm.Table("customers");
            FromTerm tProducts  = FromTerm.Table("products", "p");
            FromTerm tOrders    = FromTerm.Table("orders", "o");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers));
            query.Columns.Add(new SelectColumn("name", tProducts));
            query.FromClause.BaseTable = tCustomers;
            query.FromClause.Join(JoinType.Left, tCustomers, tOrders, new JoinCondition("customerId"));
            query.FromClause.Join(JoinType.Left, tOrders, tProducts, new JoinCondition("productId", "productId"));

            RenderSelect(query);
        }
示例#22
0
        /// <summary>
        /// Renders a SELECT statement
        /// </summary>
        /// <param name="query">Query definition</param>
        /// <returns>Generated SQL statement</returns>
        public override string RenderSelect(SelectQuery query)
        {
            if (query.Top > -1 && query.OrderByTerms.Count > 0)
            {
                string baseSql = RenderSelect(query, -1);

                SelectQuery  countQuery = new SelectQuery();
                SelectColumn col        = new SelectColumn("*");
                countQuery.Columns.Add(col);
                countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
                return(RenderSelect(countQuery, query.Top));
            }
            else
            {
                return(RenderSelect(query, query.Top));
            }
        }
示例#23
0
        /// <summary>
        /// Builds the query
        /// </summary>
        /// <param name="whereClause">The WHERE conditions</param>
        /// <param name="maxResults">The number of results to return</param>
        /// <returns>The constructed SQL query</returns>
        private string BuildQuery(WhereClause whereClause, int maxResults)
        {
            SelectQuery         query             = new SelectQuery();
            AcmaSchemaAttribute objectIdAttribute = ActiveConfig.DB.GetAttribute("objectId");
            FromTerm            objectBaseTable   = objectIdAttribute.DBTable;

            query.Columns.Add(new SelectColumn("*", objectBaseTable));
            query.Distinct             = true;
            query.FromClause.BaseTable = objectBaseTable;

            foreach (FromTerm term in this.attributeTables)
            {
                query.FromClause.Join(JoinType.Left, objectBaseTable, term, new JoinCondition("objectId", "objectId"));
            }

            if (maxResults > 0)
            {
                query.Top = maxResults;
            }

            //if (this.parameters.Count == 0)
            //{
            //    return null;
            //}

            // Prevents returning the querying object as a search result
            if (Guid.Empty != this.SourceObjectId)
            {
                string param = this.AddParameter(this.SourceObjectId);
                query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(objectIdAttribute.Name, objectIdAttribute.DBTable), SqlExpression.Parameter(param), CompareOperator.NotEqual));
            }

            if (this.OrderByTerms != null)
            {
                query.OrderByTerms.AddRange(this.OrderByTerms);
            }

            query.WherePhrase.SubClauses.Add(whereClause);

            SqlServerRenderer renderer = new SqlServerRenderer();

            return(renderer.RenderSelect(query));
        }
示例#24
0
        /// <summary>
        /// Renders a single FROM term
        /// </summary>
        /// <param name="builder"></param>
        /// <param name="table"></param>
        /// <param name="tableSpace">Common prefix for all tables in the term</param>
        protected virtual void RenderFromTerm(StringBuilder builder, FromTerm table, string tableSpace)
        {
            if (table.Type == FromTermType.Table)
            {
                if (table.Ns1 != null)
                {
                    TableNamespace(builder, table.Ns1);
                }
                if (table.Ns2 != null)
                {
                    TableNamespace(builder, table.Ns2);
                }
                if (table.Ns1 == null && table.Ns2 == null && tableSpace != null)
                {
                    TableNamespace(builder, tableSpace);
                }
                Identifier(builder, (string)table.Expression);
            }
            else if (table.Type == FromTermType.SubQuery)
            {
                builder.AppendFormat("( {0} )", table.Expression);
            }
            else if (table.Type == FromTermType.SubQueryObj)
            {
                builder.AppendFormat("( {0} )", RenderSelect((SelectQuery)table.Expression));
            }
            else
            {
                throw new InvalidQueryException("Unknown FromExpressionType: " + table.Type.ToString());
            }

            if (table.Alias != null)
            {
                builder.AppendFormat(" ");

                if (Prealias != string.Empty)
                {
                    builder.AppendFormat("as ");
                }

                Identifier(builder, table.Alias);
            }
        }
示例#25
0
        public virtual void Join5()
        {
            FromTerm tCustomers = FromTerm.Table("customers");
            FromTerm tProducts  = FromTerm.Table("products", "p");
            FromTerm tOrders    = FromTerm.Table("orders", "o");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers));
            query.Columns.Add(new SelectColumn("name", tProducts));
            query.FromClause.BaseTable = tCustomers;
            query.FromClause.Join(JoinType.Left, tCustomers, tOrders, new JoinCondition("customerId"), new JoinCondition("customerId"));
            WhereClause condition = new WhereClause(WhereClauseRelationship.Or);

            condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
            condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("orderId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
            query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition);

            RenderSelect(query);
        }
示例#26
0
        public virtual void Paging4()
        {
            FromTerm tCustomers = FromTerm.Table("customers");
            FromTerm tProducts  = FromTerm.Table("products", "p");
            FromTerm tOrders    = FromTerm.Table("orders", "o");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers, "customerName"));
            query.Columns.Add(new SelectColumn("name", tProducts, "productName"));
            query.FromClause.BaseTable = tCustomers;
            query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId");
            query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");

            query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending));

            int count = RenderRowCount(query);

            RenderPage(0, 2, count, query);
        }
示例#27
0
        public virtual void SubQuery()
        {
            FromTerm tCustomers = FromTerm.Table("customers");

            SelectQuery subQuery = new SelectQuery();

            subQuery.Top = 1;
            subQuery.Columns.Add(new SelectColumn("name", tCustomers));
            subQuery.FromClause.BaseTable = FromTerm.Table("customers");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers));
            query.Columns.Add(new SelectColumn(SqlExpression.SubQuery("select count(*) from customers"), "cnt"));
            query.Columns.Add(new SelectColumn(SqlExpression.SubQuery(subQuery), "subq"));
            query.FromClause.BaseTable = tCustomers;
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("customerId", tCustomers), SqlExpression.SubQuery("select customerId from customers where name='John'"), CompareOperator.Equal));

            RenderSelect(query);
        }
示例#28
0
        public virtual void GroupByWithCube()
        {
            FromTerm tCustomers = FromTerm.Table("customers");
            FromTerm tProducts  = FromTerm.Table("products", "p");
            FromTerm tOrders    = FromTerm.Table("orders", "o");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers));
            query.Columns.Add(new SelectColumn("price", tProducts, "sum", SqlAggregationFunction.Sum));

            query.FromClause.BaseTable = tCustomers;
            query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId");
            query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");

            query.GroupByTerms.Add(new GroupByTerm("name", tCustomers));

            query.GroupByWithCube = true;

            RenderSelect(query);
        }
示例#29
0
        public virtual void Having()
        {
            FromTerm tCustomers = FromTerm.Table("customers");
            FromTerm tProducts  = FromTerm.Table("products", "p");
            FromTerm tOrders    = FromTerm.Table("orders", "o");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers));
            query.Columns.Add(new SelectColumn("price", tProducts, "sum", SqlAggregationFunction.Sum));

            query.FromClause.BaseTable = tCustomers;
            query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId");
            query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");

            query.GroupByTerms.Add(new GroupByTerm("name", tCustomers));

            query.HavingPhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));

            RenderSelect(query);
        }
示例#30
0
        public virtual void Parameter()
        {
            FromTerm tCustomers = FromTerm.Table("customers");

            SelectQuery query = new SelectQuery();

            query.Columns.Add(new SelectColumn("name", tCustomers));
            query.FromClause.BaseTable = tCustomers;
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Parameter(GetParameterName("pName")), SqlExpression.Field("name", tCustomers), CompareOperator.Equal));

            string sql = Renderer.RenderSelect(query);

            Console.WriteLine(sql);
            if (connection != null)
            {
                IDbCommand command = connection.CreateCommand();
                command.CommandText = sql;
                command.Parameters.Add(CreateParameter("@pName", "John"));
                command.ExecuteNonQuery();
            }
        }
示例#31
0
		/// <summary>
		/// Renders a single FROM term
		/// </summary>
		/// <param name="builder"></param>
		/// <param name="table"></param>
		/// <param name="tableSpace">Common prefix for all tables in the term</param>
		protected virtual void RenderFromTerm(StringBuilder builder, FromTerm table, string tableSpace)
		{
			if (table.Type == FromTermType.Table)
			{
				if (table.Ns1 != null)
				{
					TableNamespace(builder, table.Ns1);
				}
				if (table.Ns2 != null)
				{
					TableNamespace(builder, table.Ns2);
				}
				if (table.Ns1 == null && table.Ns2 == null && tableSpace != null)
				{
					TableNamespace(builder, tableSpace);
				}
				Identifier(builder, (string) table.Expression);
			}
			else if (table.Type == FromTermType.SubQuery)
			{
				builder.AppendFormat("( {0} )", table.Expression);
			}
			else if (table.Type == FromTermType.SubQueryObj)
			{
				builder.AppendFormat("( {0} )", RenderSelect((SelectQuery) table.Expression));
			}
			else
			{
				throw new InvalidQueryException("Unknown FromExpressionType: " + table.Type.ToString());
			}

			if (table.Alias != null)
			{
				builder.AppendFormat(" ");

				if (Prealias != string.Empty)
				{
					builder.AppendFormat("as ");
				}

				Identifier(builder, table.Alias);
			}
		}
示例#32
0
		private void ApplyOrderBy(OrderByTermCollection terms, SelectQuery orderQuery, bool forward, FromTerm table)
		{
			foreach (OrderByTerm expr in terms)
			{
				OrderByDirection dir = expr.Direction;

				//Reverse order direction if required
				if (!forward && dir == OrderByDirection.Ascending)
				{
					dir = OrderByDirection.Descending;
				}
				else if (!forward && dir == OrderByDirection.Descending)
				{
					dir = OrderByDirection.Ascending;
				}

				orderQuery.OrderByTerms.Add(new OrderByTerm(FormatSortFieldName(expr.Field.ToString()), table, dir));
			}
		}