public bool SetNewPassword(ResetPassword model)
{
try
{
ISqlConnectionService _sqlConnService = new SqlConnectionService();
SqlCommand cmd;
try
{
_sqlConnService.OpenConnection();
cmd = new SqlCommand("SP_SetNewPassword", _sqlConnService.CreateConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", model.UserID);
cmd.Parameters.AddWithValue("@Password", Utility.MD5(model.Password));
cmd.Parameters.AddWithValue("@StatementType", model.UserType);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.InnerException.Message : ex.Message;
throw new Exception(message);
}
finally
{
_sqlConnService.CloseConnection();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(true);
}
/// <summary>
/// Deletes all tables from the database
/// </summary>
public void DeleteDatabaseTables()
{
// command string to remove all constraints
string str = "EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"";
// Open connection
_connService.OpenConnection();
SqlCommand myCommand = new SqlCommand(str, _connService.Conn);
try
{
// Execute command
myCommand.ExecuteNonQuery();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Corona Statistics Database Helper");
}
// Delete all tables
try
{
using (var command = new SqlCommand("Procedure_DeleteAllTables", _connService.Conn)
{
CommandType = CommandType.StoredProcedure
})
{
_connService.Conn.InfoMessage += new SqlInfoMessageEventHandler(_connService.ConnectionInfoMessage);
command.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Corona Statistics Database Helper");
}
// Close Connection
_connService.CloseConnection();
}
public bool StoredProceduresLoaded()
{
// help on checking if the stored procedure exists: https://stackoverflow.com/a/13797842
_connService.OpenConnection();
string query = "select * from sysobjects where type='P'";
int storedProcedureCount = Directory.GetFiles(_connService.StoredProcedureDirectory).Length;
int numOfFiles = 0;
using (SqlCommand command = new SqlCommand(query, _connService.Conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
numOfFiles++;
}
}
}
_connService.CloseConnection();
if (storedProcedureCount == numOfFiles)
{
return(true);
}
else
{
return(false);
}
}