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); }