Example #1
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);
        }
Example #2
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.GetColumn(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);
        }
Example #3
0
        /// <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);
        }