//Author: Joel---------------------------------------------------------------------
public static ProductSupplier GetProductSupplier(int productID, int supplierID)
{
ProductSupplier prodSup = null;
SqlConnection con = TravelExpertsDB.GetConnection();
string SelectSmt = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductId = @pID AND SupplierId = @sID " +
"ORDER BY ProductSupplierId";
SqlCommand selectCmd = new SqlCommand(SelectSmt, con);
selectCmd.Parameters.AddWithValue("@pID", productID);
selectCmd.Parameters.AddWithValue("@sID", supplierID);
try
{
con.Open();
SqlDataReader dr = selectCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
if (dr.Read())
{
prodSup = new ProductSupplier();
prodSup.ProductSupplierId = (int)dr["ProductSupplierId"];
prodSup.ProductId = (int)dr["ProductId"];
prodSup.SupplierId = (int)dr["SupplierId"];
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(prodSup);
}
//Author: Lindsay---------------------------------------------------------------------
/// <summary>
/// Adds a record to the Products_Suppliers table in Travel Experts database
/// </summary>
/// <param name="supProd"> ProductSupplier object that containg data for the new record</param>
/// <returns>generated productSupplierId</returns>
public static int AddSupProd(ProductSupplier supProd)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertSmt = "INSERT INTO Products_Suppliers (SupplierId, ProductId) " +
"VALUES(@SupplierId, @ProductId)";
SqlCommand cmd = new SqlCommand(insertSmt, con);
cmd.Parameters.AddWithValue("@SupplierId", supProd.SupplierId);
cmd.Parameters.AddWithValue("@ProductId", supProd.ProductId);
try
{
con.Open();
cmd.ExecuteNonQuery(); // run the insert command
//get the generated ID - current identity value of Products_Suppliers table
string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') From Products_Suppliers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int productSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar());
return(productSupplierId);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
//Author: Lindsay-----------------------------------------------------
//the method of getting all the suppliers data from the database
public static List <ProductSupplier> GetProductSuppliers()
{
List <ProductSupplier> prodSups = new List <ProductSupplier>();
ProductSupplier prodSup;
SqlConnection con = TravelExpertsDB.GetConnection();
string SelectSmt = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"ORDER BY ProductSupplierId";
SqlCommand selectCmd = new SqlCommand(SelectSmt, con);
try
{
con.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
while (dr.Read())
{
prodSup = new ProductSupplier();
prodSup.ProductSupplierId = (int)dr["ProductSupplierId"];
prodSup.ProductId = (int)dr["ProductId"];
prodSup.SupplierId = (int)dr["SupplierId"];
prodSups.Add(prodSup);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(prodSups);
}
//Author: Lindsay-----------------------------------------------------------------------------
/// <summary>
/// Delete a record to the Products_Suppliers table in Travel Experts database
/// </summary>
/// <param name="supProd">ProductSupplier object that containg data for the new record</param>
/// <returns>generated productSupplierId</returns>
public static bool DeleteSupProd(ProductSupplier supProd)
{
SqlConnection con = TravelExpertsDB.GetConnection();
String deleteSmt = "DELETE FROM Products_Suppliers " +
"WHERE SupplierId = @SupplierId " +
"And ProductId = @ProductId";
SqlCommand cmd = new SqlCommand(deleteSmt, con);
cmd.Parameters.AddWithValue("@SupplierId", supProd.SupplierId);
cmd.Parameters.AddWithValue("@ProductId", supProd.ProductId);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <NamedPackageProductSupplier> GetPackageProductSuppliersByPackage(int pkgID)
{
List <NamedPackageProductSupplier> productSuppliers = new List <NamedPackageProductSupplier>();
SqlConnection conn = TravelExpertsDB.GetConnection();
string sqlQuery = "SELECT pkgps.PackageId as pkgID, ps.ProductSupplierId as psID, ProdName, SupName " +
"FROM Products p " +
"INNER JOIN Products_Suppliers ps ON p.ProductId = ps.ProductId " +
"INNER JOIN Suppliers s ON s.SupplierId = ps.SupplierId " +
"INNER JOIN Packages_Products_Suppliers pkgps ON pkgps.ProductSupplierId = ps.ProductSupplierId " +
"WHERE pkgps.PackageID = @PackageID " +
"ORDER BY pkgps.ProductSupplierId";
//string sqlQuery = "SELECT PackageID, ProductSupplierID FROM Packages_Products_Suppliers "+
// "WHERE PackageID = @PackageID ORDER BY ProductSupplierID";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
cmd.Parameters.AddWithValue("@PackageId", pkgID);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
NamedPackageProductSupplier prodSup = new NamedPackageProductSupplier
{
PackageID = (int)reader["pkgID"],
ProdSuppID = (int)reader["psID"],
ProductName = reader["ProdName"].ToString(),
SupplierName = reader["SupName"].ToString()
};
productSuppliers.Add(prodSup);
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(productSuppliers);
}
public static int Delete(TravelPackage package)
{
//Delete all related Package_Product_Suppliers
foreach (PackageProdSupplier pps in package.ProductsAndSuppliers)
{
if (PackageProdSuppDB.Delete(pps) < 1)
{
throw new Exception("Error deleting Package Product/Supplier");
}
}
int deletedPkgs = 0;
SqlConnection conn = TravelExpertsDB.GetConnection();
string sqlQuery = "DELETE FROM Packages " +
"WHERE PackageId = @pkgID AND PkgName = @Name " +
"AND PkgStartDate = @start AND PkgEndDate = @end " +
"AND PkgDesc = @desc AND PkgBasePrice = @price " +
"AND PkgAgencyCommission = @commission";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
//TODO: Account for null values
cmd.Parameters.AddWithValue("@pkgID", package.ID);
cmd.Parameters.AddWithValue("@name", package.Name);
// cmd.Parameters.AddWithValue("@start", package.StartDate); //Nullable
cmd.Parameters.AddWithValue("@start", package.StartDate == null ? (object)DBNull.Value : package.StartDate);
cmd.Parameters.AddWithValue("@end", package.EndDate); //Nullable
cmd.Parameters.AddWithValue("@desc", package.Description ?? (object)DBNull.Value); //Nullable
cmd.Parameters.AddWithValue("@price", package.BasePrice);
cmd.Parameters.AddWithValue("@commission", package.Commission ?? (object)DBNull.Value); //Nullable
try
{
conn.Open();
deletedPkgs += cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(deletedPkgs);
}
public static void Update(TravelPackage oldPkg, TravelPackage newPkg)
{
if (oldPkg.ID != newPkg.ID)
{
throw new ArgumentException("ID mismatch between old package and new package");
}
SqlConnection conn = TravelExpertsDB.GetConnection();
string updStmt = "UPDATE Packages " +
"SET PkgName = @name, " +
"PkgStartDate = @start, " +
"PkgEndDate = @end, " +
"PkgDesc = @desc, " +
"PkgBasePrice = @price, " +
"PkgAgencyCommission = @commiss " +
"WHERE PackageId = @id";
SqlCommand cmd = new SqlCommand(updStmt, conn);
cmd.Parameters.AddWithValue("@name", newPkg.Name);
cmd.Parameters.AddWithValue("@desc", newPkg.Description ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@start", newPkg.StartDate);
cmd.Parameters.AddWithValue("@end", newPkg.EndDate);
cmd.Parameters.AddWithValue("@price", newPkg.BasePrice);
cmd.Parameters.AddWithValue("@commiss", newPkg.Commission ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@id", oldPkg.ID);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error while updating database: " + ex.Message);
}
finally
{
conn.Close();
}
}
public static List <Product> GetProducts()
{
List <Product> products = new List <Product>();
Product product;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT ProductId, ProdName " +
"FROM Products ORDER BY ProductId";
SqlCommand selectCmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
while (dr.Read())
{
product = new Product();
product.ProductId = (int)dr["ProductId"];
product.ProdName = dr["ProdName"].ToString();
products.Add(product);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(products);
}
/// <summary>
/// Adds a new supplier to the Suppliers table in Travel Experts database
/// </summary>
/// <param name="sup"> Supplier object that containg data for the new record</param>
/// <returns>generated SupplierID</returns>
public static void AddSupplier(Supplier sup)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertSmt = "INSERT INTO Suppliers (Supplierid, SupName) " +
"VALUES(@SupplierId, @SupName)";
SqlCommand cmd = new SqlCommand(insertSmt, con);
cmd.Parameters.AddWithValue("@SupplierId", sup.SupplierId);
cmd.Parameters.AddWithValue("@SupName", sup.SupName);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// Updates existing product record
/// </summary>
/// <param name="oldProd">data before update</param>
/// <param name="newProd">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateProduct(Product oldProd, Product newProd)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Products " +
"SET ProdName = @NewProdName " +
"WHERE ProductId = @OldProductId " +
"AND ProdName = @OldProdName";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewProdName", newProd.ProdName);
cmd.Parameters.AddWithValue("@OldProductId", oldProd.ProductId);
cmd.Parameters.AddWithValue("@OldProdName", oldProd.ProdName);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
MessageBox.Show("Product Name updated successfully !");
return(true);
}
else
{
MessageBox.Show("Product Name is not updated successfully !");
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static TravelPackage Insert(TravelPackage newPkg)
{
SqlConnection conn = TravelExpertsDB.GetConnection();
string columns = "PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission";
string values = "@name, @start, @end, @desc, @price, @commiss";
string insStmt = "INSERT INTO Packages (" + columns + ") VALUES (" + values + ")";
SqlCommand cmd = new SqlCommand(insStmt, conn);
cmd.Parameters.AddWithValue("@name", newPkg.Name);
cmd.Parameters.AddWithValue("@start", newPkg.StartDate);
cmd.Parameters.AddWithValue("@end", newPkg.EndDate);
cmd.Parameters.AddWithValue("@desc", newPkg.Description ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@price", newPkg.BasePrice);
cmd.Parameters.AddWithValue("@commiss", newPkg.Commission ?? (object)DBNull.Value);
try
{
conn.Open();
cmd.ExecuteNonQuery();
//Retreive package ID
string selStmt = "SELECT IDENT_CURRENT('Packages') FROM Packages";
SqlCommand selCmd = new SqlCommand(selStmt, conn);
newPkg.ID = Convert.ToInt32(selCmd.ExecuteScalar());
}
catch (Exception ex)
{
MessageBox.Show("Error while inserting into database: " + ex.Message);
newPkg.ID = -1;
}
finally
{
conn.Close();
}
return(newPkg);
}
//getting all the products not supplieried by the supplier
public static List <Product> GetProdsUnsuppliedBySup(int supplierId)
{
List <Product> unsuppliedProds = new List <Product>();
Product unsupplied;
SqlConnection con = TravelExpertsDB.GetConnection();
string SelectSmt = "SELECT ProductId, ProdName FROM Products " +
"WHERE ProductId NOT IN " +
"(SELECT ProductId FROM Products_Suppliers " +
"WHERE SupplierId = @SupplierId) " +
"ORDER BY ProductId";
SqlCommand selectCmd = new SqlCommand(SelectSmt, con);
selectCmd.Parameters.AddWithValue("@SupplierId", supplierId);
try
{
con.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
while (dr.Read())
{
unsupplied = new Product();
unsupplied.ProductId = (int)dr["ProductId"];
unsupplied.ProdName = dr["prodName"].ToString();
unsuppliedProds.Add(unsupplied);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(unsuppliedProds);
}
//getting all the products supplieried by the supplier
public static List <Product> GetProductsBySupplier(int supplierId)
{
List <Product> SuppliedProds = new List <Product>();
Product suppliedProd;
SqlConnection con = TravelExpertsDB.GetConnection();
string SelectSmt = "SELECT SupplierId, p.ProductId, p.ProdName " +
"FROM Products_Suppliers ps JOIN Products p " +
"ON ps.ProductId = p.ProductId " +
"WHERE SupplierId = @SupplierId " +
"ORDER BY p.ProductId";
SqlCommand selectCmd = new SqlCommand(SelectSmt, con);
selectCmd.Parameters.AddWithValue("@SupplierId", supplierId);
try
{
con.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
while (dr.Read())//while there is a product
{
suppliedProd = new Product();
suppliedProd.ProductId = (int)dr["ProductId"];
suppliedProd.ProdName = dr["prodName"].ToString();
SuppliedProds.Add(suppliedProd);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(SuppliedProds);
}
