public void AddCategoryToDb(Category category)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string countCommad = "SELECT COUNT(*) FROM Category WHERE id=" + category.Id;
SqlCommand sqlCountCommand = new SqlCommand(countCommad, connection);
var count = (int)sqlCountCommand.ExecuteScalar();
if (count >= 1)
{
string updateCommand = "UPDATE Category SET Name=@name,Code=@code WHERE id=" + category.Id;
AddOrEditCategory(updateCommand, category, connection);
}
else
{
string addCommand = "INSERT INT Category (Name,Code) VALUES (@name,@code)";
AddOrEditCategory(addCommand, category, connection);
SqlCommand getLastIdCmd = new SqlCommand("SELECT @@IDENTITY", connection);
int insertedRecordId = (int)(decimal)getLastIdCmd.ExecuteScalar();
category.Id = insertedRecordId;
}
}
}
public List<Category> GetAllCategorys()
{
var listCategory = new List<Category>();
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string command = "SELECT * FROM Category";
SqlCommand sqlCommand = new SqlCommand(command, connection);
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var category = new Category();
category.Id = (int)reader["Id"];
category.Name = (string)reader["Name"];
category.Code = (string)reader["Code"];
listCategory.Add(category);
}
return listCategory.ToList();
}
}
private void AddOrEditCategory(string command, Category category, SqlConnection connection)
{
SqlCommand sqlCommand = new SqlCommand(command, connection);
sqlCommand.Parameters.AddWithValue("@name", category.Name);
sqlCommand.Parameters.AddWithValue("@code", category.Code);
var rows = sqlCommand.ExecuteNonQuery();
Console.WriteLine("Rows Affected: {0}", rows);
}