private bool RecordExistsCheck(
SqlCommand cmd,
IPayrollModel model,
TypeOfExistenceCheck typeOfExistenceCheck,
RequestType requestType
)
{
Int32 count0fRecordsFound = 0;
bool recordExistsCheckPassed = true;
DataAccessStatus dataAccessStatus = new DataAccessStatus();
SqlCommand cmdCheck = new SqlCommand(null, cmd.Connection);
cmdCheck.Prepare();
if ((requestType == RequestType.Add) || (requestType == RequestType.ConfirmAdd))
{
cmdCheck.CommandText = "SELECT count(*) FROM Payrolls where EmployeeID = @EmployeeID";
cmdCheck.Parameters.AddWithValue("@EmployeeID", model.EmployeeID);
}
else if ((requestType == RequestType.Update) || (requestType == RequestType.Delete) || (requestType == RequestType.ConfirmDelete))
{
cmdCheck.CommandText = "SELECT count(*) FROM Payrolls WHERE ID = @ID";
cmdCheck.Parameters.AddWithValue("@ID", model.ID);
}
try
{
count0fRecordsFound = Convert.ToInt32(cmdCheck.ExecuteScalar());
}
catch (SqlException e)
{
string msg = e.Message;
throw e;
}
if ((typeOfExistenceCheck == TypeOfExistenceCheck.DoesNotExistInDB) && (count0fRecordsFound > 0))
{
dataAccessStatus.Status = "Error";
recordExistsCheckPassed = false;
throw new DataAccessException(dataAccessStatus);
}
else if ((typeOfExistenceCheck == TypeOfExistenceCheck.DoesExistInDB) && (count0fRecordsFound == 0))
{
dataAccessStatus.Status = "Error";
recordExistsCheckPassed = false;
throw new DataAccessException(dataAccessStatus);
}
return(recordExistsCheckPassed);
}
public void ValidateModelDataAnnotations(IPayrollModel model)
{
modelCheck.ValidateModelDataAnnotations(model);
}
public void Update(IPayrollModel model)
{
ValidateModelDataAnnotations(model);
repository.Update(model);
}
public void Remove(IPayrollModel model)
{
repository.Remove(model);
}
public void Add(IPayrollModel model)
{
ValidateModelDataAnnotations(model);
repository.Add(model);
}
public void Add(IPayrollModel 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 Payroll. 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 Payrolls (GrossPay, NetPay, EmployeeID) " +
"VALUES (@GrossPay, @NetPay, @EmployeeID)";
using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
{
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesNotExistInDB, RequestType.Add);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Payroll could not be added because the employee has already payroll.";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
cmd.CommandText = addQuery;
cmd.Parameters.AddWithValue("@GrossPay", model.GrossPay).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@NetPay", model.NetPay).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@EmployeeID", model.EmployeeID).SqlDbType = SqlDbType.Int;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message,
customMessage: "Unable to add the payroll.", helpLink: e.HelpLink, errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
//Confirm the Payroll 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 payroll 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(IPayrollModel 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 Payroll. 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 Payrolls " +
"SET GrossPay = @GrossPay, " +
"NetPay = @NetPay, " +
"EmployeeID = @EmployeeID " +
"WHERE ID = @ID";
using (SqlCommand cmd = new SqlCommand(updateDepQuery, sqlConnection))
{
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesExistInDB, RequestType.Update);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Payroll 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("@GrossPay", model.GrossPay).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@NetPay", model.NetPay).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 Payroll",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
}
sqlConnection.Close();
}
}
public void Remove(IPayrollModel 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 Payroll. 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 Payrolls WHERE ID = @ID";
using (SqlCommand cmd = new SqlCommand(deleteQuery, sqlConnection))
{
try
{
RecordExistsCheck(cmd, model, TypeOfExistenceCheck.DoesExistInDB, RequestType.Delete);
}
catch (DataAccessException e)
{
e.DataAccessStatusInfo.CustomMessage = "Payroll 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 Payroll.",
helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus);
}
//Confirm that the Payroll 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 Payroll in Database";
e.DataAccessStatusInfo.ExceptionMessage = string.Copy(e.Message);
e.DataAccessStatusInfo.StackTrace = string.Copy(e.StackTrace);
throw e;
}
}
sqlConnection.Close();
}
}
