/// <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(); } }