Example #1
0
        protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
        {
            switch (node.Operator)
            {
                case SqlColumnConstraintOperator.Equal:
                case SqlColumnConstraintOperator.NotEqual:
                    //在 Oracle 中,空字符串的对比,需要转换为对 Null 值的对比。
                    var strValue = node.Value as string;
                    if (strValue != null && strValue.Length == 0)
                    {
                        node.Value = null;
                    }
                    break;
                default:
                    break;
            }

            return base.VisitSqlColumnConstraint(node);
        }
Example #2
0
        protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
        {
            switch (node.Operator)
            {
            case SqlColumnConstraintOperator.Equal:
            case SqlColumnConstraintOperator.NotEqual:
                //在 Oracle 中,空字符串的对比,需要转换为对 Null 值的对比。
                var strValue = node.Value as string;
                if (strValue != null && strValue.Length == 0)
                {
                    node.Value = null;
                }
                break;

            default:
                break;
            }

            return(base.VisitSqlColumnConstraint(node));
        }
Example #3
0
        /// <summary>
        /// 转换空值比较
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
        {
            switch (node.Operator)
            {
            case SqlColumnConstraintOperator.Equal:
            case SqlColumnConstraintOperator.NotEqual:
                //在 MySql 中,空字符串的对比,需要转换为对 Null 值的对比。
                if (node.Value is string)
                {
                    if (node.Value != null)
                    {
                        if (node.Value.ToString().Length == 0)
                        {
                            node.Value = "";
                        }
                    }
                }
                break;

            //case SqlColumnConstraintOperator.Contains:
            //    if (node.Value is string && node.Value.ToString().IndexOf('%')>=0)
            //    {
            //        node.Value = "locate('" + node.Value + "'," + node.Column.ColumnName + ")!=0";
            //    }
            //    break;
            //case SqlColumnConstraintOperator.NotContains:
            //    if (node.Value is string&& node.Value.ToString().IndexOf('%') >= 0)
            //    {
            //        node.Value = "locate('" + node.Value + "'," + node.Column.ColumnName + ") = 0";
            //    }
            //    break;
            default:
                break;
            }

            return(base.VisitSqlColumnConstraint(node));
        }
Example #4
0
        /// <summary>
        /// 为指定的原始查询生成指定分页效果的新查询。
        /// </summary>
        /// <param name="raw">原始查询</param>
        /// <param name="pagingInfo">分页信息。</param>
        /// <returns></returns>
        private ISqlSelect ModifyToPagingTreeWithNotIn(SqlSelect raw, PagingInfo pagingInfo)
        {
            if (PagingInfo.IsNullOrEmpty(pagingInfo))
            {
                throw new ArgumentNullException("pagingInfo");
            }
            if (!raw.HasOrdered())
            {
                throw new InvalidProgramException("必须排序后才能使用分页功能。");
            }

            //如果是第一页,则只需要使用 TOP 语句即可。
            if (pagingInfo.PageNumber == 1)
            {
                return(new SqlSelect
                {
                    Selection = new SqlNodeList
                    {
                        new SqlLiteral {
                            FormattedSql = "TOP " + pagingInfo.PageSize + " "
                        },
                        raw.Selection ?? SqlSelectAll.Default
                    },
                    From = raw.From,
                    Where = raw.Where,
                    OrderBy = raw.OrderBy
                });
            }

            /*********************** 代码块解释 *********************************
             *
             * 转换方案:
             *
             * 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
             *
             **********************************************************************/

            //先要找到主表的 PK,分页时需要使用此主键列来生成分页 Sql。
            //这里约定 Id 为主键列名。
            var finder   = new FirstTableFinder();
            var pkTable  = finder.Find(raw.From);
            var pkColumn = new SqlColumn {
                Table = pkTable, ColumnName = EntityConvention.IdColumnName
            };

            //先生成内部的 Select
            var excludeSelect = new SqlSelect
            {
                Selection = new SqlNodeList
                {
                    new SqlLiteral {
                        FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " "
                    },
                    pkColumn
                },
                From    = raw.From,
                Where   = raw.Where,
                OrderBy = raw.OrderBy,
            };

            var res = new SqlSelect
            {
                Selection = new SqlNodeList
                {
                    new SqlLiteral {
                        FormattedSql = "TOP " + pagingInfo.PageSize + " "
                    },
                    raw.Selection ?? SqlSelectAll.Default
                },
                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);
        }
Example #5
0
        protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
        {
            var op = node.Operator;
            var value = node.Value;

            value = this.PrepareConstraintValue(value);

            #region 处理一些特殊的值

            switch (op)
            {
                case SqlColumnConstraintOperator.Like:
                case SqlColumnConstraintOperator.Contains:
                case SqlColumnConstraintOperator.StartsWith:
                case SqlColumnConstraintOperator.EndsWith:
                    //如果是空字符串的模糊对比操作,直接认为是真。
                    var strValue = value as string;
                    if (string.IsNullOrEmpty(strValue))
                    {
                        _sql.Append("1 = 1");
                        return node;
                    }
                    break;
                case SqlColumnConstraintOperator.NotLike:
                case SqlColumnConstraintOperator.NotContains:
                case SqlColumnConstraintOperator.NotStartsWith:
                case SqlColumnConstraintOperator.NotEndsWith:
                    //如果是空字符串的模糊对比操作,直接认为是假。
                    var strValue2 = value as string;
                    if (string.IsNullOrEmpty(strValue2))
                    {
                        _sql.Append("1 != 1");
                        return node;
                    }
                    break;
                case SqlColumnConstraintOperator.In:
                case SqlColumnConstraintOperator.NotIn:
                    //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理:
                    //In(Empty) 表示 false,NotIn(Empty) 表示 true。
                    if (value is IEnumerable)
                    {
                        bool hasValue = false;
                        foreach (var item in value as IEnumerable)
                        {
                            hasValue = true;
                            break;
                        }
                        if (!hasValue)
                        {
                            if (op == SqlColumnConstraintOperator.In)
                            {
                                _sql.Append("0 = 1");
                            }
                            else
                            {
                                _sql.Append("1 = 1");
                            }

                            return node;
                        }
                    }
                    break;
                default:
                    break;
            }

            #endregion

            this.AppendColumnUsage(node.Column);

            //根据不同的操作符,来生成不同的_sql。
            switch (op)
            {
                case SqlColumnConstraintOperator.Equal:
                    if (value == null || value == DBNull.Value)
                    {
                        _sql.Append(" IS NULL");
                    }
                    else
                    {
                        _sql.Append(" = ");
                        _sql.AppendParameter(value);
                    }
                    break;
                case SqlColumnConstraintOperator.NotEqual:
                    if (value == null || value == DBNull.Value)
                    {
                        _sql.Append(" IS NOT NULL");
                    }
                    else
                    {
                        _sql.Append(" != ");
                        _sql.AppendParameter(value);
                    }
                    break;
                case SqlColumnConstraintOperator.Greater:
                    _sql.Append(" > ");
                    _sql.AppendParameter(value);
                    break;
                case SqlColumnConstraintOperator.GreaterEqual:
                    _sql.Append(" >= ");
                    _sql.AppendParameter(value);
                    break;
                case SqlColumnConstraintOperator.Less:
                    _sql.Append(" < ");
                    _sql.AppendParameter(value);
                    break;
                case SqlColumnConstraintOperator.LessEqual:
                    _sql.Append(" <= ");
                    _sql.AppendParameter(value);
                    break;
                case SqlColumnConstraintOperator.Like:
                    _sql.Append(" LIKE ");
                    _sql.AppendParameter(value);
                    break;
                case SqlColumnConstraintOperator.NotLike:
                    _sql.Append(" NOT LIKE ");
                    _sql.AppendParameter(value);
                    break;
                case SqlColumnConstraintOperator.Contains:
                    _sql.Append(" LIKE ");
                    _sql.AppendParameter("%" + value + "%");
                    break;
                case SqlColumnConstraintOperator.NotContains:
                    _sql.Append(" NOT LIKE ");
                    _sql.AppendParameter("%" + value + "%");
                    break;
                case SqlColumnConstraintOperator.StartsWith:
                    _sql.Append(" LIKE ");
                    _sql.AppendParameter(value + "%");
                    break;
                case SqlColumnConstraintOperator.NotStartsWith:
                    _sql.Append(" NOT LIKE ");
                    _sql.AppendParameter(value + "%");
                    break;
                case SqlColumnConstraintOperator.EndsWith:
                    _sql.Append(" LIKE ");
                    _sql.AppendParameter("%" + value);
                    break;
                case SqlColumnConstraintOperator.NotEndsWith:
                    _sql.Append(" NOT LIKE ");
                    _sql.AppendParameter("%" + value);
                    break;
                case SqlColumnConstraintOperator.In:
                case SqlColumnConstraintOperator.NotIn:
                    var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
                    _sql.Append(" ").Append(opSql).Append(" (");
                    if (value is IEnumerable)
                    {
                        bool first = true;
                        bool needDelimiter = false;
                        int i = 0;
                        foreach (var item in value as IEnumerable)
                        {
                            if (++i > this.MaxItemsInInClause) throw new TooManyItemsInInClauseException();

                            if (first)
                            {
                                first = false;
                                needDelimiter = item is string || item is DateTime || item is Guid;
                            }
                            else { _sql.Append(','); }

                            if (needDelimiter)
                            {
                                _sql.Append('\'').Append(item).Append('\'');
                            }
                            else
                            {
                                _sql.Append(item);
                            }

                            //由于集合中的数据可能过多,所以这里不要使用参数化的查询。
                            //_sql.AppendParameter(item);
                        }
                    }
                    else if (value is SqlNode)
                    {
                        _sql.AppendLine();
                        this.Indent++;
                        this.Visit(value as SqlNode);
                        this.Indent--;
                        _sql.AppendLine();
                    }
                    _sql.Append(')');
                    break;
                default:
                    throw new NotSupportedException();
            }

            return node;
        }
Example #6
0
        /// <summary>
        /// 为指定的原始查询生成指定分页效果的新查询。
        /// </summary>
        /// <param name="raw">原始查询</param>
        /// <param name="pagingInfo">分页信息。</param>
        /// <returns></returns>
        private ISqlSelect ModifyToPagingTree_With_NotIn(SqlSelect raw, PagingInfo pagingInfo)
        {
            /*********************** 代码块解释 *********************************
             *
             * 转换方案:
             *
             * 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
             *
             **********************************************************************/

            //先要找到主表的 PK,分页时需要使用此主键列来生成分页 Sql。
            //这里约定 Id 为主键列名。
            var finder   = new FirstTableFinder();
            var pkTable  = finder.Find(raw.From);
            var pkColumn = new SqlColumn {
                Table = pkTable, ColumnName = EntityConvention.IdColumnName
            };

            //先生成内部的 Select
            var excludeSelect = new SqlSelect
            {
                Selection = new SqlNodeList
                {
                    new SqlLiteral {
                        FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " "
                    },
                    pkColumn
                },
                From    = raw.From,
                Where   = raw.Where,
                OrderBy = raw.OrderBy,
            };

            var res = new SqlSelect
            {
                Selection = new SqlNodeList
                {
                    new SqlLiteral {
                        FormattedSql = "TOP " + pagingInfo.PageSize + " "
                    },
                    raw.Selection ?? SqlSelectAll.Default
                },
                From    = raw.From,
                OrderBy = raw.OrderBy,
            };

            //where
            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);
        }
Example #7
0
 protected virtual SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
 {
     this.Visit(node.Column);
     return node;
 }
Example #8
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);
        }
Example #9
0
        protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
        {
            var op    = node.Operator;
            var value = node.Value;

            #region 处理一些特殊的值

            switch (op)
            {
            case SqlColumnConstraintOperator.Like:
            case SqlColumnConstraintOperator.Contains:
            case SqlColumnConstraintOperator.StartWith:
            case SqlColumnConstraintOperator.EndWith:
                //如果是空字符串的模糊对比操作,直接认为是真。
                var strValue = value as string;
                if (string.IsNullOrEmpty(strValue))
                {
                    _sql.Append("1 = 1");
                    return(node);
                }
                break;

            case SqlColumnConstraintOperator.In:
            case SqlColumnConstraintOperator.NotIn:
                //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理:
                //In(Empty) 表示 false,NotIn(Empty) 表示 true。
                if (value is IEnumerable)
                {
                    bool hasValue = false;
                    foreach (var item in value as IEnumerable)
                    {
                        hasValue = true;
                        break;
                    }
                    if (!hasValue)
                    {
                        if (op == SqlColumnConstraintOperator.In)
                        {
                            _sql.Append("0 = 1");
                        }
                        else
                        {
                            _sql.Append("1 = 1");
                        }

                        return(node);
                    }
                }
                break;

            default:
                break;
            }

            #endregion

            this.AppendColumnUsage(node.Column);

            //根据不同的操作符,来生成不同的_sql。
            switch (op)
            {
            case SqlColumnConstraintOperator.Equal:
                if (value == null || value == DBNull.Value)
                {
                    _sql.Append(" IS NULL");
                }
                else
                {
                    _sql.Append(" = ");
                    _sql.AppendParameter(value);
                }
                break;

            case SqlColumnConstraintOperator.NotEqual:
                if (value == null || value == DBNull.Value)
                {
                    _sql.Append(" IS NOT NULL");
                }
                else
                {
                    _sql.Append(" != ");
                    _sql.AppendParameter(value);
                }
                break;

            case SqlColumnConstraintOperator.Greater:
                _sql.Append(" > ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.GreaterEqual:
                _sql.Append(" >= ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.Less:
                _sql.Append(" < ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.LessEqual:
                _sql.Append(" <= ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.Like:
                _sql.Append(" LIKE ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.Contains:
                _sql.Append(" LIKE ");
                _sql.AppendParameter("%" + value + "%");
                break;

            case SqlColumnConstraintOperator.StartWith:
                _sql.Append(" LIKE ");
                _sql.AppendParameter(value + "%");
                break;

            case SqlColumnConstraintOperator.EndWith:
                _sql.Append(" LIKE ");
                _sql.AppendParameter("%" + value);
                break;

            case SqlColumnConstraintOperator.In:
            case SqlColumnConstraintOperator.NotIn:
                var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
                _sql.Append(" ").Append(opSql).Append(" (");
                if (value is IEnumerable)
                {
                    bool first = true;
                    foreach (var item in value as IEnumerable)
                    {
                        if (!first)
                        {
                            _sql.Append(',');
                        }
                        _sql.AppendParameter(item);
                        first = false;
                    }
                }
                else if (value is SqlNode)
                {
                    _sql.AppendLine();
                    this.Indent++;
                    this.Visit(value as SqlNode);
                    this.Indent--;
                    _sql.AppendLine();
                }
                _sql.Append(')');
                break;

            default:
                throw new NotSupportedException();
            }

            return(node);
        }
Example #10
0
        protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
        {
            var op    = node.Operator;
            var value = node.Value;

            value = this.PrepareConstraintValue(value);

            #region 处理一些特殊的值

            switch (op)
            {
            case SqlColumnConstraintOperator.Like:
            case SqlColumnConstraintOperator.Contains:
            case SqlColumnConstraintOperator.StartsWith:
            case SqlColumnConstraintOperator.EndsWith:
                //如果是空字符串的模糊对比操作,直接认为是真。
                var strValue = value as string;
                if (string.IsNullOrEmpty(strValue))
                {
                    _sql.Append("1 = 1");
                    return(node);
                }
                break;

            case SqlColumnConstraintOperator.NotLike:
            case SqlColumnConstraintOperator.NotContains:
            case SqlColumnConstraintOperator.NotStartsWith:
            case SqlColumnConstraintOperator.NotEndsWith:
                //如果是空字符串的模糊对比操作,直接认为是假。
                var strValue2 = value as string;
                if (string.IsNullOrEmpty(strValue2))
                {
                    _sql.Append("1 != 1");
                    return(node);
                }
                break;

            case SqlColumnConstraintOperator.In:
            case SqlColumnConstraintOperator.NotIn:
                //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理:
                //In(Empty) 表示 false,NotIn(Empty) 表示 true。
                if (value is IEnumerable)
                {
                    bool hasValue = false;
                    foreach (var item in value as IEnumerable)
                    {
                        hasValue = true;
                        break;
                    }
                    if (!hasValue)
                    {
                        if (op == SqlColumnConstraintOperator.In)
                        {
                            _sql.Append("0 = 1");
                        }
                        else
                        {
                            _sql.Append("1 = 1");
                        }

                        return(node);
                    }
                }
                break;

            default:
                break;
            }

            #endregion

            this.AppendColumnUsage(node.Column);

            //根据不同的操作符,来生成不同的_sql。
            switch (op)
            {
            case SqlColumnConstraintOperator.Equal:
                if (value == null || value == DBNull.Value)
                {
                    _sql.Append(" IS NULL");
                }
                else
                {
                    _sql.Append(" = ");
                    _sql.AppendParameter(value);
                }
                break;

            case SqlColumnConstraintOperator.NotEqual:
                if (value == null || value == DBNull.Value)
                {
                    _sql.Append(" IS NOT NULL");
                }
                else
                {
                    _sql.Append(" != ");
                    _sql.AppendParameter(value);
                }
                break;

            case SqlColumnConstraintOperator.Greater:
                _sql.Append(" > ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.GreaterEqual:
                _sql.Append(" >= ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.Less:
                _sql.Append(" < ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.LessEqual:
                _sql.Append(" <= ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.Like:
                _sql.Append(" LIKE ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.NotLike:
                _sql.Append(" NOT LIKE ");
                _sql.AppendParameter(value);
                break;

            case SqlColumnConstraintOperator.Contains:
                _sql.Append(" LIKE ");
                _sql.AppendParameter(WILDCARD_ALL + this.Escape(value) + WILDCARD_ALL);
                this.AppendEscapePlause(value);
                break;

            case SqlColumnConstraintOperator.NotContains:
                _sql.Append(" NOT LIKE ");
                _sql.AppendParameter(WILDCARD_ALL + this.Escape(value) + WILDCARD_ALL);
                this.AppendEscapePlause(value);
                break;

            case SqlColumnConstraintOperator.StartsWith:
                _sql.Append(" LIKE ");
                _sql.AppendParameter(this.Escape(value) + WILDCARD_ALL);
                this.AppendEscapePlause(value);
                break;

            case SqlColumnConstraintOperator.NotStartsWith:
                _sql.Append(" NOT LIKE ");
                _sql.AppendParameter(this.Escape(value) + WILDCARD_ALL);
                this.AppendEscapePlause(value);
                break;

            case SqlColumnConstraintOperator.EndsWith:
                _sql.Append(" LIKE ");
                _sql.AppendParameter(WILDCARD_ALL + this.Escape(value));
                this.AppendEscapePlause(value);
                break;

            case SqlColumnConstraintOperator.NotEndsWith:
                _sql.Append(" NOT LIKE ");
                _sql.AppendParameter(WILDCARD_ALL + this.Escape(value));
                this.AppendEscapePlause(value);
                break;

            case SqlColumnConstraintOperator.In:
            case SqlColumnConstraintOperator.NotIn:
                var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
                _sql.Append(" ").Append(opSql).Append(" (");
                if (value is IEnumerable)
                {
                    bool first         = true;
                    bool needDelimiter = false;
                    int  i             = 0;
                    foreach (var item in value as IEnumerable)
                    {
                        if (++i > this.MaxItemsInInClause)
                        {
                            throw new TooManyItemsInInClauseException();
                        }

                        if (first)
                        {
                            first         = false;
                            needDelimiter = item is string || item is DateTime || item is Guid;
                        }
                        else
                        {
                            _sql.Append(',');
                        }

                        if (needDelimiter)
                        {
                            _sql.Append('\'').Append(item).Append('\'');
                        }
                        else
                        {
                            _sql.Append(item);
                        }

                        //由于集合中的数据可能过多,所以这里不要使用参数化的查询。
                        //_sql.AppendParameter(item);
                    }
                }
                else if (value is SqlNode)
                {
                    _sql.AppendLine();
                    this.Indent++;
                    this.Visit(value as SqlNode);
                    this.Indent--;
                    _sql.AppendLine();
                }
                _sql.Append(')');
                break;

            default:
                throw new NotSupportedException();
            }

            return(node);
        }
Example #11
0
        /// <summary>
        /// 重写VisitSqlColumnConstraint方法
        /// 处理In、NotIn操作,如果传入的值为字符串,则查询条件增加N前缀
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
        {
            var op    = node.Operator;
            var value = node.Value;

            value = this.DbTypeCoverter.ToDbParameterValue(value);

            #region 处理一些特殊的值

            switch (op)
            {
            case SqlColumnConstraintOperator.In:
            case SqlColumnConstraintOperator.NotIn:
                //对于 In、NotIn 操作,如果传入的是空列表时,需要特殊处理:
                //In(Empty) 表示 false,NotIn(Empty) 表示 true。
                if (value is IEnumerable)
                {
                    bool hasValue = false;
                    foreach (var item in value as IEnumerable)
                    {
                        hasValue = true;
                        break;
                    }
                    if (!hasValue)
                    {
                        if (op == SqlColumnConstraintOperator.In)
                        {
                            Sql.Append("0 = 1");
                        }
                        else
                        {
                            Sql.Append("1 = 1");
                        }

                        return(node);
                    }
                }
                break;

            default:
                break;
            }

            #endregion

            //根据不同的操作符,来生成不同的Sql。
            switch (op)
            {
            case SqlColumnConstraintOperator.In:
            case SqlColumnConstraintOperator.NotIn:

                var column = node.Column;
                base.AppendColumnUsage(node.Column);

                var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
                Sql.Append(" ").Append(opSql).Append(" (");
                if (value is IEnumerable)
                {
                    bool first         = true;
                    bool needDelimiter = false;
                    bool isString      = false;
                    int  i             = 0;
                    foreach (var item in value as IEnumerable)
                    {
                        if (++i > this.MaxItemsInInClause)
                        {
                            throw new TooManyItemsInInClauseException();
                        }

                        if (first)
                        {
                            first         = false;
                            needDelimiter = item is string || item is DateTime || item is Guid;
                            isString      = item is string;
                        }
                        else
                        {
                            Sql.Append(',');
                        }

                        if (needDelimiter)
                        {
                            //判断值否是为字符串,如果是查询语句增加N前缀
                            if (isString)
                            {
                                Sql.Append('N').Append('\'').Append(EscapeSpecialChar(item)).Append('\'');
                            }
                            else
                            {
                                Sql.Append('\'').Append(EscapeSpecialChar(item)).Append('\'');
                            }
                        }
                        else
                        {
                            Sql.Append(item);
                        }
                    }
                }
                else if (value is SqlNode)
                {
                    Sql.AppendLine();
                    this.Indent++;
                    this.Visit(value as SqlNode);
                    this.Indent--;
                    Sql.AppendLine();
                }
                Sql.Append(')');
                return(node);

            default:
                break;
            }

            return(base.VisitSqlColumnConstraint(node));
        }
Example #12
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;
        }