private void SeedDatabase()
{
string sqlSeedScriptFileName = Path.Combine(AssemblyDirectory,
ConfigurationManager.AppSettings["SqlSeedScript"].ToString());
string script = GetSeedScriptFromFile(sqlSeedScriptFileName);
if (script == null)
{
throw new Exception("Failed to load SQL seed script");
}
MySqlStorageContext sContext = UnitOfWork.StorageContext as MySqlStorageContext;
DbCommand command = sContext.CreateCommand();
command.CommandText = script;
DbCommandContext cmdContext = new DbCommandContext(command);
sContext.Open();
try
{
cmdContext.Execute();
}
catch (Exception)
{
}
finally
{
cmdContext.Dispose();
sContext.Close();
}
}
/// <summary>
/// Save changes in the item that is registered to be changed to a persistent storage.
/// </summary>
/// <param name="item">Entity item.</param>
protected override void SaveChangedItem(TRole item)
{
DbCommandContext cmdContext = CommandBuilder.GetUpdateCommand(
new List <TRole> {
item
});
StorageContext.AddCommand(cmdContext);
}
/// <summary>
/// Save the removing of the item that is registered to be remvoed from a persistent storage.
/// </summary>
/// <param name="item">Entity item.</param>
protected override void SaveRemovedItem(TUser item)
{
DbCommandContext cmdContext = CommandBuilder.GetDeleteCommand(
new List <TUser> {
item
});
StorageContext.AddCommand(cmdContext);
}
/// <summary>
/// Save adding of the item that is registered to be added to a persistent storage.
/// </summary>
/// <param name="item">Entity item.</param>
protected override void SaveAddedItem(TUserLogin item)
{
DbCommandContext cmdContext = CommandBuilder.GetInsertCommand(
new List <TUserLogin> {
item
});
StorageContext.AddCommand(cmdContext);
}
/// <summary>
/// Find a list of role names by user id.
/// </summary>
/// <param name="userId">Target user id.</param>
/// <returns>Returns a list of roles if found; otherwise, returns empty list.</returns>
public IList <string> FindRoleNamesByUserId(TKey userId)
{
EntityConfiguration <TUserRole> userRoleCfg = StorageContext.GetEntityConfiguration <TUserRole>();
PropertyConfiguration userIdPropCfg = userRoleCfg.Property(p => p.UserId);
PropertyConfiguration roleNamePropCfg = Configuration.Property(p => p.Name);
DbCommand command = StorageContext.CreateCommand();
command.CommandText = String.Format(
@"SELECT {2} FROM {0} INNER JOIN {1} ON ({0}.{3} = {1}.{4}) WHERE {5} = @{6};",
QueryBuilder.GetQuotedIdentifier(Configuration.TableName),
QueryBuilder.GetQuotedIdentifier(userRoleCfg.TableName),
// Configured field names
QueryBuilder.GetQuotedIdentifier(roleNamePropCfg.ColumnName),
QueryBuilder.GetQuotedIdentifier(Configuration.Property(p => p.Id).ColumnName),
QueryBuilder.GetQuotedIdentifier(userRoleCfg.Property(p => p.RoleId).ColumnName),
QueryBuilder.GetQuotedIdentifier(userIdPropCfg.ColumnName),
// Parameter names
userIdPropCfg.PropertyName);
DbCommandContext cmdContext = new DbCommandContext(command);
cmdContext.Parameters[userIdPropCfg.PropertyName].Value = userId;
DbDataReader reader = null;
List <string> list = new List <string>();
string roleName = null;
StorageContext.Open();
try
{
reader = cmdContext.ExecuteReader();
while (reader.Read())
{
roleName = reader.GetSafeString(roleNamePropCfg.ColumnName);
list.Add(roleName);
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
cmdContext.Dispose();
StorageContext.Close();
}
return(list);
}
public static DbCommandContext GetLoggedDbCommand <TResult>(DbCommand command, DbCommandInterceptionContext <TResult> interceptionContext)
{
var context = new DbCommandContext
{
IsAsync = interceptionContext.IsAsync,
IsCanceled = interceptionContext.TaskStatus.HasFlag(TaskStatus.Canceled),
Exception = interceptionContext.OriginalException ?? interceptionContext.Exception
};
setBaseInfo(interceptionContext, context);
return(context);
}
/// <summary>
/// Check whether the user belongs to the given role.
/// </summary>
/// <param name="userId">Target user id.</param>
/// <param name="roleName">Target role name.</param>
/// <returns>Returns true if belongs; otherwise, returns false.</returns>
public bool IsInRole(TKey userId, string roleName)
{
EntityConfiguration <TUserRole> userRoleCfg = StorageContext.GetEntityConfiguration <TUserRole>();
PropertyConfiguration userIdPropCfg = userRoleCfg.Property(p => p.UserId);
PropertyConfiguration roleNamePropCfg = Configuration.Property(p => p.Name);
DbCommand command = StorageContext.CreateCommand();
command.CommandText = String.Format(
@"SELECT {2} FROM {0} INNER JOIN {1} ON ({0}.{2} = {1}.{3})
WHERE {4} = @{6} AND LOWER({5}) = LOWER(@{7});",
QueryBuilder.GetQuotedIdentifier(Configuration.TableName),
QueryBuilder.GetQuotedIdentifier(userRoleCfg.TableName),
// Configured field names
QueryBuilder.GetQuotedIdentifier(Configuration.Property(p => p.Id).ColumnName),
QueryBuilder.GetQuotedIdentifier(userRoleCfg.Property(p => p.RoleId).ColumnName),
QueryBuilder.GetQuotedIdentifier(userIdPropCfg.ColumnName),
QueryBuilder.GetQuotedIdentifier(roleNamePropCfg.ColumnName),
// Parameter names
userIdPropCfg.PropertyName,
roleNamePropCfg.PropertyName);
DbCommandContext cmdContext = new DbCommandContext(command);
cmdContext.Parameters[userIdPropCfg.PropertyName].Value = userId;
cmdContext.Parameters[roleNamePropCfg.PropertyName].Value = roleName;
DbDataReader reader = null;
bool inRole = false;
StorageContext.Open();
try
{
reader = cmdContext.ExecuteReader();
inRole = reader.Read();
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
StorageContext.Close();
}
return(inRole);
}
/// <summary>
/// Find user login by login information and user id.
/// </summary>
/// <param name="userId">Target user.</param>
/// <param name="loginInfo">User login information.</param>
/// <returns>Returns the user login if found; otherwise, returns null.</returns>
public TUserLogin Find(TKey userId, UserLoginInfo loginInfo)
{
PropertyConfiguration loginProviderPropCfg = Configuration.Property(p => p.LoginProvider);
PropertyConfiguration providerKeyPropCfg = Configuration.Property(p => p.ProviderKey);
PropertyConfiguration userIdPropCfg = Configuration.Property(p => p.UserId);
DbCommand command = StorageContext.CreateCommand();
command.CommandText = String.Format(
@"SELECT * FROM {0} WHERE {1} = @{4} AND {2} = @{5} AND {3} = @{6};",
QueryBuilder.GetQuotedIdentifier(Configuration.TableName),
// Configured field names
QueryBuilder.GetQuotedIdentifier(loginProviderPropCfg.ColumnName),
QueryBuilder.GetQuotedIdentifier(providerKeyPropCfg.ColumnName),
QueryBuilder.GetQuotedIdentifier(userIdPropCfg.ColumnName),
// Parameter names
loginProviderPropCfg.PropertyName,
providerKeyPropCfg.PropertyName,
userIdPropCfg.PropertyName);
DbCommandContext cmdContext = new DbCommandContext(command);
cmdContext.Parameters[loginProviderPropCfg.PropertyName].Value = loginInfo.LoginProvider;
cmdContext.Parameters[providerKeyPropCfg.PropertyName].Value = loginInfo.ProviderKey;
cmdContext.Parameters[userIdPropCfg.PropertyName].Value = userId;
DbDataReader reader = null;
TUserLogin userLogin = default(TUserLogin);
StorageContext.Open();
try
{
reader = cmdContext.ExecuteReader();
userLogin = EntityBuilder.Build(reader);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
cmdContext.Dispose();
StorageContext.Close();
}
return(userLogin);
}
/// <summary>
/// Find all user claims by user id and the given claim.
/// </summary>
/// <param name="userId">Target user id.</param>
/// <param name="claim">Target claim.</param>
/// <returns>Returns a list of user claims if found; otherwise, returns empty list.</returns>
public ICollection <TUserClaim> FindAllByUserId(TKey userId, Claim claim)
{
PropertyConfiguration userIdPropCfg = Configuration.Property(p => p.UserId);
PropertyConfiguration claimTypePropCfg = Configuration.Property(p => p.ClaimType);
PropertyConfiguration claimValuePropCfg = Configuration.Property(p => p.ClaimValue);
DbCommand command = StorageContext.CreateCommand();
command.CommandText = String.Format(
@"SELECT * FROM {0} WHERE {1} = @{4} AND {2} = @{5} AND {3} = @{6};",
QueryBuilder.GetQuotedIdentifier(Configuration.TableName),
// Configured field names
QueryBuilder.GetQuotedIdentifier(userIdPropCfg.ColumnName),
QueryBuilder.GetQuotedIdentifier(claimTypePropCfg.ColumnName),
QueryBuilder.GetQuotedIdentifier(claimValuePropCfg.ColumnName),
// Parameter names
userIdPropCfg.PropertyName,
claimTypePropCfg.PropertyName,
claimValuePropCfg.PropertyName);
DbCommandContext cmdContext = new DbCommandContext(command);
cmdContext.Parameters[userIdPropCfg.PropertyName].Value = userId;
cmdContext.Parameters[claimTypePropCfg.PropertyName].Value = claim.Type;
cmdContext.Parameters[claimValuePropCfg.PropertyName].Value = claim.Value;
DbDataReader reader = null;
ICollection <TUserClaim> list = null;
StorageContext.Open();
try
{
reader = cmdContext.ExecuteReader();
list = EntityBuilder.BuildAll(reader);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
cmdContext.Dispose();
StorageContext.Close();
}
return(list);
}
public static DbCommandContext GetLoggedDbCommand(DbCommand command, Exception exception)
{
var context = new DbCommandContext
{
IsAsync = false,
IsCanceled = false,
Exception = exception,
ObjectContextId = SessionIdLoggingContext.SessionId.GetUniqueId(),
ObjectContextName = SessionId,
ConnectionId = UniqueIdExtensions <DbConnection> .GetUniqueId(command.Connection).ToInt()
};
return(context);
}
public static DbCommandContext GetLoggedResult <TResult>(DbCommand command, DbCommandInterceptionContext <TResult> interceptionContext, long?elapsedMilliseconds, DataTable dataTable)
{
var context = new DbCommandContext
{
IsAsync = interceptionContext.IsAsync,
IsCanceled = interceptionContext.TaskStatus.HasFlag(TaskStatus.Canceled),
Exception = interceptionContext.OriginalException ?? interceptionContext.Exception,
Result = interceptionContext.Result,
ElapsedMilliseconds = elapsedMilliseconds,
DataTable = dataTable
};
setBaseInfo(interceptionContext, context);
return(context);
}
/// <summary>
/// Find user by id.
/// </summary>
/// <param name="email">Target user email.</param>
/// <returns>Returns the user if found; otherwise, returns null.</returns>
public TUser FindByEmail(string email)
{
PropertyConfiguration emailPropCfg = Configuration.Property(p => p.Email);
DbCommand command = StorageContext.CreateCommand();
command.CommandText = String.Format(
@"SELECT * FROM {0} WHERE LOWER({1}) = LOWER(@{2});",
QueryBuilder.GetQuotedIdentifier(Configuration.TableName),
// Configured field names
QueryBuilder.GetQuotedIdentifier(emailPropCfg.ColumnName),
// Parameter names
emailPropCfg.PropertyName);
DbCommandContext cmdContext = new DbCommandContext(command);
cmdContext.Parameters[emailPropCfg.PropertyName].Value = email;
DbDataReader reader = null;
TUser user = default(TUser);
StorageContext.Open();
try
{
reader = cmdContext.ExecuteReader();
user = EntityBuilder.Build(reader);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
cmdContext.Dispose();
StorageContext.Close();
}
return(user);
}
/// <summary>
/// Find role by id.
/// </summary>
/// <param name="id">Target role id.</param>
/// <returns>Returns the role if found; otherwise, returns null.</returns>
public TRole FindById(TKey id)
{
PropertyConfiguration idPropCfg = Configuration.Property(p => p.Id);
DbCommand command = StorageContext.CreateCommand();
command.CommandText = String.Format(
@"SELECT * FROM {0} WHERE {1} = @{2};",
QueryBuilder.GetQuotedIdentifier(Configuration.TableName),
// Configured field names
QueryBuilder.GetQuotedIdentifier(idPropCfg.ColumnName),
// Parameter names
idPropCfg.PropertyName);
DbCommandContext cmdContext = new DbCommandContext(command);
cmdContext.Parameters[idPropCfg.PropertyName].Value = id;
DbDataReader reader = null;
TRole role = default(TRole);
StorageContext.Open();
try
{
reader = cmdContext.ExecuteReader();
role = EntityBuilder.Build(reader);
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
cmdContext.Dispose();
StorageContext.Close();
}
return(role);
}
public static DbCommandContext GetLoggedResult(
DbCommand command, Exception exception, object result,
long?elapsedMilliseconds, DataTable dataTable)
{
var context = new DbCommandContext
{
IsAsync = false,
IsCanceled = false,
Exception = exception,
Result = result,
ElapsedMilliseconds = elapsedMilliseconds,
DataTable = dataTable,
ObjectContextId = SessionIdLoggingContext.SessionId.GetUniqueId(),
ObjectContextName = SessionId,
ConnectionId = UniqueIdExtensions <DbConnection> .GetUniqueId(command.Connection).ToInt()
};
return(context);
}
