public static List<Supplier> GetSuppliersFromProductId(int ProductId)
// Get the List of suppliers from Suppliers Table for a given ProductId from Table Products
// SupplierId is an int and the PK for Table Package
// returns List type Product, null if no product or exception
// throws SqlException and Exception
// checked Jan 17/16 DS
{
SqlConnection connection = MMATravelExperts.GetConnection();
List<Supplier> ListSupplier = new List<Supplier>();
string selectStatement = "SELECT s.SupplierId, s.SupName FROM Products p, " +
"Products_Suppliers ps, Suppliers s " +
"WHERE p.ProductId=ps.ProductId and ps.SupplierId=s.SupplierId and " +
"p.ProductId=@ProductId";
//SELECT p.ProductId, p.ProdName, s.SupName
// FROM [dbo].[Suppliers] s, [dbo].[Products_Suppliers] ps, [dbo].[Products] p
// WHERE s.SupplierId=ps.SupplierId and p.ProductId=ps.ProductId and s.SupplierId=1713
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", ProductId);
try
{
connection.Open();
SqlDataReader pkgReader = selectCommand.ExecuteReader();
while (pkgReader.Read())
{
Supplier supp = new Supplier();
supp.SupName = Convert.ToString(pkgReader["SupName"]);
supp.SupplierID = (int)pkgReader["SupplierId"];
ListSupplier.Add(supp);
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
return ListSupplier; // return the list of suppliers
}
/// <summary>
/// retrieves each product from the products table
/// </summary>
/// <returns>the complete list of Products</returns>
public static List<Supplier> GetAllSuppliers()
{
List<Supplier> products = new List<Supplier>();
SqlConnection connection = MMATravelExperts.GetConnection();
string selectString = "select * from Suppliers";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Supplier s = new Supplier();
s.SupplierID= Convert.ToInt32(reader["SupplierId"]);
s.SupName = Convert.ToString(reader["SupName"]);
products.Add(s);
}
return products;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool UpdateSupplier(Supplier oldSupp, Supplier newSupp)
{
// Updates the Suppliers Table
// parameter oldSupp ... the old row as an instance of Supplier class
// parameter newSupp ... the new row as an instance of Supplier class
// returns true row updated, false row not updated
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string updateStatement = "UPDATE Suppliers SET SupName=@newSupName " +
"WHERE SupplierId=@oldSupplierId and SupName=@oldSupName";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
// new supplier listing
updateCommand.Parameters.AddWithValue("@newSupplierId", newSupp.SupplierID);
updateCommand.Parameters.AddWithValue("@newSupName", newSupp.SupName);
// old supplier listing
updateCommand.Parameters.AddWithValue("@oldSupplierId", oldSupp.SupplierID);
updateCommand.Parameters.AddWithValue("@oldSupName", oldSupp.SupName);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return true; // rows updated
}
else
{
return false; //rows not updated
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
public static int AddSupplier(Supplier supp)
{
// Add a Supplier to the Suppliers Table
// supp is the instance of Supplier class
// returns the SupplierId of the row inserted or -1 if not added to table
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
String insertStatement = "INSERT INTO Suppliers (SupplierId,SupName) VALUES (@SupplierId, @SupName)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@SupplierId", supp.SupplierID);
insertCommand.Parameters.AddWithValue("@SupName", supp.SupName);
try
{
connection.Open();
int numRows = insertCommand.ExecuteNonQuery();
return numRows;
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
//add a new product or supplier.
private void btnAddProdSup_Click(object sender, EventArgs e)
{
//the user wants to add a product
if (btnEditProdSupp.Text == PRODUCT_MESSAGE)
{
Product newProduct = new Product();
newProduct.ProdName = txtNewName.Text;
TravelExpertsDB.TravelExpertsDB.AddProduct(newProduct);
fillDGVs();
}
//the user wants to add a supplier
if (btnEditProdSupp.Text == SUPPLIER_MESSAGE)
{
Supplier newSupp = new Supplier();
newSupp.SupName = txtNewName.Text;
newSupp.SupplierID = GenerateSupplierId();
TravelExpertsDB.TravelExpertsDB.AddSupplier(newSupp);
fillDGVs();
}
}
//edit the name of a product or supplier
private void btnEditProdSupp_Click(object sender, EventArgs e)
{
//the user wants to edit a product
if (btnEditProdSupp.Text == PRODUCT_MESSAGE)
{
Product newProduct = new Product();
newProduct.ProdName = txtName.Text;
newProduct.ProductId = ActiveProduct.ProductId;
TravelExpertsDB.TravelExpertsDB.UpdateProduct(ActiveProduct,newProduct);
fillDGVs();
}
//the user wants to edit a supplier
if(btnEditProdSupp.Text == SUPPLIER_MESSAGE)
{
Supplier newSupp = new Supplier();
newSupp.SupName = txtName.Text;
newSupp.SupplierID = activeSupplier.SupplierID;
TravelExpertsDB.TravelExpertsDB.UpdateSupplier(activeSupplier,newSupp);
fillDGVs();
}
}