/// <summary>
/// Retrieves each Product Supplier in a list
/// </summary>
/// <returns></returns>
public static List <Product_Supplier> getAllProductSuppliers()
{
SqlConnection connection = MMATravelExperts.GetConnection();
string selectStatement = "select ProdName, SupName, Products.ProductId, Suppliers.SupplierId, ProductSupplierId from Products_Suppliers, Products, Suppliers " +
"where Products.ProductId=Products_Suppliers.ProductId AND Suppliers.SupplierId=Products_Suppliers.SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
List <Product_Supplier> productSuppliersList = new List <Product_Supplier>();
while (reader.Read())
{
Product_Supplier myPS = new Product_Supplier();
myPS.ProductSupplierId = (int)reader["ProductSupplierId"];
myPS.ProductName = reader["ProdName"].ToString();
myPS.ProductId = (int)reader["ProductID"];
myPS.SupName = reader["SupName"].ToString();
myPS.SupplierId = (int)reader["SupplierID"];
productSuppliersList.Add(myPS);
}
return(productSuppliersList);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Adds the input product supplier to the input package with a new row inserted into the packages_products_suppliers table
/// </summary>
/// <param name="ps">the product-supplier to add</param>
/// <param name="pack">the package to add to</param>
/// <returns>true if successful, false if fail</returns>
public static bool addProductSupplierToPackage(Product_Supplier ps, Package pack)
{
SqlConnection connection = MMATravelExperts.GetConnection();
string insertStatement = "Insert into Packages_Products_Suppliers values (@packageId,@productSupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PackageId", pack.PackageId);
insertCommand.Parameters.AddWithValue("@productSupplierId", ps.ProductSupplierId);
try
{
connection.Open();
int result = insertCommand.ExecuteNonQuery();
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//get the product supplier selected by the user.
private void dgvProdSups_SelectionChanged(object sender, EventArgs e)
{
if (dgvProdSups.SelectedRows.Count != 0)
{
selectedProductSupplier = (Product_Supplier)dgvProdSups.SelectedRows[0].DataBoundItem;
}
}
public static bool UpdateProduct_Supplier(Product_Supplier oldSupp, Product_Supplier newSupp)
{
// Updates the Product_Suppliers Table
// parameter oldProdSupp ... the old row as an instance of Product_Supplier class
// parameter newProdSupp ... the new row as an instance of Product_Supplier class
// returns true row updated, false row not updated
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string updateStatement = "UPDATE Products_Suppliers SET ProductSupplierId=@newProductSupplierId, ProductId=@newSupplierId, " +
"SupplierID=@newSupplierId WHERE " +
"ProductSupplierId=@oldProductSupplierId and ProductId=@newProductId and SupplierId=@oldSupplierId";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
// new products_supplier listing
updateCommand.Parameters.AddWithValue("@newProductSupplierId", newSupp.ProductSupplierId);
updateCommand.Parameters.AddWithValue("@newProductId", newSupp.ProductId);
updateCommand.Parameters.AddWithValue("@newSupplierId", newSupp.SupplierId);
// old products_supplier listing
updateCommand.Parameters.AddWithValue("@oldProductSupplierId", oldSupp.ProductSupplierId);
updateCommand.Parameters.AddWithValue("@newProductId", oldSupp.ProductId);
updateCommand.Parameters.AddWithValue("@oldSupplierId", oldSupp.SupplierId);
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 AddProduct_Supplier(Product_Supplier prodSupp)
{
// Add a Product_Supplier instance to the Product_Suppliers Table
// ProdSupp is the instance of Product_Supplier class
// returns the ProductSupplierId (pk) of the row inserted or -1 if not added to table
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
String insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierID) " +
"VALUES (@ProductId, @SupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@ProductId", prodSupp.ProductId);
insertCommand.Parameters.AddWithValue("@SupplierId", prodSupp.SupplierId);
try
{
connection.Open();
int numRows = insertCommand.ExecuteNonQuery();
if (numRows > 0)
{
string selectStatement = "SELECT ProductSupplierId FROM Products_Suppliers where ProductId=@ProductId and SupplierID=@SupplierID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", prodSupp.ProductId);
selectCommand.Parameters.AddWithValue("@SupplierID", prodSupp.SupplierId);
int suppId = (int)(selectCommand.ExecuteScalar());
return(suppId);
}
else
{
return(-1);
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Retrieves each Product Supplier in a list
/// </summary>
/// <returns></returns>
public static List<Product_Supplier> getAllProductSuppliers()
{
SqlConnection connection = MMATravelExperts.GetConnection();
string selectStatement = "select ProdName, SupName, Products.ProductId, Suppliers.SupplierId, ProductSupplierId from Products_Suppliers, Products, Suppliers "+
"where Products.ProductId=Products_Suppliers.ProductId AND Suppliers.SupplierId=Products_Suppliers.SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
List<Product_Supplier> productSuppliersList = new List<Product_Supplier>();
while (reader.Read())
{
Product_Supplier myPS = new Product_Supplier();
myPS.ProductSupplierId = (int)reader["ProductSupplierId"];
myPS.ProductName = reader["ProdName"].ToString();
myPS.ProductId = (int)reader["ProductID"];
myPS.SupName = reader["SupName"].ToString();
myPS.SupplierId = (int)reader["SupplierID"];
productSuppliersList.Add(myPS);
}
return productSuppliersList;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool UpdateProduct_Supplier(Product_Supplier oldSupp, Product_Supplier newSupp)
{
// Updates the Product_Suppliers Table
// parameter oldProdSupp ... the old row as an instance of Product_Supplier class
// parameter newProdSupp ... the new row as an instance of Product_Supplier class
// returns true row updated, false row not updated
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string updateStatement = "UPDATE Products_Suppliers SET ProductSupplierId=@newProductSupplierId, ProductId=@newSupplierId, "+
"SupplierID=@newSupplierId WHERE "+
"ProductSupplierId=@oldProductSupplierId and ProductId=@newProductId and SupplierId=@oldSupplierId";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
// new products_supplier listing
updateCommand.Parameters.AddWithValue("@newProductSupplierId", newSupp.ProductSupplierId);
updateCommand.Parameters.AddWithValue("@newProductId", newSupp.ProductId);
updateCommand.Parameters.AddWithValue("@newSupplierId", newSupp.SupplierId);
// old products_supplier listing
updateCommand.Parameters.AddWithValue("@oldProductSupplierId", oldSupp.ProductSupplierId);
updateCommand.Parameters.AddWithValue("@newProductId", oldSupp.ProductId);
updateCommand.Parameters.AddWithValue("@oldSupplierId", oldSupp.SupplierId);
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 AddProduct_Supplier(Product_Supplier prodSupp)
{
// Add a Product_Supplier instance to the Product_Suppliers Table
// ProdSupp is the instance of Product_Supplier class
// returns the ProductSupplierId (pk) of the row inserted or -1 if not added to table
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
String insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierID) " +
"VALUES (@ProductId, @SupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@ProductId", prodSupp.ProductId);
insertCommand.Parameters.AddWithValue("@SupplierId", prodSupp.SupplierId);
try
{
connection.Open();
int numRows = insertCommand.ExecuteNonQuery();
if (numRows > 0)
{
string selectStatement = "SELECT ProductSupplierId FROM Products_Suppliers where ProductId=@ProductId and SupplierID=@SupplierID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId",prodSupp.ProductId);
selectCommand.Parameters.AddWithValue("@SupplierID",prodSupp.SupplierId);
int suppId = (int)(selectCommand.ExecuteScalar());
return suppId;
}
else
{
return -1;
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Adds the input product supplier to the input package with a new row inserted into the packages_products_suppliers table
/// </summary>
/// <param name="ps">the product-supplier to add</param>
/// <param name="pack">the package to add to</param>
/// <returns>true if successful, false if fail</returns>
public static bool addProductSupplierToPackage(Product_Supplier ps, Package pack)
{
SqlConnection connection = MMATravelExperts.GetConnection();
string insertStatement = "Insert into Packages_Products_Suppliers values (@packageId,@productSupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PackageId", pack.PackageId);
insertCommand.Parameters.AddWithValue("@productSupplierId", ps.ProductSupplierId);
try
{
connection.Open();
int result = insertCommand.ExecuteNonQuery();
if (result == 1)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//checks if the product supplier the user wants to add exists.
public bool ProdSupDoesNotExist(Product_Supplier newPS)
{
var prodSups = productSuppliers.Where<Product_Supplier>(x => x.ProductName == newPS.ProductName && x.SupName == newPS.SupName);
if (prodSups.Count() == 0)
{
return true;
}
return false;
}
// creates a Product_Supplier obbject from the selected indices of each datagrid
private Product_Supplier createProductSupplier()
{
Product_Supplier newProdSup = new Product_Supplier();
newProdSup.ProductId = ActiveProduct.ProductId;
newProdSup.ProductName = ActiveProduct.ProdName;
newProdSup.SupName = activeSupplier.SupName;
newProdSup.SupplierId = activeSupplier.SupplierID;
return newProdSup;
}
private void cbProductSuppliers_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbProductSuppliers.SelectedItem != null)
{
selectedProductSupplier = (Product_Supplier)cbProductSuppliers.SelectedItem;
txtSearch.Text = (selectedProductSupplier.ToString() == null) ? "" : selectedProductSupplier.ToString();
if (cbProductSuppliers.DroppedDown != false)
{
cbProductSuppliers.DroppedDown = false;
}
}
}