public List<Category> FindAll()
{
List<Category> Categories = new List<Category>();
string query = "SELECT * FROM Category";
using (SqlConnection db = new SqlConnection(CONN_STR))
{
if (db.State != System.Data.ConnectionState.Open)
{
db.Open();
}
using (SqlCommand cmd = db.CreateCommand())
{
cmd.CommandText = query;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Category c = new Category();
c.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
c.CategoryName = reader.GetString(reader.GetOrdinal("CategoryName"));
Categories.Add(c);
}
}
}
}
return Categories;
}
public Category Save(Category category)
{
string query = @"INSERT INTO Category (
CategoryID,
CategoryName
) VALUES (
@CategoryID,
@CategoryName
)";
using (SqlConnection db = new SqlConnection(CONN_STR))
{
if (db.State != System.Data.ConnectionState.Open)
{
db.Open();
}
using (SqlCommand cmd = db.CreateCommand())
{
cmd.CommandText = query;
cmd.Parameters.AddWithValue("CategoryID", category.CategoryID);
cmd.Parameters.AddWithValue("CategoryName", category.CategoryName);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@Identity";
category.CategoryID = (int)(decimal)cmd.ExecuteScalar();
}
}
return category;
}
public void Delete(Category category)
{
string query = "DELETE FROM Category";
using (SqlConnection db = new SqlConnection(CONN_STR))
{
if (db.State != System.Data.ConnectionState.Open)
{
db.Open();
}
using (SqlCommand cmd = db.CreateCommand())
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
}