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