public static List <ProductsSuppliers> GetProductsSupplierId()
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement = "select ProductSupplierId, ProductId, SupplierId "
                                     + "from Products_Suppliers  "
                                     + "ORDER BY ProductSupplierId";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    p.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
                    p.SupplierId        = Convert.ToInt32(reader["SupplierId"]);
                    p.ProductId         = Convert.ToInt32(reader["ProductId"]);
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
Example #2
0
        // Delete operation returns bool success flag
        static public bool DeleteProduct(Product product)
        {
            bool successful = false;

            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        deleteString = "delete from Products " +
                                         "where " + // checking for optimistic concurrency
                                                    // "ProductId = @ProductId and " +
                                         "ProdName = @ProdName";
            SqlCommand deleteCommand = new SqlCommand(deleteString, connection);

            //  deleteCommand.Parameters.AddWithValue("@ProductId", product.ProductId);
            deleteCommand.Parameters.AddWithValue("@ProdName", product.ProdName);

            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("the existing product is connected with packages, delete the product from packages first");
            }
            finally
            {
                connection.Close();
            }

            return(successful);
        }
        public static ProductsSuppliers GetSelected(int pId)
        {
            ProductsSuppliers selected        = new ProductsSuppliers();
            SqlConnection     connection      = TRAVELEXPERTSDB.GetConnection();
            string            selectStatement = "select p.ProductId, ProdName, ProductSupplierId, ps.SupplierId, s.SupName " +
                                                "from Products p, Products_Suppliers ps, suppliers s " +
                                                "where p.ProductId = ps.ProductId and ps.SupplierId = s.SupplierId and ProductSupplierId = @ProductSupplierId";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProductSupplierId", pId);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.Read())
                {
                    selected.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
                    selected.SupplierId        = Convert.ToInt32(reader["SupplierId"]);
                    selected.ProductId         = Convert.ToInt32(reader["ProductId"]);
                    selected.SupName           = reader["SupName"].ToString();
                    selected.ProdName          = reader["ProdName"].ToString();
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(selected);
        }
        public static bool UpdateProductSupplier(ProductsSuppliers oldProd, ProductsSuppliers newProd)
        {
            bool          successful   = false;
            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        updateString = "update Products_Suppliers set ProductId = @newProd, SupplierId = @newSup " +
                                         "where ProductSupplierId = @oldProdSupId and ProductId = @oldProdId and SupplierId = @oldSupId";

            SqlCommand updateCommand = new SqlCommand(updateString, connection);

            updateCommand.Parameters.AddWithValue("@newProd", newProd.ProductId);
            updateCommand.Parameters.AddWithValue("@newSup", newProd.SupplierId);
            updateCommand.Parameters.AddWithValue("@oldProdSupId", oldProd.ProductSupplierId);
            updateCommand.Parameters.AddWithValue("@oldProdId", oldProd.ProductId);
            updateCommand.Parameters.AddWithValue("@oldSupId", oldProd.SupplierId);

            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //connection.Close();
            }
            return(successful);
        }
Example #5
0
        //method to add product supplier Alex
        public static void AddProductSupplier(int PackageId, int ProductSupplierId)
        {
            //int prodID = 0;
            // prepare connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // prepare the statement
            string     insertString  = " insert into Packages_Products_Suppliers(PackageId, ProductSupplierId) Values(@PackageId, @ProductSupplierId)";
            SqlCommand insertCommand = new SqlCommand(insertString, connection);

            insertCommand.Parameters.AddWithValue("@PackageId", PackageId);
            insertCommand.Parameters.AddWithValue("@ProductSupplierId", ProductSupplierId);
            try
            {
                // open connection
                connection.Open();

                // execute the statement
                int i = insertCommand.ExecuteNonQuery();
                if (i == 1) // one record inserted
                {
                    // retrieve customer id from the added record
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("The selected PackageId and Products Suppliers Id already exists!"); // pass the buck
            }
            finally
            {
                connection.Close();
            }
        }
        public static List <ProductsSuppliers> GetSupplierName(int selectedprodid)
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement = "select SupName, ps.SupplierId " +
                                     "from Products_Suppliers ps, Suppliers s " +
                                     "where ps.SupplierId = s.SupplierId and ProductId = @ProductId " +
                                     "order by SupName";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProductId", selectedprodid);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    p.SupName    = reader["SupName"].ToString();
                    p.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
Example #7
0
        public static bool Delete(int ps)
        {
            bool successful = false;

            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        deleteString = "delete from Packages where PackageId =@PackageId";

            SqlCommand deleteCommand = new SqlCommand(deleteString, connection);

            deleteCommand.Parameters.AddWithValue("@PackageId", ps);

            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
                else
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                connection.Close();
            }

            return(successful);
        }
        // Delete operation returns bool success flag
        static public bool DeleteSupplier(Supplier supplier)
        {
            bool successful = false;

            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        deleteString = "delete from Suppliers " +
                                         "where " + // checking for optimistic concurrency
                                                    //   "SupplierId = @SupplierId and " +
                                         "SupplierId = @SupplierId";
            SqlCommand deleteCommand = new SqlCommand(deleteString, connection);

            // deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
            deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);

            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
            }
            catch (Exception ex)
            {
                //throw ex;
                MessageBox.Show("Delete supplier from other tables first");
            }
            finally
            {
                connection.Close();
            }

            return(successful);
        }
        public static List <ProductsSuppliers> GetProductSupplierId2Inputs(int selectedprodid, int?selectedsupplier)
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement = "select ProductSupplierId  from Products_Suppliers " +
                                     "where ProductId= @ProductId and SupplierId=@SupplierId";
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProductId", selectedprodid);
            selectCommand.Parameters.AddWithValue("@SupplierId", selectedsupplier);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    p.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
Example #10
0
        public static List <package> GetPackages()
        {
            //make empty list
            List <package> packages = new List <package>();
            //new Order object
            package pkg;
            //create connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            //create selectcommand
            string selectString = "select PackageId,PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, Products from packages " +
                                  "order by PackageId";
            SqlCommand selectCommand = new SqlCommand(selectString, connection);

            try
            {
                connection.Open();

                //run the selectCommand and process the results adding Orders to the list
                SqlDataReader reader = selectCommand.ExecuteReader();

                //process one row at a time
                while (reader.Read())
                {
                    pkg = new package();

                    //define the OrderID and CustomerID of thew new object by using the reader
                    pkg.PackageId = (int)reader["PackageId"];
                    pkg.PkgName   = (string)reader["PkgName"];

                    DateTime tripEnd = (DateTime)reader["PkgEndDate"];
                    pkg.PkgEndDate = tripEnd.ToString("d");

                    DateTime tripStart = (DateTime)reader["PkgStartDate"];
                    pkg.PkgStartDate = tripStart.ToString("d");


                    pkg.PkgDesc = (string)reader["PkgDesc"];
                    decimal cost = (decimal)reader["PkgAgencyCommission"];
                    pkg.PkgAgencyCommission = cost.ToString("C");

                    decimal price = (decimal)reader["PkgBasePrice"];
                    pkg.PkgBasePrice = price.ToString("C");

                    packages.Add(pkg);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(packages);
        }
        public static List <ProductsSuppliers> GetUniqueProSupId(string logic)
        {
            List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
            SqlConnection            connection         = TRAVELEXPERTSDB.GetConnection();
            string selectStatement;

            if (logic == "p")
            {
                selectStatement = "select ProductId, ProdName from Products ";
            }
            else
            {
                selectStatement = "select SupplierId, SupName from Suppliers ";
            }
            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    ProductsSuppliers p = new ProductsSuppliers();
                    if (logic == "p")
                    {
                        p.ProductId = Convert.ToInt32(reader["ProductId"]);
                        p.ProdName  = reader["ProdName"].ToString();
                    }
                    else
                    {
                        p.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                        p.SupName    = reader["SupName"].ToString();
                    }
                    productssupplierid.Add(p);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(productssupplierid);
        }
Example #12
0
        public static int AddPackage(package pkg) // returns generated customer id
        {
            int           PackageId  = 0;
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // prepare the statement
            string insertString = "insert into packages " +
                                  "(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
                                  "values(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
            SqlCommand insertCommand = new SqlCommand(insertString, connection);

            // insertCommand.Parameters.AddWithValue("@PackageId", pkg.PackageId);
            insertCommand.Parameters.AddWithValue("@PkgName", pkg.PkgName);
            insertCommand.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate);
            insertCommand.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate);
            insertCommand.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc);
            insertCommand.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);
            insertCommand.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission);



            try
            {
                // open connection
                connection.Open();

                // execute the statement
                int i = insertCommand.ExecuteNonQuery();
                if (i == 1) // one record inserted
                {
                    // retrieve customer id from the added record
                    //    string selectString = "select @PackageId " +
                    //                           "from packages";
                    //    SqlCommand selectCommand = new SqlCommand(selectString, connection);
                    //    PackageId = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!!
                }
            }
            catch (Exception ex)
            {
                throw ex; // pass the buck
            }
            finally
            {
                connection.Close();
            }
            return(PackageId);
        }
Example #13
0
        public static List <Packages_Products_Suppliers> GetPackages_Products_Supplierss()
        {
            List <Packages_Products_Suppliers> prods = new List <Packages_Products_Suppliers>(); // product found

            // define the connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // define the select query command
            string selectQuery = "select  PackageId, ps.ProductId, ProdName,pps.ProductSupplierId, SupplierId " +
                                 "from Packages_Products_Suppliers pps, Products_Suppliers ps, Products p " +
                                 "where pps.ProductSupplierId = ps.ProductSupplierId and ps.ProductId = p.ProductId " +
                                 "order by PackageId, ProductSupplierId";
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            try
            {
                // open the connection
                connection.Open();

                // exectute the select query
                SqlDataReader reader = selectCommand.ExecuteReader();

                // process the result if any
                while (reader.Read()) // if there is an oerder
                {
                    Packages_Products_Suppliers prod = new Packages_Products_Suppliers();
                    prod.PackageId         = Convert.ToInt32(reader["PackageId"]);
                    prod.ProductId         = Convert.ToInt32(reader["ProductId"]);
                    prod.ProductName       = Convert.ToString(reader["ProdName"]);
                    prod.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
                    prod.SupplierId        = Convert.ToInt32(reader["SupplierId"]);
                    prods.Add(prod);
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form deal with it
            }
            finally
            {
                // close the connection not matter what
                connection.Close();
            }
            return(prods);
        }
Example #14
0
        // retrieves the order with given id
        public static Product GetProduct(int ProductId)
        {
            Product prod = null; // order found

            // define the connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // define the select query command
            string selectQuery = "select ProductId, ProdName " +
                                 "from products " +
                                 "where ProductId = @ProductId";
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            selectCommand.Parameters.AddWithValue("@ProductId", ProductId);
            try
            {
                // open the connection
                connection.Open();

                // exectute the select query
                SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);

                // process the result if any
                if (reader.Read()) // if there is an oerder
                {
                    prod           = new Product();
                    prod.ProductId = (int)reader["ProductId"];
                    prod.ProdName  = reader["ProdName"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form deal with it
            }
            finally
            {
                // close the connection not matter what
                connection.Close();
            }

            return(prod);
        }
Example #15
0
        // retrieves the supplier with given id
        public static Supplier GetSupplier(int SupplierId)
        {
            Supplier sup = null; // supplier found

            // define the connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // define the select query command
            string selectQuery = "select SupplierId, SupName " +
                                 "from suppliers " +
                                 "where SupplierId = @SupplierId";
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            selectCommand.Parameters.AddWithValue("@SupplierId", SupplierId);
            try
            {
                // open the connection
                connection.Open();

                // exectute the select query
                SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);

                // process the result if any
                if (reader.Read()) // if there is a supplier
                {
                    sup            = new Supplier();
                    sup.SupplierId = (int)reader["SupplierId"];
                    sup.SupName    = reader["SupName"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form deal with it
            }
            finally
            {
                // close the connection not matter what
                connection.Close();
            }

            return(sup);
        }
Example #16
0
        public static List <Product> GetProducts()
        {
            List <Product> prods = new List <Product>(); // product found

            // define the connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // define the select query command
            string selectQuery = "select ProductId, ProdName " +
                                 "from products";
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            try
            {
                // open the connection
                connection.Open();

                // exectute the select query
                SqlDataReader reader = selectCommand.ExecuteReader();

                // process the result if any
                while (reader.Read()) // if there is an oerder
                {
                    Product prod = new Product();
                    prod.ProductId = Convert.ToInt32(reader["ProductId"]);
                    prod.ProdName  = reader["ProdName"].ToString();
                    prods.Add(prod);
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form deal with it
            }
            finally
            {
                // close the connection not matter what
                connection.Close();
            }
            return(prods);
        }
Example #17
0
        public static List <Supplier> GetSuppliers()
        {
            List <Supplier> sups = new List <Supplier>(); // supplier found

            // define the connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // define the select query command
            string selectQuery = "select SupplierId, SupName " +
                                 "from suppliers";
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            try
            {
                // open the connection
                connection.Open();

                // exectute the select query
                SqlDataReader reader = selectCommand.ExecuteReader();

                // process the result if any
                while (reader.Read()) // if there is a supplier
                {
                    Supplier sup = new Supplier();
                    sup.SupplierId = Convert.ToInt32(reader["SupplierId"]);
                    sup.SupName    = reader["SupName"].ToString();
                    sups.Add(sup);
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form deal with it
            }
            finally
            {
                // close the connection not matter what
                connection.Close();
            }
            return(sups);
        }
Example #18
0
        // insert order
        public static int AddProduct(Product prod) // returns the generate order id
        {
            int prodId = 0;

            // prepare connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // prepare the statement
            string insertString = "insert into Products " +
                                  "(ProdName) " +
                                  "values(@ProdName)";
            SqlCommand insertCommand = new SqlCommand(insertString, connection);

            insertCommand.Parameters.AddWithValue("@ProductId", prod.ProductId);
            insertCommand.Parameters.AddWithValue("@ProdName", prod.ProdName);

            try
            {
                // open connection
                connection.Open();

                // execute the statement
                int i = insertCommand.ExecuteNonQuery();

                if (i == 1) // one record is inserted
                {
                }
            }
            catch (Exception ex)
            {
                throw ex; // pass the buck
            }
            finally
            {
                connection.Close();
            }
            return(prodId);
        }
Example #19
0
        // updates existing suppliers record and returns bool success flag
        public static bool UpdateSupplier(Supplier oldSupplier, Supplier newSupplier)
        {
            bool          successful   = false;
            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        updateString = "update Suppliers set " +
                                                    // "SupplierId = @NewSupplierId, " +
                                         "SupName = @NewSupName " +
                                         "where " + // update succeeds only if record not changed by other users
                                                    //  "SupplierId = @OldSupplierId and " +
                                         "SupName = @OldSupName";
            SqlCommand updateCommand = new SqlCommand(updateString, connection);

            //  updateCommand.Parameters.AddWithValue("@OldSupplierId", oldSupplier.SupplierId);
            updateCommand.Parameters.AddWithValue("@OldSupName", oldSupplier.SupName);

            //     updateCommand.Parameters.AddWithValue("@NewSupplierId", newSupplier.SupplierId);
            updateCommand.Parameters.AddWithValue("@NewSupName", newSupplier.SupName);

            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(successful);
        }
Example #20
0
        //method to delete Alex
        public static bool Delete(package psup, ProductsSuppliers pps)
        {
            bool successful = false;

            SqlConnection connection   = TRAVELEXPERTSDB.GetConnection();
            string        deleteString = "delete from Packages_Products_Suppliers where PackageId = @PackageId and ProductSupplierId=@ProductSupplierId";

            SqlCommand deleteCommand = new SqlCommand(deleteString, connection);

            deleteCommand.Parameters.AddWithValue("@PackageId", psup.PackageId);
            deleteCommand.Parameters.AddWithValue("@ProductSupplierId", pps.ProductSupplierId);

            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
                else
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
                //throw ex;
                MessageBox.Show("Delete supplier from other tables first");
            }
            finally
            {
                connection.Close();
            }

            return(successful);
        }
        public static int AddProductSupplier(ProductsSuppliers productsupplier)
        {
            int prodID = 0;
            // prepare connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // prepare the statement
            string     insertString  = "insert into Products_Suppliers(ProductId, SupplierId) Values(@ProductId, @SupplierId)";
            SqlCommand insertCommand = new SqlCommand(insertString, connection);

            insertCommand.Parameters.AddWithValue("@ProductId", productsupplier.ProductId);
            insertCommand.Parameters.AddWithValue("@SupplierId", productsupplier.SupplierId);
            try
            {
                // open connection
                connection.Open();

                // execute the statement
                int i = insertCommand.ExecuteNonQuery();
                if (i == 1) // one record inserted
                {
                    // retrieve customer id from the added record
                    string     selectString  = "select ident_current('Products_Suppliers') from Products_Suppliers";
                    SqlCommand selectCommand = new SqlCommand(selectString, connection);
                    prodID = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!!
                }
            }
            catch (Exception ex)
            {
                throw ex; // pass the buck
            }
            finally
            {
                connection.Close();
            }
            return(prodID);
        }
Example #22
0
        // insert supplier
        public static int AddSupplier(Supplier sup) // returns the generate supplier id
        {
            // prepare connection
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // prepare the statement
            string insertString = "insert into Suppliers " +
                                  "(SupName, SupplierId) " +
                                  "values(@SupName, @SupplierId)";
            SqlCommand insertCommand = new SqlCommand(insertString, connection);

            insertCommand.Parameters.AddWithValue("@SupplierId", sup.SupplierId);
            insertCommand.Parameters.AddWithValue("@SupName", sup.SupName);

            try
            {
                // open connection
                connection.Open();

                // execute the statement
                int i = insertCommand.ExecuteNonQuery();

                if (i == 1) // one record is inserted
                {
                }
            }
            catch (Exception ex)
            {
                throw ex; // pass the buck
            }
            finally
            {
                connection.Close();
            }
            return(3);
        }
Example #23
0
        //the get order method returns an individual order object defined by the OrderID passed
        //by order form

        public static package GetPackage(int PackageID)
        {
            // reference to new (null) order object
            package pkg = null;

            //create connection to Northwinds database
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            // create select command that pulls specific values from Orders table for 1 Order
            string selectQuery = "select * " +
                                 "from Packages " +
                                 "where PackageId = @PackageId";

            //Represents a stored procedure to execute against a SQL Server database
            //info from website:   msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            //use the store procedure to select the order using the parameter OrderID passed from the main form
            selectCommand.Parameters.AddWithValue("@PackageId", PackageID);

            try
            {
                // open connection
                connection.Open();

                //DataReader provides a way of reading a forward-only stream of rows from a SQL Server database
                //Info from msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx
                SqlDataReader reader = selectCommand.ExecuteReader();


                //this if statements processes one row at a time
                if (reader.Read())
                {
                    //each row is a new object during importing
                    pkg = new package();

                    //define the OrderID and CustomerID of thew new object by using the reader
                    pkg.PackageId = (int)reader["PackageId"];
                    pkg.PkgName   = (string)reader["PkgName"];

                    DateTime tripEnd = (DateTime)reader["PkgEndDate"];
                    pkg.PkgEndDate = tripEnd.ToString("d");

                    DateTime tripStart = (DateTime)reader["PkgStartDate"];
                    pkg.PkgStartDate = tripStart.ToString("d");


                    pkg.PkgDesc = (string)reader["PkgDesc"];
                    decimal cost = (decimal)reader["PkgAgencyCommission"];
                    pkg.PkgAgencyCommission = cost.ToString("C");

                    decimal price = (decimal)reader["PkgBasePrice"];
                    pkg.PkgBasePrice = price.ToString("C");
                    //pkg.Products = (string)reader["Products"];
                }

                //this catches the null exceptions on import
            }
            catch (Exception ex)
            {
                // throw exception to the form to handle
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            //if no exceptions return the imported order object
            return(pkg);
        }
Example #24
0
        public static bool UpdatePackage(package oldPackage, package newPackage)
        {
            bool          successful = false;
            SqlConnection connection = TRAVELEXPERTSDB.GetConnection();

            string updateString = "update packages set " +
                                  //   "PackageId = @newPackageId, " +
                                  "PkgName = @newPkgName, " +
                                  "PkgStartDate = @newPkgStartDate, " +
                                  "PkgEndDate = @newPkgEndDate, " +
                                  "PkgDesc = @newPkgDesc, " +
                                  "PkgBasePrice = @newPkgBasePrice, " +
                                  "PkgAgencyCommission = @newPkgAgencyCommission " +
                                             //"Products = @newProducts " +
                                  "where " + // update succeeds only if record not changed by other users
                                  "PackageId = @oldPackageId and " +
                                  "PkgName = @oldPkgName and " +
                                  "PkgStartDate = @oldPkgStartDate and " +
                                  "PkgEndDate = @oldPkgEndDate and " +
                                  "PkgDesc = @oldPkgDesc and " +
                                  "PkgBasePrice = @oldPkgBasePrice and " +
                                  "PkgAgencyCommission = @oldPkgAgencyCommission";
            //  "Products = @oldProducts";

            SqlCommand updateCommand = new SqlCommand(updateString, connection);

            //  updateCommand.Parameters.AddWithValue("@oldPackageId", oldPackage.PackageId);
            updateCommand.Parameters.AddWithValue("@oldPkgName", oldPackage.PkgName);
            updateCommand.Parameters.AddWithValue("@oldPkgStartDate", oldPackage.PkgStartDate);
            updateCommand.Parameters.AddWithValue("@oldPkgEndDate", oldPackage.PkgEndDate);
            updateCommand.Parameters.AddWithValue("@oldPkgDesc", oldPackage.PkgDesc);
            updateCommand.Parameters.AddWithValue("@oldPkgBasePrice", oldPackage.PkgBasePrice);
            updateCommand.Parameters.AddWithValue("@oldPkgAgencyCommission", oldPackage.PkgAgencyCommission);
            // updateCommand.Parameters.AddWithValue("@oldProducts", oldPackage.Products);

            // updateCommand.Parameters.AddWithValue("@newPackageId", newPackage.PackageId);
            updateCommand.Parameters.AddWithValue("@newPkgName", newPackage.PkgName);
            updateCommand.Parameters.AddWithValue("@newPkgStartDate", newPackage.PkgStartDate);
            updateCommand.Parameters.AddWithValue("@newPkgEndDate", newPackage.PkgEndDate);
            updateCommand.Parameters.AddWithValue("@newPkgDesc", newPackage.PkgDesc);
            updateCommand.Parameters.AddWithValue("@newPkgBasePrice", newPackage.PkgBasePrice);
            updateCommand.Parameters.AddWithValue("@newPkgAgencyCommission", newPackage.PkgAgencyCommission);
            //updateCommand.Parameters.AddWithValue("@newProducts", newPackage.Products);
            updateCommand.Parameters.AddWithValue("@OldPackageId", oldPackage.PackageId);

            //     updateCommand.Parameters.AddWithValue("@NewProductId", newProduct.ProductId);
            updateCommand.Parameters.AddWithValue("@NewPackageId", newPackage.PackageId);


            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(successful);
        }