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