/// <summary>
/// Inserts a new packaage product supplier
/// from an existing package and product supplier
/// </summary>
/// <param name="pack"></param>
/// <param name="prod_supp"></param>
/// <returns>Indicator of success</returns>
public static bool AddPackages_Products_Supplier(Package pack, Products_Supplier prod_supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string insertStatement = "INSERT INTO Packages_Products_Suppliers(PackageId, ProductSupplierId) " +
"VALUES(@PackageId, @ProductSupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@PackageId", pack.PackageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", prod_supp.ProductSupplierId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Gets unique product based on passed product ID
/// </summary>
/// <param name="productId"></param>
/// <returns></returns>
public static List <Products> GetProducts(int productId)
{
List <Products> products = new List <Products>();
Products prod = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT ProductID, ProdName " +
"From Products " +
"WHERE ProductId = @ProductId " +
"ORDER By ProductID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", productId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod = new Products();
prod.ProductId = (int)dr["ProductID"];
prod.ProdName = (string)dr["ProdName"];
products.Add(prod);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products);
}
public static List <Suppliers> GetAllSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>();
Suppliers supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT SupplierID, SupName " +
"From Suppliers ORDER By SupplierID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
supp = new Suppliers();
supp.SupplierId = (int)dr["SupplierID"];
supp.SupName = (string)dr["SupName"];
suppliers.Add(supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(suppliers);
}
/// <summary>
/// Add passed supplier to DB
/// </summary>
/// <param name="supp"></param>
/// <returns></returns>
public static bool AddSuppliers(Suppliers supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string insertStatement = "INSERT INTO Suppliers (SupplierId, SupName) " +
"VALUES(@SupplierId, @SupName)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@SupplierId", supp.SupplierId);
cmd.Parameters.AddWithValue("@SupName", supp.SupName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Deletes a package product supplier
/// </summary>
/// <param name="pack_prod_supp"></param>
/// <returns>Indicator of success</returns>
public static bool DeletePackages_Products_Supplier(Packages_Products_Supplier pack_prod_supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string deleteStatement = "DELETE FROM Packages_Products_Suppliers " +
"WHERE PackageId = @PackageId " + // to identify the product to be deleted
"AND ProductSupplierId = @ProductSupplierId";
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@ProductSupplierId", pack_prod_supp.ProductSupplierId);
cmd.Parameters.AddWithValue("@PackageId", pack_prod_supp.PackageId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Selects all package products suppliers
/// </summary>
/// <returns>A list of existing package products suppliers</returns>
public static List <Packages_Products_Supplier> GetAllPackages_Product_Suppliers()
{
List <Packages_Products_Supplier> packages_Products_Suppliers = new List <Packages_Products_Supplier>();
Packages_Products_Supplier pack_prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT PkgName, Packages_Products_Suppliers.PackageId, ProductSupplierId " +
"From Packages_Products_Suppliers, Packages " +
"where Packages_Products_Suppliers.PackageId = Packages.PackageId " +
"ORDER BY PkgName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
pack_prod_supp = new Packages_Products_Supplier();
pack_prod_supp.PackageId = (int)dr["PackageId"];
pack_prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
packages_Products_Suppliers.Add(pack_prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages_Products_Suppliers);
}
/// <summary>
/// Add product Suppliier by linking a passed products and supplier ID
/// </summary>
/// <param name="prod"></param>
/// <param name="supp"></param>
/// <returns></returns>
public static int AddProduct_Supplier(Products prod, Suppliers supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " +
"VALUES(@ProductId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@ProductId", prod.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", supp.SupplierId);
try
{
connection.Open();
cmd.ExecuteNonQuery(); // run the insert command
// get the generated ID - current identity value for Products_Supplier table
string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers";
SqlCommand selectCmd = new SqlCommand(selectQuery, connection);
int ProductSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
// typecase (int) does NOT work!
return(ProductSupplierId);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Products> GetAllProducts()
{
List <Products> products = new List <Products>();
Products prod = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT ProductID, ProdName " +
"From Products ORDER By ProductID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod = new Products();
prod.ProductId = (int)dr["ProductID"];
prod.ProdName = (string)dr["ProdName"];
prod.Suppliers = Products_SupplierDB.GetUniqueProduct_Suppliers(prod.ProductId);
products.Add(prod);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products);
}
/// <summary>
/// Updates existing product
/// </summary>
/// <param name="oldProd">data before update</param>
/// <param name="newrod">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateProduct(Products oldProd, Products newProd)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string updateStatement = "UPDATE Products " +
"SET ProdName = @NewProdName " +
"WHERE ProductID = @OldProdID " +
"AND ProdName = @OldProdName"; // remaining conditions - to ensure optimistic concurrency
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewProdName", newProd.ProdName);
cmd.Parameters.AddWithValue("@OldProdID", oldProd.ProductId);
cmd.Parameters.AddWithValue("@OldProdName", oldProd.ProdName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Adds a new packages to the Packages Table in Travel Experts Database
/// </summary>
/// <param name="pack"> Package object that cotaing data for the new record</param>
/// <returns>generated PackageId</returns>
public static int AddPackage(Package pack)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@PkgName", pack.Name);
cmd.Parameters.AddWithValue("@PkgDesc", pack.Desc);
cmd.Parameters.AddWithValue("@PkgBasePrice", pack.BasePrice);
//This block of code specifies passing a nullable type as a parameter to SQL for StartDate
SqlParameter startDateParam = new SqlParameter("@PkgStartDate", pack.StartDate == null ?
(Object)DBNull.Value : pack.StartDate);
startDateParam.IsNullable = true;
startDateParam.Direction = ParameterDirection.Input;
startDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(startDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for endDate
SqlParameter endDateParam = new SqlParameter("@PkgEndDate", pack.EndDate == null ?
(Object)DBNull.Value : pack.EndDate);
endDateParam.IsNullable = true;
endDateParam.Direction = ParameterDirection.Input;
endDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(endDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
SqlParameter pkgAgencyParam = new SqlParameter("@PkgAgencyCommission", pack.AgencyCommission == null ?
(Object)DBNull.Value : pack.AgencyCommission);
pkgAgencyParam.IsNullable = true;
pkgAgencyParam.Direction = ParameterDirection.Input;
pkgAgencyParam.SqlDbType = SqlDbType.Money;
cmd.Parameters.Add(pkgAgencyParam);
try
{
connection.Open();
cmd.ExecuteNonQuery(); // run the insert command
// get the generated Id - current Identity value for Packages table
string selectQuery = "SELECT IdENT_CURRENT('Packages') FROM Packages";
SqlCommand selectCmd = new SqlCommand(selectQuery, connection);
int PackageId = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
// typecase (int) does NOT work!
return(PackageId);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Delete PAssed Supplier from DB
/// </summary>
/// <param name="supp"></param>
/// <returns></returns>
public static bool DeleteSupplier(Suppliers supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string deleteStatement = "delete Products_Suppliers from Products_Suppliers " +
"inner join Suppliers on Products_Suppliers.SupplierId = Suppliers.SupplierId " +
"inner join Products on Products_Suppliers.ProductId = Products.ProductId " +
"where Suppliers.SupplierId = @SupplierId " +
//first part deletes the link to product supplier table if it exists
//Second part deletes the actual supplier
"DELETE FROM Suppliers " +
"WHERE SupplierId = @SupplierId " + // to identify the supplier to be deleted
"AND SupName = @SupName"; // remaining conditions - to ensure optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@SupplierId", supp.SupplierId);
cmd.Parameters.AddWithValue("@SupName", supp.SupName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Delete passed products from datatabse
/// </summary>
/// <param name="prod"></param>
/// <returns></returns>
public static bool DeleteProduct(Products prod)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string deleteStatement = "delete Products_Suppliers from Products_Suppliers " +
"inner join Products on Products_Suppliers.ProductId = Products.ProductId " +
"inner join Suppliers on Products_Suppliers.SupplierId = Suppliers.SupplierId " +
"where Products.ProductId = @ProductId " +
//first part deletes the link to product supplier table if it exists
//second part deltes the actual product
"delete from Products " +
"where ProductId = @ProductId " +
"and ProdName = @ProdName";
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@ProductID", prod.ProductId);
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Gets packages product suppliers with a specific package ID
/// </summary>
/// <param name="packageId"></param>
/// <returns></returns>
public static List <Packages_Products_Supplier> GetPackages_Product_Suppliers(int packageId)
{
List <Packages_Products_Supplier> packages_Products_Suppliers = new List <Packages_Products_Supplier>();
Packages_Products_Supplier pack_prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT Packages_Products_Suppliers.PackageId, Products_Suppliers.ProductSupplierId, ProdName, SupName " +
"From Packages_Products_Suppliers, Products_Suppliers, Products, Suppliers " +
"where Packages_Products_Suppliers.PackageId = @PackageId " +
"And Packages_Products_Suppliers.ProductSupplierId = Products_Suppliers.ProductSupplierId " +
"and Products_Suppliers.ProductId = Products.ProductId " +
"and Products_Suppliers.SupplierId = Suppliers.SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", packageId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
pack_prod_supp = new Packages_Products_Supplier();
pack_prod_supp.PackageId = (int)dr["PackageId"];
pack_prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
pack_prod_supp.ProdName = dr["ProdName"].ToString();
pack_prod_supp.SupName = dr["SupName"].ToString();
packages_Products_Suppliers.Add(pack_prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages_Products_Suppliers);
}
/// <summary>
/// Get unique products suppliers with passed product supplier ID
/// </summary>
/// <param name="prodId"></param>
/// <returns></returns>
public static List <Products_Supplier> GetUniqueProduct_Suppliers(int prodId)
{
List <Products_Supplier> products_Suppliers = new List <Products_Supplier>();
Products_Supplier prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "select SupName, Products_Suppliers.SupplierId, ProductSupplierId " +
"from Products_Suppliers, Suppliers " +
"where ProductId = @ProdId " +
"and Suppliers.SupplierId = Products_Suppliers.SupplierId " +
"order by SupName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProdId", prodId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod_supp = new Products_Supplier();
prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
prod_supp.SupplierId = (int)dr["SupplierId"];
prod_supp.SupName = dr["SupName"].ToString();
products_Suppliers.Add(prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products_Suppliers);
}
/// <summary>
/// Delete Product Supplier
/// </summary>
/// <param name="prod_supp"></param>
/// <returns></returns>
public static bool DeleteProdSupplier(Products_Supplier prod_supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string deleteStatement = "DELETE FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId " + // to identify the product to be deleted
"AND SupplierId = @SupplierId " +
"AND ProductId = @ProductId "; // remaining conditions - to ensure optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@ProductSupplierId", prod_supp.ProductSupplierId);
cmd.Parameters.AddWithValue("@SupplierId", prod_supp.SupplierId);
cmd.Parameters.AddWithValue("@ProductId", prod_supp.ProductId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Get package with a specific id
/// </summary>
/// <param name="packageId"></param>
/// <returns></returns>
public static Package GetPackage(int packageId)
{
Package pack = null;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = @PackageId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", packageId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) // found a package
{
pack = new Package();
pack.PackageId = (int)reader["PackageId"];
pack.Name = reader["PkgName"].ToString();
pack.StartDate = reader["PkgStartDate"] as DateTime?;
pack.EndDate = reader["PkgEndDate"] as DateTime?;
pack.Desc = reader["PkgDesc"].ToString();
pack.BasePrice = (Decimal)reader["PkgBasePrice"];
pack.AgencyCommission = reader["PkgAgencyCommission"] as Decimal?;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(pack);
}
/// <summary>
/// Updates existing package_product_supplier
/// </summary>
/// <param name="oldPackProdSupp">data before update</param>
/// <param name="newPackProdSupp">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateSupplier(Packages_Products_Supplier oldPackProdSupp, Packages_Products_Supplier newPackProdSupp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string updateStatement = "UPDATE Packages_Products_Suppliers " +
"SET PackageId = @NewPackageId, " +
"ProductSupplierId = @NewProductSupplierId " +
"WHERE PackageId = @OldPackageId " +
"AND ProductSupplierId = @OldProductSupplierId";
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewProductSupplierId", newPackProdSupp.ProductSupplierId);
cmd.Parameters.AddWithValue("@NewPackageId", newPackProdSupp.PackageId);
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldPackProdSupp.ProductSupplierId);
cmd.Parameters.AddWithValue("@OldPackageId", oldPackProdSupp.PackageId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Get all packages
/// </summary>
/// <param name="packageId"></param>
/// <returns>List of Packages</returns>
public static List <Package> GetAllPackages()
{
List <Package> packages = new List <Package>();
Package pack = null;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages ORDER BY PackageId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read()) // found a package
{
pack = new Package();
pack.PackageId = (int)reader["PackageId"];
pack.Name = reader["PkgName"].ToString();
pack.StartDate = reader["PkgStartDate"] as DateTime?;
pack.EndDate = reader["PkgEndDate"] as DateTime?;
pack.Desc = reader["PkgDesc"].ToString();
pack.BasePrice = (Decimal)reader["PkgBasePrice"];
pack.AgencyCommission = reader["PkgAgencyCommission"] as Decimal?;
pack.packProd = Packages_Products_SuppliersDB.GetPackages_Product_Suppliers(pack.PackageId);
packages.Add(pack);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages);
}
/// <summary>
/// Gets all products suppliers and returns a list
/// </summary>
/// <returns></returns>
public static List <Products_Supplier> GetAllProduct_Suppliers()
{
List <Products_Supplier> products_Suppliers = new List <Products_Supplier>();
Products_Supplier prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT ProductSupplierId, Products_Suppliers.SupplierId, Products_Suppliers.ProductId, SupName, ProdName " +
"From Products_Suppliers, Suppliers, Products " +
"Where Products_Suppliers.ProductId = Products.ProductId " +
"AND Products_Suppliers.SupplierId = Suppliers.SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod_supp = new Products_Supplier();
prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
prod_supp.SupplierId = (int)dr["SupplierId"];
prod_supp.ProductId = (int)dr["ProductId"];
prod_supp.ProdName = dr["ProdName"].ToString();
prod_supp.SupName = dr["SupName"].ToString();
products_Suppliers.Add(prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products_Suppliers);
}
/// <summary>
/// Updates existing package
/// </summary>
/// <param name="oldPack">data before update</param>
/// <param name="newPack">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdatePackage(Package oldPack, Package newPack)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string updateStatement = "UPDATE Packages " +
"SET PkgName = @NewName, " +
" PkgStartDate = @NewPkgStartDate, " +
" PkgEndDate = @NewPkgEndDate, " +
" PkgDesc = @NewPkgDesc, " +
" PkgBasePrice = @NewPkgBasePrice, " +
" PkgAgencyCommission = @NewPkgAgencyCommission " +
"WHERE PackageId = @OldPackageId " +
"AND PkgName = @OldPkgName " + // remaining conditions - to ensure optimistic concurrency
"AND (PkgStartDate = @OldPkgStartDate " +
"OR PkgStartDate IS NULL) " + //Database null values can only be tested with IS NULL
"AND (PkgEndDate = @OldPkgEndDate " +
"OR PkgEndDate IS NULL) " + //Database null values can only be tested with IS NULL
"AND PkgDesc = @OldPkgDesc " +
"AND PkgBasePrice = @OldPkgBasePrice " +
"AND (PkgAgencyCommission = @OldPkgAgencyCommission " +
"OR PkgAgencyCommission IS NULL)"; //Database null values can only be tested with IS NULL
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewName", newPack.Name);
cmd.Parameters.AddWithValue("@NewPkgDesc", newPack.Desc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPack.BasePrice);
cmd.Parameters.AddWithValue("@OldPackageId", oldPack.PackageId);
cmd.Parameters.AddWithValue("@OldPkgName", oldPack.Name);
cmd.Parameters.AddWithValue("@OldPkgDesc", oldPack.Desc);
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPack.BasePrice);
//This block of code specifies passing a nullable type as a parameter to SQL for StartDate
SqlParameter newStartDateParam = new SqlParameter("@NewPkgStartDate", newPack.StartDate == null ?
(Object)DBNull.Value : newPack.StartDate);
newStartDateParam.IsNullable = true;
newStartDateParam.Direction = ParameterDirection.Input;
newStartDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(newStartDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for endDate
SqlParameter newEndDateParam = new SqlParameter("@NewPkgEndDate", newPack.EndDate == null ?
(Object)DBNull.Value : newPack.EndDate);
newEndDateParam.IsNullable = true;
newEndDateParam.Direction = ParameterDirection.Input;
newEndDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(newEndDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
SqlParameter newPkgAgencyParam = new SqlParameter("@NewPkgAgencyCommission", newPack.AgencyCommission == null ?
(Object)DBNull.Value : newPack.AgencyCommission);
newPkgAgencyParam.IsNullable = true;
newPkgAgencyParam.Direction = ParameterDirection.Input;
newPkgAgencyParam.SqlDbType = SqlDbType.Money;
cmd.Parameters.Add(newPkgAgencyParam);
//This block of code specifies passing a nullable type as a parameter to SQL for StartDate
SqlParameter oldStartDateParam = new SqlParameter("@OldPkgStartDate", oldPack.StartDate == null ?
(Object)DBNull.Value : oldPack.StartDate);
oldStartDateParam.IsNullable = true;
oldStartDateParam.Direction = ParameterDirection.Input;
oldStartDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(oldStartDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for endDate
SqlParameter oldEndDateParam = new SqlParameter("@OldPkgEndDate", oldPack.EndDate == null ?
(Object)DBNull.Value : oldPack.EndDate);
oldEndDateParam.IsNullable = true;
oldEndDateParam.Direction = ParameterDirection.Input;
oldEndDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(oldEndDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
SqlParameter oldPkgAgencyParam = new SqlParameter("@OldPkgAgencyCommission", oldPack.AgencyCommission == null ?
(Object)DBNull.Value : oldPack.AgencyCommission);
oldPkgAgencyParam.IsNullable = true;
oldPkgAgencyParam.Direction = ParameterDirection.Input;
oldPkgAgencyParam.SqlDbType = SqlDbType.Money;
cmd.Parameters.Add(oldPkgAgencyParam);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Deletes the passed package from the database
/// </summary>
/// <param name="pack"></param>
/// <returns></returns>
public static bool DeletePackage(Package pack)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string deleteStatement =
"delete Packages_Products_Suppliers from Packages_Products_Suppliers " +
"inner join Products_Suppliers on Products_Suppliers.ProductSupplierId = Packages_Products_Suppliers.ProductSupplierId " +
"inner join Suppliers on Products_Suppliers.SupplierId = Suppliers.SupplierId " +
"where Packages_Products_Suppliers.PackageId = @PackageId " +
//first part deletes the link to package product supplier table if it exists
//second part deltes the actual package
"DELETE FROM Packages " +
"WHERE PackageId = @PackageId " + // to Identify the package to be deleted
"AND PkgName = @PkgName " + // remaining conditions - to ensure optimistic concurrency
"AND (PkgStartDate = @PkgStartDate " +
"OR PkgStartDate IS NULL) " + //Database null values can only be tested with IS NULL
"AND (PkgStartDate = @PkgStartDate " +
"OR PkgStartDate IS NULL) " + //Database null values can only be tested with IS NULL
"AND PkgDesc = @PkgDesc " +
"AND PkgBasePrice = @PkgBasePrice " +
"AND (PkgAgencyCommission = @PkgAgencyCommission " +
"OR PkgAgencyCommission IS NULL)"; //Database null values can only be tested with IS NULL
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@PackageId", pack.PackageId);
cmd.Parameters.AddWithValue("@PkgName", pack.Name);
cmd.Parameters.AddWithValue("@PkgDesc", pack.Desc);
cmd.Parameters.AddWithValue("@PkgBasePrice", pack.BasePrice);
//This block of code specifies passing a nullable type as a parameter to SQL for StartDate
SqlParameter startDateParam = new SqlParameter("@PkgStartDate", pack.StartDate == null ?
(Object)DBNull.Value : pack.StartDate);
startDateParam.IsNullable = true;
startDateParam.Direction = ParameterDirection.Input;
startDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(startDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for endDate
SqlParameter endDateParam = new SqlParameter("@PkgEndDate", pack.EndDate == null ?
(Object)DBNull.Value : pack.EndDate);
endDateParam.IsNullable = true;
endDateParam.Direction = ParameterDirection.Input;
endDateParam.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(endDateParam);
//This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
SqlParameter pkgAgencyParam = new SqlParameter("@PkgAgencyCommission", pack.AgencyCommission == null ?
(Object)DBNull.Value : pack.AgencyCommission);
pkgAgencyParam.IsNullable = true;
pkgAgencyParam.Direction = ParameterDirection.Input;
pkgAgencyParam.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pkgAgencyParam);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
