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