private ParametrizedSqlPart CreateWhereClause(SelectQuery query)
{
var context = new ConditionGeneratorContext();
var columnTypeMap = query.Columns.ToDictionary(x => x.ColumnName, x => x.DbType);
var sb = new StringBuilder();
var parameters = new List <SqlQueryParameter>();
foreach (var condition in query.Conditions)
{
if (sb.Length > 0)
{
sb.Append(" AND ");
}
var conditionPart = CreateCondition(condition, columnTypeMap, context);
sb.Append(conditionPart.Sql);
parameters.AddRange(conditionPart.Parameters);
}
var sqlPart = new ParametrizedSqlPart()
{
Sql = sb.ToString(),
Parameters = parameters
};
return(sqlPart);
}
private void GenerateByConditions(ref GenerateContext context, IEnumerable <ConditionInfo> conditions)
{
if (conditions == null)
{
return;
}
if (!conditions.Any())
{
return;
}
var sqlBuilder = context.StringBuilder;
var result = context.SqlCommandDescription;
sqlBuilder.Append(" WHERE");
foreach (var condition in conditions)
{
if (condition.IsNot)
{
sqlBuilder.Append(" NOT(");
}
ConditionGeneratorContext conditionContext = new ConditionGeneratorContext(this,
result,
sqlBuilder,
condition);
this.conditionGenerateHandler.Handle(conditionContext);
if (condition.IsNot)
{
sqlBuilder.Append(")");
}
if (condition.JoinerToNext != ConditionJoiners.Null)
{
sqlBuilder.Append($" {condition.JoinerToNext}");
}
}
}
private ParametrizedSqlPart CreateConditionWithValue(
FilterConditionDTO condition,
object value,
ConditionGeneratorContext context)
{
var paramName = context.GetNextParameterName();
var sb = new StringBuilder();
sb.Append("(");
sb.Append("[");
sb.Append(condition.Field);
sb.Append("]");
sb.Append(" ");
sb.Append(GetSqlOperator(condition.Operator));
sb.Append(" ");
sb.Append(paramName);
sb.Append(")");
var sqlPart = new ParametrizedSqlPart()
{
Sql = sb.ToString(),
Parameters = new List <SqlQueryParameter>()
{
new SqlQueryParameter(paramName, value != null ? value : DBNull.Value)
}
};
return(sqlPart);
}
private ParametrizedSqlPart CreateCondition(
FilterConditionDTO condition,
IDictionary <string, DbType> columnTypeMap,
ConditionGeneratorContext context)
{
DbType dbType;
if (!columnTypeMap.TryGetValue(condition.Field, out dbType))
{
throw new ApplicationException("Invalid column name.");
}
switch (dbType)
{
case DbType.Boolean:
return(CreateBooleanCondition(condition, context));
case DbType.String:
return(CreateStringCondition(condition, context));
case DbType.Int32:
return(CreateInt32Condition(condition, context));
default:
throw new ApplicationException("Invalid column data-type.");
}
}
private ParametrizedSqlPart CreateBooleanCondition(
FilterConditionDTO condition,
ConditionGeneratorContext context)
{
var booleanValue = string.Equals(
condition.Value,
"true",
StringComparison.InvariantCultureIgnoreCase
);
return(CreateConditionWithValue(condition, booleanValue, context));
}
private ParametrizedSqlPart CreateInt32Condition(
FilterConditionDTO condition,
ConditionGeneratorContext context)
{
int intValue;
if (!Int32.TryParse(condition.Value, out intValue))
{
intValue = 0;
}
return(CreateConditionWithValue(condition, intValue, context));
}
private ParametrizedSqlPart CreateStringCondition(
FilterConditionDTO condition,
ConditionGeneratorContext context)
{
return(CreateConditionWithValue(condition, condition.Value, context));
}
