public void ORM_TableQuery_Where_AndOr_Demo() { var f = QueryFactory.Instance; var table = f.Table<Book>(); var code = table.Column(Book.CodeProperty); var name = table.Column(Book.NameProperty); var query = f.Query( from: table, where: f.Or( left: f.Constraint(code, "A"), right: f.And( left: f.Or( left: f.Constraint(name, "A2"), right: f.Constraint(name, "B2") ), right: f.Constraint(code, "B") ) ) ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Book WHERE Book.Code = {0} OR (Book.Name = {1} OR Book.Name = {2}) AND Book.Code = {3}"); Assert.IsTrue(sql.Parameters.Count == 4); Assert.IsTrue(sql.Parameters[0].ToString() == "A"); Assert.IsTrue(sql.Parameters[1].ToString() == "A2"); Assert.IsTrue(sql.Parameters[2].ToString() == "B2"); Assert.IsTrue(sql.Parameters[3].ToString() == "B"); }
public void ORM_TableQuery_Top() { var f = QueryFactory.Instance; var query = f.Query( top: 10, from: f.Table<Book>() ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT TOP 10 * FROM Book"); }
public void ORM_TableQuery_Paging_PageNumer1() { var f = QueryFactory.Instance; var source = f.Table(RF.Concrete<ArticleRepository>()); var query = f.Query( from: source, where: f.Constraint(source.Column(Entity.IdProperty), PropertyOperator.Greater, 0), orderBy: new List<IOrderBy> { f.OrderBy(source.Column(Article.CodeProperty)) } ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Article WHERE Article.Id > {0} ORDER BY Article.Code ASC"); //对于已经组装完成的 IQuery 对象,ModifyToPagingTree 方法同样可以为其生成相应的分页 SqlSelect 语句。 generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(query as SqlSelect, new PagingInfo(1, 10)); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM Article WHERE Article.Id > {0} ORDER BY Article.Code ASC"); }
public void ORM_TableQuery_Star() { var f = QueryFactory.Instance; var table = f.Table<Book>(); var query = f.Query( selection: f.SelectAll(), from: table ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Book"); }
public void ORM_TableQuery_InSubSelect_Join() { var f = QueryFactory.Instance; var articleSource = f.Table(RF.Concrete<ArticleRepository>()); var userSource = f.Table(RF.Concrete<BlogUserRepository>()); var adminSource = f.Table(RF.Concrete<BlogUserRepository>(), "Administrator"); var query = f.Query( from: userSource, where: f.Constraint( column: userSource.Column(BlogUser.IdProperty), op: PropertyOperator.In, value: f.Query( selection: articleSource.Column(Article.UserIdProperty), from: f.Join( left: articleSource, right: adminSource, condition: f.Constraint( articleSource.Column(Article.AdministratorIdProperty), adminSource.Column(BlogUser.IdProperty) ) ), where: f.Constraint(adminSource.Column(BlogUser.IdProperty), 1) ) ) ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM BlogUser WHERE BlogUser.Id IN ( SELECT Article.UserId FROM Article INNER JOIN BlogUser AS Administrator ON Article.AdministratorId = Administrator.Id WHERE Administrator.Id = {0} )"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].Equals(1)); }
public void ORM_TableQuery_OuterJoin() { var f = QueryFactory.Instance; var articleSource = f.Table(RF.Concrete<ArticleRepository>(), "a"); var userSource = f.Table(RF.Concrete<BlogUserRepository>(), "u"); var query = f.Query( selection: f.SelectAll(articleSource), from: f.Join( left: articleSource, joinType: JoinType.LeftOuter, right: userSource, condition: f.Constraint( leftColumn: articleSource.Column(Article.UserIdProperty), rightColumn: userSource.Column(BlogUser.IdProperty) ) ), where: f.Constraint(userSource.Column(BlogUser.UserNameProperty), "HuQingfang") ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT a.* FROM Article AS a LEFT OUTER JOIN BlogUser AS u ON a.UserId = u.Id WHERE u.UserName = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "HuQingfang"); }
public void ORM_SqlTree_Select_OuterJoin() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Article", Alias = "a" }; var userTable = new SqlTable { TableName = "User", Alias = "u" }; select.Selection = new SqlSelectAll { Table = table }; select.From = new SqlJoin { Left = table, JoinType = SqlJoinType.LeftOuter, Right = userTable, Condition = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = table, ColumnName = "UserId" }, RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" }, } }; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "UserName" }, Operator = SqlColumnConstraintOperator.Equal, Value = "HuQingfang" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT a.* FROM Article AS a LEFT OUTER JOIN User AS u ON a.UserId = u.Id WHERE u.UserName = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "HuQingfang"); }
public void ORM_TableQuery_InnerJoin_TwoToSingleTable() { var f = QueryFactory.Instance; var articleSource = f.Table(RF.Concrete<ArticleRepository>(), "a"); var userSource1 = f.Table(RF.Concrete<BlogUserRepository>(), "u1"); var userSource2 = f.Table(RF.Concrete<BlogUserRepository>(), "u2"); var query = f.Query( selection: f.SelectAll(articleSource), from: f.Join( left: f.Join( left: articleSource, right: userSource1, condition: f.Constraint( leftColumn: articleSource.Column(Article.UserIdProperty), rightColumn: userSource1.Column(BlogUser.IdProperty) ) ), right: userSource2, condition: f.Constraint( leftColumn: articleSource.Column(Article.AdministratorIdProperty), rightColumn: userSource2.Column(BlogUser.IdProperty) ) ), where: f.Constraint(userSource2.Column(BlogUser.UserNameProperty), "HuQingfang") ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT a.* FROM Article AS a INNER JOIN BlogUser AS u1 ON a.UserId = u1.Id INNER JOIN BlogUser AS u2 ON a.AdministratorId = u2.Id WHERE u2.UserName = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "HuQingfang"); }
public void ORM_SqlTree_Select_InSubSelect_Join() { var select = new SqlSelect(); var adminTable = new SqlTable { TableName = "User", Alias = "Administrator" }; var articleTable = new SqlTable { TableName = "Article" }; var subSelect = new SqlSelect { Selection = new SqlColumn { Table = articleTable, ColumnName = "UserId" }, From = new SqlJoin { Left = articleTable, JoinType = SqlJoinType.Inner, Right = adminTable, Condition = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "AdministratorId" }, RightColumn = new SqlColumn { Table = adminTable, ColumnName = "Id" }, } }, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = adminTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Equal, Value = 1 } }; var userTable = new SqlTable { TableName = "User" }; select.Selection = new SqlSelectAll(); select.From = userTable; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.In, Value = subSelect }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM User WHERE User.Id IN ( SELECT Article.UserId FROM Article INNER JOIN User AS Administrator ON Article.AdministratorId = Administrator.Id WHERE Administrator.Id = {0} )"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].Equals(1)); }
public void ORM_SqlTree_Select_OrderBy2() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Table1", Alias = "t1" }; var column1 = new SqlColumn { Table = table, ColumnName = "Column1", Alias = "c1" }; var column2 = new SqlColumn { Table = table, ColumnName = "Column2", Alias = "c2" }; select.Selection = new SqlArray { Items = { column1, column2 } }; select.From = table; select.Where = new SqlColumnConstraint { Column = column2, Operator = SqlColumnConstraintOperator.Equal, Value = "Column2Value" }; select.OrderBy = new SqlOrderByList { new SqlOrderBy { Column = column1, Direction = OrderDirection.Ascending }, new SqlOrderBy { Column = column2, Direction = OrderDirection.Descending } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT t1.Column1 AS c1, t1.Column2 AS c2 FROM Table1 AS t1 WHERE t1.Column2 = {0} ORDER BY t1.Column1 ASC, t1.Column2 DESC"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value"); }
public void ORM_SqlTree_Select_InSubSelect() { var select = new SqlSelect(); var articleTable = new SqlTable { TableName = "Article" }; var subSelect = new SqlSelect { Selection = new SqlColumn { Table = articleTable, ColumnName = "UserId" }, From = articleTable, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = articleTable, ColumnName = "CreateDate" }, Operator = SqlColumnConstraintOperator.Equal, Value = DateTime.Today } }; var userTable = new SqlTable { TableName = "User" }; select.Selection = new SqlSelectAll(); select.From = userTable; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.In, Value = subSelect }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM User WHERE User.Id IN ( SELECT Article.UserId FROM Article WHERE Article.CreateDate = {0} )"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].Equals(DateTime.Today)); }
public void ORM_SqlTree_Select_Count() { var select = new SqlSelect(); select.IsCounting = true; select.From = new SqlTable { TableName = "Table1" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); Assert.IsTrue(generator.Sql.ToString() == @"SELECT COUNT(0) FROM Table1"); }
public void ORM_SqlTree_Select_ChildrenExists() { var articleTable = new SqlTable { TableName = "Article" }; var userTable = new SqlTable { TableName = "User", Alias = "u" }; var select = new SqlSelect(); select.Selection = new SqlSelectAll(); select.From = userTable; select.Where = new SqlBinaryConstraint { Left = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlExistsConstraint { Select = new SqlSelect { Selection = new SqlLiteral { FormattedSql = "0" }, From = articleTable, Where = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "UserId" }, RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" } } } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM User AS u WHERE u.Id > {0} AND EXISTS ( SELECT 0 FROM Article WHERE Article.UserId = u.Id )"); }
public void ORM_TableQuery_ChildrenExists() { var f = QueryFactory.Instance; var articleSource = f.Table(RF.Concrete<ArticleRepository>()); var userSource = f.Table(RF.Concrete<BlogUserRepository>(), "u"); var query = f.Query( from: userSource, where: f.And( f.Constraint( userSource.Column(Entity.IdProperty), PropertyOperator.Greater, 0 ), f.Exists(f.Query( selection: f.Literal("0"), from: articleSource, where: f.Constraint( articleSource.Column(Article.UserIdProperty), userSource.Column(Entity.IdProperty) ) )) ) ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM BlogUser AS u WHERE u.Id > {0} AND EXISTS ( SELECT 0 FROM Article WHERE Article.UserId = u.Id )"); }
public void ORM_SqlTree_Select_Paging_PageNumer1() { var table = new SqlTable { TableName = "ASN" }; var select = new SqlSelect { Selection = new SqlSelectAll(), From = table, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = Entity.IdProperty.Name }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 }, OrderBy = new SqlOrderByList { Items = { new SqlOrderBy { Column = new SqlColumn{ Table = table, ColumnName = "AsnCode" }, Direction = OrderDirection.Ascending } } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ASN WHERE ASN.Id > {0} ORDER BY ASN.AsnCode ASC"); generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select, new PagingInfo(1, 10)); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM ASN WHERE ASN.Id > {0} ORDER BY ASN.AsnCode ASC"); }
public void ORM_TableQuery_Distinct() { var f = QueryFactory.Instance; var query = f.Query( isDistinct: true, from: f.Table<Book>() ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); Assert.IsTrue(generator.Sql.ToString() == @"SELECT DISTINCT * FROM Book"); }
public void ORM_SqlTree_Select_SelectFromSelectResult() { var userTable = new SqlTable { TableName = "User" }; var subSelectRef = new SqlSubSelect { Select = new SqlSelect { Selection = new SqlSelectAll(), From = userTable, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 } }, Alias = "T" }; var select = new SqlSelect(); select.Selection = new SqlSelectAll(); select.From = subSelectRef; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = subSelectRef, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Less, Value = 100 }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ( SELECT * FROM User WHERE User.Id > {0} ) AS T WHERE T.Id < {1}"); }
public void ORM_TableQuery_InSubSelect() { var f = QueryFactory.Instance; var articleSource = f.Table(RF.Concrete<ArticleRepository>()); var userSource = f.Table(RF.Concrete<BlogUserRepository>()); var query = f.Query( from: userSource, where: f.Constraint( column: userSource.Column(BlogUser.IdProperty), op: PropertyOperator.In, value: f.Query( selection: articleSource.Column(Article.UserIdProperty), from: articleSource, where: f.Constraint(articleSource.Column(Article.CreateDateProperty), DateTime.Today) ) ) ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM BlogUser WHERE BlogUser.Id IN ( SELECT Article.UserId FROM Article WHERE Article.CreateDate = {0} )"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].Equals(DateTime.Today)); }
public void ORM_SqlTree_Select_Star() { var select = new SqlSelect(); select.Selection = new SqlSelectAll(); select.From = new SqlTable { TableName = "Table1" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Table1"); }
public void ORM_TableQuery_OrderBy2() { var f = QueryFactory.Instance; var table = f.Table<Book>("t1"); var code = table.Column(Book.CodeProperty, "c1"); var name = table.Column(Book.NameProperty, "c2"); var query = f.Query( selection: f.Array(code, name), from: table, where: f.Constraint(name, "name"), orderBy: new List<IOrderBy> { f.OrderBy(code), f.OrderBy(name, OrderDirection.Descending) } ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT t1.Code AS c1, t1.Name AS c2 FROM Book AS t1 WHERE t1.Name = {0} ORDER BY t1.Code ASC, t1.Name DESC"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "name"); }
public void ORM_SqlTree_Select_Top() { var select = new SqlSelect(); select.Selection = new SqlSelectAll(); var t = new SqlTable { TableName = "Table1" }; ; select.From = t; select.OrderBy = new SqlOrderByList { new SqlOrderBy { Column = new SqlColumn { Table = t, ColumnName = "Id", } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select, new PagingInfo(1, 10)); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT TOP 10 * FROM Table1 ORDER BY Table1.Id ASC"); }
public void ORM_TableQuery_Paging() { var f = QueryFactory.Instance; var source = f.Table(RF.Concrete<ArticleRepository>()); var pk = source.Column(Entity.IdProperty); var query = f.Query( from: source, where: f.Constraint(pk, PropertyOperator.Greater, 0), orderBy: new List<IOrderBy> { f.OrderBy(source.Column(Article.CodeProperty)) } ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Article WHERE Article.Id > {0} ORDER BY Article.Code ASC"); generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(query as SqlSelect, new PagingInfo(3, 10)); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM Article WHERE Article.Id > {0} AND Article.Id NOT IN ( SELECT TOP 20 Article.Id FROM Article WHERE Article.Id > {1} ORDER BY Article.Code ASC ) ORDER BY Article.Code ASC"); }
public void ORM_SqlTree_Select_Where_AndOr() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Table1" }; select.Selection = new SqlSelectAll(); select.From = table; select.Where = new SqlBinaryConstraint { Left = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column1" }, Operator = SqlColumnConstraintOperator.Equal, Value = "A" }, Opeartor = SqlBinaryConstraintType.Or, Right = new SqlBinaryConstraint { Left = new SqlBinaryConstraint { Left = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column2" }, Operator = SqlColumnConstraintOperator.Equal, Value = "A2" }, Opeartor = SqlBinaryConstraintType.Or, Right = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column2" }, Operator = SqlColumnConstraintOperator.Equal, Value = "B2" } }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column1" }, Operator = SqlColumnConstraintOperator.Equal, Value = "B" } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Table1 WHERE Table1.Column1 = {0} OR (Table1.Column2 = {1} OR Table1.Column2 = {2}) AND Table1.Column1 = {3}"); Assert.IsTrue(sql.Parameters.Count == 4); Assert.IsTrue(sql.Parameters[0].ToString() == "A"); Assert.IsTrue(sql.Parameters[1].ToString() == "A2"); Assert.IsTrue(sql.Parameters[2].ToString() == "B2"); Assert.IsTrue(sql.Parameters[3].ToString() == "B"); }
public void ORM_TableQuery_SelectFromSelectResult() { var f = QueryFactory.Instance; var userSource = f.Table(RF.Concrete<BlogUserRepository>()); var subQuery = f.SubQuery( query: f.Query( from: userSource, where: f.Constraint( userSource.Column(Entity.IdProperty), PropertyOperator.Greater, 0 ) ), alias: "T" ); var query = f.Query( from: subQuery, where: f.Constraint( subQuery.Column(userSource.Column(Entity.IdProperty)), PropertyOperator.Less, 100 ) ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ( SELECT * FROM BlogUser WHERE BlogUser.Id > {0} ) AS T WHERE T.Id < {1}"); }
public void ORM_SqlTree_Select_WithoutQuota() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Table1" }; var column1 = new SqlColumn { Table = table, ColumnName = "Column1" }; var column2 = new SqlColumn { Table = table, ColumnName = "Column2" }; select.Selection = new SqlArray { Items = { column1, column2 } }; select.From = table; select.Where = new SqlColumnConstraint { Column = column2, Operator = SqlColumnConstraintOperator.Equal, Value = "Column2Value" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT Table1.Column1, Table1.Column2 FROM Table1 WHERE Table1.Column2 = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value"); }
public void ORM_TableQuery_Top() { var f = QueryFactory.Instance; var table = f.Table<Book>(); var query = f.Query( from: table, orderBy: new List<IOrderBy> { f.OrderBy(table.IdColumn) } ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query, new PagingInfo(1, 10)); var sql = generator.Sql; Assert.IsTrue(sql.ToString().StartsWith(@"SELECT TOP 10 * FROM Book ORDER BY Book.Id ASC")); }
public void ORM_TableQuery_Alias() { var f = QueryFactory.Instance; var table = f.Table<Book>("t1"); var code = table.Column(Book.CodeProperty, "c1"); var name = table.Column(Book.NameProperty, "c2"); var query = f.Query( selection: f.Array(code, name), from: table, where: f.Constraint(code, "code") ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT t1.Code AS c1, t1.Name AS c2 FROM Book AS t1 WHERE t1.Code = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "code"); }
public void ORM_TableQuery_WithoutQuota() { var f = QueryFactory.Instance; var table = f.Table<Book>(); var code = table.Column(Book.CodeProperty); var name = table.Column(Book.NameProperty); var query = f.Query( selection: f.Array(code, name), from: table, where: f.Constraint(column: code, value: "code", op: PropertyOperator.Equal) ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT Book.Code, Book.Name FROM Book WHERE Book.Code = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "code"); }
public void ORM_SqlTree_Select_Paging() { var table = new SqlTable { TableName = "ASN" }; var pk = new SqlColumn { Table = table, ColumnName = "Id" }; var select = new SqlSelect { Selection = new SqlSelectAll(), From = table, Where = new SqlColumnConstraint { Column = pk, Operator = SqlColumnConstraintOperator.Greater, Value = 0 }, OrderBy = new List<SqlOrderBy> { new SqlOrderBy { Column = new SqlColumn{ Table = table, ColumnName = "AsnCode" }, Direction = OrderDirection.Ascending } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ASN WHERE ASN.Id > {0} ORDER BY ASN.AsnCode ASC"); var pagedSelect = generator.ModifyToPagingTree(select, pk, new PagingInfo(3, 10)); generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(pagedSelect); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM ASN WHERE ASN.Id > {0} AND ASN.Id NOT IN ( SELECT TOP 20 ASN.Id FROM ASN WHERE ASN.Id > {1} ORDER BY ASN.AsnCode ASC ) ORDER BY ASN.AsnCode ASC"); }