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