/// <summary>
/// 根据查询条件生成SQL语句与SQL参数
/// </summary>
/// <param name="context">用来生成SQL的上下文</param>
/// <param name="conditon">查询条件，可为查询条件集合或单个条件，为空表示无条件</param>
/// <param name="outputParams">供输出的参数列表，在该列表中添加SQL参数</param>
/// <returns>生成的SQL语句，null表示无条件</returns>
public virtual string BuildConditionSql(SqlBuildContext context, Condition conditon, IList outputParams)
{
if (conditon == null)
{
return(null);
}
else if (conditon is SimpleCondition)
{
return(BuildSimpleConditionSql(context, conditon as SimpleCondition, outputParams));
}
else if (conditon is ConditionSet)
{
return(BuildConditionSetSql(context, conditon as ConditionSet, outputParams));
}
else if (conditon is ForeignCondition)
{
return(BuildForeignConditionSql(context, conditon as ForeignCondition, outputParams));
}
else
{
if (extCondtionBuilders.ContainsKey(conditon.GetType()))
{
return(extCondtionBuilders[conditon.GetType()].BuildConditionSql(context, conditon, outputParams));
}
else
{
throw new Exception(String.Format("Unsupported condition type \"{0}\"! Please register ConditionBuilder before call BuildConditionSql method.", conditon.GetType().FullName));
}
}
}
/// <summary>
/// 根据简单查询条件生成SQL语句与SQL参数
/// </summary>
/// <param name="context">用来生成SQL的上下文</param>
/// <param name="simpleCondition">简单查询条件</param>
/// <param name="outputParams">参数列表，在该列表中添加SQL参数</param>
/// <returns>生成的SQL语句</returns>
protected string BuildSimpleConditionSql(SqlBuildContext context, SimpleCondition simpleCondition, IList outputParams)
{
Column column = context.Table.GetColumn(simpleCondition.Property);
if (column == null)
{
throw new Exception(String.Format("Property \"{0}\" does not exist in type \"{1}\".", simpleCondition.Property, context.Table.DefinitionType.FullName));
}
string tableAlias = context.TableAliasName;
string columnName = tableAlias == null?column.FormattedExpression(this) : String.Format("[{0}].[{1}]", tableAlias, column.Name);
string expression = columnName;
object value = simpleCondition.Value;
string strOpposite = simpleCondition.Opposite ? "not" : null;
if ((simpleCondition.Value == null || simpleCondition.Value == DBNull.Value) && simpleCondition.Operator == ConditionOperator.Equals)
{
return(string.Format("{0} is {1} null", expression, strOpposite));
}
ConditionOperator positiveOp = simpleCondition.Operator;
if (positiveOp == ConditionOperator.Contains || positiveOp == ConditionOperator.EndsWith || positiveOp == ConditionOperator.StartsWith)
{
value = sqlLike.Replace(Convert.ToString(value), LikeEscapeChar + "$1");
}
switch (simpleCondition.Operator)
{
case ConditionOperator.Equals: return(String.Format(simpleCondition.Opposite ? "{0} <> {1}" : "{0} = {1}", expression, ToSqlParam(outputParams.Add(value).ToString())));
case ConditionOperator.LargerThan: return(String.Format(simpleCondition.Opposite ? "{0} <= {1}" : "{0} > {1}", expression, ToSqlParam(outputParams.Add(value).ToString())));
case ConditionOperator.SmallerThan: return(String.Format(simpleCondition.Opposite ? "{0} >= {1}" : "{0} < {1}", expression, ToSqlParam(outputParams.Add(value).ToString())));
case ConditionOperator.Like: return(String.Format(@"{0} {1} like {2}", expression, strOpposite, ToSqlParam(outputParams.Add(value).ToString())));
case ConditionOperator.StartsWith: return(String.Format(@"{0} {1} like {2} escape '{3}'", expression, strOpposite, ConcatSql(ToSqlParam(outputParams.Add(value).ToString()), "'%'"), LikeEscapeChar));
case ConditionOperator.EndsWith: return(String.Format(@"{0} {1} like {2} escape '{3}'", expression, strOpposite, ConcatSql("'%'", ToSqlParam(outputParams.Add(value).ToString())), LikeEscapeChar));
case ConditionOperator.Contains: return(String.Format(@"{0} {1} like {2} escape '{3}'", expression, strOpposite, ConcatSql("'%'", ToSqlParam(outputParams.Add(value).ToString()), "'%'"), LikeEscapeChar));
case ConditionOperator.In:
List <string> paramNames = new List <string>();
foreach (object item in value as IEnumerable)
{
paramNames.Add(ToSqlParam(outputParams.Add(item).ToString()));
}
return(String.Format("{0} {1} in ({2})", expression, strOpposite, String.Join(",", paramNames.ToArray())));
default:
return(string.Empty);
}
}
/// <summary>
/// 根据查询条件集合生成SQL语句与SQL参数
/// </summary>
/// <param name="context">用来生成SQL的上下文</param>
/// <param name="conditionSet">查询条件的集合</param>
/// <param name="outputParams">供输出的参数列表，在该列表中添加SQL参数</param>
/// <returns>生成的SQL语句，null表示无条件</returns>
protected string BuildConditionSetSql(SqlBuildContext context, ConditionSet conditionSet, IList outputParams)
{
List <string> conditions = new List <string>();
foreach (Condition subConditon in conditionSet.SubConditions)
{
string str = BuildConditionSql(context, subConditon, outputParams);
if (!String.IsNullOrEmpty(str))
{
conditions.Add(str);
}
}
if (conditions.Count == 0)
{
return(null);
}
return(String.Format("{0} ({1})", conditionSet.Opposite ? "not" : null, String.Join(" " + conditionSet.JoinType + " ", conditions.ToArray())));
}
/// <summary>
/// 根据外部对象查询条件生成SQL语句与SQL参数
/// </summary>
/// <param name="context">用来生成SQL的上下文</param>
/// <param name="condition">外部对象的查询条件</param>
/// <param name="outputParams">供输出的参数列表，在该列表中添加SQL参数</param>
/// <returns>生成的SQL语句，null表示无条件</returns>
protected string BuildForeignConditionSql(SqlBuildContext context, ForeignCondition condition, IList outputParams)
{
TableDefinition tableDefinition = context.Table.Definition;
ColumnDefinition joinedColumn = tableDefinition.GetColumn(condition.JoinedProperty);
Type foreignType = condition.ForeignType;
if (foreignType == null)
{
if (joinedColumn == null)
{
throw new ArgumentException(String.Format("Property {0} not exists.", condition.JoinedProperty), condition.JoinedProperty);
}
if (joinedColumn.ForeignType == null)
{
throw new ArgumentException(String.Format("Property {0} does not point to a foreign type.", condition.JoinedProperty), condition.JoinedProperty);
}
foreignType = joinedColumn.ForeignType;
}
TableDefinition foreignTable = Provider.GetTableDefinition(foreignType);
ColumnDefinition foreignColumn = foreignTable.GetColumn(condition.ForeignProperty);
if (joinedColumn == null && foreignColumn == null)
{
foreach (ColumnDefinition column in tableDefinition.Columns)
{
if (column.ForeignType == foreignType)
{
if (joinedColumn != null || foreignColumn != null)
{
throw new ArgumentException(String.Format("Uncertain relation between Type {0} and Type {1}. Please specify the ForeignCondition.JoinedProperty.", context.Table.DefinitionType.FullName, foreignTable.ObjectType.FullName), "condition");
}
joinedColumn = column;
}
}
foreach (ColumnDefinition column in foreignTable.Columns)
{
if (column.ForeignType == context.Table.DefinitionType)
{
if (joinedColumn != null || foreignColumn != null)
{
throw new ArgumentException(String.Format("Uncertain relation between Type {0} and Type {1}. Please specify the ForeignCondition.JoinedProperty.", context.Table.DefinitionType.FullName, foreignTable.ObjectType.FullName), "condition");
}
foreignColumn = column;
}
}
if (joinedColumn == null && foreignColumn == null)
{
throw new ArgumentException(String.Format("No relation between Type {0} and Type {1}", context.Table.DefinitionType.FullName, foreignTable.ObjectType.FullName), "condition");
}
}
if (foreignColumn == null)
{
if (foreignTable.Keys.Count != 1)
{
throw new ArgumentException(String.Format("Type \"{0}\" does not support foreign condition,which only take effect on type with one and only key column.", foreignType.FullName), "condition");
}
foreignColumn = foreignTable.Keys[0];
}
else if (joinedColumn == null)
{
if (context.Table.Definition.Keys.Count != 1)
{
throw new ArgumentException(String.Format("Type \"{0}\" does not support foreign condition,which only take effect on type with one and only key column.", context.Table.DefinitionType.FullName), "condition");
}
joinedColumn = context.Table.Definition.Keys[0];
}
string tableAlias = context.TableAliasName ?? context.Table.Name;
string foreignTableAlias = "T" + context.Sequence;
return(String.Format("{0}exists (select 1 from [{1}] [{2}] where [{3}].[{4}] = [{5}].[{6}] and ({7}))",
condition.Opposite ? "not " : null,
foreignTable.Name,
foreignTableAlias,
tableAlias,
joinedColumn.PropertyName,
foreignTableAlias,
foreignColumn.Name,
BuildConditionSql(new SqlBuildContext()
{
TableAliasName = foreignTableAlias, Sequence = context.Sequence + 1, Table = foreignTable
}, condition.Condition, outputParams)));
}
