/// <summary> /// 使用 ROWNUM 来进行分页。 /// </summary> /// <param name="raw">The raw.</param> /// <param name="pagingInfo">The paging information.</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException">pagingInfo</exception> /// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception> protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1; var endRow = startRow + pagingInfo.PageSize - 1; var res = MakePagingTree(raw, startRow, endRow); return res; }
protected virtual SqlSelect VisitSqlSelect(SqlSelect sqlSelect) { if (sqlSelect.Selection != null) { this.Visit(sqlSelect.Selection); } this.Visit(sqlSelect.From); if (sqlSelect.Where != null) { this.Visit(sqlSelect.Where); } if (sqlSelect.HasOrdered()) { for (int i = 0, c = sqlSelect.OrderBy.Count; i < c; i++) { var item = sqlSelect.OrderBy[i] as SqlNode; this.Visit(item); } } return(sqlSelect); }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException">pagingInfo</exception> /// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception> protected virtual ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } /*********************** 代码块解释 ********************************* * * 使用 ROW_NUMBER() 函数,此函数 SqlServer、Oracle 都可使用。 * 注意,这个方法只支持不太复杂 SQL 的转换。 * * 源格式: * select ...... from ...... order by xxxx asc, yyyy desc * 不限于以上格式,只要满足没有复杂的嵌套查询,最外层是一个 Select 和 From 语句即可。 * * 目标格式: * select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5; **********************************************************************/ var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1; var endRow = startRow + pagingInfo.PageSize - 1; var innerSelect = new SqlSelect(); var selection = new SqlArray(); if (raw.Selection != null) { selection.Items.Add(raw.Selection); } selection.Items.Add(new SqlNodeList { new SqlLiteral { FormattedSql = "row_number() over (" }, raw.OrderBy, new SqlLiteral { FormattedSql = ") _rowNumber" } }); innerSelect.Selection = selection; var subSelect = new SqlSubSelect { Select = innerSelect, Alias = "x" }; var rowNumberColumn = new SqlTree.SqlColumn { Table = subSelect, ColumnName = "_rowNumber" }; var pagingSelect = new SqlSelect(); pagingSelect.From = subSelect; pagingSelect.Where = new SqlTree.SqlBinaryConstraint { Left = new SqlTree.SqlColumnConstraint { Column = rowNumberColumn, Operator = SqlColumnConstraintOperator.GreaterEqual, Value = startRow }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlTree.SqlColumnConstraint { Column = rowNumberColumn, Operator = SqlColumnConstraintOperator.LessEqual, Value = endRow } }; return pagingSelect; }
/// <summary> /// 访问 sql 语法树中的每一个结点,并生成相应的 Sql 语句。 /// </summary> /// <param name="tree">The tree.</param> /// <param name="pagingInfo">The paging information.</param> public void Generate(SqlSelect tree, PagingInfo pagingInfo = null) { ISqlSelect res = tree; if (!PagingInfo.IsNullOrEmpty(pagingInfo)) { res = ModifyToPagingTree(tree, pagingInfo); } base.Visit(res); }
protected virtual SqlSelect VisitSqlSelect(SqlSelect sqlSelect) { if (sqlSelect.Selection != null) { this.Visit(sqlSelect.Selection); } this.Visit(sqlSelect.From); if (sqlSelect.Where != null) { this.Visit(sqlSelect.Where); } if (sqlSelect.HasOrdered()) { for (int i = 0, c = sqlSelect.OrderBy.Count; i < c; i++) { var item = sqlSelect.OrderBy.Items[i] as SqlNode; this.Visit(item); } } return sqlSelect; }
public void ORM_SqlTree_Select_Where_AndOr_ORA() { 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 OracleSqlGenerator { 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_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_SqlTree_Select_SelectFromSelectResult_ORA() { 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 OracleSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ( SELECT * FROM USER WHERE USER.ID > {0} ) T WHERE T.ID < {1}"); }
public void ORM_SqlTree_Select_Paging() { 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(3, 10)); 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"); }
public void ORM_SqlTree_Select_ChildrenAll() { var articleTable = new SqlTable { TableName = "Article" }; var userTable = new SqlTable { TableName = "User", Alias = "u" }; var select = new SqlSelect { Selection = new SqlSelectAll(), From = userTable, Where = new SqlBinaryConstraint { Left = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlNotConstraint { Constraint = new SqlExistsConstraint { Select = new SqlSelect { Selection = new SqlLiteral { FormattedSql = "0" }, From = articleTable, Where = new SqlBinaryConstraint { Left = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "UserId" }, RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" } }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlColumnConstraint { Column = new SqlColumn { Table = articleTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 } } } } } } }; 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 NOT (EXISTS ( SELECT 0 FROM Article WHERE Article.UserId = u.Id AND Article.Id > {1} ))"); }
public void ORM_SqlTree_Select_Alias() { 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" }; 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}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value"); }
public void ORM_SqlTree_Select() { 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(); 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_SqlTree_Select_Top() { var select = new SqlSelect(); select.Top = 10; 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 TOP 10 * FROM Table1"); }
public void ORM_SqlTree_Select_Paging_PageNumer1() { 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(1, 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} ORDER BY ASN.AsnCode ASC"); }
public void ORM_SqlTree_Select_Oracle() { 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 OracleSqlGenerator(); 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_SqlTree_Select_OuterJoin_ORA() { 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 OracleSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT A.* FROM ARTICLE A LEFT OUTER JOIN USER 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_ChildrenExists_ORA() { 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 OracleSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM USER U WHERE U.ID > {0} AND EXISTS ( SELECT 0 FROM ARTICLE WHERE ARTICLE.USERID = U.ID )"); }
public void ORM_SqlTree_Select_Paging_PageNumer1_ORA() { 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 OracleSqlGenerator { 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 OracleSqlGenerator { AutoQuota = false }; generator.Generate(select, new PagingInfo(1, 10)); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT * FROM ( SELECT T.*, ROWNUM RN FROM ( SELECT * FROM ASN WHERE ASN.ID > {0} ORDER BY ASN.ASNCODE ASC ) T WHERE ROWNUM <= 10 ) WHERE RN >= 1"); }
public void ORM_SqlTree_Select_Count_ORA() { var select = new SqlSelect(); select.IsCounting = true; select.From = new SqlTable { TableName = "Table1" }; var generator = new OracleSqlGenerator { AutoQuota = false }; generator.Generate(select); Assert.IsTrue(generator.Sql.ToString() == @"SELECT COUNT(0) FROM TABLE1"); }
public void ORM_SqlTree_Select_Star_ORA() { var select = new SqlSelect(); select.Selection = new SqlSelectAll(); select.From = new SqlTable { TableName = "Table1" }; var generator = new OracleSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM TABLE1"); }
public void ORM_SqlTree_Select_Distinct() { var select = new SqlSelect(); select.IsDistinct = true; select.Selection = new SqlSelectAll(); select.From = new SqlTable { TableName = "Table1" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); Assert.IsTrue(generator.Sql.ToString() == @"SELECT DISTINCT * FROM Table1"); }
public void ORM_SqlTree_Select_Top_ORA() { 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 OracleSqlGenerator { AutoQuota = false }; generator.Generate(select, new PagingInfo(1, 10)); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ( SELECT T.*, ROWNUM RN FROM ( SELECT * FROM TABLE1 ORDER BY TABLE1.ID ASC ) T WHERE ROWNUM <= 10 ) WHERE RN >= 1"); }
public void ORM_SqlTree_Select_InnerJoin_TwoToSingleTable() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Article", Alias = "a" }; var userTable1 = new SqlTable { TableName = "User", Alias = "u1" }; var userTable2 = new SqlTable { TableName = "User", Alias = "u2" }; select.Selection = new SqlSelectAll { Table = table }; select.From = new SqlJoin { Left = new SqlJoin { Left = table, JoinType = SqlJoinType.Inner, Right = userTable1, Condition = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = table, ColumnName = "UserId" }, RightColumn = new SqlColumn { Table = userTable1, ColumnName = "Id" }, } }, JoinType = SqlJoinType.Inner, Right = userTable2, Condition = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = table, ColumnName = "AdministratorId" }, RightColumn = new SqlColumn { Table = userTable2, ColumnName = "Id" }, } }; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable2, 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 INNER JOIN User AS u1 ON a.UserId = u1.Id INNER JOIN User 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_WithoutQuota_ORA() { 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 OracleSqlGenerator { 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_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_InSubSelect_Join_ORA() { 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 OracleSqlGenerator { 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 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_InSubSelect_ORA() { 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 OracleSqlGenerator { 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)); }
/// <summary> /// 生成 Selection 中的语句 /// </summary> /// <param name="sqlSelect"></param> protected virtual void GenerateSelection(SqlSelect sqlSelect) { if (sqlSelect.IsCounting) { _sql.Append("COUNT(0)"); } else { if (sqlSelect.IsDistinct) { _sql.Append("DISTINCT "); } if (sqlSelect.Selection == null) { _sql.Append("*"); } else { this.Visit(sqlSelect.Selection); } } }
public void ORM_SqlTree_Select_OrderBy_ORA() { 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 = column2, Direction = OrderDirection.Descending } }; var generator = new OracleSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT T1.COLUMN1 C1, T1.COLUMN2 C2 FROM TABLE1 T1 WHERE T1.COLUMN2 = {0} ORDER BY T1.COLUMN2 DESC"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value"); }
protected override SqlSelect VisitSqlSelect(SqlSelect sqlSelect) { _sql.Append("SELECT "); //SELECT this.GenerateSelection(sqlSelect); //FROM _sql.AppendLine(); _sql.Append("FROM "); this.Visit(sqlSelect.From); //WHERE if (sqlSelect.Where != null) { _sql.AppendLine(); _sql.Append("WHERE "); this.Visit(sqlSelect.Where); } //ORDER BY if (!sqlSelect.IsCounting && sqlSelect.OrderBy != null && sqlSelect.OrderBy.Count > 0) { _sql.AppendLine(); this.Visit(sqlSelect.OrderBy); } return sqlSelect; }
private static ISqlSelect MakePagingTree_ReserveMethod(SqlSelect raw, int startRow, int endRow) { /*********************** 代码块解释 ********************************* * 源格式: * SELECT * * FROM A * WHERE A.Id > 0 * ORDER BY A.NAME ASC * * 目标格式: * SELECT * FROM * (SELECT A.*, ROWNUM RN * FROM A * WHERE A.Id > 0 AND ROWNUM <= 20 * ORDER BY A.NAME ASC) * WHERE RN >= 10 * * 这种方法可能存在问题: * 因为源 Sql 可能是:Select * From A Join B,这时表示结果集需要显示 A 和 B 的所有字段, * 但是此方法会转换为:Select A.* From A Join B。比较麻烦,暂不处理 **********************************************************************/ var innerSelect = new SqlSelect { IsDistinct = raw.IsDistinct, From = raw.From, Where = AppendWhere(raw.Where, new SqlLiteral("ROWNUM <= " + endRow)), OrderBy = raw.OrderBy }; //内部的 Select 子句中,不能简单地使用 "*, ROWNUM",而是需要使用 "A.*, ROWNUM" var rawSelection = raw.Selection; if (rawSelection == null) { //默认约定第一张表,就是 var table = new FirstTableFinder().Find(raw.From); rawSelection = new SqlSelectAll { Table = table }; } innerSelect.Selection = new SqlNodeList { rawSelection, new SqlLiteral(", ROWNUM RN") }; var res = new SqlSelect { Selection = SqlSelectAll.Default, From = new SqlSubSelect { Select = innerSelect }, Where = new SqlLiteral("RN >= " + startRow) }; return res; }