public override IList <IUser> GetUsersBySearchCriteria(IEnumerable <SearchCriteria> searchCriteriaCollection) { if (searchCriteriaCollection == null) { throw new ArgumentNullException("searchCriteriaCollection"); } const string sqlSinglequote = "'"; const string sqlSinglequoteEscaped = "''"; const string sqlWhere = "WHERE "; const string sqlAnd = "AND "; const string sqlOr = "OR "; const string sqlNot = "NOT "; const string sqlBasicQuery = "SELECT u.UserID, u.Username, u.Domain FROM dbo.USER_User u "; const string sqlBasicSubqueryStart = "EXISTS(SELECT * FROM dbo.USER_Property p JOIN dbo.USER_PropertySchema ps ON p.PropertyID = ps.PropertyID "; const string sqlBasicSubqueryEnd = sqlAnd + "u.UserID = p.UserID) "; const string sqlContextJoin = "JOIN dbo.USER_Context c ON p.ContextID = c.ContextID "; const string sqlCriteriaProperty = "ps.PropertyName = {0} "; const string sqlCriteriaContext = sqlAnd + "c.ContextName = {0} "; const string sqlCriteriaDomain = sqlAnd + "u.Domain = {0} "; const string sqlCriteriaCondNomatch = sqlAnd + "1 = 0 "; const string sqlCriteriaCondLike = sqlAnd + "(p.PropertyValue LIKE {0} OR p.ExtendedPropertyValue LIKE {0}) "; const string sqlCriteriaCondLikeOnlyext = sqlAnd + "p.ExtendedPropertyValue LIKE {0} "; const string sqlCriteriaCondNotlike = sqlAnd + "(p.PropertyValue NOT LIKE {0} OR p.ExtendedPropertyValue NOT LIKE {0}) "; const string sqlCriteriaCondNotlikeOnlyext = sqlAnd + "p.ExtendedPropertyValue NOT LIKE {0} "; const string sqlCriteriaCondEqual = sqlAnd + "p.PropertyValue = {0} "; const string sqlCriteriaCondEqualExt = sqlCriteriaCondLikeOnlyext; const string sqlCriteriaCondNotequal = sqlAnd + "p.PropertyValue <> {0} "; const string sqlCriteriaCondNotequalExt = sqlCriteriaCondNotlikeOnlyext; const string sqlCriteriaCondGreater = sqlAnd + "(p.PropertyValue > {0} OR CAST(p.ExtendedPropertyValue AS NVARCHAR(4000)) > {0})"; const string sqlCriteriaCondGreaterequal = sqlAnd + "(p.PropertyValue >= {0} OR CAST(p.ExtendedPropertyValue AS NVARCHAR(4000)) >= {0})"; const string sqlCriteriaCondLess = sqlAnd + "(p.PropertyValue < {0} OR CAST(p.ExtendedPropertyValue AS NVARCHAR(4000)) < {0})"; const string sqlCriteriaCondLessequal = sqlAnd + "(p.PropertyValue <= {0} OR CAST(p.ExtendedPropertyValue AS NVARCHAR(4000)) <= {0})"; const string sqlCriteriaCondContainsstartswith = sqlAnd + @"CONTAINS(p.*,N'""{0}*""') "; const string sqlParameter = "@param"; var command = CurrentDB.CreateTextCommand(); var queryBuilder = new StringBuilder(sqlBasicQuery); var requiredCriterias = new ArrayList(); var notRequiredCriterias = new ArrayList(); var parameterCount = 0; foreach (var sc in searchCriteriaCollection) { var serializedValue = sc.Property.ToSerializedString(); var likeEscapedSerializedValue = EscapeForLikeClause(serializedValue); var isDefaultValue = sc.Property.IsEmpty(); var parameterNameName = sqlParameter + parameterCount++; var parameterNameValue = sqlParameter + parameterCount++; var contextIsSet = sc.Property.Context != null && !string.IsNullOrEmpty(sc.Property.Context.Name); var domainIsSet = !string.IsNullOrEmpty(sc.Domain); var criteria = new StringBuilder(sqlBasicSubqueryStart); if (contextIsSet) { // Add context criteria to query if context is set criteria.Append(sqlContextJoin); } criteria.Append(sqlWhere).AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaProperty, parameterNameName); if (contextIsSet) { // Add context criteria to query if context is set var parameterContext = sqlParameter + parameterCount++; criteria.AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaContext, parameterContext); command.AddInParameter(parameterContext, DbType.String, sc.Property.Context.Name); } if (domainIsSet) { // Add domain criteria to query if domain is set var parameterDomain = sqlParameter + parameterCount++; criteria.AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaDomain, parameterDomain); command.AddInParameter(parameterDomain, DbType.String, sc.Domain); } var basicSubQueryStart = criteria.ToString(); switch (sc.Condition) { // If value is bigger than 4000 bytes, search in the extended value column case CompareCondition.ContainsStartsWith: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondContainsstartswith, serializedValue.Replace(sqlSinglequote, sqlSinglequoteEscaped)); } else { //return all if defaultValue criteria. Append(sqlBasicSubqueryEnd).Append(sqlOr).Append(sqlNot). Append(basicSubQueryStart); } break; case CompareCondition.StartsWith: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondLike : sqlCriteriaCondLikeOnlyext, parameterNameValue); command.AddInParameter(parameterNameValue, DbType.String, likeEscapedSerializedValue + "%"); } else { //return all if defaultValue criteria. Append(sqlBasicSubqueryEnd).Append(sqlOr).Append(sqlNot). Append(basicSubQueryStart); } break; case CompareCondition.NotStartsWith: if (!isDefaultValue) { criteria. AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondNotlike : sqlCriteriaCondNotlikeOnlyext, parameterNameValue). Append(sqlBasicSubqueryEnd).Append(sqlOr).Append(sqlNot). Append(basicSubQueryStart); } else { //return none if defaultValue criteria.Append(sqlCriteriaCondNomatch); } command.AddInParameter(parameterNameValue, DbType.String, likeEscapedSerializedValue + "%"); break; case CompareCondition.EndsWith: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondLike : sqlCriteriaCondLikeOnlyext, parameterNameValue); command.AddInParameter(parameterNameValue, DbType.String, "%" + likeEscapedSerializedValue); } else { //return all if defaultValue criteria. Append(sqlBasicSubqueryEnd).Append(sqlOr).Append(sqlNot). Append(basicSubQueryStart); } break; case CompareCondition.NotEndsWith: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondNotlike : sqlCriteriaCondNotlikeOnlyext, parameterNameValue). Append(sqlBasicSubqueryEnd).Append(sqlOr).Append(sqlNot). Append(basicSubQueryStart); command.AddInParameter(parameterNameValue, DbType.String, "%" + likeEscapedSerializedValue); } else { //return none if defaultValue criteria.Append(sqlCriteriaCondNomatch); } break; case CompareCondition.Contains: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondLike : sqlCriteriaCondLikeOnlyext, parameterNameValue); command.AddInParameter(parameterNameValue, DbType.String, "%" + likeEscapedSerializedValue + "%"); } else { //return all if defaultValue criteria. Append(sqlBasicSubqueryEnd).Append(sqlOr).Append(sqlNot). Append(basicSubQueryStart); } break; case CompareCondition.NotContains: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondNotlike : sqlCriteriaCondNotlikeOnlyext, parameterNameValue). Append(sqlBasicSubqueryEnd).Append(sqlOr).Append(sqlNot). Append(basicSubQueryStart); command.AddInParameter(parameterNameValue, DbType.String, "%" + likeEscapedSerializedValue + "%"); } else { //return none if defaultValue criteria.Append(sqlCriteriaCondNomatch); } break; case CompareCondition.NotEqual: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondNotequal : sqlCriteriaCondNotequalExt, parameterNameValue); command.AddInParameter(parameterNameValue, DbType.String, serializedValue.Length <= 4000 ? serializedValue : likeEscapedSerializedValue); } //return all that has a row if defaultValue break; case CompareCondition.GreaterThan: case CompareCondition.GreaterThanOrEqual: case CompareCondition.LessThan: case CompareCondition.LessThanOrEqual: if (sc.Property is ISqlProperty && !((ISqlProperty)sc.Property).SerializationPreservesOrder) { throw new InvalidOperationException( "Can not use inequality comparison on a property that does not maintain sort order of its underlying type in its serialized form."); } var comparedWithDefaultValue = sc.Property.Value == null ? sc.Property.DefaultValue == null ? 0 : -1 : ((IComparable)sc.Property.Value).CompareTo(sc.Property.DefaultValue); switch (sc.Condition) { case CompareCondition.GreaterThan: if (comparedWithDefaultValue < 0) // value > (<defaultValue) { criteria.Insert(0, sqlNot).AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondLessequal, parameterNameValue); } else // value > (>=defaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondGreater, parameterNameValue); } break; case CompareCondition.GreaterThanOrEqual: if (comparedWithDefaultValue > 0) //value >= (>defaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondGreaterequal, parameterNameValue); } else //value >= (<=defaultValue> { criteria.Insert(0, sqlNot).AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondLess, parameterNameValue); } break; case CompareCondition.LessThan: if (comparedWithDefaultValue > 0) //value < (>defaultValue> { criteria.Insert(0, sqlNot).AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondGreaterequal, parameterNameValue); } else //value < (<=defaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondLess, parameterNameValue); } break; case CompareCondition.LessThanOrEqual: if (comparedWithDefaultValue < 0) //value <= (<defaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondLessequal, parameterNameValue); } else //value <= (>=defaultValue> { criteria.Insert(0, sqlNot).AppendFormat(CultureInfo.InvariantCulture, sqlCriteriaCondGreater, parameterNameValue); } break; } command.AddInParameter(parameterNameValue, DbType.String, serializedValue); break; default: if (!isDefaultValue) { criteria.AppendFormat(CultureInfo.InvariantCulture, serializedValue.Length <= 4000 ? sqlCriteriaCondEqual : sqlCriteriaCondEqualExt, parameterNameValue); command.AddInParameter(parameterNameValue, DbType.String, serializedValue.Length <= 4000 ? serializedValue : likeEscapedSerializedValue); } else { //return all that has no row if defaultValue criteria.Insert(0, sqlNot); } break; } criteria.Append(sqlBasicSubqueryEnd); (sc.Required ? requiredCriterias : notRequiredCriterias).Add(criteria.ToString()); command.AddInParameter(parameterNameName, DbType.String, sc.Property.Name); } if (requiredCriterias.Count > 0 || notRequiredCriterias.Count > 0) { queryBuilder.Append(sqlWhere); // Populate required criterias if (requiredCriterias.Count > 0) { queryBuilder.Append("("); for (var i = 0; i < requiredCriterias.Count; i++) { queryBuilder.Append(requiredCriterias[i]); if (i != (requiredCriterias.Count - 1)) // If last criteria, do not add AND { queryBuilder.Append(sqlAnd); } } queryBuilder.Append(") "); if (notRequiredCriterias.Count > 0) { queryBuilder.Append(sqlAnd); } } // Populate non-required criterias if (notRequiredCriterias.Count > 0) { queryBuilder.Append("("); for (var i = 0; i < notRequiredCriterias.Count; i++) { queryBuilder.Append(notRequiredCriterias[i]); if (i != (notRequiredCriterias.Count - 1)) // If last criteria, do not add OR { queryBuilder.Append(sqlOr); } } queryBuilder.Append(") "); } } command.CommandText = queryBuilder.ToString(); try { var uc = GetUsersFromDataSet(CurrentDB.ExecuteDataSet(command)); UserCache.AddUsersToCache(uc); return(uc); } catch (SqlException ex) { //the execution of a full-text operation failed because if (ex.Number.Equals(7603) || //full-text predicate was null or empty (SQL Server 2000) ex.Number.Equals(7619) || //the clause of the query contained only ignored words. (SQL Server 2000) ex.Number.Equals(7643) || //the search generated too many results ex.Number.Equals(7645)) //full-text predicate was null or empty (SQL Server 2005) //return empty user collection { return(new List <IUser>()); } throw; } }