/// <summary> /// Returns all categories. /// </summary> public IEnumerable<Category> GetAllCategories() { var categories = new List<Category>(); using (var connection = new SqliteConnection("Data Source=" + dbPath)) using (var query = new SqliteCommand("SELECT * FROM Categories", connection)) { connection.Open(); var reader = query.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { var category = new Category(); category.Id = int.Parse(reader["id"].ToString()); category.Name = reader ["name"].ToString(); categories.Add(category); } reader.Close(); } return categories; }
/// <summary> /// Returns the category for the specified id. /// </summary> /// <param name="categoryId">Category identifier.</param> public Category GetCategory(int categoryId) { Category category = null; using (var connection = new SqliteConnection("Data Source=" + dbPath)) using (var query = new SqliteCommand("SELECT * FROM Categories WHERE id = @categoryId", connection)) { query.Parameters.AddWithValue("@categoryId", categoryId); connection.Open(); var reader = query.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { category = new Category(); category.Id = int.Parse(reader["id"].ToString()); category.Name = reader ["name"].ToString(); } reader.Close(); } return category; }
/// <summary> /// Saves the specified category. /// </summary> /// <param name="category">Category to save</param> public void Save(Category category) { if (category == null) throw new ArgumentNullException("category", "Category should not be null"); if (category.Id == 0) { using (var connection = new SqliteConnection("Data Source=" + dbPath)) using (var query = new SqliteCommand("INSERT INTO Categories VALUES (NULL, @categoryName)", connection)) { query.Parameters.AddWithValue("@categoryName", category.Name); connection.Open(); query.ExecuteNonQuery(); connection.Close(); } } else { using (var connection = new SqliteConnection("Data Source=" + dbPath)) using (var query = new SqliteCommand("UPDATE Categories SET Name = @categoryName WHERE Id = @id", connection)) { query.Parameters.AddWithValue("@id", category.Id); query.Parameters.AddWithValue("@categoryName", category.Name); connection.Open(); query.ExecuteNonQuery(); connection.Close(); } } }