/// <summary> /// Public static method gets Suppliers from database, builds Supplier objects from /// their returned info, then loads each object into a list /// </summary> /// <returns>list of Supplier objects</returns> public static List<Supplier> GetSuppliers() { List<Supplier> suppliers = new List<Supplier>(); Supplier supplier; // for reading //Sql connection block to connect to TravelExpertsDB; closes connection at end of block //Executes query to retrieve SupplierId's using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT * " + "FROM Suppliers " + "ORDER BY SupplierId"; //sql command block; disposes command at end of block using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//new sqldatareader for accessing db while (reader.Read()) { // Create new supplier with current row information supplier = new Supplier((int)reader["SupplierId"], reader["SupName"].ToString()); suppliers.Add(supplier); // add to the list } } // cmd object recycled }// connection object recycled SqlConnection connection2 = TravelExpertsDB.GetConnection();//create new sql connection //for loop that iterates through the created list of Supplier objects for (int i = 0; i < suppliers.Count(); i++) { //runs a query that retrieves Products associated with each Supplier String subQuery = "SELECT p.ProductId, p.ProdName " + "FROM Products p JOIN Products_Suppliers ps " + "ON p.ProductId = ps.ProductId " + "WHERE ps.SupplierId = @SupplierId; "; //clear current Suppliers's list property for Products //to be loaded with the retrieved Products suppliers[i].Products.Clear(); //query that populates each retrieved Product's information into the Supplier //object's class property for Products //sql command block; disposes command at end of block using (SqlCommand subCmd = new SqlCommand(subQuery, connection2)) { connection2.Open(); //define and add sql parameter for our sqlcommand subCmd.Parameters.AddWithValue("@SupplierId", suppliers[i].SupplierId); SqlDataReader subReader = subCmd.ExecuteReader(CommandBehavior.CloseConnection);//new sqldatareader for accessing db while (subReader.Read()) { //add a new product to the current supplier suppliers[i].Products.Add(new Product((int)subReader["ProductId"], subReader["ProdName"].ToString())); } connection2.Close(); } } return suppliers; }
// Update Package: oldPackage - before update, newPackage - new data public static bool UpdatePackage(Packages oldPackage, Packages newPackage) { bool success = false; // create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string updateStatement = "UPDATE Packages SET " + "PkgName=@NewPkgName, " + "PkgStartDate=@NewPkgStartDate, " + "PkgEndDate=@NewPkgEndDate, " + "PkgDesc=@NewPkgDesc, " + "PkgBasePrice=@NewPkgBasePrice, " + "PkgAgencyCommission=@NewPkgAgencyCommission " + "WHERE PackageId=@OldPackageId " + // need for identification "AND PkgName=@OldPkgName " + "AND (PkgStartDate=@OldPkgStartDate OR PkgStartDate IS NULL AND @OldPkgStartDate IS NULL) " + "AND (PkgEndDate=@OldPkgEndDate OR PkgEndDate IS NULL AND @OldPkgEndDate IS NULL) " + "AND (PkgDesc=@OldPkgDesc OR PkgDesc IS NULL AND @OldPkgDesc IS NULL) " + "AND PkgBasePrice=@OldPkgBasePrice " + "AND (PkgAgencyCommission = @OldPkgAgencyCommission OR PkgAgencyCommission IS NULL AND @OldPkgAgencyCommission IS NULL) "; using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) { //supply paramter value, this way can avoid sql injection problem cmd.Parameters.AddWithValue("@OldPackageId", oldPackage.PackageId); cmd.Parameters.AddWithValue("@NewPkgName", newPackage.PkgName); cmd.Parameters.AddWithValue("@NewPkgDesc", newPackage.PkgDesc); cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPackage.PkgBasePrice); if (newPackage.PkgStartDate == null) { cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgStartDate", (DateTime)newPackage.PkgStartDate); } if (newPackage.PkgEndDate == null) { cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgEndDate", (DateTime)newPackage.PkgEndDate); } if (newPackage.PkgAgencyCommission.Equals(null)) { cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", Convert.ToDecimal(newPackage.PkgAgencyCommission)); } cmd.Parameters.AddWithValue("@OldPkgName", oldPackage.PkgName); cmd.Parameters.AddWithValue("@OldPkgDesc", oldPackage.PkgDesc); cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPackage.PkgBasePrice); if (oldPackage.PkgStartDate == null) { cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgStartDate", (DateTime)oldPackage.PkgStartDate); } if (oldPackage.PkgEndDate == null) { cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgEndDate", (DateTime)oldPackage.PkgEndDate); } if (oldPackage.PkgAgencyCommission.Equals(null)) { cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", Convert.ToDecimal(oldPackage.PkgAgencyCommission)); } try { // open the connection connection.Open(); // execute the delete command int count = cmd.ExecuteNonQuery(); // returns the number of rows affected // check if successful if (count > 0) { success = true; } } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } } } //return the indicator of success return(success); }
// delete a product from Products table // return indicator of success public static bool DeleteProduct(Products product) { bool success = false; // create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string deleteStatement1 = "DELETE FROM Packages_Products_Suppliers " + "WHERE ProductSupplierId IN " + "(SELECT ProductSupplierId " + "FROM Products_Suppliers " + "WHERE ProductId=@ProductId)"; string deleteStatement2 = "DELETE FROM Products_Suppliers " + "WHERE ProductId=@ProductId"; string deleteStatement3 = "DELETE FROM Products " + "WHERE ProductId=@ProductId " + // need for identification "AND ProdName=@ProdName"; // the AND is to ensure no one is updating this product connection.Open(); // start a location transaction SqlTransaction sqlTran = connection.BeginTransaction(); // Enlist a command in the current transaction SqlCommand command = connection.CreateCommand(); command.Transaction = sqlTran; //supply paramter value, this way can avoid sql injection problem command.Parameters.AddWithValue("@ProductId", product.ProductId); command.Parameters.AddWithValue("@ProdName", product.ProdName); try { // Execute three separate commands command.CommandText = deleteStatement1; command.ExecuteNonQuery(); command.CommandText = deleteStatement2; command.ExecuteNonQuery(); command.CommandText = deleteStatement3; command.ExecuteNonQuery(); // Commit the transaction sqlTran.Commit(); success = true; } catch (Exception) { try { //Atttemp to roll back the transaction sqlTran.Rollback(); success = false; } catch (Exception exRollback) { throw exRollback; } } finally // executes always { connection.Close(); } } //return the indicator of success return(success); }
// insert a new package into Packages table // return new package Id public static int AddPackage(Packages package) { int packageId = 0; // create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create INSERT command // CustomerID is IDENTITY so no value provided string insertStatement = "INSERT INTO Packages(PkgName, PkgStartDate, " + " PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " + "OUTPUT inserted.PackageId " + "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)"; using (SqlCommand cmd = new SqlCommand(insertStatement, connection)) { //supply paramter value, this way can avoid sql injection problem cmd.Parameters.AddWithValue("@PkgName", package.PkgName); if (package.PkgStartDate == null) { cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgStartDate", (DateTime)package.PkgStartDate); } if (package.PkgEndDate == null) { cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgEndDate", (DateTime)package.PkgEndDate); } cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc); cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice); if (package.PkgAgencyCommission.Equals(null)) { cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission); } // execute INSERT command try { // open the connection connection.Open(); // execute insert command and get inserted ID packageId = (int)cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } } } return(packageId); }
// delete a package from Packages table // return indicator of success public static bool DeletePackage(Packages package) { bool success = false; // create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string deleteStatement1 = "DELETE FROM Packages_Products_Suppliers " + "WHERE PackageId=@PackageId "; string deleteStatement2 = "DELETE FROM Packages " + "WHERE PackageId=@PackageId " + // need for identification "AND PkgName=@PkgName " + "AND (PkgStartDate=@PkgStartDate OR PkgStartDate IS NULL AND @PkgStartDate IS NULL) " + "AND (PkgEndDate=@PkgEndDate OR PkgEndDate IS NULL AND @PkgEndDate IS NULL) " + "AND (PkgDesc = @PkgDesc OR PkgDesc IS NULL AND @PkgDesc IS NULL) " + "AND PkgBasePrice=@PkgBasePrice " + "AND (PkgAgencyCommission=@PkgAgencyCommission OR PkgAgencyCommission IS NULL AND @PkgAgencyCommission IS NULL) "; connection.Open(); // start a location transaction SqlTransaction sqlTran = connection.BeginTransaction(); // Enlist a command in the current transaction SqlCommand cmd = connection.CreateCommand(); cmd.Transaction = sqlTran; //supply paramter value, this way can avoid sql injection problem cmd.Parameters.AddWithValue("@PackageId", package.PackageId); cmd.Parameters.AddWithValue("@PkgName", package.PkgName); if (package.PkgStartDate == null) { cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate); } if (package.PkgEndDate == null) { cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate); } cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc); cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice); if (package.PkgAgencyCommission.Equals(null)) { cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission); } try { // Execute three separate commands cmd.CommandText = deleteStatement1; cmd.ExecuteNonQuery(); cmd.CommandText = deleteStatement2; cmd.ExecuteNonQuery(); // Commit the transaction sqlTran.Commit(); success = true; } catch (Exception) { try { //Atttemp to roll back the transaction sqlTran.Rollback(); success = false; } catch (Exception exRollback) { throw exRollback; } } finally // executes always { connection.Close(); } } //return the indicator of success return(success); }
// retrieve package info with given package ID public static Packages GetPackageById(int packageId) { Packages package = null; //create the conneciton using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create select command string query = "SELECT PackageId, PkgName,PkgStartDate, PkgEndDate, " + "PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages " + "WHERE PackageId=@PackageId"; // any exception not handled here is automaticlly thrown to the form // where the method was called using (SqlCommand cmd = new SqlCommand(query, connection)) { //supply parameter value cmd.Parameters.AddWithValue("@PackageId", packageId); //open the connection connection.Open(); //run the command SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close connection as soon as done with reading //build product object to return if (reader.Read()) // if there is a product with this ID { package = new Packages(); package.PackageId = (int)reader["PackageId"]; package.PkgName = reader["PkgName"].ToString(); int col_st = reader.GetOrdinal("PkgStartDate"); //column number of Start Date if (reader.IsDBNull(col_st)) // if reader contains DBNull in this column { package.PkgStartDate = null; // make it null in the object } else // it is not null { package.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]); } int col_en = reader.GetOrdinal("PkgEndDate"); //column number of End Date if (reader.IsDBNull(col_en)) // if reader contains DBNull in this column { package.PkgEndDate = null; // make it null in the object } else // it is not null { package.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]); } package.PkgDesc = reader["PkgDesc"].ToString(); package.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]); int col_comm = reader.GetOrdinal("PkgAgencyCommission"); //column number of End Date if (reader.IsDBNull(col_comm)) // if reader contains DBNull in this column { package.PkgEndDate = null; // make it null in the object } else // it is not null { package.PkgAgencyCommission = (Decimal)reader["PkgAgencyCommission"]; } } } // command object recycled } // connection object recyled return(package); }
// get all Packages public static List <Packages> GetPackages() { List <Packages> packages = new List <Packages>(); // empty list of packages //create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create select command string query = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages " + "ORDER BY PackageId"; // any exception not handled here is automaticlly thrown to the form // where the method was called using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close connection as soon as done with reading while (reader.Read()) { Packages pkg = new Packages(); // for reading pkg.PackageId = (int)reader["PackageId"]; pkg.PkgName = reader["PkgName"].ToString(); int col_std = reader.GetOrdinal("PkgStartDate"); //column number of Start Date if (reader.IsDBNull(col_std)) // if reader contains DBNull in this column { pkg.PkgStartDate = null; // make it null in the object } else // it is not null { pkg.PkgStartDate = (DateTime)reader["PkgStartDate"]; } int col_end = reader.GetOrdinal("PkgEndDate"); //column number of End Date if (reader.IsDBNull(col_end)) // if reader contains DBNull in this column { pkg.PkgEndDate = null; // make it null in the object } else // it is not null { pkg.PkgEndDate = (DateTime)reader["PkgEndDate"]; } pkg.PkgDesc = reader["PkgDesc"].ToString(); pkg.PkgBasePrice = (Decimal)reader["PkgBasePrice"]; int col_comm = reader.GetOrdinal("PkgAgencyCommission"); //column number of End Date if (reader.IsDBNull(col_comm)) // if reader contains DBNull in this column { pkg.PkgAgencyCommission = null; // make it null in the object } else // it is not null { pkg.PkgAgencyCommission = (Decimal)reader["PkgAgencyCommission"]; } packages.Add(pkg); } } // command object recycled } // connection object recyled return(packages); }
// Update Product: oldProduct - before update, newProduct - new data public static bool UpdateProductSupplier(Products_Suppliers oldProductSupplier, Products_Suppliers newProductSupplier) { bool success = false; // create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string updateStatement = "UPDATE Products_Suppliers SET " + "ProductId=@NewProductId " + "SupplierId=@NewSupplierId " + "WHERE ProductSupplierId=@OldProductSupplierId " + // need for identification "AND (ProductId=@OldProductId OR ProductId IS NULL AND @OldProductId IS NULL) " + "AND (SupplierId=@OldSupplierId OR SupplierId IS NULL AND @OldSupplierId IS NULL)"; // the AND is to ensure no one is updating this product using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) { //supply paramter value, this way can avoid sql injection problem if (newProductSupplier.ProductId == null) { cmd.Parameters.AddWithValue("@NewProductId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewProductId", newProductSupplier.ProductId); } if (newProductSupplier.SupplierId == null) { cmd.Parameters.AddWithValue("@NewSupplierId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewSupplierId", newProductSupplier.SupplierId); } if (oldProductSupplier.ProductId == null) { cmd.Parameters.AddWithValue("@OldProductId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldProductId", oldProductSupplier.ProductId); } if (oldProductSupplier.SupplierId == null) { cmd.Parameters.AddWithValue("@OldSupplierId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldSupplierId", oldProductSupplier.SupplierId); } try { // open the connection connection.Open(); // execute the delete command int count = cmd.ExecuteNonQuery(); // returns the number of rows affected // check if successful if (count > 0) { success = true; } } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } } } //return the indicator of success return(success); }
/// <summary> /// Generates custom list of Supplier Names. /// Begins with names of suppliers that provide a given product. /// Then proceeds with names of suppliers that do NOT provide that product. /// </summary> /// <param name="prodName"></param> /// <returns>List of supplier names.</returns> public static List <string> GetSuppliersForPPS(string prodName) { List <string> supplierList = new List <string>(); supplierList.Add(""); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT Suppliers.SupName " + "FROM Products_Suppliers " + "JOIN Suppliers ON Products_Suppliers.SupplierId = Suppliers.SupplierId " + "JOIN Products ON Products_Suppliers.ProductId = Products.ProductId " + "WHERE ProdName = '" + prodName + "' " + "ORDER BY Suppliers.SupName"; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { string supName = (string)dr["SupName"]; supplierList.Add(supName); } } } } supplierList.Add(""); supplierList.Add("----------"); supplierList.Add(""); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT Suppliers.SupName " + "FROM Suppliers " + "WHERE SupName NOT IN (" + "SELECT Suppliers.SupName " + "FROM Products_Suppliers " + "JOIN Suppliers ON Products_Suppliers.SupplierId = Suppliers.SupplierId " + "JOIN Products ON Products_Suppliers.ProductId = Products.ProductId " + "WHERE ProdName = '" + prodName + "') " + "ORDER BY Suppliers.SupName"; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { string supName = (string)dr["SupName"]; supplierList.Add(supName); } } } } return(supplierList); }
public static PackageHC GetPackageById(int packageId) { PackageHC selectedPkg = new PackageHC(); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, " + "PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages " + "WHERE PackageId = @PackageId"; using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) { cmd.Parameters.AddWithValue("@PackageId", packageId); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) // if package with given ID exists { selectedPkg = new PackageHC(); selectedPkg.PackageId = (int)reader["PackageId"]; selectedPkg.PkgName = reader["PkgName"].ToString(); // for any column that can be null need to determine if it is DBNull // and set accordingly int colSD = reader.GetOrdinal("PkgStartDate"); // column number of PkgStartDate if (reader.IsDBNull(colSD)) // if reader contains DBNull in this column { selectedPkg.PkgStartDate = null; // make it null in the object } else // it is not null { selectedPkg.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]); } int colED = reader.GetOrdinal("PkgEndDate"); // column number of PkgEndDate if (reader.IsDBNull(colED)) // if reader contains DBNull in this column { selectedPkg.PkgEndDate = null; // make it null in the object } else // it is not null { selectedPkg.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]); } selectedPkg.PkgDesc = reader["PkgDesc"].ToString(); selectedPkg.PkgBasePrice = (decimal)reader["PkgBasePrice"]; int colAC = reader.GetOrdinal("PkgAgencyCommission"); // column number of PkgAgencyCommission if (reader.IsDBNull(colAC)) // if reader contains DBNull in this column { selectedPkg.PkgAgencyCommission = null; // make it null in the object } else // it is not null { selectedPkg.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; } } } } return(selectedPkg); }
public static List <PackageHC> GetPackages() { List <PackageHC> packages = new List <PackageHC>(); // empty list using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, " + "PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages"; using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) { connection.Open(); using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { PackageHC pkg; while (reader.Read()) { pkg = new PackageHC(); pkg.PackageId = (int)reader["PackageId"]; pkg.PkgName = reader["PkgName"].ToString(); // for any column that can be null need to determine if it is DBNull // and set accordingly int colSD = reader.GetOrdinal("PkgStartDate"); // column number of PkgStartDate if (reader.IsDBNull(colSD)) // if reader contains DBNull in this column { pkg.PkgStartDate = null; // make it null in the object } else // it is not null { pkg.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]); } int colED = reader.GetOrdinal("PkgEndDate"); // column number of PkgEndDate if (reader.IsDBNull(colED)) // if reader contains DBNull in this column { pkg.PkgEndDate = null; // make it null in the object } else // it is not null { pkg.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]); } pkg.PkgDesc = reader["PkgDesc"].ToString(); pkg.PkgBasePrice = (decimal)reader["PkgBasePrice"]; int colAC = reader.GetOrdinal("PkgAgencyCommission"); // column number of PkgAgencyCommission if (reader.IsDBNull(colAC)) // if reader contains DBNull in this column { pkg.PkgAgencyCommission = null; // make it null in the object } else // it is not null { pkg.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; } packages.Add(pkg); } } } } return(packages); }
/// <summary> /// Public static method gets Products from database, builds Product objects from /// their returned info, then loads each object into a list /// </summary> /// <returns>list of Product objects</returns> public static List <Product> GetProducts() { List <Product> products = new List <Product>(); Product product; // for reading //Sql connection block to connect to TravelExpertsDB; closes connection at end of block //Executes query to retrieve ProductId's using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT * " + "FROM Products " + "ORDER BY ProductId"; //sql command block; disposes command at end of block using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { int productId = (int)reader["ProductId"]; // get product ID // Run subquery on product id to get list of suppliers // Create new product with current row information product = new Product((int)reader["ProductId"], reader["ProdName"].ToString()); products.Add(product); // add to the list } } // cmd object recycled }// connection object recycled SqlConnection connection2 = TravelExpertsDB.GetConnection();//create new sql connection //for loop that iterates through the created list of Product objects for (int i = 0; i < products.Count(); i++) { //runs a query that retrieves Suppliers associated with each Product String subQuery = "SELECT s.SupplierId, s.SupName " + "FROM Suppliers s JOIN Products_Suppliers ps " + "ON s.SupplierId = ps.SupplierId " + "WHERE ps.ProductId = @ProductId; "; //clear current Product's list property for Suppliers //to be loaded with the retrieved Suppliers products[i].Suppliers.Clear(); //query that populates each retrieved Supplier's information into the Product //object's class property for Suppliers //sql command block; disposes command at end of block using (SqlCommand subCmd = new SqlCommand(subQuery, connection2)) { connection2.Open(); //define and add sql parameter for our sqlcommand subCmd.Parameters.AddWithValue("@ProductId", products[i].ProductId); SqlDataReader subReader = subCmd.ExecuteReader(CommandBehavior.CloseConnection);//new sqldatareader for accessing db while (subReader.Read()) { //add a new supplier to the current product products[i].Suppliers.Add(new Supplier((int)subReader["SupplierId"], subReader["SupName"].ToString())); } connection2.Close(); } } return(products); }