public IEnumerable <IPaycheckModel> GetByEmployee(int employeeID)
{
List <PaycheckModel> paychecks = new List <PaycheckModel>();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
bool matchingRecoredFound = false;
string selectByEmpQuery = "SELECT ID, Amount, PayrollID, ReceiptionDate " +
"FROM Paychecks WHERE EmployeeID = @EmployeeID";
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(selectByEmpQuery, sqlConnection))
{
cmd.CommandText = selectByEmpQuery;
cmd.Prepare();
cmd.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
using (SqlDataReader reader = cmd.ExecuteReader())
{
matchingRecoredFound = reader.HasRows;
while (reader.Read())
{
PaycheckModel paycheck = new PaycheckModel();
paycheck.ID = Convert.ToInt32(reader["ID"].ToString());
paycheck.EmployeeID = employeeID;
paycheck.PayrollID = Convert.ToInt32(reader["PayrollID"].ToString());
paycheck.Amount = Convert.ToInt32(reader["Amount"].ToString());
paycheck.ReceiptionDate = (DateTime)reader["ReceiptionDate"];
paychecks.Add(paycheck);
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get the paychecks records from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
if (!matchingRecoredFound)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "",
customMessage: $"Record not found!. Unable to get Paycheck record for with employee id: {employeeID}.It does not exist in the database.", helpLink: "", errorCode: 0,
stackTrace: "");
throw new DataAccessException(dataAccessStatus);
}
return(paychecks);
}
}
public DepartmentModel GetByID(int departmentId)
{
DepartmentModel departmentModel = new DepartmentModel();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
bool matchingRecoredFound = false;
string selectByIdQuery = "SELECT [ID], [Name], [PhoneNumber], [ManagerID] " +
"FROM [Departments] WHERE [ID] = @DepartmentID";
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(selectByIdQuery, sqlConnection))
{
cmd.CommandText = selectByIdQuery;
cmd.Prepare();
cmd.Parameters.Add(new SqlParameter("@DepartmentID", departmentId));
using (SqlDataReader reader = cmd.ExecuteReader())
{
matchingRecoredFound = reader.HasRows;
while (reader.Read())
{
departmentModel.DepartmentId = departmentId;
departmentModel.DepartmentName = reader["Name"].ToString();
departmentModel.PhoneNumber = reader["PhoneNumber"].ToString();
if (!string.IsNullOrEmpty(reader["ManagerID"].ToString()))
{
departmentModel.ManagerID = Int32.Parse(reader["ManagerID"].ToString());
}
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get Department Model record from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
if (!matchingRecoredFound)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "",
customMessage: $"Record not found!. Unable to get Department Model record for department id {departmentId}. Id {departmentId} does not exist in the database.", helpLink: "", errorCode: 0,
stackTrace: "");
throw new DataAccessException(dataAccessStatus);
}
return(departmentModel);
}
}
public IEnumerable <IPaycheckModel> GetByMonth(DateTime date)
{
List <PaycheckModel> paychecks = new List <PaycheckModel>();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
bool recordsFound = false;
string selectQuery = "Select * FROM Paychecks WHERE Month(ReceiptionDate) = @Month AND YEAR(ReceiptionDate) = @Year ";
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get the paychecks records from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
using (SqlCommand cmd = new SqlCommand(selectQuery, sqlConnection))
{
cmd.CommandText = selectQuery;
cmd.Prepare();
cmd.Parameters.AddWithValue("@Month", date.Month);
cmd.Parameters.AddWithValue("@Year", date.Year);
using (SqlDataReader reader = cmd.ExecuteReader())
{
recordsFound = reader.HasRows;
while (reader.Read())
{
PaycheckModel paycheck = new PaycheckModel();
paycheck.ID = Convert.ToInt32(reader["ID"].ToString());
paycheck.EmployeeID = Convert.ToInt32(reader["EmployeeID"].ToString());;
paycheck.PayrollID = Convert.ToInt32(reader["PayrollID"].ToString());
paycheck.Amount = Convert.ToInt32(reader["Amount"].ToString());
paycheck.ReceiptionDate = (DateTime)reader["ReceiptionDate"];
paychecks.Add(paycheck);
}
}
}
sqlConnection.Close();
}
if (!recordsFound)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "",
customMessage: $"Record not found!. Unable to get Paycheck records in Month {date.Month}.", helpLink: "", errorCode: 0,
stackTrace: "");
throw new DataAccessException(dataAccessStatus);
}
return(paychecks);
}
public AccountModel GetByUsername(string username)
{
AccountModel account = new AccountModel();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
bool matchingRecoredFound = false;
string selectByIdQuery = "SELECT ID, Password, EmployeeID, RoleID " +
"FROM Accounts WHERE Username = @User";
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(selectByIdQuery, sqlConnection))
{
cmd.CommandText = selectByIdQuery;
cmd.Prepare();
cmd.Parameters.Add(new SqlParameter("@User", username));
using (SqlDataReader reader = cmd.ExecuteReader())
{
matchingRecoredFound = reader.HasRows;
while (reader.Read())
{
account.ID = Convert.ToInt32(reader["ID"].ToString());
account.Username = username;
account.Password = reader["Password"].ToString();
account.EmployeeID = Convert.ToInt32(reader["EmployeeID"].ToString());
account.RoleID = Convert.ToInt32(reader["RoleID"].ToString());
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get the account record from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
if (!matchingRecoredFound)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "",
customMessage: $"Record not found!. Unable to get account record for with username : {username}. {username} does not exist in the database.", helpLink: "", errorCode: 0,
stackTrace: "");
throw new DataAccessException(dataAccessStatus);
}
return(account);
}
}
public PayrollModel GetByEmployee(int employeeID)
{
PayrollModel account = new PayrollModel();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
bool matchingRecoredFound = false;
string selectByIdQuery = "SELECT ID, GrossPay, NetPay " +
"FROM Payrolls WHERE EmployeeID = @EmployeeID";
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(selectByIdQuery, sqlConnection))
{
cmd.CommandText = selectByIdQuery;
cmd.Prepare();
cmd.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
using (SqlDataReader reader = cmd.ExecuteReader())
{
matchingRecoredFound = reader.HasRows;
while (reader.Read())
{
account.ID = Convert.ToInt32(reader["ID"].ToString());
account.EmployeeID = employeeID;
account.GrossPay = Convert.ToInt32(reader["GrossPay"].ToString());
account.NetPay = Convert.ToInt32(reader["NetPay"].ToString());
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get the Payroll record from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
if (!matchingRecoredFound)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "",
customMessage: $"Record not found!. Unable to get Payroll record for with employee id: {employeeID}.It does not exist in the database.", helpLink: "", errorCode: 0,
stackTrace: "");
throw new DataAccessException(dataAccessStatus);
}
return(account);
}
}
public RoleModel GetByID(int id)
{
RoleModel roleModel = new RoleModel();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
bool matchingRecoredFound = false;
string selectByIdQuery = "SELECT [ID], [Name] " +
"FROM [Roles] WHERE [ID] = @ID";
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(selectByIdQuery, sqlConnection))
{
cmd.CommandText = selectByIdQuery;
cmd.Prepare();
cmd.Parameters.Add(new SqlParameter("@ID", id));
using (SqlDataReader reader = cmd.ExecuteReader())
{
matchingRecoredFound = reader.HasRows;
while (reader.Read())
{
roleModel.ID = id;
roleModel.Name = reader["Name"].ToString();
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get Role Model record from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
if (!matchingRecoredFound)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "",
customMessage: $"Record not found!. Unable to get Role Model record for role id : {id}. Id : {id} does not exist in the database.", helpLink: "", errorCode: 0,
stackTrace: "");
throw new DataAccessException(dataAccessStatus);
}
return(roleModel);
}
}
public MealModel GetByID(int id)
{
//throw new NotImplementedException();
MealModel mealModel = new MealModel();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
bool MatchingMealFound = false;
string sql = "SELECT * FROM Meals WHERE MealID = @MealID";
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
sqLiteConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@MealID", id));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
MatchingMealFound = reader.HasRows;
while (reader.Read())
{
mealModel.MealID = Int32.Parse(reader["MealID"].ToString());
mealModel.MealName = reader["MealName"].ToString();
mealModel.MealPrice = Int32.Parse(reader["MealPrice"].ToString());
mealModel.MealRestaurant = Int32.Parse(reader["MealRestaurant"].ToString());
mealModel.MealSaltTaste = Int32.Parse(reader["MealSaltTaste"].ToString());
mealModel.MealSourTaste = Int32.Parse(reader["MealSourTaste"].ToString());
mealModel.MealSpicyTaste = Int32.Parse(reader["MealSpicyTaste"].ToString());
mealModel.MealSweetTaste = Int32.Parse(reader["MealSweetTaste"].ToString());
mealModel.MealBitterTaste = Int32.Parse(reader["MealBitterTaste"].ToString());
}
}
sqLiteConnection.Close();
}
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to get Meal Model from db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
if (!MatchingMealFound)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: "", customMessage: "Unable to find Meal Model in db", helpLink: "", errorCode: 0, stackTrace: "");
throw new DataAccessException(dataAccesStatus);
}
return(mealModel);
}
}
public UserModel GetByEmail(string email)
{
//throw new NotImplementedException();
UserModel userModel = new UserModel();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
bool MatchingUserFound = false;
string sql = "SELECT * FROM Users WHERE UserEmail = @UserEmail";
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
sqLiteConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@UserEmail", email));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
MatchingUserFound = reader.HasRows;
while (reader.Read())
{
userModel.UserID = Int32.Parse(reader["UserID"].ToString());
userModel.UserEmail = reader["UserEmail"].ToString();
userModel.UserName = reader["UserName"].ToString();
userModel.UserPassword = reader["UserPassword"].ToString();
userModel.UserRestaurant = Int32.Parse(reader["UserRestaurant"].ToString());
}
}
sqLiteConnection.Close();
}
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to get User Model from db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
if (!MatchingUserFound)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: "", customMessage: "Unable to find User Model in db", helpLink: "", errorCode: 0, stackTrace: "");
throw new DataAccessException(dataAccesStatus);
}
return(userModel);
}
}
public void Add(IMealModel mealModel)
{
//throw new NotImplementedException();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
sqLiteConnection.Open();
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to open db connection", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
string sql = "INSERT INTO Meals (MealName, MealPrice, MealRestaurant, MealSaltTaste, MealSourTaste, MealSpicyTaste, MealSweetTaste, MealBitterTaste)" +
"VALUES (@MealName, @MealPrice, @MealRestaurant, @MealSaltTaste, @MealSourTaste, @MealSpicyTaste, @MealSweetTaste, @MealBitterTaste)";
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.AddWithValue("@MealName", mealModel.MealName);
cmd.Parameters.AddWithValue("@MealPrice", mealModel.MealPrice);
cmd.Parameters.AddWithValue("@MealRestaurant", mealModel.MealRestaurant);
cmd.Parameters.AddWithValue("@MealSaltTaste", mealModel.MealSaltTaste);
cmd.Parameters.AddWithValue("@MealSourTaste", mealModel.MealSourTaste);
cmd.Parameters.AddWithValue("@MealSpicyTaste", mealModel.MealSpicyTaste);
cmd.Parameters.AddWithValue("@MealSweetTaste", mealModel.MealSweetTaste);
cmd.Parameters.AddWithValue("@MealBitterTaste", mealModel.MealBitterTaste);
try
{
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to add Meal to db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
sqLiteConnection.Close();
}
}
}
public RestaurantModel GetByID(int id)
{
//throw new NotImplementedException();
RestaurantModel restaurantModel = new RestaurantModel();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
bool MatchingRestaurantFound = false;
string sql = "SELECT * FROM Restaurants WHERE RestaurantID = @RestaurantID";
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
sqLiteConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@RestaurantID", id));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
MatchingRestaurantFound = reader.HasRows;
while (reader.Read())
{
restaurantModel.RestaurantID = Int32.Parse(reader["RestaurantID"].ToString());
restaurantModel.RestaurantName = reader["RestaurantName"].ToString();
restaurantModel.RestaurantAddress = reader["RestaurantAddress"].ToString();
}
}
sqLiteConnection.Close();
}
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to get Restaurant Model from db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
if (!MatchingRestaurantFound)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: "", customMessage: "Unable to find Restaurant Model in db", helpLink: "", errorCode: 0, stackTrace: "");
throw new DataAccessException(dataAccesStatus);
}
return(restaurantModel);
}
}
public void Add(IUserModel userModel)
{
//throw new NotImplementedException();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
sqLiteConnection.Open();
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to open db connection", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
string sql = "INSERT INTO Users (UserName, UserEmail, UserPassword, UserRestaurant)" +
"VALUES (@UserName, @UserEmail, @UserPassword, @UserRestaurant)";
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
/*try
* {
* UserExistsCheck(cmd, userModel, TypeOfExsistenceCheck.DoesNotExistInDB, RequestType.Add)
* }*/
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.AddWithValue("@UserName", userModel.UserName);
cmd.Parameters.AddWithValue("@UserEmail", userModel.UserEmail);
cmd.Parameters.AddWithValue("@UserPassword", userModel.UserPassword);
cmd.Parameters.AddWithValue("@UserRestaurant", userModel.UserRestaurant);
try
{
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to add User to db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
sqLiteConnection.Close();
}
}
}
public void Delete(IUserModel userModel)
{
//throw new NotImplementedException();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
sqLiteConnection.Open();
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to open db connection", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
string sql = "DELETE FROM Users WHERE UserID=@UserID";
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
/*try
* {
* UserExistsCheck(cmd, userModel, TypeOfExsistenceCheck.DoesNotExistInDB, RequestType.Add)
* }*/
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.AddWithValue("@UserID", userModel.UserID);
try
{
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to remove User from db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
sqLiteConnection.Close();
}
}
}
public bool Add(ISetModel setModel, ITopicModel topicModel, byte[] file)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
try
{
string path = Path.Combine(Base._setsFolder, setModel.SetName);
if (Directory.Exists(path))
{
return(false);
}
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path); //Create set folder
}
path = Path.Combine(path, topicModel.TopicName); //Change path to /SetName/TopicName
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path); //Create topic folder
}
string filename = Path.Combine(path, topicModel.TopicName);
filename += ".rtf";
if (!File.Exists(filename))
{
//using(File.Create(filename));
using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
{
MemoryStream ms = new MemoryStream(file);
ms.WriteTo(fs);
fs.Close();
ms.Close();
}
}
path = Path.Combine(path, "Videos");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path); //Create Videos folder
}
path = Path.Combine(path, "Thumbnails");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path); //Create Thumbnail folder
}
return(true);
}
catch (IOException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: "Set already exists", stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
}
public static SetModel GetByName(string selectedSet)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
try
{
int index = 0;
//Get all sets
foreach (string setFolder in Directory.GetDirectories(Base._setsFolder))
{
_setModel = new SetModel();
_setModel.SetName = setFolder.Remove(0, setFolder.LastIndexOf('\\') + 1);
_setModel.SetID = index;
//If set folder matches passed in set name
if (setFolder.Remove(0, setFolder.LastIndexOf('\\') + 1) == selectedSet)
{
//Get set's topics from folder names
foreach (string topicFolder in Directory.GetDirectories(setFolder))
{
TopicModel topicModel = new TopicModel();
topicModel.TopicName = topicFolder.Remove(0, topicFolder.LastIndexOf('\\') + 1);
string tfile = topicFolder + @"\" + topicModel.TopicName + ".rtf";
byte[] topicFileInBytes = Encoding.ASCII.GetBytes(tfile);
topicModel.TopicFile = topicFileInBytes; //Set topicModel.TopicFile
//Get list of videos
string videoFolder = Path.Combine(topicFolder, "Videos");
foreach (string topicVideo in Directory.GetFiles(videoFolder, ("*.mp4, *.avi")))
{
//TODO: Sort results?
topicModel.TopicVideos.Add(topicVideo);
}
_setModel.SetTopics.Add(topicModel); //Add topicModel to set
}
return(_setModel);
}
index++;
}
}
catch (DirectoryNotFoundException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: $"Directory {_setModel.SetName} wasn't found", stackTrace: e.StackTrace);
return(null);
//throw new DataAccessException(dataAccessStatus);
}
return(null);
}
public static void Delete(string selectedSetName)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
try
{
var path = Path.Combine(Base._setsFolder, selectedSetName);
Directory.Delete(path, true);
}
catch (DirectoryNotFoundException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: $"Directory {SetModel.SelectedSet} wasn't found", stackTrace: e.StackTrace);
throw new DataAccessException(dataAccessStatus);
}
}
public IEnumerable <IDepartmentModel> GetAll()
{
List <DepartmentModel> departmentModels = new List <DepartmentModel>();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
string selectAllQuery = "Select * FROM [Departments]";
using (SqlCommand cmd = new SqlCommand(selectAllQuery, sqlConnection))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DepartmentModel departmentModel = new DepartmentModel();
departmentModel.DepartmentId = Int32.Parse(reader["ID"].ToString());
departmentModel.DepartmentName = reader["Name"].ToString();
departmentModel.PhoneNumber = reader["PhoneNumber"].ToString();
if (!string.IsNullOrEmpty(reader["ManagerID"].ToString()))
{
departmentModel.ManagerID = Int32.Parse(reader["ManagerID"].ToString());
}
departmentModels.Add(departmentModel);
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get Department Model list from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
return(departmentModels);
}
}
public IEnumerable <MealModel> GetAll(int id)
{
//throw new NotImplementedException();
List <MealModel> mealModelList = new List <MealModel>();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
//SQLitePCL.Batteries.Init();
string sql = "SELECT * FROM Meals";
sqLiteConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MealModel mealModel = new MealModel();
mealModel.MealID = Int32.Parse(reader["MealID"].ToString());
mealModel.MealName = reader["MealName"].ToString();
mealModel.MealPrice = Int32.Parse(reader["MealPrice"].ToString());
mealModel.MealRestaurant = Int32.Parse(reader["MealRestaurant"].ToString());
mealModel.MealSaltTaste = Int32.Parse(reader["MealSaltTaste"].ToString());
mealModel.MealSourTaste = Int32.Parse(reader["MealSourTaste"].ToString());
mealModel.MealSpicyTaste = Int32.Parse(reader["MealSpicyTaste"].ToString());
mealModel.MealSweetTaste = Int32.Parse(reader["MealSweetTaste"].ToString());
mealModel.MealBitterTaste = Int32.Parse(reader["MealBitterTaste"].ToString());
if (mealModel.MealRestaurant == id)
{
mealModelList.Add(mealModel);
}
}
}
sqLiteConnection.Close();
}
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to get Meal Model list from db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
return(mealModelList);
}
}
public static IEnumerable <ISetModel> GetAll()
{
List <SetModel> setModelList = new List <SetModel>();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
try
{
int index = 0;
//Get list of sets
foreach (string setFolder in Directory.GetDirectories(Base._setsFolder))
{
SetModel _setModel = new SetModel();
_setModel.SetName = setFolder.Remove(0, setFolder.LastIndexOf('\\') + 1);
_setModel.SetID = index;
//Get list of topics
foreach (string topicFolder in Directory.GetDirectories(setFolder))
{
TopicModel topicModel = new TopicModel();
if (topicFolder != setFolder + @"\Videos")
{
topicModel.TopicName = topicFolder.Remove(0, topicFolder.LastIndexOf('\\') + 1);
string tfile = topicFolder + @"\" + topicModel.TopicName + ".rtf";
byte[] topicFileInBytes = Encoding.ASCII.GetBytes(tfile);
topicModel.TopicFile = topicFileInBytes;
//Get list of videos
string videoFolder = Path.Combine(topicFolder, "Videos");
foreach (string topicVideo in Directory.GetFiles(videoFolder, ("*.mp4")))
{
//TODO: Sort results?
topicModel.TopicVideos.Add(topicVideo);
}
_setModel.SetTopics.Add(topicModel); //Add topicModel to setModel
}
}
setModelList.Add(_setModel); //Add setModel to setModelList
index++;
}
}
catch (DirectoryNotFoundException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: "Directory doesn't exist", stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
return(setModelList);
}
public IEnumerable <IPaycheckModel> GetAll()
{
List <PaycheckModel> paychecks = new List <PaycheckModel>();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
string selectAllQuery = "Select * FROM Paychecks";
using (SqlCommand cmd = new SqlCommand(selectAllQuery, sqlConnection))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PaycheckModel paycheck = new PaycheckModel();
paycheck.ID = Int32.Parse(reader["ID"].ToString());
paycheck.Amount = Int32.Parse(reader["Amount"].ToString());
paycheck.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
paycheck.PayrollID = Int32.Parse(reader["PayrollID"].ToString());
paycheck.ReceiptionDate = (DateTime)reader["ReceiptionDate"];
paychecks.Add(paycheck);
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get Paychecks list from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
return(paychecks);
}
}
public IEnumerable <IAccountModel> GetAll()
{
List <AccountModel> departmentModels = new List <AccountModel>();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
string selectAllQuery = "Select * FROM Accounts";
using (SqlCommand cmd = new SqlCommand(selectAllQuery, sqlConnection))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AccountModel account = new AccountModel();
account.ID = Int32.Parse(reader["ID"].ToString());
account.Username = reader["Username"].ToString();
account.Password = reader["Password"].ToString();
account.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
account.RoleID = Int32.Parse(reader["RoleID"].ToString());
departmentModels.Add(account);
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get Accounts list from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
return(departmentModels);
}
}
public IEnumerable <IRoleModel> GetAll()
{
List <RoleModel> roleModels = new List <RoleModel>();
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
try
{
sqlConnection.Open();
string selectAllQuery = "Select * FROM [Roles]";
using (SqlCommand cmd = new SqlCommand(selectAllQuery, sqlConnection))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
RoleModel roleModel = new RoleModel();
roleModel.ID = Int32.Parse(reader["ID"].ToString());
roleModel.Name = string.Copy(reader["Name"].ToString());
roleModels.Add(roleModel);
}
}
}
sqlConnection.Close();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to get Role Model list from database", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
return(roleModels);
}
}
public IEnumerable <UserModel> GetAll()
{
//throw new NotImplementedException();
List <UserModel> userModelList = new List <UserModel>();
DataAccessStatus dataAccesStatus = new DataAccessStatus();
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(_connectionString))
{
try
{
//SQLitePCL.Batteries.Init();
string sql = "SELECT * FROM Users";
sqLiteConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, sqLiteConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UserModel userModel = new UserModel();
userModel.UserID = Int32.Parse(reader["UserID"].ToString());
userModel.UserEmail = reader["UserEmail"].ToString();
userModel.UserName = reader["UserName"].ToString();
userModel.UserPassword = reader["UserPassword"].ToString();
userModel.UserRestaurant = Int32.Parse(reader["UserRestaurant"].ToString());
userModelList.Add(userModel);
}
}
sqLiteConnection.Close();
}
}
catch (SQLiteException e)
{
dataAccesStatus.setValues(status: "Error", operationSucceded: false, exceptionMessage: e.Message, customMessage: "Unable to get User Model list from db", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccesStatus);
}
return(userModelList);
}
}
public void Add(IAccountModel model)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add Account. Could not open a database connection", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string addQuery =
"INSERT INTO Accounts (Username, Password, EmployeeID, RoleID) " +
"VALUES (@User, @Password, @EmployeeID, @RoleID)";
using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
{
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesNotExistInDB, RequestType.Add);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Account could not be added because the username already exists.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = addQuery;
SqlParameter username = new SqlParameter("@User", System.Data.SqlDbType.VarChar);
SqlParameter password = new SqlParameter("@Password", System.Data.SqlDbType.VarChar);
SqlParameter employee = new SqlParameter("@EmployeeID", System.Data.SqlDbType.Int);
SqlParameter role = new SqlParameter("@RoleID", System.Data.SqlDbType.Int);
username.Value = model.Username;
password.Value = model.Password;
employee.Value = Convert.ToInt32(model.EmployeeID);
role.Value = Convert.ToInt32(model.RoleID);
cmd.Parameters.Add(username);
cmd.Parameters.Add(password);
cmd.Parameters.Add(employee);
cmd.Parameters.Add(role);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add the account.", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
//Confirm the Account Model was Added to the database
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesExistInDB, RequestType.ConfirmAdd);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.Status = "Error";
e.DataAccessStatusInfo.OperationSucceeded = false;
e.DataAccessStatusInfo.CustomMessage = "Failed to find the account in database after add operation completed.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw new DataAccessException(dataAccessStatus);
}
sqlConnection.Close();
}
}
}
public void Update(IAccountModel model)
{
int result = -1;
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Update Acocunt " + model.Username + ". Could not open database connection.",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string updateDepQuery =
"UPDATE Accounts " +
"SET Username = @User, " +
"Password = @Password, " +
"EmployeeID = @EmployeeID, " +
"RoleID = @RoleID " +
"WHERE ID = @ID";
using (SqlCommand cmd = new SqlCommand(updateDepQuery, sqlConnection))
{
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesExistInDB, RequestType.Update);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Account " + model.Username + " could not be updated because it could not be found in the database";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = updateDepQuery;
SqlParameter user = new SqlParameter("@User", SqlDbType.VarChar, 20);
user.Value = model.Username;
SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
password.Value = model.Password;
cmd.Parameters.Add(user);
cmd.Parameters.Add(password);
cmd.Parameters.AddWithValue("@EmployeeID", model.EmployeeID).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@RoleID", model.RoleID).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@ID", model.ID).SqlDbType = SqlDbType.Int;
cmd.Prepare();
try
{
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Update The Account",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
}
sqlConnection.Close();
}
}
public void Add(IRoleModel roleModel)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add Role Model. Could not open a database connection", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string addQuery =
"INSERT INTO [Roles] ([Name]) " +
"VALUES (@Name)";
using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
{
try
{
RecordExistsCheck(cmd, roleModel, TypeOfExistenceCheck.DoesNotExistInDB, RequestType.Add);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Role model could not be added because it is already in the database.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = addQuery;
SqlParameter roleName = new SqlParameter("@Name", System.Data.SqlDbType.VarChar, 20);
roleName.Value = roleModel.Name;
cmd.Parameters.Add(roleName);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add Role Model.", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
//Confirm the Department Model was Added to the database
try
{
RecordExistsCheck(cmd, roleModel, TypeOfExistenceCheck.DoesExistInDB, RequestType.ConfirmAdd);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.Status = "Error";
e.DataAccessStatusInfo.OperationSucceeded = false;
e.DataAccessStatusInfo.CustomMessage = "Failed to find role model in database after add operation completed.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw new DataAccessException(dataAccessStatus);
}
sqlConnection.Close();
}
}
}
public void Remove(IAccountModel model)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Delete The Account. Could not open database connection.",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string deleteQuery = "DELETE FROM Accounts WHERE ID = @ID";
using (SqlCommand cmd = new SqlCommand(deleteQuery, sqlConnection))
{
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesExistInDB, RequestType.Delete);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Account " + model.Username + " could not be deleted because it could not be found in the database";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = deleteQuery;
cmd.Prepare();
cmd.Parameters.AddWithValue("@ID", model.ID);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Delete The Account.",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
//Confirm that the department model has been deleted
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesNotExistInDB, RequestType.ConfirmDelete);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.Status = "Error";
e.DataAccessStatusInfo.OperationSucceeded = false;
e.DataAccessStatusInfo.CustomMessage = "Failed to Delete The Account in Database";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
}
sqlConnection.Close();
}
}
public void Update(IDepartmentModel departmentModel)
{
int result = -1;
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Update Department Model. Could not open database connection.",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string updateDepQuery =
"UPDATE [Departments]" +
"SET [Name] = @DepartmentName," +
"[PhoneNumber] = @PhoneNumber ," +
"[ManagerID] = @ManagerId " +
"WHERE [ID] = @DepartmentId ";
using (SqlCommand cmd = new SqlCommand(updateDepQuery, sqlConnection))
{
try
{
RecordExistsCheck(cmd, departmentModel, TypeOfExistenceCheck.DoesExistInDB, RequestType.Update);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Department model could not be updated because it could not be found in the database";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = updateDepQuery;
cmd.Parameters.Add("@DepartmentName", System.Data.SqlDbType.NVarChar, 40).Value = departmentModel.DepartmentName;
cmd.Parameters.Add("@PhoneNumber", System.Data.SqlDbType.NVarChar, 20).Value = departmentModel.PhoneNumber;
if (!(departmentModel.ManagerID == 0))
{
cmd.Parameters.Add("@ManagerId", System.Data.SqlDbType.Int).Value = Convert.ToInt32(departmentModel.ManagerID);
}
else
{
cmd.Parameters.Add("@ManagerId", System.Data.SqlDbType.Int).Value = DBNull.Value;
}
cmd.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = Convert.ToInt32(departmentModel.DepartmentId);
cmd.Prepare();
try
{
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Update Department Model.",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
}
sqlConnection.Close();
}
}
public void Add(IDepartmentModel departmentModel)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add Department Model. Could not open a database connection", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string addQuery =
"INSERT INTO [Departments] ([Name], PhoneNumber, ManagerID) " +
"VALUES (@DepartmentName, @PhoneNumber, @ManagerId)";
using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
{
try
{
RecordExistsCheck(cmd, departmentModel, TypeOfExistenceCheck.DoesNotExistInDB, RequestType.Add);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Department model could not be added because it is already in the database.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = addQuery;
SqlParameter depName = new SqlParameter("@DepartmentName", System.Data.SqlDbType.VarChar, 40);
SqlParameter phone = new SqlParameter("@PhoneNumber", System.Data.SqlDbType.VarChar, 20);
SqlParameter manID = new SqlParameter("@ManagerId", System.Data.SqlDbType.Int);
depName.Value = departmentModel.DepartmentName;
phone.Value = departmentModel.PhoneNumber;
manID.IsNullable = true;
if (!(departmentModel.ManagerID == 0))
{
manID.Value = Convert.ToInt32((departmentModel.ManagerID));
}
else
{
manID.Value = DBNull.Value;
}
cmd.Parameters.Add(depName);
cmd.Parameters.Add(phone);
cmd.Parameters.Add(manID);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add Department Model.", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
//Confirm the Department Model was Added to the database
try
{
RecordExistsCheck(cmd, departmentModel, TypeOfExistenceCheck.DoesExistInDB, RequestType.ConfirmAdd);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.Status = "Error";
e.DataAccessStatusInfo.OperationSucceeded = false;
e.DataAccessStatusInfo.CustomMessage = "Failed to find deaptment model in database after add o[eration completed.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw new DataAccessException(dataAccessStatus);
}
sqlConnection.Close();
}
}
}
public void Add(IPaycheckModel model)
{
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add paycheck. Could not open a database connection", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string addQuery =
"INSERT INTO Paychecks (Amount, EmployeeID, PayrollID) " +
"VALUES (@Amount, @EmployeeID, @PayrollID)";
using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
{
cmd.CommandText = addQuery;
cmd.Parameters.AddWithValue("@Amount", model.Amount).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@EmployeeID", model.EmployeeID).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@PayrollID", model.PayrollID).SqlDbType = SqlDbType.Int;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add the paycheck.", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
//Confirm the Paycheck Model was Added to the database
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesExistInDB, RequestType.ConfirmAdd);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.Status = "Error";
e.DataAccessStatusInfo.OperationSucceeded = false;
e.DataAccessStatusInfo.CustomMessage = "Failed to find the paycheck in database after add operation completed.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw new DataAccessException(dataAccessStatus);
}
sqlConnection.Close();
}
}
}
public void Update(IPaycheckModel model)
{
int result = -1;
DataAccessStatus dataAccessStatus = new DataAccessStatus();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Update Paycheck. Could not open database connection.",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
string updateDepQuery =
"UPDATE Paychecks " +
"SET Amount = @Amount, " +
"EmployeeID = @EmployeeID, " +
"PayrollID = @PayrollID " +
"WHERE ID = @ID";
using (SqlCommand cmd = new SqlCommand(updateDepQuery, sqlConnection))
{
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesExistInDB, RequestType.Update);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "paycheck could not be updated because it could not be found in the database";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = updateDepQuery;
cmd.Parameters.AddWithValue("@Amount", model.Amount).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@PayrollID", model.PayrollID).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@EmployeeID", model.EmployeeID).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@ID", model.ID).SqlDbType = SqlDbType.Int;
cmd.Prepare();
try
{
result = cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to Update The Paycheck",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
}
sqlConnection.Close();
}
}