private void AddConstraint(string columnName, Comparison comp, object value)
        {
            Constraint c = new Constraint(ConstraintType.Where, columnName);

            if(result.Count > 1)
                c = new Constraint(ConstraintType.And, columnName);

            //c.ParameterName = columnName;

            if(comp == Comparison.StartsWith)
            {
                value = string.Format("{0}%", value);
                comp = Comparison.Like;
            }
            else if(comp == Comparison.EndsWith)
            {
                value = string.Format("%{0}", value);
                comp = Comparison.Like;
            }

            c.Comparison = comp;
            c.ParameterValue = value;

            result.Add(c);
        }
Beispiel #2
0
        ///<summary>
        /// Builds a SubSonic DELETE query from the passed-in object
        ///</summary>
        public static ISqlQuery ToDeleteQuery <T>(this T item, IDataProvider provider) where T : class, new()
        {
            Type   type  = typeof(T);
            ITable tbl   = provider.FindOrCreateTable <T>();
            var    query = new Delete <T>(tbl, provider);

            if (tbl != null)
            {
                IColumn pk       = tbl.PrimaryKey;
                var     settings = item.ToDictionary();
                if (pk != null)
                {
                    var c = new Constraint(ConstraintType.Where, pk.Name)
                    {
                        ParameterValue       = settings[pk.Name],
                        ParameterName        = pk.Name,
                        ConstructionFragment = pk.Name
                    };
                    query.Constraints.Add(c);
                }
                else
                {
                    query.Constraints = item.ToConstraintList();
                }
            }
            return(query);
        }
Beispiel #3
0
 public IList<Constraint> GetConstraints(Expression ex)
 {
     constraints = new List<Constraint>();
     current = new Constraint();
     query = new SqlQuery();
     var expression = Visit(ex);
     return query.Constraints;
 }
Beispiel #4
0
        public SqlQuery Delete <T>(Expression <Func <T, bool> > column) where T : new()
        {
            LambdaExpression lamda  = column;
            SqlQuery         result = new Delete <T>(this.Provider);

            result = result.From <T>();
            SubSonic.Query.Constraint c = lamda.ParseConstraint();
            result.Constraints.Add(c);
            return(result);
        }
Beispiel #5
0
        ///<summary>
        /// Builds a SubSonic UPDATE query from the passed-in object
        ///</summary>
        public static ISqlQuery ToUpdateQuery <T>(this T item, IDataProvider provider) where T : class, new()
        {
            Type type     = typeof(T);
            var  settings = item.ToDictionary();

            ITable tbl = provider.FindOrCreateTable <T>();

            Update <T> query = new Update <T>(tbl.Provider);

            if (item is IActiveRecord)
            {
                var ar = item as IActiveRecord;
                foreach (var dirty in ar.GetDirtyColumns())
                {
                    if (!dirty.IsPrimaryKey && !dirty.IsReadOnly)
                    {
                        query.Set(dirty.Name).EqualTo(settings[dirty.Name]);
                    }
                }
            }
            else
            {
                foreach (string key in settings.Keys)
                {
                    IColumn col = tbl.GetColumnByPropertyName(key);
                    if (col != null)
                    {
                        if (!col.IsPrimaryKey && !col.IsReadOnly)
                        {
                            query.Set(col).EqualTo(settings[key]);
                        }
                    }
                }
            }

            //add the PK constraint
            Constraint c = new Constraint(ConstraintType.Where, tbl.PrimaryKey.Name)
            {
                ParameterValue       = settings[tbl.PrimaryKey.Name],
                ParameterName        = tbl.PrimaryKey.Name,
                ConstructionFragment = tbl.PrimaryKey.Name
            };

            query.Constraints.Add(c);

            return(query);
        }
Beispiel #6
0
 protected void SetConstraintWildcards(Constraint constraint)
 {
     if (constraint.ParameterValue is string)
     {
         switch (constraint.Comparison)
         {
             case Comparison.StartsWith:
                 constraint.ParameterValue = constraint.ParameterValue + "%";
                 break;
             case Comparison.EndsWith:
                 constraint.ParameterValue = "%" + constraint.ParameterValue;
                 break;
             case Comparison.Like:
                 constraint.ParameterValue = "%" + constraint.ParameterValue + "%";
                 break;
         }
     }
 }
Beispiel #7
0
        protected override Expression VisitBinary(BinaryExpression b)
        {
            b = ConvertVbCompareString(b);

            current = new Constraint();

            if(b.NodeType == ExpressionType.AndAlso)
            {
                if(query.Constraints.Count > 0)
                    current.Condition = ConstraintType.And;
            }
            else if(b.NodeType == ExpressionType.OrElse || b.NodeType == ExpressionType.Or)
            {
                if(query.Constraints.Count > 0)
                    current.Condition = ConstraintType.Or;
            }
            else if(b.NodeType == ExpressionType.AndAlso) {}
            else if(b.NodeType == ExpressionType.Or || b.NodeType == ExpressionType.OrElse)
            {
                if(query.Constraints.Count > 0)
                    current.Condition = ConstraintType.Or;
            }
            else if(b.NodeType == ExpressionType.NotEqual)
                current.Comparison = Comparison.NotEquals;
            else if(b.NodeType == ExpressionType.Equal)
                current.Comparison = Comparison.Equals;
            else if(b.NodeType == ExpressionType.GreaterThan)
                current.Comparison = Comparison.GreaterThan;
            else if(b.NodeType == ExpressionType.GreaterThanOrEqual)
                current.Comparison = Comparison.GreaterOrEquals;
            else if(b.NodeType == ExpressionType.LessThan)
                current.Comparison = Comparison.LessThan;
            else if(b.NodeType == ExpressionType.Not)
                Visit(b);
            else if(b.NodeType == ExpressionType.LessThanOrEqual)
                current.Comparison = Comparison.LessOrEquals;

            isLeft = true;
            Expression left = Visit(b.Left);
            isLeft = false;
            Expression right = Visit(b.Right);
            Expression conversion = Visit(b.Conversion);

            if(b.NodeType == ExpressionType.AndAlso)
            {
                if(query.Constraints.Count > 0)
                    current.Condition = ConstraintType.And;
            }
            else if(b.NodeType == ExpressionType.OrElse || b.NodeType == ExpressionType.Or)
            {
                if(query.Constraints.Count > 0)
                    current.Condition = ConstraintType.Or;
            }

            //query.OpenExpression();
            AddConstraint();
            //query.CloseExpression();
            //if (expressionOpen) {
            //    query.CloseExpression();
            //    expressionOpen = false;
            //}

            if (left != b.Left || right != b.Right || conversion != b.Conversion)
            {
                if(b.NodeType == ExpressionType.Coalesce && b.Conversion != null)
                    return Expression.Coalesce(left, right, conversion as LambdaExpression);
                return Expression.MakeBinary(b.NodeType, left, right, b.IsLiftedToNull, b.Method);
            }
            return b;
        }
        /// <summary>
        /// Generates the constraints.
        /// </summary>
        /// <returns></returns>
        public virtual string GenerateConstraints()
        {
            string whereOperator = this.sqlFragment.WHERE;

            if (query.Aggregates.Count > 0 && query.Aggregates.Any(x => x.AggregateType == AggregateFunction.GroupBy))
            {
                whereOperator = this.sqlFragment.HAVING;
            }

            StringBuilder sb = new StringBuilder();

            sb.AppendLine();
            bool isFirst = true;

            //int paramCount;
            bool expressionIsOpen = false;
            int  indexer          = 0;

            foreach (Constraint c in query.Constraints)
            {
                string columnName  = String.Empty;
                bool   foundColumn = false;
                if (c.ConstructionFragment == c.ColumnName && c.ConstructionFragment != "##")
                {
                    IColumn col = FindColumn(c.ColumnName);

                    if (col != null)
                    {
                        columnName      = col.QualifiedName;
                        c.ParameterName = string.Format("{0}{1}", GetParameterPrefix(), indexer);
                        c.DbType        = col.DataType;
                        foundColumn     = true;
                    }
                }

                if (!foundColumn && c.ConstructionFragment != "##")
                {
                    bool isAggregate = false;
                    //this could be an expression
                    //string rawColumnName = c.ConstructionFragment;
                    if (c.ConstructionFragment.StartsWith("("))
                    {
                        //rawColumnName = c.ConstructionFragment.Replace("(", String.Empty);
                        expressionIsOpen = true;
                    }
                    //this could be an aggregate function
                    else if (c.IsAggregate ||
                             (c.ConstructionFragment.Contains("(") && c.ConstructionFragment.Contains(")")))
                    {
                        //rawColumnName = c.ConstructionFragment.Replace("(", String.Empty).Replace(")", String.Empty);
                        isAggregate = true;
                    }

                    IColumn col = FindColumn(c.ColumnName);
                    if (!isAggregate && col != null)
                    {
                        columnName      = c.ConstructionFragment.FastReplace(col.Name, col.QualifiedName);
                        c.ParameterName = String.Concat(col.ParameterName, indexer.ToString());
                        c.DbType        = col.DataType;
                    }
                    else
                    {
                        c.ParameterName = query.FromTables[0].Provider.ParameterPrefix + indexer;
                        columnName      = c.ConstructionFragment;
                    }
                }

                //paramCount++;

                if (!isFirst)
                {
                    whereOperator = Enum.GetName(typeof(ConstraintType), c.Condition);
                    whereOperator = String.Concat(" ", whereOperator.ToUpper(), " ");
                }

                if (c.Comparison != Comparison.OpenParentheses && c.Comparison != Comparison.CloseParentheses)
                {
                    sb.Append(whereOperator);
                }

                if (c.Comparison == Comparison.BetweenAnd)
                {
                    sb.Append(columnName);
                    sb.Append(this.sqlFragment.BETWEEN);
                    sb.Append(c.ParameterName + "_start");
                    sb.Append(this.sqlFragment.AND);
                    sb.Append(c.ParameterName + "_end");
                }
                else if (c.Comparison == Comparison.In || c.Comparison == Comparison.NotIn)
                {
                    sb.Append(columnName);
                    if (c.Comparison == Comparison.In)
                    {
                        sb.Append(this.sqlFragment.IN);
                    }
                    else
                    {
                        sb.Append(this.sqlFragment.NOT_IN);
                    }

                    sb.Append("(");

                    if (c.InSelect != null)
                    {
                        //create a sql statement from the passed-in select
                        string sql = c.InSelect.BuildSqlStatement();
                        sb.Append(sql);
                    }
                    else
                    {
                        //enumerate INs
                        IEnumerator   en    = c.InValues.GetEnumerator();
                        StringBuilder sbIn  = new StringBuilder();
                        bool          first = true;
                        int           i     = 1;
                        while (en.MoveNext())
                        {
                            if (!first)
                            {
                                sbIn.Append(",");
                            }
                            else
                            {
                                first = false;
                            }

                            sbIn.Append(String.Concat(c.ParameterName, "In", i));
                            i++;
                        }

                        string inList = sbIn.ToString();
                        //inList = Sugar.Strings.Chop(inList);
                        sb.Append(inList);
                    }

                    sb.Append(")");
                }
                else if (c.Comparison == Comparison.OpenParentheses)
                {
                    expressionIsOpen = true;
                    sb.Append("(");
                }
                else if (c.Comparison == Comparison.CloseParentheses)
                {
                    expressionIsOpen = false;
                    sb.Append(")");
                }
                else
                {
                    if (columnName.StartsWith("("))
                    {
                        expressionIsOpen = true;
                    }
                    if (c.ConstructionFragment != "##")
                    {
                        sb.Append(columnName);
                        sb.Append(Constraint.GetComparisonOperator(c.Comparison));
                        if (c.Comparison == Comparison.Is || c.Comparison == Comparison.IsNot)
                        {
                            if (c.ParameterValue == null || c.ParameterValue == DBNull.Value)
                            {
                                sb.Append("NULL");
                            }
                        }
                        else
                        {
                            sb.Append(c.ParameterName);
                        }
                    }
                }
                indexer++;

                isFirst = false;
            }

            string result = sb.ToString();

            //a little help...
            if (expressionIsOpen & !result.EndsWith(")"))
            {
                result = String.Concat(result, ")");
            }

            return(result);
        }
        private Constraint BuildAlwaysFalseConstraint()
        {
            var falseConstraint = new Constraint();

            falseConstraint.ConstructionFragment = "1";
            falseConstraint.ParameterValue = 0;
            falseConstraint.ColumnName = String.Empty;
            falseConstraint.Comparison = Comparison.Equals;

            return falseConstraint;
        }
Beispiel #10
0
        private void BuildCollectionConstraint(MethodCallExpression methodCallExpression)
        {
            if (methodCallExpression.Arguments.Count == 2)
            {
                isLeft = true;
                Visit(methodCallExpression.Arguments[1]);

                isLeft = false;

                var c = Visit(methodCallExpression.Arguments[0]) as ConstantExpression;

                if (c != null)
                {
                    // Constants
                    current.InValues = c.Value as IEnumerable;
                }
                else
                {
                    // something parsed to parameter values
                    current.InValues = current.ParameterValue as IEnumerable;
                }

                current.Comparison = isNot ? Comparison.NotIn : Comparison.In;

                if (current.InValues == null || !current.InValues.GetEnumerator().MoveNext())
                {
                    current = BuildAlwaysFalseConstraint();
                }
            }
        }
Beispiel #11
0
        /// <summary>
        /// Converts the string method calls Contains,EndsWith and StartsWith into queries
        /// </summary>
        /// <param name="methodCallExpression">The MethodCall we are attempting to map to a query.</param>
        /// <returns>an expression tree.</returns>
        protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
        {
            // TODO: Here we only support member expressions -> Extend to solve http://github.com/subsonic/SubSonic-3.0/issues#issue/59
            Expression result = methodCallExpression;
            var obj = methodCallExpression.Object as MemberExpression;
            if (obj != null)
            {
                var constraint = new Constraint();
                switch (methodCallExpression.Method.Name)
                {
                    case "Contains":
                        constraint.Comparison = Comparison.Like;
                        break;
                    case "EndsWith":
                        constraint.Comparison = Comparison.EndsWith;
                        break;
                    case "StartsWith":
                        constraint.Comparison = Comparison.StartsWith;
                        break;
                    default:
                        return base.VisitMethodCall(methodCallExpression);
                }
                // Set the starting / ending wildcards on the parameter value... not the best place to do this, but I'm 
                // attempting to constrain the scope of the change.
                constraint.ConstructionFragment = obj.Member.Name;
                // Set the current constraint... Visit will be using it, I don't know what it would do with multiple args....
                current = constraint;
                foreach (var arg in methodCallExpression.Arguments)
                {
                    isLeft = false;
                    Visit(arg);
                }
                isLeft = true;
                // After Visit, the current constraint will have some parameters, so set the wildcards on the parameter.
                SetConstraintWildcards(constraint);
            }
            else
            {
                switch (methodCallExpression.Method.Name)
                {
                    case "Contains":
                    case "Any":
                        BuildCollectionConstraint(methodCallExpression);
                        break;
                    default:
                        throw new InvalidOperationException(
                            String.Format("Method {0} is not supported in linq statement!",
                            methodCallExpression.Method.Name));
                }
            }

            AddConstraint();
            return methodCallExpression;
        }
Beispiel #12
0
				/// Converts the string method calls Contains,EndsWith and StartsWith into queries
				/// </summary>
				/// <param name="m">The MethodCall we are attempting to map to a query.</param>
				/// <returns>an expression tree.</returns>
				protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
				{
					Expression result = methodCallExpression;
					var obj = methodCallExpression.Object as MemberExpression;
					if (obj != null)
					{
						var constraint = new Constraint();
						switch (methodCallExpression.Method.Name)
						{
							case "Contains":
								constraint.Comparison = Comparison.Like;
								break;
							case "EndsWith":
								constraint.Comparison = Comparison.EndsWith;
								break;
							case "StartsWith":
								constraint.Comparison = Comparison.StartsWith;
								break;
							default:
								return base.VisitMethodCall(methodCallExpression);
						}
						// Set the starting / ending wildcards on the parameter value... not the best place to do this, but I'm 
						// attempting to constrain the scope of the change.
						constraint.ConstructionFragment = obj.Member.Name;
						// Set the current constraint... Visit will be using it, I don't know what it would do with multiple args....
						current = constraint;
						foreach (var arg in methodCallExpression.Arguments)
						{
							isLeft = false;
							Visit(arg);
						}
						isLeft = true;
						// After Visit, the current constraint will have some parameters, so set the wildcards on the parameter.
						SetConstraintWildcards(constraint);
					}

					AddConstraint();
					return methodCallExpression;
				}
Beispiel #13
0
            /// <summary>
            /// 生成SqlQuery类调用时所需要的条件格式——目前查询条件中不能使用括号,如果需要须修改代码
            /// </summary>
            /// <param name="scd">SqlQuery条件类</param>
            public static List<Constraint> Condition(List<SqlqueryCondition> scd)
            {
                if (scd == null)
                    return null;

                var constrain = new List<Constraint>();

                foreach (SqlqueryCondition item in scd) {
                    if (item != null) {
                        var con = new Constraint(item.SQConstraintType, item.SQColumnName);
                        con.Comparison = item.SQComparison;
                        con.ParameterValue = item.SQValue;
                        constrain.Add(con);
                    }
                }

                return constrain;
            }