public override IEnumerable <IContext> GetContexts()
{
if (contexts != null)
{
return(contexts);
}
lock (padlock) {
if (contexts == null)
{
var contextCollection = new List <IContext>();
var dsFunction = CurrentDB.ExecuteDataSet("dbo.USER_GetContexts");
if (dsFunction.Tables.Count > 0)
{
foreach (DataRow row in dsFunction.Tables[0].Rows)
{
var contextName = (string)row["ContextName"];
IContext uc = new Context(contextName);
contextCollection.Add(uc);
}
}
contexts = contextCollection;
}
}
return(contexts);
}
public override string[] GetDomains()
{
var dsDomains = CurrentDB.ExecuteDataSet("dbo.USER_GetDomains");
if (dsDomains.Tables.Count == 1 && dsDomains.Tables[0].Rows.Count > 0)
{
var result = new string[dsDomains.Tables[0].Rows.Count];
for (var i = 0; i < result.Length; i++)
{
result[i] = dsDomains.Tables[0].Rows[i]["Domain"].ToString();
}
return(result);
}
return(new string[0]);
}
public override IUser GetUserById(string userId)
{
var user = UserCache.GetUserById(userId);
if (user != null)
{
return(user);
}
var dsUser = CurrentDB.ExecuteDataSet("dbo.USER_GetByID",
CurrentDB.CreateInputParameter("@guidUserID", DbType.Guid, new Guid(userId)));
user = GetUserFromDataSet(dsUser);
UserCache.AddUserToCache(user);
return(user);
}
public override IList <IUser> GetUsersByDomain(string domain)
{
if (domain == null)
{
domain = string.Empty;
}
var dsUser = CurrentDB.ExecuteDataSet("dbo.USER_GetUsersByDomain",
CurrentDB.CreateStringInputParameter("@chvDomain",
DbType.AnsiString,
domain,
false));
var uc = GetUsersFromDataSet(dsUser);
UserCache.AddUsersToCache(uc);
return(uc);
}
public override IUser GetUserByUserName(string userName, string domain)
{
var user = UserCache.GetUserByUserName(userName, domain);
if (user != null)
{
return(user);
}
var dsUser = CurrentDB.ExecuteDataSet("dbo.USER_GetByUsername",
CurrentDB.CreateStringInputParameter("@chvnUsername",
DbType.String,
userName),
CurrentDB.CreateStringInputParameter("@chvDomain",
DbType.AnsiString,
domain ?? string.Empty));
user = GetUserFromDataSet(dsUser);
UserCache.AddUserToCache(user);
return(user);
}
protected virtual IPropertyCollection GetPropertiesByUserId(string userId, IContext context)
{
IPropertyCollection upc = null;
var dsUser = CurrentDB.ExecuteDataSet("dbo.USER_GetProperties",
CurrentDB.CreateInputParameter("@guidUserID", DbType.Guid, new Guid(userId)),
CurrentDB.CreateStringInputParameter("@chvContext",
DbType.AnsiString,
context == null ||
string.IsNullOrEmpty(context.Name)
? null
: context.Name));
// translate dataset
if (dsUser.Tables.Count > 0)
{
dsUser.Tables[0].PrimaryKey = new[] { dsUser.Tables[0].Columns["PropertyName"] };
var rows = dsUser.Tables[0].Rows;
upc = GetPropertiesFromDataRows(rows, context, null);
}
return(upc);
}
protected virtual void SaveUserInstance(IUser user, ITransaction transaction)
{
var dsUser = CurrentDB.ExecuteDataSet("dbo.USER_Update",
transaction,
CurrentDB.CreateInputParameter("@guidUserId",
DbType.Guid,
new Guid(user.Id)),
CurrentDB.CreateStringInputParameter("@chvnUsername",
DbType.String,
user.UserName,
false),
CurrentDB.CreateStringInputParameter("@chvDomain",
DbType.AnsiString,
user.Domain,
false));
var status = (int)dsUser.Tables[0].Rows[0]["STATUS"];
if (status == AddStatusUsernameTaken)
{
throw new UserNameAlreadyExistsException("Cannot save user. User name already exists.");
}
}
public override IList <IUser> GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
var returnParam = CurrentDB.CreateReturnParameter("@intPagingTotalNumber", DbType.Int32);
DataSet dsUser;
if (pageSize.Equals(int.MaxValue) || pageIndex.Equals(int.MaxValue))
{
dsUser = CurrentDB.ExecuteDataSet("dbo.USER_FilterUsers", returnParam);
}
else
{
dsUser = CurrentDB.ExecuteDataSet("dbo.USER_FilterUsers",
CurrentDB.CreateInputParameter("@intLimitPage", DbType.Int32, pageIndex + 1),
CurrentDB.CreateInputParameter("@intLimitSize", DbType.Int32, pageSize),
returnParam);
}
var uc = GetUsersFromDataSet(dsUser);
UserCache.AddUsersToCache(uc);
totalRecords = (int)returnParam.Value;
return(uc);
}
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;
}
}
protected virtual ContextSchema GetContextSchema(string contextName, ITransaction transaction)
{
if (contextSchema.Keys.Contains(contextName))
{
return(contextSchema[contextName]);
}
lock (padlock) {
if (contextSchema.Keys.Contains(contextName))
{
return(contextSchema[contextName]);
}
var pdt = new List <PropertyDefinition>();
IDataParameter[] prams =
{
CurrentDB.CreateStringInputParameter("@chvContext", DbType.AnsiString, contextName, true)
};
DataSet dsUser;
if (transaction == null)
{
dsUser = CurrentDB.ExecuteDataSet("dbo.USER_GetPropertySchema", prams);
}
else
{
dsUser = CurrentDB.ExecuteDataSet("dbo.USER_GetPropertySchema", transaction, prams);
}
// translate dataset
if (dsUser.Tables.Count > 0)
{
foreach (DataRow row in dsUser.Tables[0].Rows)
{
var propertyName = (string)row["PropertyName"];
Type propertyType;
if (!row.IsNull("AssemblyPath"))
{
var assembly = Assembly.LoadFrom((string)row["AssemblyPath"]);
propertyType = assembly.GetType((string)row["DataType"]);
}
else if (!row.IsNull("AssemblyName"))
{
var assembly = Assembly.Load((string)row["AssemblyName"]);
propertyType = assembly.GetType((string)row["DataType"]);
}
else
{
propertyType = Type.GetType((string)row["DataType"]);
}
if (propertyType == null)
{
throw new NotSupportedException(string.Format("The given property has a type {0} that can not be loaded.",
row["DataType"]));
}
var pd = new PropertyDefinition(propertyName, propertyType);
pdt.Add(pd);
}
}
var schema = new ContextSchema(pdt);
contextSchema.Add(contextName, schema);
return(schema);
}
}
