public bool Insert(Product product)
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
string query = @"SELECT count(*) FROM Product c WHERE c.Id='" + product.Id + "'";
var command = new SqlCommand(query, connection);
int rows = (int)command.ExecuteScalar();
if (rows == 1)
{
string updateQuery =
string.Format(
"UPDATE Product SET Name='{0}',singlePrice='{1}', Category='{2}' WHERE Id={3}",
product.Name,
product.Price,
product.Category,
product.Id);
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.ExecuteNonQuery();
return true;
}
else if (rows == 0)
{
string insertQuery = string.Format(
"INSERT INTO Product VALUES('{0}','{1}','{2}',{3})",
product.Name,
product.Price,
product.Category);
var insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.ExecuteNonQuery();
product.Id = this.GetLastId("Product");
}
}
return true;
}
public Product GetProductByID(int id)
{
Product prod = new Product();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = @"SELECT*
FROM[PRODUCT]
WHERE PRODUCTID= " + id;
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
prod.Name = (string)reader["NAME"];
prod.SinglePrice = (Decimal)reader["SINGLEPRICE"];
prod.CategoryID = (int)reader["CATEGORYID"];
prod.ProductID = (int)reader["PRODUCTID"];
}
}
return prod;
}
public void CreateOrAddProduct(Product product)
{
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"SELECT COUNT(*)
FROM PRODUCT
WHERE PRODUCTID=" + product.ProductID;
var command = new SqlCommand(query, connection);
int counter = (int)command.ExecuteScalar();
if (counter == 1)
{
string updateQuery = @"UPDATE PRODUCT
SET NAME=@name,SINGLEPRICE=@price,CATEGORYID=@categoryID,PRODUCTID=@productID
WHERE PRODUCTID=" + product.ProductID;
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.Parameters.AddWithValue("@name", product.ProductID);
updateCommand.Parameters.AddWithValue("@price", product.SinglePrice);
updateCommand.Parameters.AddWithValue("@categoryID", product.CategoryID);
updateCommand.Parameters.AddWithValue("@productID", product.ProductID);
updateCommand.ExecuteNonQuery();
}
else
{
string addQuery = @"INSERT INTO PRODUCT(NAME,SINGLEPRICE,CATEGORYID,PRODUCTID)
VALUES(@name,@price,@categoryID,@productID)";
var addCommand = new SqlCommand(addQuery, connection);
addCommand.Parameters.AddWithValue("@name", product.ProductID);
addCommand.Parameters.AddWithValue("@price", product.SinglePrice);
addCommand.Parameters.AddWithValue("@categoryID", product.CategoryID);
addCommand.Parameters.AddWithValue("@productID", product.ProductID);
addCommand.ExecuteNonQuery();
}
}
}
public List<Product> ListProducts()
{
List<Product> product= new List<Product>();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
string query = @"SELECT*
FROM[PRODUCT]";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
Product prod = new Product();
prod.Name = (string)reader["NAME"];
prod.SinglePrice = (Decimal)reader["SINGLEPRICE"];
prod.CategoryID = (int)reader["CATEGORYID"];
prod.ProductID = (int)reader["PRODUCTID"];
product.Add(prod);
}
}
return product;
}
public void UpdateOrInsert(Product product)
{
string query = $@"IF EXISTS (SELECT TOP 1 *
FROM Products
WHERE ProductID = '{product.ProductId}')
BEGIN
UPDATE Products
SET Name = '{product.Name}'
SET Price = '{product.Price}'
SET CategoryID = '{product.CategoryId}'
WHERE ProductID = '{product.ProductId}'
END
ELSE
BEGIN
SET IDENTITY_INSERT Products ON
INSERT INTO Products
VALUES ('{product.ProductId}', '{product.Name}', '{product.Price}', '{product.CategoryId}')
SET IDENTITY_INSERT Products OFF
END";
ExecuteNonQuery(query);
}
