//This method is used to get the suppliersId when given the supplier name in the suppliers table from the db. //It returns an integer of the supplierId. public static int getSupplierId(string supName) { int supplierId = 0; //Set the supplierId to 0, if the method return 0 then no supplierId is found for that supplier Name SqlConnection con = TravelExpertsDB.GetConnection(); //Create a connection //Create a query for selecting the supplierId from the suppliers table where supplier name is given string selectQuery = "SELECT SupplierId " + "FROM Suppliers " + "WHERE SupName = @SupName"; SqlCommand selectCommand = new SqlCommand(selectQuery, con); //Create a select command using SqlCommand selectCommand.Parameters.AddWithValue("@SupName", supName); //Bind the supplier name to the @SupName try { con.Open(); //Open the connection SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); //Execute the query and store the result in reader if (reader.Read()) //read the customer if exists { supplierId = (int)reader["SupplierId"]; //save stored result in supplierId } } catch (SqlException ex) { throw ex; } finally { con.Close();//close the connection } return(supplierId);//return the supplierId }
//This method is used to get the suppliers name when given the supplierId in the suppliers table from the db. //It returns a string of the supplier name. public static string getSupName(int supplierId) { string supName = ""; //Create an empty string for supplier name SqlConnection con = TravelExpertsDB.GetConnection(); //Create a connection to the db //Create a query for selecting SupName form the suppliers table where the SuppliersId is known string selectQuery = "SELECT SupName " + "FROM Suppliers " + "WHERE SupplierId = @SupplierId"; //Build the selectCommand by giving SqlCommand the query and the connection to the db SqlCommand selectCommand = new SqlCommand(selectQuery, con); //Bind the supplierId to @SupplierId selectCommand.Parameters.AddWithValue("@SupplierId", supplierId); try { con.Open(); //Open the connection SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) //read the customer if exists { supName = (string)reader["SupName"]; // store the supplier name as supName } } catch (SqlException ex) { throw ex; } finally { con.Close();//close the connection } return(supName);//returnt he suppier name string }
public static bool DeleteSupplier(int supplierId) { SqlConnection con = TravelExpertsDB.GetConnection();//Create a connection //Create a query for inserting a supplier into the suppliers table string deleteQuery = "DELETE Suppliers " + "WHERE SupplierId = @SupplierId"; SqlCommand deleteCommand = new SqlCommand(deleteQuery, con); //Create a insert command using SqlCommand deleteCommand.Parameters.AddWithValue("@SupplierId", supplierId); //Bind the supplierId name to the @SupplierId try { con.Open(); int count = deleteCommand.ExecuteNonQuery(); //for DML statements if (count > 0) { return(true); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(false); }
public static bool InsertSupplier(int supplierId, string supName) { SqlConnection con = TravelExpertsDB.GetConnection();//Create a connection //Create a query for inserting a supplier into the suppliers table string insertQuery = "INSERT INTO Suppliers (SupplierId, SupName) " + "VALUES (@SupplierId, @SupName)"; SqlCommand insertCommand = new SqlCommand(insertQuery, con); //Create a insert command using SqlCommand insertCommand.Parameters.AddWithValue("@SupplierId", supplierId); //Bind the supplierId name to the @SupplierId insertCommand.Parameters.AddWithValue("@SupName", supName); //Bind the supplier name to the @SupName try { con.Open(); int count = insertCommand.ExecuteNonQuery(); //for DML statements if (count > 0) { return(true); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(false); }
//gets the product supplier using the package public static List <Package_Product_Suppliers> GetProductSuppliersByPackage(int pkgID) { if (pkgID == -1) // if the packageID is negative , return null (dirty workaround) { return(null); } else { // create a new list of Package Product Suppliers List <Package_Product_Suppliers> pps = new List <Package_Product_Suppliers>(); SqlConnection con = TravelExpertsDB.GetConnection(); string selectQuery = "SELECT pk.PackageID, pk.PkgName, pps.ProductSupplierID, p.ProdName, s.SupName " + "FROM Packages pk " + "INNER JOIN Packages_Products_Suppliers pps " + "ON pps.PackageID = pk.PackageID " + "INNER JOIN Products_Suppliers ps " + "ON pps.ProductSupplierID = ps.ProductSupplierID " + "INNER JOIN Products p " + "ON p.ProductID = ps.ProductID " + "INNER JOIN Suppliers s " + "ON s.SupplierID = ps.SupplierID " + "WHERE pk.PackageID = @PackageID"; // QUADRUPLE INNER JOIN -- This monster gets the Product Supplier by package ID SqlCommand selectCommand = new SqlCommand(selectQuery, con); selectCommand.Parameters.AddWithValue("@PackageID", pkgID); // bind the data try { con.Open(); // open connection SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Package_Product_Suppliers pps_o = new Package_Product_Suppliers(); pps_o.PackageID = Convert.ToInt32(reader["PackageID"]); pps_o.PkgName = (string)reader["PkgName"]; pps_o.ProductSupplierID = Convert.ToInt32(reader["ProductSupplierID"]); pps_o.ProdName = (string)reader["ProdName"]; pps_o.SupName = (string)reader["SupName"]; // Bind the data to relevant name pps.Add(pps_o); // add to list } } catch (SqlException ex) { throw ex; } finally { con.Close(); // close connection } return(pps); } }
public static bool UpdatePacakge(Packages oldPackage, Packages newPackage) { SqlConnection con = TravelExpertsDB.GetConnection();//Create a connection //Create a query for inserting a supplier into the suppliers table string updateQuery = "UPDATE Packages " + "SET PkgName=@newPkgName, PkgStartDate=@newPkgStartDate, PkgEndDate=@newPkgEndDate, " + "PkgDesc=@newPkgDesc, PkgBasePrice=@newPkgBasePrice, PkgAgencyCommission=@newPkgAgencyCommission " + "WHERE PackageID = @oldPackageID AND PkgName=@oldPkgName AND PkgStartDate=@oldPkgStartDate " + "AND PkgEndDate=@OldPkgEndDate AND PkgDesc=@oldPkgDesc AND PkgBasePrice=@oldPkgBasePrice " + "And PkgAgencyCommission=@oldPkgAgencyCommission"; SqlCommand updateCommand = new SqlCommand(updateQuery, con);//Create a insert command using SqlCommand updateCommand.Parameters.AddWithValue("@newPkgName", newPackage.PkgName); updateCommand.Parameters.AddWithValue("@newPkgStartDate", newPackage.PkgStartDate); updateCommand.Parameters.AddWithValue("@newPkgEndDate", newPackage.PkgEndDate); updateCommand.Parameters.AddWithValue("@newPkgDesc", newPackage.PkgDesc); updateCommand.Parameters.AddWithValue("@newPkgBasePrice", newPackage.PkgBasePrice); updateCommand.Parameters.AddWithValue("@newPkgAgencyCommission", newPackage.PkgAgencyCommission); updateCommand.Parameters.AddWithValue("@oldPackageID", oldPackage.PackageID); updateCommand.Parameters.AddWithValue("@oldPkgName", oldPackage.PkgName); updateCommand.Parameters.AddWithValue("@oldPkgStartDate", oldPackage.PkgStartDate); updateCommand.Parameters.AddWithValue("@OldPkgEndDate", oldPackage.PkgEndDate); updateCommand.Parameters.AddWithValue("@oldPkgDesc", oldPackage.PkgDesc); updateCommand.Parameters.AddWithValue("@oldPkgBasePrice", oldPackage.PkgBasePrice); updateCommand.Parameters.AddWithValue("@oldPkgAgencyCommission", oldPackage.PkgAgencyCommission); try { con.Open(); int count = updateCommand.ExecuteNonQuery(); //for DML statements if (count > 0) { return(true); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(false); }
//This method is used to get the suppliers for a certain productId. It returns a List of suppliers public static List <Suppliers> GetSupForProd(int productId) { List <Suppliers> supplierList = new List <Suppliers>(); //create a list of suppliers Suppliers supplier = null; //create a supplier and set it to null SqlConnection con = TravelExpertsDB.GetConnection(); //Create a connection //Create a query that selects all the supplier ID's from the product suppliers where the product ID is given string selectQuery = "SELECT SupplierId " + "FROM Products_Suppliers " + "WHERE ProductId = @ProductId"; SqlCommand selectCommand = new SqlCommand(selectQuery, con); //Create a selectCommand using SqlCommand selectCommand.Parameters.AddWithValue("@ProductId", productId); //Bind the given productId to @ProductId try { con.Open(); //Open connection SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) //read the supplierId's if they exist { supplier = new Suppliers(); //create a new supplier supplier.SupplierId = (int)reader["SupplierId"]; //Add the Id to the supplier supplierList.Add(supplier); //Add the supplier to the list of suppliers } } catch (SqlException ex) { throw ex; } finally { con.Close();//Close the connection } //We now have all the id's for the suppliers but do not have there supplier names //Go through each of the suppliers in the supplier list and find there names foreach (Suppliers sup in supplierList) { //use getSupName which is from the SuppliersDB to find the names of the suppliers and add them to there //corresponding suppliers. sup.SupName = SuppliersDB.getSupName(sup.SupplierId); } return(supplierList);//return the supplier list }
//This method is used to get the Products for a specific supplier. It returns a list of products. public static List <Products> GetProdForSup(int supplierId) { List <Products> productsList = new List <Products>(); //create a new productsList Products products = null; //create a null product SqlConnection con = TravelExpertsDB.GetConnection(); //create a connection //Create a query which selects a productId from Products_Suppliers table where the supplierId is given string selectQuery = "SELECT ProductId " + "FROM Products_Suppliers " + "WHERE SupplierId = @SupplierId"; SqlCommand selectCommand = new SqlCommand(selectQuery, con); //create a select command using SqlCommand selectCommand.Parameters.AddWithValue("@SupplierId", supplierId); //Bind the supplierId to the @supplierId try { con.Open(); //Open the connection SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the query and store the result in reader while (reader.Read()) //read the products if it exists { products = new Products(); //create new product products.ProductId = (int)reader["ProductId"]; //add productId to product productsList.Add(products); //add product to product list } } catch (SqlException ex) { throw ex; } finally { con.Close();//close connection } //Right now all the products only have productIds and no product name. Run through each one and add product Name foreach (Products prod in productsList) { //Use the ProductsDB.getProdName to get the prod name using productId prod.ProdName = ProductsDB.getProdName(prod.ProductId); } return(productsList);//Return the product list }
public static List <Packages> GetPackages() { List <Packages> pkgList = new List <Packages>(); // start with new list Packages packages = null; // set new packages object to null SqlConnection con = TravelExpertsDB.GetConnection(); // get connection string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages"; // select query to retrieve all relevant data from Packages SqlCommand selectCommand = new SqlCommand(selectQuery, con); try { con.Open(); // open connection SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { packages = new Packages(); // new packages object packages.PackageID = Convert.ToInt32(reader["PackageId"]); packages.PkgName = (string)reader["PkgName"]; packages.PkgStartDate = reader["PkgStartDate"] as DateTime?; packages.PkgEndDate = reader["PkgEndDate"] as DateTime?; packages.PkgDesc = (string)reader["PkgDesc"]; packages.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]); packages.PkgAgencyCommission = Convert.ToDecimal(reader["PkgAgencyCommission"]); // DATA BOUND TO RELATED NAME pkgList.Add(packages); // add data from packages object to the list } } catch (SqlException ex) { throw ex; // if there is an error, throw exception } finally { con.Close(); // close connection } return(pkgList); }
// Grabs all the supplier Id that is not included in the products public static List <Suppliers> GetNotInSuppliersID(int prodId) { List <Suppliers> notIn = new List <Suppliers>(); Suppliers supp; SqlConnection con = TravelExpertsDB.GetConnection(); //Create a query that selects the productId from the products where product name is given string selectQuery = "SELECT DISTINCT SupplierId " + "FROM Products_Suppliers " + "WHERE SupplierId not in (SELECT SupplierId from Products_Suppliers WHERE ProductId = @ProductId)"; SqlCommand selectCommand = new SqlCommand(selectQuery, con); selectCommand.Parameters.AddWithValue("@ProductId", prodId); try { con.Open(); //open the connection SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the query and store it in reader while (reader.Read()) //read the products if they exist { //create new product and add properties to them supp = new Suppliers(); supp.SupplierId = (int)reader["SupplierId"]; notIn.Add(supp);//Add the product to the list of products } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(notIn);//Return the product ID }
// Deletes Product_supplier ID public static bool DeleteProdSup(int prodID, int supID) { SqlConnection con = TravelExpertsDB.GetConnection(); // Connection to DB string deleteStatement = "DELETE FROM Products_Suppliers " // SQL query + "WHERE ProductId = @ProductId " + "AND SupplierId = @SupplierId"; // Deletes with given parameters of product ID and Supplier ID SqlCommand deleteCommand = new SqlCommand(deleteStatement, con); deleteCommand.Parameters.AddWithValue("@ProductId", prodID); deleteCommand.Parameters.AddWithValue("@SupplierId", supID); try { con.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
public static Packages GetPackageDetails(int packageId) // Get Package Details with PackageID as an argument -- method written by Rene Arreaza { Packages packages = null; // set new packages object to null SqlConnection con = TravelExpertsDB.GetConnection(); // get connection string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages " + "WHERE PackageId =" + packageId; // select query to retrieve all relevant data from Packages SqlCommand selectCommand = new SqlCommand(selectQuery, con); try { con.Open(); // open connection SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { packages = new Packages(); // new packages object packages.PackageID = Convert.ToInt32(reader["PackageId"]); packages.PkgName = (string)reader["PkgName"]; packages.PkgStartDate = reader["PkgStartDate"] as DateTime?; packages.PkgEndDate = reader["PkgEndDate"] as DateTime?; packages.PkgDesc = (string)reader["PkgDesc"]; packages.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]); packages.PkgAgencyCommission = Convert.ToDecimal(reader["PkgAgencyCommission"]); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(packages); }
//This method is used to get all the suppliers from he Suppliers table in the db. It returns a list of Suppliers. public static List <Suppliers> GetSuppliers() { List <Suppliers> supplierList = new List <Suppliers>(); //Create an empty list of suppliers Suppliers supplier = null; //Create a null supplier SqlConnection con = TravelExpertsDB.GetConnection(); //Create a connection to db //Build the query to access the supplierId and SupName from the Suppliers table string selectQuery = "SELECT SupplierId, SupName " + "FROM Suppliers"; //Build the selectCommand by giving SqlCommand the query and the connection to the db SqlCommand selectCommand = new SqlCommand(selectQuery, con); try { con.Open(); //Open the connection SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the select command and store results in reader while (reader.Read()) //Read the suppliers if they still exist { supplier = new Suppliers(); //Create a new supplier for this iteration //Add the supplier properties supplier.SupplierId = (int)reader["SupplierId"]; supplier.SupName = (string)reader["SupName"]; supplierList.Add(supplier);//Add this supplier to the supplier list } } catch (SqlException ex) { throw ex; } finally { con.Close(); //Close the connection } return(supplierList); //Return the supplier list }
public static bool UpdateSupplier(int supplierId, string supName, int oldsupplierId, string oldsupName) { SqlConnection con = TravelExpertsDB.GetConnection();//Create a connection //Create a query for inserting a supplier into the suppliers table string updateQuery = "UPDATE Suppliers " + "SET SupplierId=@SupplierId, SupName=@SupName " + "WHERE SupplierId = @oldsupplierId " + "AND SupName = @oldsupName"; SqlCommand updateCommand = new SqlCommand(updateQuery, con); //Create a insert command using SqlCommand updateCommand.Parameters.AddWithValue("@oldsupplierId", oldsupplierId); //Bind the supplierId name to the @SupplierId updateCommand.Parameters.AddWithValue("@oldsupName", oldsupName); //Bind the supplier name to the @SupName updateCommand.Parameters.AddWithValue("@SupplierId", supplierId); //Bind the supplierId name to the @SupplierId updateCommand.Parameters.AddWithValue("@SupName", supName); //Bind the supplier name to the @SupName try { con.Open(); int count = updateCommand.ExecuteNonQuery(); //for DML statements if (count > 0) { return(true); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(false); }