public static List <Supplier> GetSupplier(int productID)
{
List <Supplier> suppliers = new List <Supplier>();
Supplier sup = null;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT s.SupplierID, s.SupName, p.ProdName, p.ProductID " +
"FROM Suppliers s " +
"INNER JOIN Products_Suppliers ps " +
"on s.SupplierID = ps.SupplierID " +
"INNER JOIN Products p " +
"on ps.ProductID = p.ProductID " +
"WHERE p.ProductID = @ProductID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@ProductID", productID); // value comes from the method's argument
try
{
//add while loop in list form
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read()) // found a supplier
{
sup = new Supplier();
sup.ProductID = (int)reader["ProductID"];
sup.SupplierID = (int)reader["SupplierID"];
sup.SupName = reader["SupName"].ToString();
sup.ProdName = reader["ProdName"].ToString();
suppliers.Add(sup);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(suppliers);
}
public static List <Supplier> GetAllSuppliers()
{
List <Supplier> suppliers = new List <Supplier>();
Supplier sup = null;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT s.SupplierID, s.SupName, p.ProdName, p.ProductID " +
"FROM Suppliers s " +
"INNER JOIN Products_Suppliers ps " +
"on s.SupplierID = ps.SupplierID " +
"INNER JOIN Products p " +
"on ps.ProductID = p.ProductID ";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // while there are suppliers
{
sup = new Supplier();
sup.ProductID = (int)reader["ProductID"];
sup.SupplierID = (int)reader["SupplierID"];
sup.SupName = reader["SupName"].ToString();
sup.ProdName = reader["ProdName"].ToString();
suppliers.Add(sup);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(suppliers);
}
/// <summary>
/// <author> Brian Appleton</author>
/// Gets all the suppliers attached to a specific product
/// </summary>
/// <param name="productId">the product id</param>
/// <returns></returns>
public static BindingList <Supplier> GetProductSuppliers(int productId)
{
BindingList <Supplier> suppliers = new BindingList <Supplier>();
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT s.SupName,s.SupplierId,pr.ProdName " +
"FROM Products_Suppliers as p " +
"JOIN Suppliers as s " +
"ON s.SupplierId = p.SupplierId " +
"JOIN Products as pr " +
"ON pr.ProductId = p.ProductId " +
"WHERE pr.ProductID = @ProductID";
using (SqlCommand cmd = new SqlCommand(selectStatement, con))
{
cmd.Parameters.AddWithValue("@ProductID", productId);
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read()) // found a customer
{
Supplier supplier = new Supplier()
{
SupName = (string)reader[0],
SupplierID = (int)reader[1],
ProdName = (string)reader[2],
};
suppliers.Add(supplier);
}
return(suppliers);
}
}
public static bool UpdateSupplier(Supplier oldSup, Supplier newSup)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Suppliers " +
"SET SupplierID = @NewSupplierID, " +
" SupName = @NewSupName, " +
"WHERE SupplierID = @OldSupplierID " +
"AND SupName = @OldSupName ";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewSupplierID", newSup.SupplierID);
cmd.Parameters.AddWithValue("@NewSupName", newSup.SupName);
cmd.Parameters.AddWithValue("@OldSupplierID", oldSup.SupplierID);
cmd.Parameters.AddWithValue("@OldSupName", oldSup.SupName);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}