コード例 #1
0
ファイル: PackageDBOLD.cs プロジェクト: dostap/OOSD_project2
 public static int AddPackage(Package package)
 {
     SqlConnection connection = TravelExpertsDB.GetConnection();
     string insertStatement =
         "INSERT INTO Packages " +
         // "(PackageId,
        "(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
         "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
     // SqL INSERT query saved in a string
     SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
     //create and add values to parameters of the SqlCommand object (insertCommand)
     insertCommand.Parameters.AddWithValue(
         "@PkgName", package.PkgName);
     insertCommand.Parameters.AddWithValue(
         "@PkgStartDate", package.PkgStartDate);
     insertCommand.Parameters.AddWithValue(
         "@PkgEndDate", package.PkgEndDate);
     insertCommand.Parameters.AddWithValue(
         "@PkgDesc", package.PkgDesc);
     insertCommand.Parameters.AddWithValue(
         "@PkgBasePrice", package.PkgBasePrice);
     insertCommand.Parameters.AddWithValue(
         "@PkgAgencyCommission", package.PkgAgencyCommission);
     try
     {
         connection.Open();
         insertCommand.ExecuteNonQuery(); //Executes a Transact-SQL statement
         string selectStatement =
             "SELECT IDENT_CURRENT('Packages') FROM Packages";
         SqlCommand selectCommand =
            new SqlCommand(selectStatement, connection);
         int packageId = Convert.ToInt32(selectCommand.ExecuteScalar());
         return packageId;
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         connection.Close();
     }
 }
コード例 #2
0
ファイル: PackageDBOLD.cs プロジェクト: dostap/OOSD_project2
 public static bool DeletePackage(Package package)
 {
     SqlConnection connection = TravelExpertsDB.GetConnection();
     string deleteStatement =
         "DELETE FROM Packages " +
         "WHERE PackageId = @PackageId " +
         "AND PkgName = @PkgName " +
         "AND PkgStartDate = @PkgStartDate " +
         "AND PkgEndDate = @PkgEndDate " +
         "AND PkgDesc = @PkgDesc " +
         "AND PkgBasePrice = @PkgBasePrice " +
         "AND PkgAgencyCommission = @PkgAgencyCommission";
     SqlCommand deleteCommand =
         new SqlCommand(deleteStatement, connection);
     deleteCommand.Parameters.AddWithValue("@PackageId", package.PackageId);
     deleteCommand.Parameters.AddWithValue("@PkgName", package.PkgName);
     deleteCommand.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
     deleteCommand.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
     deleteCommand.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
     deleteCommand.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
     deleteCommand.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
     try
     {
         connection.Open();
         int count = deleteCommand.ExecuteNonQuery(); //works the same way as modifying
         //gives error when there is a concurrency issue
         if (count > 0)
             return true;
         else
             return false;
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         connection.Close();
     }
 }
コード例 #3
0
ファイル: frmPackage.cs プロジェクト: dostap/OOSD_project2
 private void btnAdd_Click(object sender, EventArgs e)
 {
     // open the add/modify form in the add mode
     frmPackageDataChange addPackageForm = new frmPackageDataChange();
     addPackageForm.addMode = true;
     DialogResult result = addPackageForm.ShowDialog();
     if (result == DialogResult.OK)
     {
         // copy the newly added package from the add/modify form to this form and display the package data
         lstSuppliersProducts.DataSource = null;
         package = addPackageForm.package;
         this.DisplayPackage();
     }
 }
コード例 #4
0
ファイル: frmPackage.cs プロジェクト: dostap/OOSD_project2
        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (Validator.IsNotEmpty(txtPackageId))
            {
                try
                {
                    // call the GetPackage method which will search for the package by its id and retrieve it to package variable
                    package = PackageDB.GetPackage(Convert.ToInt32(txtPackageId.Text));
                    if (package == null)
                    {
                        MessageBox.Show("No package found with this code, please try again.", "Package Not Found");
                        this.ClearControls();
                    }
                    else
                    {
                        // if a package found, displays the data and fill the grid with the suppliers-product of that package
                        this.DisplayPackage();
                        FillProductList();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, ex.GetType().ToString());
                }

            }
        }
コード例 #5
0
ファイル: frmPackage.cs プロジェクト: dostap/OOSD_project2
 private void btnModify_Click(object sender, EventArgs e)
 {
     // open the add/modify form in the modify mode
     frmPackageDataChange modifyPackageForm = new frmPackageDataChange();
     modifyPackageForm.addMode = false;
     modifyPackageForm.package = package;
     DialogResult result = modifyPackageForm.ShowDialog();
     if (result == DialogResult.OK)
     {
         // copy the modified package from the add/modify form to this form and display the package data after modification
         package = modifyPackageForm.package;
         this.DisplayPackage();
     }
     else if (result == DialogResult.Retry)
     {
         btnSearch_Click(sender, e);
         if (package != null) this.DisplayPackage();
         else this.ClearControls();
     }
 }
コード例 #6
0
ファイル: PackageDBOLD.cs プロジェクト: dostap/OOSD_project2
        public static Package GetPackage(int packageId)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection(); //connect to the database
            string selectStatement =
                "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
                "FROM Packages " +
                "WHERE PackageId = @PackageId"; // declare string carrying a query
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
            //created new SqlCommand object that will execute the select statement we saved in the string
            selectCommand.Parameters.AddWithValue("@PackageId", packageId);
            //add value to the parameters

            try
            {
                connection.Open();

                SqlDataReader reader =
                    selectCommand.ExecuteReader(CommandBehavior.SingleRow);

                if (reader.Read())
                //method returns true as long as there is a row to read
                {
                    //now we process the row
                    Package package = new Package(); // create a new instance of Package class
                    package.PackageId = (int)reader["PackageId"];
                    package.PkgName = reader["PkgName"].ToString();
                    package.PkgStartDate = (DateTime)reader["PkgStartDate"];
                    package.PkgEndDate = (DateTime)reader["PkgEndDate"];
                    package.PkgDesc = reader["PkgDesc"].ToString();
                    package.PkgBasePrice = Convert.ToDouble(reader["PkgBasePrice"]);
                    package.PkgAgencyCommission = Convert.ToDouble(reader["PkgAgencyCommission"]);

                    return package; //return populated object to the calling emthod
                }
                else // now row = no package
                {
                    return null;
                }
            }
            catch (SqlException ex)//throws exception to the calling method if SqlException occurs
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
コード例 #7
0
ファイル: PackageDBOLD.cs プロジェクト: dostap/OOSD_project2
        public static bool EditPackage(Package oldPackage, Package newPackage)
        {
            SqlConnection connection = TravelExpertsDB.GetConnection();
            string updateStatement =
                "UPDATE Packages SET " +
                "PkgName = @newPkgName, " +
                "PkgStartDate = @newPkgStartDate, " +
                "PkgEndDate = @newPkgEndDate, " +
                "PkgDesc = @newPkgDesc, " +
                "PkgBasePrice = @newPkgBasePrice, " +
                "PkgAgencyCommission = @newPkgAgencyCommission " +
                //compare to the original package
                "WHERE " +
                "PkgName = @oldPkgName " +
                "AND PkgStartDate = @oldPkgStartDate " +
                "AND PkgEndDate = @oldPkgEndDate " +
                "AND PkgDesc = @oldPkgDesc " +
                "AND PkgBasePrice = @oldPkgBasePrice " +
                "AND PkgAgencyCommission = @oldPkgAgencyCommission";

            //new update query
            //where statemenet will ensure that package will only be modified
            //if it has not been changed by another user in the meantine
            SqlCommand updateCommand =
                new SqlCommand(updateStatement, connection);
            //add values to all parameters used
            //set newPackage parameters
            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);
            //set oldPackage parameters
            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);
            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();//returns number of rows affected to count
                if (count > 0) //if count is more than zero, means query was successful, return true to calling emthod
                    return true;
                else // if now rows were affected, return false to calling method
                    //this will go back to frmAddModifyPackage and display an error message that
                    //another user has modified row in the meantime
                    return false;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }