/// <summary>
/// Get all account requests for review and approval or denial
/// </summary>
public List <AccountRequest> GetAccountRequests()
{
var proc = "[Security].[GetAccountRequests]";
Func <SqlDataReader, AccountRequest> readFx = (reader) =>
{
var acctReq = new AccountRequest {
RequestId = (int)reader["AccountRequestID"],
FirstName = reader["FirstName"] + "",
LastName = reader["LastName"] + "",
Line1 = reader["Line1"] + "",
City = reader["City"] + "",
State = reader["State"] + "",
Zip = reader["Zip"] + "",
Email = reader["Email"] + "",
Comments = reader["Comments"] + "",
ChurchId = (int)reader["ChurchId"],
DateSubmitted = (DateTime)reader["DateSubmitted"],
RoleId = (int)Roles.User // default to user
};
return(acctReq);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <AccountRequest>(proc, CommandType.StoredProcedure, null, readFx);
return(list);
}
public UserProfile SaveUserProfile(UserProfile userProfile)
{
var proc = "[Security].[SaveUserProfile]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("userId", userProfile.UserId));
paramz.Add(new SqlParameter("roleId", userProfile.RoleId));
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
userProfile.ChurchIds.ToList().ForEach(s => table.Rows.Add(s));
paramz.Add(new SqlParameter("@churchIds", table));
Func <SqlDataReader, string> readFx = (reader) =>
{
return(reader["status"].ToString());
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <string>(proc, CommandType.StoredProcedure, paramz, readFx);
return(userProfile);
}
public ReportsRepository(string connectionString)
{
this.ConnectionString = connectionString;
_executor = new SqlCmdExecutor(connectionString);
commonRepository = new CommonRepository(connectionString);
}
/// <summary>
/// Applies the specified change scripts.
/// </summary>
/// <param name="changeScripts">The change scripts.</param>
public void Apply(IEnumerable <ChangeScript> changeScripts)
{
this.infoTextWriter.WriteLine(changeScripts.Any() ? "Applying change scripts...\n" : "No changes to apply.\n");
using (var sqlCmdExecutor = new SqlCmdExecutor(this.connectionString))
{
foreach (var script in changeScripts)
{
this.schemaVersionManager.RecordScriptStatus(script, ScriptStatus.Started);
this.infoTextWriter.WriteLine(script);
this.infoTextWriter.WriteLine("----------------------------------------------------------");
var output = new StringBuilder();
var success = false;
try
{
success = sqlCmdExecutor.ExecuteFile(script.FileInfo, output);
if (!success)
{
throw new DbDeployException(string.Format(CultureInfo.InvariantCulture, "Script '{0}' failed.", script));
}
}
finally
{
this.infoTextWriter.WriteLine(output);
this.schemaVersionManager.RecordScriptStatus(script, success ? ScriptStatus.Success : ScriptStatus.Failure, output.ToString());
}
}
}
}
private static void TestSql()
{
var tr = new SqlCmdExecutor();
tr.SetContext(null, new Log(), "", @"E:\Tests\TestScripts");
tr.Run();
}
public void TestDoesNotRunSecondScriptIfFirstScriptFails()
{
this.EnsureTableDoesNotExist("TableWeWillUse");
this.EnsureTableDoesNotExist(TableName);
var factory = new DbmsFactory(this.Dbms, this.ConnectionString);
var dbmsSyntax = factory.CreateDbmsSyntax();
var output = new StringBuilder();
var applier = new TemplateBasedApplier(
new StringWriter(output),
dbmsSyntax,
TableName,
";",
DbDeployDefaults.DelimiterType,
DbDeployDefaults.TemplateDirectory);
applier.Apply(new ChangeScript[]
{
new StubChangeScript(1, "1.test.sql", "INSERT INTO TableWeWillUse VALUES (1);"),
new StubChangeScript(2, "2.test.sql", "CREATE TABLE dbo.TableWeWillUse (Id int NULL);"),
}, createChangeLogTable: true);
using (var sqlExecuter = new SqlCmdExecutor(this.ConnectionString))
{
var cmdOutput = new StringBuilder();
sqlExecuter.ExecuteString(output.ToString(), cmdOutput);
}
this.AssertTableDoesNotExist("TableWeWillUse");
}
public void TestDoesNotRunSecondScriptIfFirstScriptFails()
{
EnsureTableDoesNotExist("TableWeWillUse");
EnsureTableDoesNotExist(TableName);
var factory = new DbmsFactory(Dbms, ConnectionString);
var dbmsSyntax = factory.CreateDbmsSyntax();
var output = new StringBuilder();
var applier = new TemplateBasedApplier(
new StringWriter(output),
dbmsSyntax,
TableName,
";",
DbDeployDefaults.DelimiterType,
DbDeployDefaults.TemplateDirectory);
applier.Apply(new ChangeScript[]
{
new StubChangeScript(1, "1.test.sql", "INSERT INTO TableWeWillUse VALUES (1);"),
new StubChangeScript(2, "2.test.sql", "CREATE TABLE dbo.TableWeWillUse (Id int NULL);"),
}, true);
using (var sqlExecuter = new SqlCmdExecutor(ConnectionString))
{
var cmdOutput = new StringBuilder();
sqlExecuter.ExecuteString(output.ToString(), cmdOutput);
}
AssertTableDoesNotExist("TableWeWillUse");
}
private void RunScript(ChangeScript script, SqlCmdExecutor sqlCmdExecutor)
{
this.schemaVersionManager.RecordScriptStatus(script, ScriptStatus.Started);
this.infoTextWriter.WriteLine(script);
this.infoTextWriter.WriteLine("----------------------------------------------------------");
var output = new StringBuilder();
var success = false;
try
{
success = sqlCmdExecutor.ExecuteFile(script.FileInfo, output);
if (!success)
{
throw new DbDeployException(string.Format(CultureInfo.InvariantCulture, "Script '{0}' failed.", script));
}
}
finally
{
this.infoTextWriter.WriteLine(output);
this.schemaVersionManager.RecordScriptStatus(script, success ? ScriptStatus.Success : ScriptStatus.Failure,
output.ToString());
}
}
/// <summary>
/// Creates an account request for a new user. New request will be in pending status until approved or denied.
/// </summary>
/// <param name="accountRequest">AccountRequest</param>
/// <returns>The RowNo of the new account request.</returns>
public int CreateAccountRequest(AccountRequest accountRequest)
{
var salt = Guid.NewGuid().ToString(); // note: create a new salt every time they change their password
var passwordHash = CreatePasswordHash(accountRequest.Password, salt);
var proc = "[Security].[CreateAccountRequest]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("firstName", accountRequest.FirstName));
paramz.Add(new SqlParameter("lastName", accountRequest.LastName));
paramz.Add(new SqlParameter("email", accountRequest.Email));
paramz.Add(new SqlParameter("salt", salt));
paramz.Add(new SqlParameter("passwordHash", passwordHash));
//paramz.Add(new SqlParameter("pastorName", accountRequest.PastorName));
paramz.Add(new SqlParameter("churchId", accountRequest.ChurchId));
paramz.Add(new SqlParameter("line1", accountRequest.Line1));
paramz.Add(new SqlParameter("city", accountRequest.City));
paramz.Add(new SqlParameter("state", accountRequest.State));
paramz.Add(new SqlParameter("zip", accountRequest.Zip));
paramz.Add(new SqlParameter("comments", accountRequest.Comments));
Func <SqlDataReader, int> readFx = (reader) =>
{
return((int)reader["AccountRequestID"]);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <int>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.First());
}
/// <summary>
/// Change a user's password
/// </summary>
/// <param name="accountRequest">AccountPasswordChange</param>
/// <returns>true if the password was changed, falst if it was not</returns>
public bool ChangePassword(AccountPasswordChange accountRequest)
{
// perform login to validate the old credentials
var spice = GetUserLoginSpice(accountRequest.Email);
if (spice == null)
{
return(false);
}
var oldPasswordHash = CreatePasswordHash(accountRequest.OldPassword, spice.Salt);
// save new password.
var salt = Guid.NewGuid().ToString(); // note: create a new salt every time they change their password
var passwordHash = CreatePasswordHash(accountRequest.NewPassword, salt);
var proc = "[Security].[ChangePassword]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("email", accountRequest.Email));
paramz.Add(new SqlParameter("oldPasswordHash", passwordHash));
paramz.Add(new SqlParameter("newPasswordHash", passwordHash));
paramz.Add(new SqlParameter("newSalt", salt));
Func <SqlDataReader, bool> readFx = (reader) =>
{
return((bool)reader["Success"]);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <bool>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.FirstOrDefault());
}
/// <summary>
/// Applies the specified change scripts.
/// </summary>
/// <param name="changeScripts">The change scripts.</param>
/// <param name="createChangeLogTable">Whether the change log table script should also be generated at the top</param>
public void Apply(IEnumerable <ChangeScript> changeScripts, bool createChangeLogTable)
{
using (var sqlCmdExecutor = new SqlCmdExecutor(this.connectionString))
{
if (createChangeLogTable)
{
CreateChangeLogTable(sqlCmdExecutor);
}
this.infoTextWriter.WriteLine(changeScripts.Any() ? "Applying change scripts...\n" : "No changes to apply.\n");
foreach (var script in changeScripts)
{
RunScript(script, sqlCmdExecutor);
}
}
}
private void CreateChangeLogTable(SqlCmdExecutor sqlCmdExecutor)
{
this.infoTextWriter.WriteLine("Creating change log table");
var output = new StringBuilder();
try
{
if (!sqlCmdExecutor.ExecuteString(this.dbmsSyntax.CreateChangeLogTableSqlScript(this.changeLogTableName), output))
{
throw new DbDeployException(string.Format("Create ChangeLog Table '{0}' failed.", this.changeLogTableName));
}
}
finally
{
this.infoTextWriter.WriteLine(output);
}
}
/// <summary>
/// Creates an account request for a new user. New request will be in pending status until approved or denied.
/// </summary>
/// <param name="accountRequest">AccountRequest</param>
/// <returns>The RowNo of the new account request.</returns>
public string GetAccountRequestStatus(int accountRequestId)
{
var proc = "[Security].[GetAccountRequestStatus]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("accountRequestId", accountRequestId));
Func <SqlDataReader, string> readFx = (reader) =>
{
return(reader["Status"].ToString());
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <string>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.First());
}
public List <Role> GetRoles()
{
var proc = "[Security].[GetRoles]";
Func <SqlDataReader, Role> readFx = (reader) =>
{
return(new Role
{
RoleId = (int)reader["RoleID"],
RoleDesc = reader["RoleDesc"].ToString(),
});
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <Role>(proc, CommandType.StoredProcedure, null, readFx);
return(list);
}
/// <summary>
/// Gets a user's PersonIdentityID & Salt
/// </summary>
/// <param name="email">Email is the Username</param>
/// <returns>LoginSpice</returns>
private LoginSpice GetUserLoginSpice(string email)
{
var proc = "[Security].[Login_GetUserSpice]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("userName", email));
Func <SqlDataReader, LoginSpice> readFx = (reader) =>
{
var spice = new LoginSpice();
spice.PersonIdentityID = (int)reader["PersonIdentityID"];
spice.Salt = reader["Salt"].ToString();
return(spice);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <LoginSpice>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.FirstOrDefault());
}
/// <summary>
/// returs the new IdentityID of the user
/// </summary>
/// <param name="accountRequest"></param>
/// <returns></returns>
public int ProcessAccountRequest(AccountRequest accountRequest)
{
var proc = "[Security].[ProcessAccountRequest]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("accountRequestId", accountRequest.RequestId));
paramz.Add(new SqlParameter("approved", accountRequest.IsApproved));
paramz.Add(new SqlParameter("denied", !accountRequest.IsApproved));
paramz.Add(new SqlParameter("processedByUserID", accountRequest.ReviewerUserId));
paramz.Add(new SqlParameter("defaultUserRoleId", Roles.User));
paramz.Add(new SqlParameter("memberTypeEnumId", MemberType.Member));
paramz.Add(new SqlParameter("roleId", accountRequest.RoleId));
paramz.Add(new SqlParameter("firstName", accountRequest.FirstName));
paramz.Add(new SqlParameter("lastName", accountRequest.LastName));
paramz.Add(new SqlParameter("line1", accountRequest.Line1));
paramz.Add(new SqlParameter("city", accountRequest.City));
paramz.Add(new SqlParameter("state", accountRequest.State));
paramz.Add(new SqlParameter("zip", accountRequest.Zip));
paramz.Add(new SqlParameter("email", accountRequest.Email));
paramz.Add(new SqlParameter("churchId", accountRequest.ChurchId));
paramz.Add(new SqlParameter("comments", accountRequest.Comments));
// pass all info as parameters
Func <SqlDataReader, int> readFx = (reader) =>
{
return(reader.ValueOrDefault("IdentityID", 0));
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <int>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.First());
}
public TeamRepository(string connectionString)
{
this.ConnectionString = connectionString;
_executor = new SqlCmdExecutor(connectionString);
}
