public static int CreateNewPlan(Plan plan)
{
using (SqlCeConnection con = DB.GetSqlCeConnection())
{
String query = "INSERT INTO Plans (Name, Price, Duration, Notes) VALUES (@name, @price, @duration, @notes)";
SqlCeCommand cmd = new SqlCeCommand(query, con);
cmd.Parameters.AddWithValue("@name", plan.Name);
cmd.Parameters.AddWithValue("@price", plan.Price);
cmd.Parameters.AddWithValue("@duration", plan.Duration);
cmd.Parameters.AddWithValue("@notes", plan.Notes);
int res = cmd.ExecuteNonQuery();
return res;
}
}
/// <summary>
/// retrieves the specified plan
/// </summary>
/// <param name="id"></param>
public static Plan GetPlan(int id)
{
Plan plan = new Plan();
using (SqlCeConnection con = DB.GetSqlCeConnection())
{
String query = "SELECT * FROM Plans WHERE Id = @id";
SqlCeCommand cmd = new SqlCeCommand(query, con);
cmd.Parameters.AddWithValue("@id", id);
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
plan.Id = reader.GetInt32(0);
plan.Name = reader.GetString(1);
plan.Duration = reader.GetInt32(2);
plan.Price = reader.GetDecimal(3);
if (!reader.IsDBNull(4))
plan.Notes = reader.GetString(4);
}
}
return plan;
}
/// <summary>
/// updates the details of specified programme/plan
/// </summary>
/// <param name="plan"></param>
/// <param name="id"></param>
/// <returns></returns>
public static int UpdatePlan(Plan plan)
{
using (SqlCeConnection con = DB.GetSqlCeConnection())
{
String query = "UPDATE Plans SET Name = @name, Price = @price, Duration = @duration, Notes = @notes WHERE Id = @id";
SqlCeCommand cmd = new SqlCeCommand(query, con);
cmd.Parameters.AddWithValue("@id", plan.Id);
cmd.Parameters.AddWithValue("@name", plan.Name);
cmd.Parameters.AddWithValue("@duration", plan.Duration);
cmd.Parameters.AddWithValue("@price", plan.Price);
cmd.Parameters.AddWithValue("@notes", plan.Notes);
int res = cmd.ExecuteNonQuery();
return res;
}
}