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; } }