/// <summary> /// Katrina Spencer: Retrieves list of products-suppliers /// </summary> /// <returns>products-suppliers</returns> public static List <ProductSupplier> GetAllProductsSuppliers() { List <ProductSupplier> prodsSups = new List <ProductSupplier>(); // empty ProductSupplier prodSup; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProductSupplierId, ProductId, SupplierId " + "FROM Products_Suppliers " + "ORDER BY ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { prodSup = new ProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; prodSup.ProductId = (int)dr["ProductId"]; prodSup.SupplierId = (int)dr["ProdName"]; prodsSups.Add(prodSup); } } } } return(prodsSups); }
/// <summary> /// Katrina Spencer: Adds new product-supplier /// </summary> /// <param name="prodSup">new product-supplier info</param> /// <returns>new product-supplier id</returns> public static int AddProductSupplier(ProductSupplier prodSup) { int prodSupId = 0; using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " + "OUTPUT INSERTED.ProductSupplierId " + // returns single value "VALUES(@ProductId, @SupplierId)"; using (SqlCommand cmd = new SqlCommand(insertStatement, connection)) { if (prodSup.ProductId == null) { cmd.Parameters.AddWithValue("@ProductId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@ProductId", (int)prodSup.ProductId); } if (prodSup.SupplierId == null) { cmd.Parameters.AddWithValue("@SupplierId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@SupplierId", (int)prodSup.SupplierId); } // open connection connection.Open(); prodSupId = (int)cmd.ExecuteScalar(); // returns one value } } return(prodSupId); }
/// <summary> /// Katrina Spencer: Retrieves product-supplier info with given id /// </summary> /// <param name="prodSupId">id of product-supplier to get</param> /// <returns>product-supplier object</returns> public static ProductSupplier GetProductSupplier(int prodSupId) { ProductSupplier prodSup = null; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProductSupplierId, ps.ProductId, ProdName, ps.SupplierId, SupName " + "FROM Products_Suppliers AS ps " + "JOIN Products ON ps.ProductId = Products.ProductId " + "JOIN Suppliers ON ps.SupplierId = Suppliers.SupplierId " + "WHERE ProductSupplierId = @ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@ProductSupplierId", prodSupId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { if (dr.Read()) // if data { prodSup = new ProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; // determine if it is DBNull and set int colProd = dr.GetOrdinal("ProductId"); // column number of ProductId if (dr.IsDBNull(colProd)) // if reader contains DBNull in this column { prodSup.ProductId = null; // make null in the object } else // if not null { prodSup.ProductId = (int)(dr["ProductId"]); } int colSup = dr.GetOrdinal("SupplierId"); // column number of SupplierId if (dr.IsDBNull(colSup)) // if reader contains DBNull in this column { prodSup.SupplierId = null; // make null in the object } else // if not null { prodSup.SupplierId = (int)(dr["SupplierId"]); } int colProdName = dr.GetOrdinal("ProdName"); // column number of ProdName if (dr.IsDBNull(colProdName)) // if reader contains DBNull in this column { prodSup.ProdName = null; // make null in the object } else // if not null { prodSup.ProdName = (string)(dr["ProdName"]); } int colSupName = dr.GetOrdinal("SupName"); // column number of SupName if (dr.IsDBNull(colSupName)) // if reader contains DBNull in this column { prodSup.SupName = null; // make null in the object } else // if not null { prodSup.SupName = (string)(dr["SupName"]); } } } } } return(prodSup); }
/// <summary> /// Katrina Spencer: Updates product-supplier info /// </summary> /// <param name="oldProdSup">old product-supplier info</param> /// <param name="newProdSup">new product-supplier info</param> /// <returns>success indicator</returns> public static bool UpdateProductSupplier(ProductSupplier oldProdSup, ProductSupplier newProdSup) { bool result = false; // no success yet using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string updateStatement = "UPDATE Products_Suppliers " + "SET ProductId = @NewProductId, " + "SupplierId = @NewSupplierId " + "WHERE ProductSupplierId = @OldProductSupplierId " + // identifies product-supplier "AND (ProductId = @OldProductId " + "OR ProductId IS NULL " + "AND @OldProductId IS NULL) " + "AND (SupplierId = @OldSupplierId " + "OR SupplierId IS NULL " + "AND @OldSupplierId IS NULL)"; using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) { if (newProdSup.ProductId == null) // if new ProductId is null { cmd.Parameters.AddWithValue("@NewProductId", DBNull.Value); // set DBNull } else { cmd.Parameters.AddWithValue("@NewProductId", (int)newProdSup.ProductId); } if (newProdSup.SupplierId == null) // if new SupplierId is null { cmd.Parameters.AddWithValue("@NewSupplierId", DBNull.Value); // set DBNull } else { cmd.Parameters.AddWithValue("@NewSupplierId", (int)newProdSup.SupplierId); } cmd.Parameters.AddWithValue("@OldProductSupplierId", oldProdSup.ProductSupplierId); if (oldProdSup.ProductId == null) // if old ProductId is null { cmd.Parameters.AddWithValue("@OldProductId", DBNull.Value); // set DBNull } else { cmd.Parameters.AddWithValue("@OldProductId", (int)oldProdSup.ProductId); } if (oldProdSup.SupplierId == null) // if old SupplierId is null { cmd.Parameters.AddWithValue("@OldSupplierId", DBNull.Value); // set DBNull } else { cmd.Parameters.AddWithValue("@OldSupplierId", (int)oldProdSup.SupplierId); } // open connection connection.Open(); // execute UPDATE command int count = cmd.ExecuteNonQuery(); if (count > 0) // if row(s) affected { result = true; } } } return(result); }