private static int DoRegisterNewExpenseInDb(ExpenseInfo expenseDetails)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "INSERT INTO expense(expense_id,expense_purpose,expense_doe,expensed_by,expensed_amount) "
+ "VALUES(@expense_id,@expense_purpose,@expense_doe,@expensed_by,@expensed_amount)";
msqlCommand.Parameters.AddWithValue("@expense_id", expenseDetails.id);
msqlCommand.Parameters.AddWithValue("@expense_purpose", expenseDetails.purpose);
msqlCommand.Parameters.AddWithValue("@expense_doe", expenseDetails.doe);
msqlCommand.Parameters.AddWithValue("@expensed_by", expenseDetails.expensed_by);
msqlCommand.Parameters.AddWithValue("@expensed_amount", expenseDetails.amount);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public static int DoRegisterNewExpense(ExpenseInfo expenseDetails)
{
return DoRegisterNewExpenseInDb(expenseDetails);
}
public static void EditExpense(ExpenseInfo expenseToEdit)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "UPDATE expense SET expense_purpose=@expense_purpose,expense_doe=@expense_doe,expensed_by=@expensed_by,expensed_amount=@expensed_amount WHERE expense_id=@expense_id";
msqlCommand.Parameters.AddWithValue("@expense_purpose", expenseToEdit.purpose);
msqlCommand.Parameters.AddWithValue("@expense_doe", expenseToEdit.doe);
msqlCommand.Parameters.AddWithValue("@expensed_by", expenseToEdit.expensed_by);
msqlCommand.Parameters.AddWithValue("@expensed_amount", expenseToEdit.amount);
msqlCommand.Parameters.AddWithValue("@expense_id", expenseToEdit.id);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private static List<ExpenseInfo> QueryAllExpenseList()
{
List<ExpenseInfo> ExpenseList = new List<ExpenseInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From expense;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ExpenseInfo Expense = new ExpenseInfo();
Expense.id = msqlReader.GetString("expense_id");
Expense.purpose = msqlReader.GetString("expense_purpose");
Expense.doe = msqlReader.GetDateTime("expense_doe");
Expense.expensed_by = msqlReader.GetString("expensed_by");
Expense.amount = msqlReader.GetDouble("expensed_amount");
ExpenseList.Add(Expense);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return ExpenseList;
}