public void DeleteRecordTest()
{
if (!this.dataAccessFactory.ExpenseTableExists)
{
// Create Table if it has not been created yet
this.dataAccessFactory.CreateNewExpenseTable();
}
Expense e = new Expense(
50.00f,
DateTime.Today,
"Walmart",
new Cash(),
null,
"bought groceries and deoderant");
e.AddTag("Food");
e.AddTag("Hygene");
this.dataAccessFactory.InsertRecord(e);
// Now delete it
this.dataAccessFactory.DeleteRecord(1);
// verify columns created correctly
using (SqliteConnection db = new SqliteConnection(this.CreateConnectionString()))
{
db.Open();
using (SqliteCommand cmd = db.CreateCommand())
{
cmd.CommandText = string.Format("SELECT count(*) FROM {0};", TABLENAME);
using (SqliteDataReader res = cmd.ExecuteReader())
{
while (res.Read())
{
Console.WriteLine(
$@"{res.GetInt32(0),-3}");
Assert.AreEqual(0, res.GetInt32(0)); // count
}
}
}
// Clean table of all entries for consecutive runs
using (SqliteCommand cmd = db.CreateCommand())
{
cmd.CommandText = string.Format("DROP TABLE {0};", TABLENAME);
cmd.ExecuteNonQuery();
}
}
}
public void GetAllTest()
{
if (!this.dataAccessFactory.ExpenseTableExists)
{
// Create Table if it has not been created yet
this.dataAccessFactory.CreateNewExpenseTable();
}
Expense e = new Expense(
50.00f,
DateTime.Today,
"Walmart",
new Cash(),
null,
"bought groceries and deoderant");
e.AddTag("Food");
e.AddTag("Hygene");
this.dataAccessFactory.InsertRecord(e);
this.dataAccessFactory.InsertRecord(e);
this.dataAccessFactory.InsertRecord(e);
List <Expense> expenses = this.dataAccessFactory.GetAll();
Assert.AreEqual(3, expenses.Count);
foreach (Expense expense in expenses)
{
Assert.AreEqual(e, expense);
}
// Clean table of all entries for consecutive runs
using (SqliteConnection db = new SqliteConnection(this.CreateConnectionString()))
{
db.Open();
using (SqliteCommand cmd = db.CreateCommand())
{
cmd.CommandText = string.Format("DROP TABLE {0};", TABLENAME);
cmd.ExecuteNonQuery();
}
}
}
public void UpdateRecordTest()
{
if (!this.dataAccessFactory.ExpenseTableExists)
{
// Create Table if it has not been created yet
this.dataAccessFactory.CreateNewExpenseTable();
}
Expense e = new Expense(
50.00f,
DateTime.Today,
"Walmart",
new Cash(),
null,
"bought groceries and deoderant");
e.AddTag("Food");
e.AddTag("Hygene");
this.dataAccessFactory.InsertRecord(e);
// Now change e and update it
e = new Expense(
1, // Note: never hard assign this value except here for testing.
999.00f,
DateTime.Today,
"Walmart",
new Cash(),
null,
"bought drugz");
e.AddTag("Drugs");
this.dataAccessFactory.UpdateRecord(e);
// verify columns created correctly
using (SqliteConnection db = new SqliteConnection(this.CreateConnectionString()))
{
db.Open();
using (SqliteCommand cmd = db.CreateCommand())
{
cmd.CommandText = string.Format("SELECT * FROM {0};", TABLENAME);
using (SqliteDataReader res = cmd.ExecuteReader())
{
while (res.Read())
{
Console.WriteLine(this.StringifyDataRow(res));
Assert.AreEqual(1, res.GetInt32(0)); // Id
Assert.AreEqual(e.Value, (float)res.GetDecimal(1)); // Value
Assert.AreEqual(e.Date, res.GetDateTime(2)); // Date
Assert.AreEqual(e.Place, res.GetString(3)); // Place
Assert.AreEqual(string.Join(":", e.Tag), res.GetString(4)); // Tag
Assert.AreEqual(e.Notes, res.GetString(5)); // Notes
Assert.AreEqual((int)DataAccessFactory.EpurchaseMethod.CASH, res.GetInt32(6)); // type
Assert.True(res.IsDBNull(7)); // Debit field should be null. Debit
Assert.True(res.IsDBNull(8)); // Provider field should be null. Provider
Assert.True(res.IsDBNull(9)); // Number should be null. Number
Assert.True(res.IsDBNull(10)); // Name should be null. Name
Assert.False(res.IsDBNull(11)); // Currency should NOT be null. Currency
Assert.AreEqual((e.Method as Cash).Currency, res.GetValue(11).ToString()); // Currency
Assert.True(res.IsDBNull(12)); // Savings
Assert.True(res.IsDBNull(13)); // Bankname
}
}
}
// Clean table of all entries for consecutive runs
using (SqliteCommand cmd = db.CreateCommand())
{
cmd.CommandText = string.Format("DROP TABLE {0};", TABLENAME);
cmd.ExecuteNonQuery();
}
}
}