public static SqlCommand AddFiltering(this SqlCommand cmd, FilterParameterCollection filters)
{
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
if (filters == null || filters.Count == 0)
{
return(cmd);
}
cmd.CommandText = EnvelopeExpression(cmd.CommandText, "*") + " where 1=1";
StringBuilder sb = new StringBuilder();
Dictionary <FilterParameter, string> filterParameter_SqlPars = new Dictionary <FilterParameter, string>();
const string parameterPrefix = "@";
Dictionary <string, int> columnStats = new Dictionary <string, int>();
Dictionary <string, int> columnCurStats = new Dictionary <string, int>();
Dictionary <string, int> orGroupStats = new Dictionary <string, int>();
Dictionary <string, int> andGroupStats = new Dictionary <string, int>();
foreach (FilterParameter fp in filters.Where(f => f.Operator != OperatorType.Top))
{
if (columnStats.ContainsKey(fp.ColumnName) == false)
{
columnStats.Add(fp.ColumnName, 0);
}
columnStats[fp.ColumnName]++;
if (!string.IsNullOrEmpty(fp.AndGroupName))
{
if (andGroupStats.ContainsKey(fp.AndGroupName) == false)
{
andGroupStats.Add(fp.AndGroupName, 1);
}
andGroupStats[fp.AndGroupName]++;
}
if (!string.IsNullOrEmpty(fp.OrGroupName))
{
if (orGroupStats.ContainsKey(fp.OrGroupName) == false)
{
orGroupStats.Add(fp.OrGroupName, 1);
}
orGroupStats[fp.OrGroupName]++;
}
}
foreach (FilterParameter fp in filters.Where(f => f.Operator != OperatorType.Top))
{
string safeParameterBaseName = fp.ColumnName.Replace("[", "").Replace("]", "").Replace(".", "__");
if (columnStats[fp.ColumnName] == 1)
{
filterParameter_SqlPars.Add(fp, parameterPrefix + safeParameterBaseName);
}
else
{
if (columnCurStats.ContainsKey(fp.ColumnName) == false)
{
columnCurStats.Add(fp.ColumnName, 0);
}
columnCurStats[fp.ColumnName]++;
filterParameter_SqlPars.Add(fp, parameterPrefix + safeParameterBaseName + "_" + columnCurStats[fp.ColumnName]);
}
}
string orGroup = string.Empty;
string andGroup = string.Empty;
foreach (FilterParameter fp in filters.Where(f => f.Operator != OperatorType.Top))
{
OperatorType op = fp.Operator;
string columnNameWithDelimiter = string.Empty;
if (
(fp.Value == null ||
(fp.Value.GetType() == typeof(Guid) && (Guid)fp.Value == Guid.Empty) ||
(fp.Value.GetType().IsArray&& ((Array)fp.Value).Length == 0)
/*HARDCODE*/
|| (fp.Value.GetType() == typeof(bool) && (bool)fp.Value == false) ||
(fp.Value.GetType() == typeof(string) && string.IsNullOrEmpty((string)fp.Value) && op == OperatorType.Query)
) &&
op != OperatorType.IsFalse &&
op != OperatorType.IsTrue &&
op != OperatorType.IsNull &&
op != OperatorType.IsNotNull &&
op != OperatorType.EqualsWithNull
)
{
continue;
}
else if (op != OperatorType.None)
{
if (!string.IsNullOrEmpty(orGroup) && orGroup != fp.OrGroupName)
{
columnNameWithDelimiter += " ) ";
orGroup = string.Empty;
}
if (!string.IsNullOrEmpty(andGroup) && andGroup != fp.AndGroupName)
{
columnNameWithDelimiter += " ) ";
andGroup = string.Empty;
}
if (string.IsNullOrEmpty(orGroup) && string.IsNullOrEmpty(andGroup))
{
columnNameWithDelimiter += " and ";
}
if (!string.IsNullOrEmpty(fp.OrGroupName) && !string.IsNullOrEmpty(fp.AndGroupName) && fp.OrGroupName != orGroup && fp.AndGroupName != andGroup)
{
columnNameWithDelimiter += " (( ";
orGroup = fp.OrGroupName;
andGroup = fp.AndGroupName;
}
else if (!string.IsNullOrEmpty(fp.OrGroupName) && !string.IsNullOrEmpty(fp.AndGroupName) && fp.OrGroupName == orGroup && fp.AndGroupName != andGroup)
{
columnNameWithDelimiter += " or ( ";
andGroup = fp.AndGroupName;
}
else if (!string.IsNullOrEmpty(fp.OrGroupName) && !string.IsNullOrEmpty(fp.AndGroupName) && fp.OrGroupName != orGroup && fp.AndGroupName == andGroup)
{
columnNameWithDelimiter += " and ( ";
orGroup = fp.OrGroupName;
}
else if (!string.IsNullOrEmpty(fp.OrGroupName) && !string.IsNullOrEmpty(fp.AndGroupName) && fp.OrGroupName == orGroup && fp.AndGroupName == andGroup)
{
//если группа And является вторичной по отношению к группе Or [например ((X and Y) or Z)]
if (andGroupStats[fp.AndGroupName] < orGroupStats[fp.OrGroupName])
{
columnNameWithDelimiter += " and ";
}
else
{
columnNameWithDelimiter += " or ";
}
}
else if (string.IsNullOrEmpty(fp.OrGroupName) && !string.IsNullOrEmpty(fp.AndGroupName) && fp.AndGroupName != andGroup)
{
columnNameWithDelimiter += " ( ";
andGroup = fp.AndGroupName;
}
else if (string.IsNullOrEmpty(fp.OrGroupName) && !string.IsNullOrEmpty(fp.AndGroupName) && fp.AndGroupName == andGroup)
{
columnNameWithDelimiter += " and ";
}
else if (!string.IsNullOrEmpty(fp.OrGroupName) && string.IsNullOrEmpty(fp.AndGroupName) && fp.OrGroupName != orGroup)
{
columnNameWithDelimiter += " ( ";
orGroup = fp.OrGroupName;
}
else if (!string.IsNullOrEmpty(fp.OrGroupName) && string.IsNullOrEmpty(fp.AndGroupName) && fp.OrGroupName == orGroup)
{
columnNameWithDelimiter += " or ";
}
columnNameWithDelimiter += fp.ColumnName + " ";
}
if (fp.Value != null && fp.Value is IEnumerable && fp.Value.GetType() != typeof(string))
{
op = OperatorType.In;
IEnumerable en = (IEnumerable)fp.Value;
if (en.GetEnumerator().MoveNext() == false)
{
continue;
}
sb.Append(columnNameWithDelimiter);
int inParamIndex = 0;
sb.Append(" in (");
foreach (object o in en)
{
if (inParamIndex > 0)
{
sb.Append(",");
}
bool isGuid = (o != null && o.GetType() == typeof(Guid));
if (isGuid)
{
sb.Append(CreateSqlLiteral(o));
}
else
{
string baseParameter = filterParameter_SqlPars[fp];
string arrParameter = baseParameter + "_" + (inParamIndex + 1);
cmd.AddParameter(arrParameter, o);
sb.Append(arrParameter);
}
inParamIndex++;
}
sb.Append(")");
continue;
}
string format = "";
if (op == OperatorType.IsFalse)
{
sb.Append(columnNameWithDelimiter);
sb.Append("= 'false'");
continue;
}
else if (op == OperatorType.IsTrue)
{
sb.Append(columnNameWithDelimiter);
sb.Append("= 'true'");
continue;
}
else if (op == OperatorType.IsNull)
{
sb.Append(columnNameWithDelimiter);
sb.Append("is null");
continue;
}
else if (op == OperatorType.IsNotNull)
{
sb.Append(columnNameWithDelimiter);
sb.Append("is not null");
continue;
}
else if (op == OperatorType.Like)
{
format = "like ('%' + {0} + '%')";
}
else if (op == OperatorType.NotLike)
{
format = "not like ('%' + {0} + '%')";
}
else if (op == OperatorType.StartsWith)
{
format = "like ({0} + '%')";
}
else if (op == OperatorType.Equals)
{
format = "= {0}";
}
else if (op == OperatorType.EqualsWithNull && fp.Value != null && fp.Value != DBNull.Value)
{
format = "= {0}";
}
else if (op == OperatorType.EqualsWithNull && (fp.Value == null || fp.Value == DBNull.Value))
{
format = "is null";
}
else if (op == OperatorType.NotEquals)
{
format = "!= {0}";
}
else if (op == OperatorType.LessOrEquals)
{
format = "<= {0}";
}
else if (op == OperatorType.MoreOrEquals)
{
format = ">= {0}";
}
else if (op == OperatorType.LessThan)
{
format = "< {0}";
}
else if (op == OperatorType.MoreThan)
{
format = "> {0}";
}
if (op == OperatorType.LessOrEquals || op == OperatorType.LessThan)
{
if (fp.Value is DateTime)
{
DateTime dt = (DateTime)fp.Value;
if (dt.Hour == 0 && dt.Minute == 0 && dt.Second == 0 && dt.Millisecond == 0)
{
fp.Value = dt.AddDays(1).AddSeconds(-1);
}
}
}
else if (op == OperatorType.Like)
{
if (fp.Value is string)
{
fp.Value = ((string)fp.Value).Replace("[", "[[]").Replace("%", "[%]").Replace("_", "[_]");;
}
}
string parameterName = filterParameter_SqlPars[fp];
object val = fp.Value;
if (val == null)
{
val = DBNull.Value;
}
cmd.Parameters.AddWithValue(parameterName, val);
sb.Append(columnNameWithDelimiter);
sb.AppendFormat(format, parameterName);
}
if (!string.IsNullOrEmpty(orGroup))
{
sb.Append(" ) ");
}
if (!string.IsNullOrEmpty(andGroup))
{
sb.Append(" ) ");
}
if (cmd.Parameters.Count > 2100)
{
throw new ArgumentException("Задано слишком много параметров. Максимальное количество параметров: 2100.");
}
cmd.CommandText += sb.ToString();
return(cmd);
}
