/// <summary>
/// Adds a new customer to the Customers table in MMABooks database
/// </summary>
/// <param name="cust"> Customer object that cotaing data for the new record</param>
/// <returns>generated CustomerID</returns>
public static int AddProduct(Product prod)
{
SqlConnection con = TravelExpertsConnection.GetConnection();
string insertStatement = "INSERT INTO Products (ProdName) " +
"VALUES(@ProdName)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
try
{
con.Open();
cmd.ExecuteNonQuery(); // run the insert command
// get the generated ID - current identity value for Customers table
string selectQuery = "SELECT IDENT_CURRENT('Products') FROM Products";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int productID = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
// typecase (int) does NOT work!
return(productID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// <author>Stephen Sander</author>
/// Get all suppliers by themselves
/// </summary>
/// <returns></returns>
public static List <Supplier> GetAllSupplierIDAndName()
{
List <Supplier> suppliers = new List <Supplier>();
Supplier sup = null;
SqlConnection con = TravelExpertsConnection.GetConnection();
string selectStatement = "SELECT SupName, SupplierId " +
"FROM Suppliers " +
"ORDER BY SupName";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // while there are suppliers
{
sup = new Supplier();
sup.SupName = reader["SupName"].ToString();
sup.SupplierID = (int)reader["SupplierId"];
suppliers.Add(sup);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(suppliers);
}
public static bool DeleteProduct(Product prod)
{
SqlConnection con = TravelExpertsConnection.GetConnection();
string deleteStatement = "DELETE FROM Products " +
"WHERE ProductID = @ProductID " + // to identify the customer to be deleted
"AND ProdName = @ProdNAme "; // remaining conditions - to ensure optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatement, con);
cmd.Parameters.AddWithValue("@ProductID", prod.ProductID);
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// <author>Brian Appleton</author>
/// Gets a product supplier from a product id and supplier id
/// </summary>
/// <returns></returns>
public static ProductSupplier GetProductSupplier(int productId, int supplierId)
{
ProductSupplier productSupplier = null;
SqlConnection con = TravelExpertsConnection.GetConnection();
string selectStatement = "SELECT ProductSupplierID, ProductID, SupplierID " +
"FROM Products_Suppliers " +
"WHERE ProductID = @ProductID AND " +
"SupplierID = @SupplierID";
using (SqlCommand cmd = new SqlCommand(selectStatement, con))
{
cmd.Parameters.AddWithValue("@ProductID", productId);
cmd.Parameters.AddWithValue("@SupplierID", supplierId);
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) // found a customer
{
productSupplier = new ProductSupplier
{
ProductSupplierID = (int)reader["ProductSupplierID"],
ProductID = (int)reader["ProductID"],
SupplierID = (int)reader["SupplierID"]
};
}
con.Close();
}
return(productSupplier);
}
public static List <Product> GetAllProducts()
{
List <Product> products = new List <Product>();
Product prod = null;
SqlConnection con = TravelExpertsConnection.GetConnection();
string selectStatement = "SELECT ProductID, ProdName " +
"FROM Products " +
"ORDER BY ProductID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // while there are customers
{
prod = new Product();
prod.ProductID = (int)reader["ProductID"];
prod.ProdName = reader["ProdName"].ToString();
products.Add(prod);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(products);
}
/// <summary>
/// Updates existing customer record
/// </summary>
/// <param name="oldCust">data before update</param>
/// <param name="newCust">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateProduct(Product oldProd, Product newProd)
{
SqlConnection con = TravelExpertsConnection.GetConnection();
string updateStatement = "UPDATE Products " +
"SET ProdName = @NewProdName " +
"WHERE ProductID = @OldProductID ";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewProductID", newProd.ProductID);
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)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
//retrieve the suppliers by supplierid through the query
public static List <Supplier> GetSupplierByID(int supplierID)
{
List <Supplier> suppliers = new List <Supplier>(); //empty list
Supplier supp; //aux for reading
using (SqlConnection connection = TravelExpertsConnection.GetConnection())
{
string query = "SELECT Supplierid, SupName " +
"FROM Suppliers " +
"WHERE Supplierid = @Supplierid " +
"ORDER BY Supplierid";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
cmd.Parameters.AddWithValue("Supplierid", supplierID);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
supp = new Supplier();
supp.SupplierID = (int)reader["Supplierid"];
supp.SupName = (string)reader["SupName"];
suppliers.Add(supp);
}
}
}
return(suppliers);
}
public static Product GetProductById(int productId)
{
Product prod = null;
SqlConnection con = TravelExpertsConnection.GetConnection();
string selectStatement = "SELECT ProductID, ProdName " +
"FROM Products " +
"WHERE ProductID = @ProductID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@ProductID", productId); // value comes from the method's argument
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) // found a customer
{
prod = new Product();
prod.ProductID = (int)reader["ProductID"];
prod.ProdName = reader["ProdName"].ToString();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(prod);
}
/// <summary>
/// <Author>Stephen Sander</Author>
/// Gets all products linked to a specific package
/// </summary>
/// <param name="oldSup">old supplier before update</param>
/// <param name="newSup">new supplier post update</param>
/// <returns>returns if the number of updated rows is more than 1</returns>
public static bool UpdateSupplier(Supplier oldSup, Supplier newSup)
{
int count;
using (SqlConnection con = TravelExpertsConnection.GetConnection())
{
string updateStatement = "UPDATE Suppliers SET " +
" SupName = @newSupName, " +
" ProdName = @newProdName, " +
" ProductID = @newProductID " +
" WHERE SupplierId = @oldSupplierId " +
" AND SupName = @oldSupName ";
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
cmd.Parameters.AddWithValue("@newSupName", newSup.SupName);
cmd.Parameters.AddWithValue("@newProdName", newSup.ProdName);
cmd.Parameters.AddWithValue("@newProductID", newSup.ProductID);
cmd.Parameters.AddWithValue("@oldSupplierId", oldSup.SupplierID);
cmd.Parameters.AddWithValue("@oldSupName", oldSup.SupName);
cmd.Parameters.AddWithValue("@oldProdName", oldSup.ProdName);
cmd.Parameters.AddWithValue("@oldProductID", oldSup.ProductID);
con.Open();
count = cmd.ExecuteNonQuery(); // returns how many rows updated
}
}
return(count > 0);
}
/// <summary>
/// <Author>Stephen Sander</Author>
/// Adds a product to a supplier
/// </summary>
/// <param name="sup">passes supplier object to the function</param>
/// <returns>returns if the number of updated rows is more than 1</returns>
/// <summary>
/// <author>Stephen Sander </author>
public static int AddProductToSupplier(Supplier sup)
{
SqlConnection con = TravelExpertsConnection.GetConnection();
string insertStatement = "INSERT INTO Products_Suppliers (SupplierId, ProductId, SupName, ProdName) " +
"Values(@SupplierId, @ProductId, @SupName, @ProdName) ";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@SupplierId", sup.SupplierID);
cmd.Parameters.AddWithValue("@SupName", sup.SupName);
cmd.Parameters.AddWithValue("@ProdName", sup.ProdName);
cmd.Parameters.AddWithValue("@ProductId", sup.ProductID);
try
{
con.Open();
// get the generated ID - current identity value for Customers table
int supplierID = Convert.ToInt32(cmd.ExecuteScalar()); // single value
return(supplierID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// gets a package object by id from the database
/// </summary>
/// <param name="packageId"></param>
/// <returns></returns>
public static Package GetById(int packageId)
{
Package package = null;
SqlConnection con = TravelExpertsConnection.GetConnection();
string command =
"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"Where PackageId = @PackageId";
using (SqlCommand cmd = new SqlCommand(command, con))
{
con.Open();
cmd.Parameters.AddWithValue("@PackageId", packageId);
SqlDataReader read = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (read.Read())
{
package = new Package()
{
PackageId = (int)read["PackageId"],
PkgName = (string)read["PkgName"],
PkgStartDate = (read["PkgStartDate"] == DBNull.Value) ? (DateTime?)null : (DateTime)read["PkgStartDate"],
PkgEndDate = (read["PkgEndDate"] == DBNull.Value) ? (DateTime?)null : (DateTime)read["PkgEndDate"],
PkgDesc = (read["PkgDesc"] == DBNull.Value) ? String.Empty : (string)read["PkgDesc"],
PkgBasePrice = (decimal)read["PkgBasePrice"],
PkgAgencyCommission = (read["PkgAgencyCommission"] == DBNull.Value) ? (decimal?)null : (decimal)read["PkgAgencyCommission"],
};
} //close while
} // close command
return(package);
}
/// <summary>
/// <Author>Brian Appleton</Author>
/// Gets all products linked to a specific package
/// </summary>
/// <param name="packageId">package id to retrieve products for</param>
/// <returns>returns a list of packages</returns>
public static List <ProductExtended> GetPackageProductsById(int packageId)
{
List <ProductExtended> products = new List <ProductExtended>();
SqlConnection con = TravelExpertsConnection.GetConnection();
//sql command to get all products tied to a specific package
string command =
"SELECT pkg.PackageId, pro.ProductId, pro.ProdName, pps.ProductSupplierId, sup.SupName " +
"FROM Packages AS pkg " +
"JOIN Packages_Products_Suppliers as pps " +
"ON pkg.PackageId = pps.PackageId " +
"JOIN Products_Suppliers as s " +
"ON s.ProductSupplierId = pps.ProductSupplierId " +
"JOIN Products as pro " +
"ON pro.ProductId = s.ProductId " +
"JOIN Suppliers as sup " +
"ON s.SupplierId = sup.SupplierId " +
"WHERE pkg.PackageId = @PackageId";
using (SqlCommand cmd = new SqlCommand(command, con))
{
con.Open();
cmd.Parameters.AddWithValue("@PackageId", packageId);
using (SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (read.HasRows)
{
while (read.Read())
{
ProductExtended product = new ProductExtended()
{
ProductID = (int)read["ProductId"],
ProdName = read["ProdName"].ToString(),
ProductSupplierId = (int)read["ProductSupplierId"],
SupplierName = read["SupName"].ToString()
};
products.Add(product);
}
}
} // close data reader
} // close command
return(products);
}
/// <summary>
/// <auther>Muhammad Khalil</auther>
/// using the selected supplierid, delete the record from the database
/// this only works for added records not for original referenced records
/// </summary>
/// <param name="supplierID"></param>
/// <param name="supName"></param>
public static void DeleteSupplier(int supplierID, string supName)
{
using (SqlConnection connection = TravelExpertsConnection.GetConnection())
{
string insertStatement =
"DELETE FROM Suppliers " +
" Where Supplierid = @Supplierid";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
cmd.Parameters.AddWithValue("@SupName ", supName);
cmd.Parameters.AddWithValue("@Supplierid ", supplierID);
connection.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// <auther>Muhammad Khalil</auther>
/// Insert an autogenerated Suipplier ID and add an assoociated supplier name into the database
/// </summary>
/// <param name="supplierID"> Autogenerated ID</param>
/// <param name="supName">Supplier name from the textbox</param>
public static void InsertSupplier(int supplierID, string supName)
{
using (SqlConnection connection = TravelExpertsConnection.GetConnection())
{
string insertStatement =
"INSERT INTO Suppliers(SupName, Supplierid) " +
"VALUES (@SupName, @Supplierid)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
cmd.Parameters.AddWithValue("@SupName ", supName);
cmd.Parameters.AddWithValue("@Supplierid ", supplierID);
connection.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// <auther>Muhammad Khalil</auther>
/// using the selected supplierid, update the Supplier name
/// </summary>
/// <param name="supplierID">Selected supplierID</param>
/// <param name="supName">Supplier name from the textbox</param>
public static void UpdateSuppliers(int supplierID, string supName)
{
using (SqlConnection connection = TravelExpertsConnection.GetConnection())
{
string updateStatement =
"UPDATE Suppliers SET " +
"SupName = @SupName " +
"WHERE Supplierid = @Supplierid"; // needed for identification
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
cmd.Parameters.AddWithValue("@SupName ", supName);
cmd.Parameters.AddWithValue("@Supplierid ", supplierID);
connection.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// <auther>Muhammad Khalil</auther>
/// check the database for existing supplier ids
/// </summary>
/// <param name="supplierID">Selected supplier id</param>
/// <returns></returns>
public static bool CheckDataBase(int supplierID)
{
bool success = false;
int count = 0;
SqlConnection connection = TravelExpertsConnection.GetConnection();
string query = "SELECT Supplierid " +
"FROM Suppliers " +
"WHERE Supplierid = @Supplierid";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("Supplierid", supplierID);
try
{
connection.Open();
// execute the command
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
count++;
}
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
/// <summary>
/// <author>Stephen Sander </author>
/// Selects Supplier by ID
/// </summary>
///<param name="productID">passes supplier object to the function</param>
/// <returns>list of suppliers with matching productID</returns>
public static List <Supplier> GetSupplier(int productID)
{
List <Supplier> suppliers = new List <Supplier>();
Supplier sup = null;
SqlConnection con = TravelExpertsConnection.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);
}
/// <summary>
/// <author>Stephen Sander</author>
/// Get all suppliers, inner join with Products and Products_Suppliers
/// to get the product names, but order by Product Name
/// </summary>
/// <returns></returns>
public static List <Supplier> GetAllSuppliers()
{
List <Supplier> suppliers = new List <Supplier>();
Supplier sup = null;
SqlConnection con = TravelExpertsConnection.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 " +
"ORDER BY p.ProdName";
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
{
throw;
}
finally
{
con.Close();
}
return(suppliers);
}
/// <summary>
/// <Author>Brian Appleton</Author>
/// Deletes a product linked to a package
/// </summary>
public static bool DeletePackageProductsById(int packageId, int productSupplierID)
{
SqlConnection con = TravelExpertsConnection.GetConnection();
string delete = "DELETE FROM Packages_Products_Suppliers " +
"WHERE PackageId = @PackageId " +
"AND ProductSupplierId = @ProductSupplierId ";
using (SqlCommand cmd = new SqlCommand(delete, con))
{
cmd.Parameters.AddWithValue("@PackageId", packageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", productSupplierID);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
if (rowsAffected > 0)
{
return(true);
}
return(false);
}
}
/// <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 = TravelExpertsConnection.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);
}
}
/// <summary>
/// Add a PackageProductSupplier to the DB
/// </summary>
/// <param name="packageId"></param>
/// <param name="productSupplierId"></param>
/// <returns></returns>
public static bool Add(PackageProductSupplier packageProductSupplier)
{
if (Object.Equals(packageProductSupplier, null))
{
return(false);
}
string query =
"INSERT INTO Packages_Products_Suppliers(PackageId, ProductSupplierId) " +
"Values(@PackageId,@ProductSupplierId)";
int rowsUpdated;
SqlConnection connect = TravelExpertsConnection.GetConnection();
using (SqlCommand cmd = new SqlCommand(query, connect))
{
cmd.Parameters.AddWithValue("@PackageId", packageProductSupplier.PackageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", packageProductSupplier.ProductSupplierId);
connect.Open();
rowsUpdated = cmd.ExecuteNonQuery();
connect.Close();
}
//no rows updated.
if (rowsUpdated == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// <author>Stephen Sander </author>
/// Add a new supplier
/// </summary>
/// <param name="sup">passes supplier object to the function</param>
/// <returns>SupplierID</returns>
public static int AddSupplier(Supplier sup)
{
SqlConnection con = TravelExpertsConnection.GetConnection();
string insertStatement = "INSERT INTO Suppliers (SupplierId, SupName, ProdName, ProductID) " +
"VALUES(@SupplierId, @SupName, @ProdName, @ProductID)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@SupplierId", sup.SupplierID);
cmd.Parameters.AddWithValue("@SupName", sup.SupName);
cmd.Parameters.AddWithValue("@ProdName", sup.ProdName);
cmd.Parameters.AddWithValue("@ProductID", sup.ProductID);
con.Open();
cmd.ExecuteNonQuery(); // run the insert command
// get the generated ID - current identity value for Customers table
string selectQuery = "SELECT IDENT_CURRENT('Suppliers') FROM Suppliers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int supplierID = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
// typecase (int) does NOT work!
return(supplierID);
}
/// <summary>
/// Gets all package ids and names
/// </summary>
/// <returns></returns>
public static List <Package> GetAll()
{
List <Package> package = new List <Package>();
SqlConnection con = TravelExpertsConnection.GetConnection();
string command = "SELECT PackageId, PkgName FROM Packages ORDER BY PkgName";
using (SqlCommand cmd = new SqlCommand(command, con))
{
con.Open();
SqlDataReader read = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (read.Read())
{
package.Add(new Package()
{
PackageId = (int)read["PackageId"],
PkgName = (string)read["PkgName"],
});
}
} // close command
return(package);
}
public static PackageProductSupplier Get(PackageProductSupplier packageProductSupplier)
{
if (Object.Equals(packageProductSupplier, null))
{
return(null);
}
PackageProductSupplier package = null;
SqlConnection con = TravelExpertsConnection.GetConnection();
string command =
"SELECT PackageId, ProductSupplierId " +
"FROM Packages_Products_Suppliers " +
"WHERE PackageId = @PackageId " +
"AND ProductSupplierId = @ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(command, con))
{
con.Open();
cmd.Parameters.AddWithValue("@PackageId", packageProductSupplier.PackageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", packageProductSupplier.ProductSupplierId);
SqlDataReader read = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (read.Read())
{
package = new PackageProductSupplier()
{
PackageId = (int)read["PackageId"],
ProductSupplierId = (int)read["ProductSupplierId"]
};
}
} // close command
return(package);
}
/// <summary>
/// Updates a specific package property
/// </summary>
/// <param name="packageId">the package id to be updated</param>
/// <returns></returns>
public static bool UpdatePropertyById(string propertyNameToUpdate, Package package)
{
//given package is null or no id
if (Object.Equals(package, null) || package.PackageId <= 0)
{
return(false);
}
SqlConnection connect = TravelExpertsConnection.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@PackageId", package.PackageId);
string query = String.Empty;
switch (propertyNameToUpdate)
{
case nameof(Package.PkgAgencyCommission):
query = "Update Packages SET PkgAgencyCommission = @PkgAgencyCommission WHERE PackageId = @PackageId";
if (Object.Equals(package.PkgAgencyCommission, null))
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
}
break;
case nameof(Package.PkgBasePrice):
query = "Update Packages SET PkgBasePrice = @PkgBasePrice WHERE PackageId = @PackageId";
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
break;
case nameof(Package.PkgDesc):
query = "Update Packages SET PkgDesc = @PkgDesc WHERE PackageId = @PackageId";
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
break;
case nameof(Package.PkgEndDate):
query = "Update Packages SET PkgEndDate = @PkgEndDate WHERE PackageId = @PackageId";
if (Object.Equals(package.PkgEndDate, null))
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
}
break;
case nameof(Package.PkgName):
query = "Update Packages SET PkgName = @PkgName WHERE PackageId = @PackageId";
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
break;
case nameof(Package.PkgStartDate):
query = "Update Packages SET PkgStartDate = @PkgStartDate WHERE PackageId = @PackageId";
if (Object.Equals(package.PkgStartDate, null))
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
}
break;
default:
return(false);
}
cmd.CommandText = query;
cmd.Connection = connect;
connect.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
cmd.Connection.Close();
//no rows updated.
if (rowsUpdated == 0)
{
return(false);
}
else
{
return(true);
}
}
public static int InsertPackage(Package package)
{
//given package is null or no id
if (Object.Equals(package, null))
{
return(0);
}
string query =
"INSERT INTO Packages " +
"(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission) ";
SqlConnection connect = TravelExpertsConnection.GetConnection();
using (SqlCommand cmd = new SqlCommand(query, connect))
{
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
if (Object.Equals(package.PkgAgencyCommission, null))
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
}
if (Object.Equals(package.PkgEndDate, null))
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
}
if (Object.Equals(package.PkgStartDate, null))
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
}
connect.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
cmd.Connection.Close();
if (rowsUpdated == 0)
{
return(0);
}
else
{
return(GetWithoutId(package).PackageId);
}
}
}
/// <summary>
/// Updates a package by id in the database
/// </summary>
/// <param name="packageId">the package id to be updated</param>
/// <returns></returns>
public static bool Update(Package package)
{
Package oldPackage = GetById(package.PackageId);
//given package is null or no id
if (Object.Equals(package, null) || package.PackageId == 0 || oldPackage.CompareTo(package))
{
return(false);
}
string query =
"Update Packages " +
"SET PkgName = @PkgName " +
"SET PkgStartDate = @PkgStartDate " +
"SET PkgEndDate = @PkgEndDate " +
"SET PkgDesc = @PkgDesc " +
"SET PkgBasePrice = @PkgBasePrice " +
"SET PkgAgencyCommission = @PkgAgencyCommission " +
"WHERE PackageId = @PackageId";
int rowsUpdated;
SqlConnection connect = TravelExpertsConnection.GetConnection();
using (SqlCommand cmd = new SqlCommand(query, connect))
{
connect.Open();
//Non-nullable params
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
//nullable params
if (Object.Equals(package.PkgAgencyCommission, null))
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
}
if (Object.Equals(package.PkgStartDate, null))
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
}
if (Object.Equals(package.PkgEndDate, null))
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
}
rowsUpdated = cmd.ExecuteNonQuery();
connect.Close();
}
//no rows updated.
if (rowsUpdated == 0)
{
return(false);
}
else
{
return(true);
}
}