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