///<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); }
///<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); }
/// <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); }