Exemplo n.º 1
0
        // 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();
            }
        }
Exemplo n.º 2
0
        // 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);
        }
Exemplo n.º 3
0
        // 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);
        }
Exemplo n.º 4
0
        // 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);
        }
Exemplo n.º 5
0
        // 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);
        }
Exemplo n.º 6
0
        // 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);
        }
Exemplo n.º 8
0
        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();
            }
        }
Exemplo n.º 9
0
        // 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);
        }
Exemplo n.º 10
0
        // 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);
        }
Exemplo n.º 11
0
        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);
        }