private bool RecordExistsCheck(SQLiteCommand cmd, IExpenseTypeModel expenseTypeModel)
{
Int32 countOfRecsFound = 0;
bool RecordExistsCheckPassed = true;
DataAccessResult dataAccessResult = new DataAccessResult();
cmd.Prepare();
cmd.CommandText = "Select count(*) from ExpenseType where ExpenseTypeName=@ExpenseTypeName";
cmd.Parameters.AddWithValue("@ExpenseTypeName", expenseTypeModel.ExpenseTypeName);
try
{
countOfRecsFound = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SQLiteException e)
{
string msg = e.Message;
throw;
}
if (countOfRecsFound > 0)
{
dataAccessResult.Status = "Error";
RecordExistsCheckPassed = false;
throw new DataAccessException(dataAccessResult);
}
return(RecordExistsCheckPassed);
}
private ExpenseTypeDTO expenseType_modelToDto(IExpenseTypeModel expenseTypeModel)
{
ExpenseTypeDTO dto = new ExpenseTypeDTO();
dto.ExpenseTypeId = expenseTypeModel.ExpenseTypeId;
dto.ExpenseTypeName = expenseTypeModel.ExpenseTypeName;
return(dto);
}
public IEnumerable <IExpenseModel> GetAllByUserIdAndExpenseType(int UserId, IExpenseTypeModel expenseTypeModel)
{
List <ExpenseModel> list = new List <ExpenseModel>();
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "SELECT * FROM Expense WHERE Expense.UserId = @UserId AND Expense.ExpenseTypeId = @ExpenseTypeId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeId", expenseTypeModel.ExpenseTypeId));
cmd.Parameters.Add(new SQLiteParameter("@UserId", UserId));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ExpenseModel expenseModel = new ExpenseModel();
expenseModel.ExpenseId = Int32.Parse(reader["ExpenseId"].ToString());
expenseModel.ExpenseTypeId = Int32.Parse(reader["ExpenseTypeId"].ToString());
expenseModel.Date = reader["Date"].ToString();
expenseModel.Cost = Double.Parse(reader["Cost"].ToString());
list.Add(expenseModel);
}
}
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod dohvata zapisa o trošku za zadanu kategoriju troška [Expense->getAllByExpenseType]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return(list);
}
private ExpenseDTO expense_modelToDto(IExpenseModel e)
{
ExpenseDTO expenseDTO = new ExpenseDTO();
expenseDTO.ExpenseId = e.ExpenseId;
expenseDTO.ExpenseTypeId = e.ExpenseTypeId;
expenseDTO.Date = DateTime.Parse(e.Date);
expenseDTO.Cost = e.Cost;
expenseDTO.UserId = e.UserId;
IExpenseTypeModel expenseTypeModel = expenseTypeRepository.GetById(e.ExpenseTypeId);
expenseDTO.ExpenseTypeName = expenseTypeModel != null? expenseTypeModel.ExpenseTypeName : "";
return(expenseDTO);
}
public void Update(IExpenseTypeModel expenseTypeModel)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "UPDATE ExpenseType SET ExpenseTypeName = @ExpenseTypeName WHERE ExpenseType.ExpenseTypeId = @ExpenseTypeId;";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
try
{
RecordExistsCheck(cmd, expenseTypeModel);
}
catch (DataAccessException ex)
{
ex.DataAccessResult.CustomMessage = "Kategorija troška već postoji u bazi podataka";
ex.DataAccessResult.ExceptionMessage = string.Copy(ex.Message);
ex.DataAccessResult.StackTrace = string.Copy(ex.StackTrace);
throw ex;
}
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeId", expenseTypeModel.ExpenseTypeId));
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeName", expenseTypeModel.ExpenseTypeName));
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod ažuriranja zapisa kategorije troška [ExpenseType->Update]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return;
}
public void Delete(IExpenseTypeModel expenseTypeModel)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "DELETE FROM ExpenseType WHERE ExpenseType.ExpenseTypeId = @ExpenseTypeId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeId", expenseTypeModel.ExpenseTypeId));
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod brisanja zapisa o kategoriji troška [ExpenseType->Delete]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return;
}
