private void AppendColumnDeclaration(SqlColumn sqlColumn) { this.AppendColumnUsage(sqlColumn); if (!string.IsNullOrEmpty(sqlColumn.Alias)) { this.AppendNameCast(); this.QuoteAppend(sqlColumn.Alias); } }
private void AppendColumnUsage(SqlColumn sqlColumn) { var table = sqlColumn.Table; if (table != null) { this.QuoteAppend(table.GetName()); _sql.Append("."); } this.QuoteAppend(sqlColumn.ColumnName); }
/// <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; }
protected override SqlColumn VisitSqlColumn(SqlColumn sqlColumn) { this.AppendColumnDeclaration(sqlColumn); return sqlColumn; }
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"); }
protected virtual SqlColumn VisitSqlColumn(SqlColumn sqlColumn) { return sqlColumn; }
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_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"); }
/// <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); }
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_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"); }
private void AppendColumnUsage(SqlColumn sqlColumn) { this.QuoteAppend(sqlColumn.Table.GetName()); _sql.Append("."); this.QuoteAppend(sqlColumn.ColumnName); }
private void AppendColumnDeclaration(SqlColumn sqlColumn) { this.QuoteAppend(sqlColumn.Table.GetName()); _sql.Append("."); this.QuoteAppend(sqlColumn.ColumnName); if (!string.IsNullOrEmpty(sqlColumn.Alias)) { _sql.Append(" AS "); this.QuoteAppend(sqlColumn.Alias); } }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pkColumn">需要指定主键列</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } //如果是第一页,则只需要使用 TOP 语句即可。 if (pagingInfo.PageNumber == 1) { raw.Top = pagingInfo.PageSize; return raw; } /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * SELECT TOP 10 * * FROM ASN * WHERE ASN.Id > 0 AND ASN.Id NOT IN( * SELECT TOP 20 Id * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * ) * ORDER BY ASN.AsnCode ASC * **********************************************************************/ var excludeSelect = new SqlSelect { Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize, Selection = pkColumn, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy, }; var res = new SqlSelect { Top = pagingInfo.PageSize, Selection = raw.Selection, From = raw.From, OrderBy = raw.OrderBy, }; var newWhere = new SqlColumnConstraint { Column = pkColumn, Operator = SqlColumnConstraintOperator.NotIn, Value = excludeSelect }; if (raw.Where != null) { res.Where = new SqlBinaryConstraint { Left = raw.Where, Opeartor = SqlBinaryConstraintType.And, Right = newWhere }; } else { res.Where = newWhere; } return res; }