public static List<Product> GetProductsFromSupplierId(int SupplierId)
// Get the List of products from Procts Table for a given SupplierId from Table Suppliers
// 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<Product> products = new List<Product>();
string selectStatement = "SELECT p.ProductID, p.ProdName FROM Products p, " +
"Products_Suppliers ps, Suppliers s " +
"WHERE p.ProductId=ps.ProductId and ps.SupplierId=s.SupplierId and " +
"s.SupplierId=@SupplierId";
//SELECT s.SupplierId, s.SupName FROM [dbo].[Suppliers] s, [dbo].[Products_Suppliers] ps, [dbo].[Products] p
//WHERE s.SupplierId=ps.SupplierId and p.ProductId=ps.ProductId and p.ProductId=2
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@SupplierId", SupplierId);
try
{
connection.Open();
SqlDataReader pkgReader = selectCommand.ExecuteReader();
while (pkgReader.Read())
{
Product prod = new Product();
prod.ProdName = Convert.ToString(pkgReader["ProdName"]);
prod.ProductId = (int)pkgReader["ProductId"];
products.Add(prod);
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
return products; // return the list of products
}
// select the list of products for each package given the PackageId from table Packages
public static List<Product> GetProductsFromPackageId(int PackageId)
// Get the Lsit of products from Products Table for a given PackageID from Table Packages
// PackageId is an int and the PK for Table Package
// returns List type Package, null if no package or exceptio
// throws SqlException and Exception
// checked Jan 16 DS
{
SqlConnection connection = MMATravelExperts.GetConnection();
List<Product> ListProducts = new List<Product>();
string selectStatement = "SELECT p.ProductID, p.ProdName FROM Packages pack, " +
"Packages_Products_Suppliers pps, Products_Suppliers ps, Products p " +
"WHERE pack.PackageId=pps.PackageId and pps.ProductSupplierId=ps.ProductSupplierId and " +
"p.ProductId=ps.ProductId and pack.PackageId=@PackageId";
//SELECT p.ProductID, p.ProdName FROM Packages pack,
// Packages_Products_Suppliers pps, Products_Suppliers ps, Products p
// WHERE pack.PackageId=pps.PackageId and pps.ProductSupplierId=ps.ProductSupplierId and
// p.ProductId=ps.ProductId and pack.PackageId=1
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", PackageId);
try
{
connection.Open();
SqlDataReader pkgReader = selectCommand.ExecuteReader();
while (pkgReader.Read())
{
Product prod = new Product();
prod.ProdName = Convert.ToString(pkgReader["ProdName"]);
prod.ProductId = (int)pkgReader["ProductId"];
ListProducts.Add(prod);
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
return ListProducts; // return the list of products
}
/// <summary>
/// retrieves each product from the products table
/// </summary>
/// <returns>the complete list of Products</returns>
public static List<Product> GetAllProducts()
{
List<Product> products = new List<Product>();
SqlConnection connection = MMATravelExperts.GetConnection();
string selectString = "select * from products";
SqlCommand selectCommand = new SqlCommand(selectString,connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Product p = new Product();
p.ProductId = Convert.ToInt32(reader["ProductId"]);
p.ProdName = Convert.ToString(reader["ProdName"]);
products.Add(p);
}
return products;
}
catch(SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool UpdateProduct(Product oldProd,Product newProd)
{
// Updates the Products Table
// parameter oldProd ... the old row as an instance of Product class
// parameter newProd ... the new row as an instance of Product class
// returns true row updated, false row not updated
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string updateStatement="UPDATE Products SET ProdName=@newProdName "+
"WHERE ProductId=@oldProductId and ProdName=@oldProdName";
SqlCommand updateCommand = new SqlCommand(updateStatement,connection);
// new product listing
updateCommand.Parameters.AddWithValue("@newProductId",newProd.ProductId);
updateCommand.Parameters.AddWithValue("@newProdName",newProd.ProdName);
// old product listing
updateCommand.Parameters.AddWithValue("@oldProductId",oldProd.ProductId);
updateCommand.Parameters.AddWithValue("@oldProdName",oldProd.ProdName);
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();
}
}
/// <summary>
/// Add a product to the Products table
/// bugfix E.L.
/// </summary>
/// <param name="prod">product instance</param>
/// <returns>ProductId</returns>
public static int AddProduct(Product prod)
{
// Add a Product to the products Table
// prod is the instance of Product class
// returns the ProductId of the row inserted or -1 if not added to table
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string insertStatement = "INSERT INTO Products (ProdName) VALUES (@ProdName)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@ProdName", prod.ProdName);
try
{
connection.Open();
int numRows = insertCommand.ExecuteNonQuery();
if (numRows>0)
{
string selectStatement="SELECT ProductId FROM Products where ProdName=@ProdName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProdName", prod.ProdName);
int prodId = (int)(selectCommand.ExecuteScalar());
return prodId;
}
else
{
return -1;
}
}
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();
}
}