//[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));
}
public virtual void TableSpace1()
{
SelectQuery query = new SelectQuery();
query.TableSpace = "sqlom.dbo";
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers", "t");
RenderSelect(query);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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();
}
}
protected override poSelectQuery BuildQuery()
{
var selQuery = new poSelectQuery("heItems");
selQuery.AddColumn("heID", selQuery.BaseTable, "ID");
selQuery.AddColumn("heCode", selQuery.BaseTable, "heCode");
selQuery.AddColumn("heName", selQuery.BaseTable, "heName");
selQuery.AddColumns("heFactoryCode", "heSeasID", "heAMsntID", "heAuxiliaryCode", "heDetailedDescr", "heClassification", "heKind",
"heCompID", "heActive", "heNameSoundex", "heRefNumber", "heIaccID", "heProductionCatID", "heBlockSales", "heBlockPurchases", "heBlockWarehouses", "hePartInStockControl"
, "heSalInvAttrSpPrice", "hePurInvAttrSpPrice", "heCat01ID", "heCat02ID", "HECALCFROMVALUE",
"heRetailPrice", "heWholeSalesPrice");
selQuery.AddColumns("heSplrID", "heType");
selQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heCompID", selQuery.FromClause.BaseTable), selQuery.ParameterValue(AppContext.GetService <heSystemParams>().CurrentCompanyID), CompareOperator.Equal));
selQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heType", selQuery.FromClause.BaseTable), selQuery.ParameterValue(Convert.ToInt16(hewItemsDataObjectProxy.ItemTypeEnum.Service)), CompareOperator.Equal));
selQuery.AddEnumColumn(AppContext, selQuery.FromClause.BaseTable, "Hercules;heItems;heKind");
selQuery.AddEnumColumn(AppContext, selQuery.FromClause.BaseTable, "Hercules;heItems;heClassification");
var AMesUnits = FromTerm.Table("heMeasurementUnits", "MU1");
selQuery.AddColumn("heName", AMesUnits, "AMUNAME");
selQuery.FromClause.Join(JoinType.Inner, selQuery.BaseTable, AMesUnits, "heAMsntID", "heID");
var vats = FromTerm.Table("heVATClasses", "Vats");
selQuery.AddColumn("heName", vats, "VTCLNAME");
selQuery.FromClause.Join(JoinType.Inner, selQuery.FromClause.BaseTable, vats, "heVtclID", "heID");
var iaccs = FromTerm.Table("heItemAccCategories", "Iacc");
selQuery.AddColumn("heName", iaccs, "IaccName");
selQuery.FromClause.Join(JoinType.Left, selQuery.FromClause.BaseTable, iaccs, "heIaccID", "heID");
return(selQuery);
}
internal From(string tableName, string alias, string ns1, string ns2)
{
Term = FromTerm.Table(tableName, alias, ns1, ns2);
}
internal From(string tableName, string alias)
{
Term = FromTerm.Table(tableName, alias);
}
internal From(string tableName)
{
Term = FromTerm.Table(tableName);
}
/// <summary>Аналог конструкции SELECT ... JOIN</summary>
public Select Join(string rightTableName, JoinCond joinCondition)
{
this.Query.FromClause.Join(JoinType.Inner, this.Query.FromClause.BaseTable, FromTerm.Table(rightTableName), joinCondition.Condition);
return(this);
}
