public static int Delete(TravelPackage package)
{
//Delete all related Package_Product_Suppliers
foreach (PackageProdSupplier pps in package.ProductsAndSuppliers)
{
if (PackageProdSuppDB.Delete(pps) < 1)
{
throw new Exception("Error deleting Package Product/Supplier");
}
}
int deletedPkgs = 0;
SqlConnection conn = TravelExpertsDB.GetConnection();
string sqlQuery = "DELETE FROM Packages " +
"WHERE PackageId = @pkgID AND PkgName = @Name " +
"AND PkgStartDate = @start AND PkgEndDate = @end " +
"AND PkgDesc = @desc AND PkgBasePrice = @price " +
"AND PkgAgencyCommission = @commission";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
//TODO: Account for null values
cmd.Parameters.AddWithValue("@pkgID", package.ID);
cmd.Parameters.AddWithValue("@name", package.Name);
// cmd.Parameters.AddWithValue("@start", package.StartDate); //Nullable
cmd.Parameters.AddWithValue("@start", package.StartDate == null ? (object)DBNull.Value : package.StartDate);
cmd.Parameters.AddWithValue("@end", package.EndDate); //Nullable
cmd.Parameters.AddWithValue("@desc", package.Description ?? (object)DBNull.Value); //Nullable
cmd.Parameters.AddWithValue("@price", package.BasePrice);
cmd.Parameters.AddWithValue("@commission", package.Commission ?? (object)DBNull.Value); //Nullable
try
{
conn.Open();
deletedPkgs += cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(deletedPkgs);
}
public static void Update(TravelPackage oldPkg, TravelPackage newPkg)
{
if (oldPkg.ID != newPkg.ID)
{
throw new ArgumentException("ID mismatch between old package and new package");
}
SqlConnection conn = TravelExpertsDB.GetConnection();
string updStmt = "UPDATE Packages " +
"SET PkgName = @name, " +
"PkgStartDate = @start, " +
"PkgEndDate = @end, " +
"PkgDesc = @desc, " +
"PkgBasePrice = @price, " +
"PkgAgencyCommission = @commiss " +
"WHERE PackageId = @id";
SqlCommand cmd = new SqlCommand(updStmt, conn);
cmd.Parameters.AddWithValue("@name", newPkg.Name);
cmd.Parameters.AddWithValue("@desc", newPkg.Description ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@start", newPkg.StartDate);
cmd.Parameters.AddWithValue("@end", newPkg.EndDate);
cmd.Parameters.AddWithValue("@price", newPkg.BasePrice);
cmd.Parameters.AddWithValue("@commiss", newPkg.Commission ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@id", oldPkg.ID);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error while updating database: " + ex.Message);
}
finally
{
conn.Close();
}
}
public static TravelPackage Insert(TravelPackage newPkg)
{
SqlConnection conn = TravelExpertsDB.GetConnection();
string columns = "PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission";
string values = "@name, @start, @end, @desc, @price, @commiss";
string insStmt = "INSERT INTO Packages (" + columns + ") VALUES (" + values + ")";
SqlCommand cmd = new SqlCommand(insStmt, conn);
cmd.Parameters.AddWithValue("@name", newPkg.Name);
cmd.Parameters.AddWithValue("@start", newPkg.StartDate);
cmd.Parameters.AddWithValue("@end", newPkg.EndDate);
cmd.Parameters.AddWithValue("@desc", newPkg.Description ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@price", newPkg.BasePrice);
cmd.Parameters.AddWithValue("@commiss", newPkg.Commission ?? (object)DBNull.Value);
try
{
conn.Open();
cmd.ExecuteNonQuery();
//Retreive package ID
string selStmt = "SELECT IDENT_CURRENT('Packages') FROM Packages";
SqlCommand selCmd = new SqlCommand(selStmt, conn);
newPkg.ID = Convert.ToInt32(selCmd.ExecuteScalar());
}
catch (Exception ex)
{
MessageBox.Show("Error while inserting into database: " + ex.Message);
newPkg.ID = -1;
}
finally
{
conn.Close();
}
return(newPkg);
}
public static List <NamedPackageProductSupplier> GetPackageProductSuppliersByPackage(TravelPackage pkg)
{
return(GetPackageProductSuppliersByPackage(pkg.ID));
}