public void AddProductToDb(Product product)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string cmd = "SELECT COUNT(*) FROM Product WHERE Id=" + product.Id;
SqlCommand countProductsCmd = new SqlCommand(cmd, connection);
int count = (int)countProductsCmd.ExecuteScalar();
if (count >= 1)
{
string updateCommand = "UPDATE Product SET Name = @name, Price = @price, Category_Id = @categoryId WHERE Id=" + product.Id;
AddOrEdintProduct(product, updateCommand, connection);
}
else
{
string addCommand = "INSERT INTO Product (Name,Price,Category_Id) VALUES (@name,@price,@categoryId)";
AddOrEdintProduct(product, addCommand, connection);
SqlCommand getLastIdCmd = new SqlCommand("SELECT @@IDENTITY", connection);
int insertedRecordId = (int)(decimal)getLastIdCmd.ExecuteScalar();
product.Id = insertedRecordId;
}
}
}
public void AddOrEdintProduct(Product product, string command, SqlConnection connection)
{
SqlCommand sqlCommand = new SqlCommand(command, connection);
sqlCommand.Parameters.AddWithValue("@name", product.Name);
sqlCommand.Parameters.AddWithValue("@price", product.Price);
sqlCommand.Parameters.AddWithValue("@categoryId", product.CategoryId);
var rows = sqlCommand.ExecuteNonQuery();
Console.WriteLine("Rows Affected: {0}", rows);
}
public List<Product> GetAllProducts()
{
var listProducts = new List<Product>();
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string command = "SELECT * FROM Products";
SqlCommand sqlCommand = new SqlCommand(command, connection);
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var product = new Product();
product.Id = (int)reader["Id"];
product.Name = (string)reader["Name"];
product.Price = (decimal)reader["Price"];
product.CategoryId = (int)reader["Category_Id"];
listProducts.Add(product);
}
return listProducts.ToList();
}
}