public async Task <IEnumerable <ExpenseEntityDesign> > GetAllExpense()
{
List <ExpenseEntityDesign> expenseList = new List <ExpenseEntityDesign>();
string cmdText = "Select * from TblExpense ";
SqlCommand sqlcommand = new SqlCommand(cmdText, xSqlConnection);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcommand);
DataTable dataTable = new DataTable();
await xSqlConnection.OpenAsync();
sqlDataAdapter.Fill(dataTable);
xSqlConnection.Close();
foreach (DataRow dataRow in dataTable.Rows)
{
ExpenseEntityDesign expense = new ExpenseEntityDesign();
expense.Id = Convert.ToInt32(dataRow["Id"]);
expense.Name = dataRow["Name"].ToString();
expense.Amount = Convert.ToDecimal(dataRow["Amount"]);
expense.Description = dataRow["Description"].ToString();
expense.ExpenseDate = Convert.ToDateTime(dataRow["ExpenseDate"]);
if (dataRow["CreatedOn"] != DBNull.Value)
{
expense.CreatedOn = Convert.ToDateTime(dataRow["CreatedOn"]);
}
if (dataRow["LastUpdatedOn"] != DBNull.Value)
{
expense.LastUpdatedOn = Convert.ToDateTime(dataRow["LastUpdatedOn"]);
}
// expense.LastUpdatedOn = (DateTime?)(dataRow["LastUpdatedOn"] ?? DBNull.Value);
expenseList.Add(expense);
}
return(expenseList);
}
public async Task <int> UpdateExpense(ExpenseEntityDesign xExpense)
{
int isUpdated = 0;
if (xExpense == null)
{
throw new ArgumentNullException("No Expense Available");
}
try
{
string cmdText = "UPDATE TblExpense SET Name= @name,Amount=@amount, Description= @description,ExpenseDate=@expensedate,IsDeleted=@isdeleted,LastUpdatedOn=@lastupdatedon WHERE Id =@Id";
SqlCommand xsqlcommand = new SqlCommand(cmdText, xSqlConnection);
xsqlcommand = new SqlCommand(cmdText, xSqlConnection);
xsqlcommand.Parameters.AddWithValue("@Id", xExpense.Id);
xsqlcommand.Parameters.AddWithValue("@name", xExpense.Name);
xsqlcommand.Parameters.AddWithValue("@amount", xExpense.Amount);
xsqlcommand.Parameters.AddWithValue("@description", xExpense.Description);
xsqlcommand.Parameters.AddWithValue("@expensedate", xExpense.ExpenseDate);
xsqlcommand.Parameters.AddWithValue("@isdeleted", false);
xsqlcommand.Parameters.AddWithValue("@lastupdatedon", DateTime.Now);
await xSqlConnection.OpenAsync();
isUpdated = await xsqlcommand.ExecuteNonQueryAsync();
xSqlConnection.Close();
}
catch (Exception e)
{
}
return(isUpdated);
}
public async Task <int> SaveExpense(ExpenseDto expense)
{
if (expense is null)
{
throw new Exception("Expense Required.");
}
ExpenseEntityDesign entity = new ExpenseEntityDesign
{
Name = expense.Name,
Description = expense.Description,
Amount = expense.Amount,
ExpenseDate = expense.ExpenseDate
};
return(await _expenseRepository.Add(entity));
}
public async Task <int> UpdateExpense(EditExpenseDto expense)
{
//if (expense is null)
//{
// throw new Exception("Expense Required.");
//}
ExpenseEntityDesign expenseEntity = await _expenseRepository.GetExpenseById(expense.Id);
if (expense.Name == null && expense.Amount == 0)
{
throw new Exception("Invalid Id");
}
expenseEntity.Name = expense.Name;
expenseEntity.Amount = expense.Amount;
expenseEntity.Description = expense.Description;
expenseEntity.ExpenseDate = expense.ExpenseDate;
return(await _expenseRepository.UpdateExpense(expenseEntity));
}
public async Task <int> Add(ExpenseEntityDesign xExpense)
{
string cmdText = "INSERT INTO TblExpense(Name,Amount,Description,ExpenseDate,IsDeleted,CreatedOn) VALUES(@name,@amount,@description,@expensedate,@isdeleted,@createdon)";
SqlCommand xsqlcommand = new SqlCommand(cmdText, xSqlConnection);
xsqlcommand = new SqlCommand(cmdText, xSqlConnection);
xsqlcommand.Parameters.AddWithValue("@name", xExpense.Name);
xsqlcommand.Parameters.AddWithValue("@amount", xExpense.Amount);
xsqlcommand.Parameters.AddWithValue("@description", xExpense.Description);
xsqlcommand.Parameters.AddWithValue("@expensedate", xExpense.ExpenseDate);
xsqlcommand.Parameters.AddWithValue("@isdeleted", false);
xsqlcommand.Parameters.AddWithValue("@createdon", DateTime.Now);
await xSqlConnection.OpenAsync();
int IsInserted = await xsqlcommand.ExecuteNonQueryAsync();
xSqlConnection.Close();
return(IsInserted);
}
public async Task <ExpenseEntityDesign> GetExpenseById(int Id)
{
ExpenseEntityDesign entity = null;
if (Id == 0)
{
throw new ArgumentNullException();
}
string cmdText = "SELECT * FROM TblExpense WHERE Id=@Id";
SqlCommand xsqlcommand = new SqlCommand(cmdText, xSqlConnection);
xsqlcommand = new SqlCommand(cmdText, xSqlConnection);
xsqlcommand.Parameters.AddWithValue("@Id", Id);
await xSqlConnection.OpenAsync();
SqlDataReader xSqlDataReader = await xsqlcommand.ExecuteReaderAsync();
while (xSqlDataReader.HasRows && await xSqlDataReader.ReadAsync())
{
entity = new ExpenseEntityDesign
{
Id = Convert.ToInt32(xSqlDataReader["Id"]),
Name = xSqlDataReader["Name"].ToString(),
Amount = Convert.ToDecimal(xSqlDataReader["Amount"]),
Description = xSqlDataReader["Description"].ToString(),
ExpenseDate = Convert.ToDateTime(xSqlDataReader["ExpenseDate"]),
CreatedOn = Convert.ToDateTime(xSqlDataReader["CreatedOn"]),
};
if (xSqlDataReader["LastUpdatedOn"] != DBNull.Value)
{
entity.LastUpdatedOn = Convert.ToDateTime(xSqlDataReader["LastUpdatedOn"]);
}
}
xSqlConnection.Close();
return(entity);
}
public async Task <ExpenseDto> GetExpenseById(int Id)
{
if (Id == default)
{
throw new ArgumentException($"{Id} is not a vaild identifier.");
}
ExpenseEntityDesign expenseEntity = await _expenseRepository.GetExpenseById(Id);
ExpenseDto expense = new ExpenseDto();
if (expenseEntity.Name == null && expenseEntity.Amount == 0)
{
throw new Exception("Invalid Id");
}
expense.Name = expenseEntity.Name;
expense.Amount = expenseEntity.Amount;
expense.Description = expenseEntity.Description;
expense.ExpenseDate = expenseEntity.ExpenseDate;
return(expense);
}
