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