public static List <Supplier> GetSupplier(int productID) { List <Supplier> suppliers = new List <Supplier>(); Supplier sup = null; SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT s.SupplierID, s.SupName, p.ProdName, p.ProductID " + "FROM Suppliers s " + "INNER JOIN Products_Suppliers ps " + "on s.SupplierID = ps.SupplierID " + "INNER JOIN Products p " + "on ps.ProductID = p.ProductID " + "WHERE p.ProductID = @ProductID"; SqlCommand cmd = new SqlCommand(selectStatement, con); cmd.Parameters.AddWithValue("@ProductID", productID); // value comes from the method's argument try { //add while loop in list form con.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) // found a supplier { sup = new Supplier(); sup.ProductID = (int)reader["ProductID"]; sup.SupplierID = (int)reader["SupplierID"]; sup.SupName = reader["SupName"].ToString(); sup.ProdName = reader["ProdName"].ToString(); suppliers.Add(sup); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(suppliers); }
public static List <Supplier> GetAllSuppliers() { List <Supplier> suppliers = new List <Supplier>(); Supplier sup = null; SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT s.SupplierID, s.SupName, p.ProdName, p.ProductID " + "FROM Suppliers s " + "INNER JOIN Products_Suppliers ps " + "on s.SupplierID = ps.SupplierID " + "INNER JOIN Products p " + "on ps.ProductID = p.ProductID "; SqlCommand cmd = new SqlCommand(selectStatement, con); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // while there are suppliers { sup = new Supplier(); sup.ProductID = (int)reader["ProductID"]; sup.SupplierID = (int)reader["SupplierID"]; sup.SupName = reader["SupName"].ToString(); sup.ProdName = reader["ProdName"].ToString(); suppliers.Add(sup); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(suppliers); }
/// <summary> /// <author> Brian Appleton</author> /// Gets all the suppliers attached to a specific product /// </summary> /// <param name="productId">the product id</param> /// <returns></returns> public static BindingList <Supplier> GetProductSuppliers(int productId) { BindingList <Supplier> suppliers = new BindingList <Supplier>(); SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT s.SupName,s.SupplierId,pr.ProdName " + "FROM Products_Suppliers as p " + "JOIN Suppliers as s " + "ON s.SupplierId = p.SupplierId " + "JOIN Products as pr " + "ON pr.ProductId = p.ProductId " + "WHERE pr.ProductID = @ProductID"; using (SqlCommand cmd = new SqlCommand(selectStatement, con)) { cmd.Parameters.AddWithValue("@ProductID", productId); con.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) // found a customer { Supplier supplier = new Supplier() { SupName = (string)reader[0], SupplierID = (int)reader[1], ProdName = (string)reader[2], }; suppliers.Add(supplier); } return(suppliers); } }
public static bool UpdateSupplier(Supplier oldSup, Supplier newSup) { SqlConnection con = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Suppliers " + "SET SupplierID = @NewSupplierID, " + " SupName = @NewSupName, " + "WHERE SupplierID = @OldSupplierID " + "AND SupName = @OldSupName "; SqlCommand cmd = new SqlCommand(updateStatement, con); cmd.Parameters.AddWithValue("@NewSupplierID", newSup.SupplierID); cmd.Parameters.AddWithValue("@NewSupName", newSup.SupName); cmd.Parameters.AddWithValue("@OldSupplierID", oldSup.SupplierID); cmd.Parameters.AddWithValue("@OldSupName", oldSup.SupName); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }