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