// add product to db public static int AddProduct(Product product) { SqlConnection con = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT INTO Products (ProdName) " + "VALUES(@Name)"; SqlCommand cmd = new SqlCommand(insertStatement, con); cmd.Parameters.AddWithValue("@Name", product.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 return(productId); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
// get products from db public static List <Product> GetProducts() { List <Product> products = new List <Product>(); SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT ProductId, ProdName " + "FROM Products " + "ORDER BY ProductId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Product prod = new Product(); prod.ProductId = (int)reader["ProductId"]; prod.ProdName = reader["ProdName"].ToString(); products.Add(prod); } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(products); }
// update product to db public static bool UpdateProduct(string oldProd, string newProd) { bool success = false; SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string updateProduct = "UPDATE Products " + "SET ProdName = @ProdName " + "WHERE ProdName = @oldProdId "; SqlCommand cmd = new SqlCommand(updateProduct, dbConnect); cmd.Parameters.AddWithValue("@ProdName", newProd); cmd.Parameters.AddWithValue("@oldProdId", oldProd); try { dbConnect.Open(); cmd.ExecuteNonQuery(); success = true; } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } return(success); }
// get package based on prod/supp from db public static bool LinkPackageProductSuppliers(int ProSupID, int PackageID) { bool results = false; SqlConnection con = TravelExpertsDB.GetConnection(); string insertQuery = "Insert into Packages_Products_Suppliers(PackageId, ProductSupplierId) Values (@PackageID, @ProSupID)"; SqlCommand cmd = new SqlCommand(insertQuery, con); cmd.Parameters.AddWithValue("@PackageID", PackageID); cmd.Parameters.AddWithValue("@ProSupID", ProSupID); try { con.Open(); cmd.ExecuteNonQuery(); results = true; } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(results); }
// get prod/supp from packageID from db public static List <PackageProductSupplier> GetProSup(int ID) { List <PackageProductSupplier> list = new List <PackageProductSupplier>(); PackageProductSupplier ProSup; SqlConnection con = TravelExpertsDB.GetConnection(); string selectQuery = "select PackageId, ProductSupplierId from Packages_Products_Suppliers where PackageId = @ID"; SqlCommand cmd = new SqlCommand(selectQuery, con); cmd.Parameters.AddWithValue("@ID", ID); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { ProSup = new PackageProductSupplier(); ProSup.PackageID = (int)reader["PackageId"]; ProSup.ProductSupplierID = (int)reader["ProductSupplierId"]; list.Add(ProSup); } } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(list); }
// delete package from the db public static void DeletePackage(int tp) { SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string deletePackage = "DELETE FROM [Packages] " + "WHERE PackageId = @PkgID"; SqlCommand cmd = new SqlCommand(deletePackage, dbConnect); cmd.Parameters.AddWithValue("@PkgID", tp); try { dbConnect.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } }
// get prodsupp from db public static List <ProductSuppliers> GetProductSuppliers() { List <ProductSuppliers> ProSups = new List <ProductSuppliers>(); ProductSuppliers ProSup; SqlConnection con = TravelExpertsDB.GetConnection(); string selectQuery = "Select SP.ProductSupplierId, SP.ProductId, P.ProdName, S.SupName, SP.SupplierId " + "from Products P " + "Join Products_Suppliers SP on P.ProductId = SP.ProductId " + "Join Suppliers S on SP.SupplierId = S.SupplierId"; SqlCommand cmd = new SqlCommand(selectQuery, con); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { ProSup = new ProductSuppliers(); ProSup.ProdName = (string)reader["ProdName"]; ProSup.ProductSupplierId = (int)reader["ProductSupplierId"]; ProSup.SupName = (string)reader["SupName"]; ProSup.ProductId = (int)reader["ProductId"]; ProSup.SupplierId = (int)reader["SupplierId"]; ProSups.Add(ProSup); } } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(ProSups); }
public static void DeleteSupplier(string supplier) { SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string deletePackage = "DELETE FROM [Suppliers] " + "WHERE SupName = @SupName"; SqlCommand cmd = new SqlCommand(deletePackage, dbConnect); cmd.Parameters.AddWithValue("@SupName", supplier); try { dbConnect.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } }
// update package from the db public static bool UpdatePackage(TravelPackage oldPkg, TravelPackage newPkg) { bool success = false; SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string updatePackage = "UPDATE Packages " + "SET PkgName = @PkgName, " + "PkgDesc = @PkgDesc, " + "PkgStartDate = @PkgStartDate, " + "PkgEndDate = @PkgEndDate, " + "PkgBasePrice = @PkgBasePrice, " + "PkgAgencyCommission = @PkgAgencyCommission " + "WHERE PackageId = @oldPkgID "; SqlCommand cmd = new SqlCommand(updatePackage, dbConnect); cmd.Parameters.AddWithValue("@PkgName", newPkg.PkgName); cmd.Parameters.AddWithValue("@PkgStartDate", newPkg.PkgStartDate); cmd.Parameters.AddWithValue("@PkgEndDate", newPkg.PkgEndDate); cmd.Parameters.AddWithValue("@PkgDesc", newPkg.PkgDesc); cmd.Parameters.AddWithValue("@PkgBasePrice", newPkg.PkgBasePrice); cmd.Parameters.AddWithValue("@PkgAgencyCommission", newPkg.PkgAgencyCommission); cmd.Parameters.AddWithValue("@oldPkgID", oldPkg.PkgID); try { dbConnect.Open(); cmd.ExecuteNonQuery(); success = true; } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } return(success); }
// get package from db public static List <TravelPackage> GetTavelPackage() { List <TravelPackage> packages = new List <TravelPackage>(); // empty list of Packages TravelPackage travelPackage; SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages"; SqlCommand cmd = new SqlCommand(selectQuery, dbConnect); try { dbConnect.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { travelPackage = new TravelPackage(); travelPackage.PkgID = (int)reader["PackageId"]; travelPackage.PkgName = (string)reader["PkgName"]; travelPackage.PkgStartDate = (DateTime)reader["PkgStartDate"]; travelPackage.PkgEndDate = (DateTime)reader["PkgEndDate"]; travelPackage.PkgDesc = (string)reader["PkgDesc"]; travelPackage.PkgBasePrice = (decimal)reader["PkgBasePrice"]; travelPackage.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; packages.Add(travelPackage); } } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } return(packages); }
public static List <Supplier> GetSuppliers() { List <Supplier> suppliers = new List <Supplier>(); // empty list Supplier sup; // for reading SqlConnection con = TravelExpertsDB.GetConnection(); string selectQuery = "SELECT SupplierId, SupName FROM dbo.Suppliers ORDER BY SupName"; SqlCommand cmd = new SqlCommand(selectQuery, con); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { sup = new Supplier(); sup.SupplierId = (int)reader["SupplierId"]; sup.SupName = (string)reader["SupName"]; suppliers.Add(sup); // add the new order object to the list } } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(suppliers); }