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