//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);
}
