public static ProductSupplier GetProductSupplierById(int productSupplierID) { int tempSupId; int tempProdId; SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT ProductSupplierId, ProductID,SupplierId " + "FROM Products_Suppliers " + "WHERE ProductSupplierId = @ProductSupplierId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ProductSupplierId", productSupplierID); try { connection.Open(); SqlDataReader Reader = selectCommand.ExecuteReader (CommandBehavior.SingleRow); // selecting by PK value if (Reader.Read()) { // we have a customer ProductSupplier productSupplier = new ProductSupplier(); productSupplier.ProductSupplierId = (int)Reader["ProductSupplierId"]; tempSupId = (int)Reader["SupplierId"]; tempProdId = (int)Reader["ProductId"]; connection.Close(); productSupplier.Supplier = SupplierDB.GetSupplierByID(tempSupId); productSupplier.Product = ProductDB.GetEntityById(tempSupId); return(productSupplier); } else // no customer { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <ProductSupplier> GetProductSuppliers() { List <ProductSupplier> ProdSupList = new List <ProductSupplier>(); string connectionString = "Data Source=localhost\\SAIT;Initial Catalog=TravelExperts;Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionString); string selectStatement = "SELECT ProductSupplierId ,ps.ProductId as ProductId, ProdName, ps.SupplierId as SupplierId, SupName " + "FROM Products_Suppliers as ps " + "INNER JOIN Products as p ON p.ProductId = ps.ProductId " + "INNER JOIN Suppliers as s on s.SupplierId = ps.SupplierId " + "GROUP BY ProductSupplierId, ps.SupplierId, ps.ProductId, p.ProdName, s.SupName"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { ProductSupplier productSupplier = new ProductSupplier(); Product product = new Product(); Supplier supplier = new Supplier(); productSupplier.ProductSupplierId = (int)reader["ProductSupplierId"]; product.ProductId = (int)reader["ProductId"]; product.Name = (string)reader["ProdName"]; supplier.SupplierId = (int)reader["SupplierId"]; supplier.Name = (string)reader["SupName"]; productSupplier.Product = product; productSupplier.Supplier = supplier; ProdSupList.Add(productSupplier); } connection.Close(); foreach (ProductSupplier ProdSup in ProdSupList) { ProdSup.Product = ProductDB.GetEntityById(ProdSup.Product.ProductId); ProdSup.Supplier = SupplierDB.GetSupplierByID(ProdSup.Supplier.SupplierId); } } catch (Exception) { throw; } return(ProdSupList); }