//ADD NEW PRODUCT public static void AddProduct(Product product) { SqlConnection connection = AppRepairsDB.GetConnection(); string insertStatement = "INSERT Products (ProductCode, Name, YearsWarranty, ReleaseDate) " + "VALUES (@ProductCode, @Name, @YearsWarranty, @ReleaseDate)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode); insertCommand.Parameters.AddWithValue("@Name", product.ProductName); insertCommand.Parameters.AddWithValue("@YearsWarranty", product.YearsWarranty); insertCommand.Parameters.AddWithValue("@ReleaseDate", product.ReleaseDate); try { connection.Open(); insertCommand.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//GET ALL PRODUCT CODES FOR COMBO BOX public static List <string> GetProductCodes() { List <string> productCodes = new List <string>(); //empty list SqlConnection connection = AppRepairsDB.GetConnection(); string selectStatement = "SELECT ProductCode " + "FROM Products " + "ORDER BY ProductCode"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { string productCode = reader["ProductCode"].ToString(); productCodes.Add(productCode); } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(productCodes); }
//UPDATE PRODUCT DATA public static bool UpdateProduct(Product oldProduct, Product newProduct) { SqlConnection connection = AppRepairsDB.GetConnection(); string updateStatement = "UPDATE Products SET " + "ProductCode = @NewProductCode, " + "Name = @NewName, " + "YearsWarranty = @NewYearsWarranty, " + "ReleaseDate = @NewReleaseDate " + "WHERE ProductCode = @OldProductCode " + "AND Name = @OldName " + "AND YearsWarranty = @OldYearsWarranty " + "AND ReleaseDate = @OldReleaseDate"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@NewProductCode", newProduct.ProductCode); updateCommand.Parameters.AddWithValue("@NewName", newProduct.ProductName); updateCommand.Parameters.AddWithValue("@NewYearsWarranty", newProduct.YearsWarranty); updateCommand.Parameters.AddWithValue("@NewReleaseDate", newProduct.ReleaseDate); updateCommand.Parameters.AddWithValue("@OldProductCode", oldProduct.ProductCode); updateCommand.Parameters.AddWithValue("@OldName", oldProduct.ProductName); updateCommand.Parameters.AddWithValue("@OldYearsWarranty", oldProduct.YearsWarranty); updateCommand.Parameters.AddWithValue("@OldReleaseDate", oldProduct.ReleaseDate); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) //a row was affected { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//RETURN ONE PRODUCT BASED ON PRODUCT CODE public static Product GetProduct(string productCode) { Product p = new Product(); SqlConnection connection = AppRepairsDB.GetConnection(); string selectStatement = "SELECT ProductCode, Name, YearsWarranty, ReleaseDate " + "FROM Products " + "WHERE ProductCode = @ProductCode " + "ORDER BY ProductCode"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ProductCode", productCode); //assign value to @ProductCode try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) //if there was a matching product code { p.ProductCode = reader["ProductCode"].ToString(); p.ProductName = reader["Name"].ToString(); p.YearsWarranty = Convert.ToDecimal(reader["YearsWarranty"]); p.ReleaseDate = (DateTime)reader["ReleaseDate"]; } else { p = null; //product does not exist } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(p); }
//DELETE PRODUCT public static int DeleteProduct(Product product) { int deleteCount = 0; SqlConnection connection = AppRepairsDB.GetConnection(); string deleteStatement = "DELETE FROM Products " + "WHERE ProductCode = @ProductCode " + "AND Name = @Name " + "AND YearsWarranty = @YearsWarranty " + "AND ReleaseDate = @ReleaseDate"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode); deleteCommand.Parameters.AddWithValue("@Name", product.ProductName); deleteCommand.Parameters.AddWithValue("@YearsWarranty", product.YearsWarranty); deleteCommand.Parameters.AddWithValue("@ReleaseDate", product.ReleaseDate); try { connection.Open(); deleteCommand.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(deleteCount); }