public void AddItem(string item, double cost)
{
string connectionstring = @"URI=file:Item.db";
SqliteConnection sqcon = new SqliteConnection (connectionstring);
sqcon.Open ();
SqliteCommand sqcmd = sqcon.CreateCommand ();
string sql = @"INSERT INTO items(item, cost) VALUES(@item, @cost);";
sqcmd.CommandText = sql;
sqcmd.Prepare ();
sqcmd.Parameters.AddWithValue ("@item", item);
sqcmd.Parameters.AddWithValue ("@cost", cost);
sqcmd.ExecuteNonQuery ();
sqcmd.Dispose ();
sqcon.Clone ();
Console.WriteLine ("item {0} successfully added!", item);
}
private int ExecuteNonQuery(string sql)
{
SqliteConnection cnn = new SqliteConnection(dbConnection);
cnn.Open();
SqliteCommand cmd = new SqliteCommand(cnn) { CommandText = sql };
int rowUpdated = cmd.ExecuteNonQuery();
cnn.Clone();
return rowUpdated;
}
public void ShowItem()
{
List<MyItem> item_list = new List<MyItem> ();
double total_cost = 0;
string connectionstring = @"URI=file:Item.db";
SqliteConnection sqcon = new SqliteConnection (connectionstring);
sqcon.Open ();
SqliteCommand sqcmd = sqcon.CreateCommand ();
string sql = @"SELECT id, item, cost from items";
sqcmd.CommandText = sql;
IDataReader reader = sqcmd.ExecuteReader ();
while(reader.Read())
item_list.Add(new MyItem(reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2)));
sqcmd.Dispose ();
sqcon.Clone ();
foreach (MyItem i in item_list) {
Console.WriteLine ("item No {0} {1} costs {2}", i.ID, i.DESCR, i.COST);
total_cost += i.COST;
Console.WriteLine ("TotalCost: {0}", total_cost);
}
}
public void RemoveItem(int id)
{
string connectionstring = @"URI=file:Item.db";
SqliteConnection sqcon = new SqliteConnection (connectionstring);
sqcon.Open ();
SqliteCommand sqcmd = sqcon.CreateCommand ();
string sql = @"DELETE FROM items WHERE id = @idno;";
sqcmd.CommandText = sql;
sqcmd.Prepare ();
sqcmd.Parameters.AddWithValue ("@idno", id);
sqcmd.ExecuteNonQuery ();
sqcmd.Dispose ();
sqcon.Clone ();
Console.WriteLine ("item No {0} successfully removed!", id);
}
