Esempio n. 1
0
        private void AppendColumnDeclaration(SqlColumn sqlColumn)
        {
            this.AppendColumnUsage(sqlColumn);

            if (!string.IsNullOrEmpty(sqlColumn.Alias))
            {
                this.AppendNameCast();
                this.QuoteAppend(sqlColumn.Alias);
            }
        }
Esempio n. 2
0
 private void AppendColumnUsage(SqlColumn sqlColumn)
 {
     var table = sqlColumn.Table;
     if (table != null)
     {
         this.QuoteAppend(table.GetName());
         _sql.Append(".");
     }
     this.QuoteAppend(sqlColumn.ColumnName);
 }
Esempio n. 3
0
        /// <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;
        }
Esempio n. 4
0
        protected override SqlColumn VisitSqlColumn(SqlColumn sqlColumn)
        {
            this.AppendColumnDeclaration(sqlColumn);

            return sqlColumn;
        }
Esempio n. 5
0
        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");
        }
Esempio n. 6
0
 protected virtual SqlColumn VisitSqlColumn(SqlColumn sqlColumn)
 {
     return sqlColumn;
 }
Esempio n. 7
0
        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");
        }
Esempio n. 8
0
        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");
        }
Esempio n. 9
0
        /// <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);
        }
Esempio n. 10
0
        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");
        }
Esempio n. 11
0
        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");
        }
Esempio n. 12
0
        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");
        }
Esempio n. 13
0
 private void AppendColumnUsage(SqlColumn sqlColumn)
 {
     this.QuoteAppend(sqlColumn.Table.GetName());
     _sql.Append(".");
     this.QuoteAppend(sqlColumn.ColumnName);
 }
Esempio n. 14
0
        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);
            }
        }
Esempio n. 15
0
        /// <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;
        }