public static void GetObjectListFromDB(out List <Product> products, Package target)
{
products = new List <Product>();
using (SqlConnection dbConnect = TravelExpertsDB.GetConnection()) {
dbConnect.Open();
string query = "SELECT products.ProductId,ProdName FROM Packages_Products_Suppliers,Packages,Products,Products_Suppliers WHERE(Packages_Products_Suppliers.PackageId = Packages.PackageId) and(Packages_Products_Suppliers.ProductSupplierId = Products_Suppliers.ProductSupplierId) and(Products_Suppliers.ProductId = Products.ProductId) and(Packages.PackageId = " + target.PackageId + "); ";
try {
using (SqlCommand cmd = new SqlCommand(query, dbConnect)) {
//run command and process results
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read())
{
Product o = new Product();
{ ReadFromDB(reader, "ProductId", out int output); o.ProductID = output; }
{ ReadFromDB(reader, "ProdName", out string output); o.ProdName = output; }
products.Add(o);
}
}
}
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
dbConnect.Close();
}
}
public static void GetObjectListFromDB(out List <Package> packages)
{
packages = new List <Package>();
using (SqlConnection dbConnect = TravelExpertsDB.GetConnection()) {
dbConnect.Open();
string query = "select * from packages";
using (SqlCommand cmd = new SqlCommand(query, dbConnect)) {
//run command and process results
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read())
{
Package o = new Package();
{ ReadFromDB(reader, "PackageId", out int output); o.PackageId = output; }
{ ReadFromDB(reader, "PkgName", out string output); o.PkgName = output; }
{ ReadFromDB(reader, "PkgAgencyCommission", out decimal output); o.PkgAgencyCommission = output; }
{ ReadFromDB(reader, "PkgBasePrice", out decimal output); o.PkgBasePrice = output; }
{ ReadFromDB(reader, "PkgDesc", out string output); o.PkgDesc = output; }
{ ReadFromDB(reader, "PkgEndDate", out DateTime output); o.PkgEndDate = output; }
{ ReadFromDB(reader, "PkgStartDate", out DateTime output); o.PkgStartDate = output; }
// { GetObjectListFromDB(out BindingList<Product> output, o); o.ProductsList = output; }
{ GetTableFromDB(out DataTable output, o); o.ProductsTable = output; }
packages.Add(o);
}
}
}
dbConnect.Close();
}
}
//to get the data from database
public static Package GetPacks(int packID)
{
Package pack = null;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT PackageId,PkgName,PkgStartDate,PkgEndDate,PkgDesc,PkgBasePrice,PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId= @PackageId ";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@PackageId", packID);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.Read()) // if there is data
{
pack = new Package();
pack.PackageId = (int)reader["PackageId"];
pack.PkgName = Convert.ToString(reader["PkgName"]);
pack.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
pack.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
pack.PkgDesc = reader["PkgDesc"].ToString();
pack.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
pack.PkgAgencyCommission = Convert.ToDecimal(reader["PkgAgencyCommission"]);
}
}
}
}
return(pack);
}
//To fill the combo box
public static List <Package> GetPackage()
{
List <Package> pack = new List <Package>(); // an empty list
Package pk; // auxiliary for reading
//create connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create command
string query = "SELECT PackageId,PkgName " +
"FROM Packages " +
"ORDER BY PkgName";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
// run the command and process results
connection.Open();
using (SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
// process next record from data reader
pk = new Package();
pk.PackageId = Convert.ToInt32(reader["PackageId"]);
pk.PkgName = Convert.ToString(reader["PkgName"]);
pack.Add(pk);
}
} // closes reader & recycles object
} // cmd object recycled
} // connection object recycled
return(pack);
}
/*
* The get ObjectListFromDB function overlaod all serve to accept an object list from the caller and
* fill that list from the DB with the appropriate data from the table, they handle the queries and
* data creation based on the inputed list type;
*/
public static void GetObjectListFromDB(out List <Product> products)
{
products = new List <Product>();
using (SqlConnection dbConnect = TravelExpertsDB.GetConnection()) {
dbConnect.Open();
string query = "select * from products";
try {
using (SqlCommand cmd = new SqlCommand(query, dbConnect)) {
//run command and process results
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read())
{
Product o = new Product();
{ ReadFromDB(reader, "ProductId", out int output); o.ProductID = output; }
{ ReadFromDB(reader, "ProdName", out string output); o.ProdName = output; }
products.Add(o);
}
}
}
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
dbConnect.Close();
}
}
//to add new packages to the database
private void btnSubmit_Click(object sender, EventArgs e)
{
package = new Packages();
this.PutPackage(package);
try
{
package.PackageId = TravelExpertsDB.AddPackage(package);
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
Application.Restart();//You have to reload the form when you submit
}
/*
* The get ObjectListFromDB function overlaod all serve to accept an object list from the caller and
* fill that list from the DB with the appropriate data from the table, they handle the queries and
* data creation based on the inputed list type;
*/
public static void GetTableFromDB(out DataTable products, Package target)
{
using (SqlConnection dbConnect = TravelExpertsDB.GetConnection()) {
dbConnect.Open();
products = new DataTable();
string query = "SELECT products.ProductId,ProdName FROM Packages_Products_Suppliers,Packages,Products,Products_Suppliers WHERE(Packages_Products_Suppliers.PackageId = Packages.PackageId) and(Packages_Products_Suppliers.ProductSupplierId = Products_Suppliers.ProductSupplierId) and(Products_Suppliers.ProductId = Products.ProductId) and(Packages.PackageId = " + target.PackageId + "); ";
using (SqlCommand cmd = new SqlCommand(query, dbConnect)) {
//run command and process results
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) {
adapter.Fill(products);
}
}
dbConnect.Close();
}
}
public static int AddSuppliers(Suppliers suppliers)
{
int SupplierId = -1;
using (SqlConnection conn = TravelExpertsDB.GetConnection())
{
string Query = "INSERT INTO Suppliers(SupName) " +
"OUTPUT inserted.SupplierId " +
"VALUES(@SupName)";
using (SqlCommand cmd = new SqlCommand(Query, conn))
{
cmd.Parameters.AddWithValue("@SupName", suppliers.SupName);
conn.Open();
SupplierId = (int)cmd.ExecuteScalar();
}
}
return(SupplierId);
}
public static int AddProduct(Products prod)
{
int ProductId = -1;
using (SqlConnection conn = TravelExpertsDB.GetConnection())
{
string Query = "INSERT INTO Products(ProdName) " +
" OUTPUT inserted.ProductId " +
"VALUES(@ProdName)";
using (SqlCommand cmd = new SqlCommand(Query, conn))
{
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
conn.Open();
ProductId = (int)cmd.ExecuteScalar();
}
}
return(ProductId);
}
public static bool UpdatePackage(Package oldPack, Package newPack)
{
int count; // how many rows updated
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string updateStatement =
"UPDATE Packages SET " +
" PkgName = @newPkgName, " +
" PkgStartDate = @newPkgStartDate, " +
" PkgEndDate = @newPkgEndDate, " +
" PkgDesc = @newPkgDesc, " +
" PkgBasePrice = @newPkgBasePrice, " +
" PkgAgencyCommission = @newPkgAgencyCommission " +
" WHERE PackageId = @oldPackageId " +
" AND PkgName = @oldPkgName " +
" AND PkgStartDate = @oldPkgStartDate " +
" AND PkgEndDate = @oldPkgEndDate " +
" AND PkgDesc = @oldPkgDesc " +
" AND PkgBasePrice = @oldPkgBasePrice " +
" AND PkgAgencyCommission = @oldPkgAgencyCommission ";
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
cmd.Parameters.AddWithValue("@newPkgName", newPack.PkgName);
cmd.Parameters.AddWithValue("@newPkgStartDate", newPack.PkgStartDate);
cmd.Parameters.AddWithValue("@newPkgEndDate", newPack.PkgEndDate);
cmd.Parameters.AddWithValue("@newPkgDesc", newPack.PkgDesc);
cmd.Parameters.AddWithValue("@newPkgBasePrice", newPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@newPkgAgencyCommission", newPack.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@oldPackageId", oldPack.PackageId);
cmd.Parameters.AddWithValue("@oldPkgName", oldPack.PkgName);
cmd.Parameters.AddWithValue("@oldPkgStartDate", oldPack.PkgStartDate);
cmd.Parameters.AddWithValue("@oldPkgEndDate", oldPack.PkgEndDate);
cmd.Parameters.AddWithValue("@oldPkgDesc", oldPack.PkgDesc);
cmd.Parameters.AddWithValue("@oldPkgBasePrice", oldPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@oldPkgAgencyCommission", oldPack.PkgAgencyCommission);
connection.Open();
count = cmd.ExecuteNonQuery(); // returns how many rows updated
}
}
return(count > 0);
}
public static int AddPackage(Packages pack)
{
int pacakgeId = -1;
using (SqlConnection conn = TravelExpertsDB.GetConnection()) {
string Query = "INSERT INTO Packages(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
" OUTPUT inserted.PackageId " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
using (SqlCommand cmd = new SqlCommand(Query, conn)) {
cmd.Parameters.AddWithValue("@PkgName", pack.PkgName);
cmd.Parameters.AddWithValue("@PkgStartDate", pack.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", pack.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", pack.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", pack.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", pack.PkgAgencyCommission);
conn.Open();
pacakgeId = (int)cmd.ExecuteScalar();
}
}
return(pacakgeId);
}
private void btnSubmit_Click(object sender, EventArgs e)
{
if (Validator.IsPresent(txtName, "Product Name") == true)
{
prod = new Products();
this.PutProducts(prod);
try
{
prod.ProductId = TravelExpertsDB.AddProduct(prod);
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
Application.Restart();
}
else
{
ErrorBox.Visible = true;
lblError.Text = "Please insert a valid Product Name";
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{ //brandons validations
if (Validator.IsPresent(txtPkgName, "Package Name") == true &&
dtpStart.Value < dtpEnd.Value &&
dtpStart.Value != dtpEnd.Value &&
Validator.IsPresent(txtDesc, "Description") == true &&
Validator.IsDecimal(txtBase, "Base Price") == true && Validator.IsNonNegativeDecimal(txtBase, "Base Price") == true &&
Validator.IsDecimal(txtAgency, "Agency Commission") == true && Validator.IsNonNegativeDecimal(txtAgency, "Agency Commission") == true &&
Convert.ToDecimal(txtBase.Text) > Convert.ToDecimal(txtAgency.Text)
)//Everything is valid
{
//Neels Code
Packages newPack = new Packages();
newPack.PackageId = package.PackageId;
this.PutPackageData(newPack);
try
{
if (!TravelExpertsDB.UpdatePackage(package, newPack))
{
MessageBox.Show("Another user has updated or " +
"deleted that customer.", "Database Error");
this.DialogResult = DialogResult.Retry;
}
else // success
{
package = newPack;
this.DialogResult = DialogResult.OK;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
Application.Restart();//You have to reload the form when you submit
}
else //go threw each box and tell its not valid by BRANDON
{
if (Validator.IsPresent(txtPkgName, "Package Name") == false)//Package Name Error
{
lblErrorNameMassage.Text = "Package Name is Required";
lblErrorPackageName.Visible = true;
}
else
{
lblErrorPackageName.Visible = false;
lblErrorNameMassage.Text = "";
}
////////////////////////////////////////////////////////////////
if (dtpStart.Value > dtpEnd.Value) //Date Error Start is > End
{
lblDateError.Text = "Start Date must be after End Date";
lblErrorStart.Visible = true;
lblErrorEnd.Visible = true;
}
else if (dtpStart.Value == dtpEnd.Value)//Date Error Start is = End
{
lblDateError.Text = "Start Date cannot be on End Date";
lblErrorStart.Visible = true;
lblErrorEnd.Visible = true;
}
else
{
lblDateError.Text = "";
lblErrorStart.Visible = false;
lblErrorEnd.Visible = false;
}
////////////////////////////////////////////////////////////////
if (Validator.IsPresent(txtDesc, "Description") == false)//Description Error
{
lblDescError.Text = "Please insert a description";
lblErrorPackageDesc.Visible = true;
}
else
{
lblDescError.Text = "";
lblErrorPackageDesc.Visible = false;
}
////////////////////////////////////////////////////////////////
if (Validator.IsDecimal(txtBase, "Base Price") == false) //Base Price Error
{
lblErrorBasePrice.Visible = true;
lblBaseError.Text = "Please insert a number that is greater than zero";
}
else if (Validator.IsDecimal(txtBase, "Base Price") == true && Validator.IsNonNegativeDecimal(txtBase, "Base Price") == false)
{
lblErrorBasePrice.Visible = true;
lblBaseError.Text = "Number inserted must be greater than zero";
}
else
{
lblErrorBasePrice.Visible = false;
lblBaseError.Text = "";
}
////////////////////////////////////////////////////////////////
if (Validator.IsDecimal(txtAgency, "Agency Commission") == false) //commison error
{
lblErrorAgencyCommission.Visible = true;
lblAgencyError.Text = "Please insert a number that is greater than zero ";
}
else if (Validator.IsDecimal(txtAgency, "Agency Commission") == true && Validator.IsNonNegativeDecimal(txtAgency, "Agency Commission") == false)
{
lblErrorAgencyCommission.Visible = true;
lblAgencyError.Text = "Number inserted must be greater than zero";
}
else if (Validator.IsDecimal(txtAgency, "Agency Commission") == true &&
Validator.IsDecimal(txtBase, "Base Price") == true &&
Convert.ToDecimal(txtBase.Text) <= Convert.ToDecimal(txtAgency.Text))
{
lblErrorAgencyCommission.Visible = true;
lblErrorBasePrice.Visible = true;
lblAgencyError.Text = "Agency Commission cannot be greater than or equal to the Base price";
}
else
{
lblErrorAgencyCommission.Visible = false;
lblAgencyError.Text = "";
}
////////////////////////////////////////////////////////////////
}
/* */
}
//to add new packages to the database
private void btnSubmit_Click(object sender, EventArgs e)
{
if (Validator.IsPresent(txtName, "Package Name") == true &&
dtpStart.Value < dtpEnd.Value &&
dtpStart.Value != dtpEnd.Value &&
Validator.IsPresent(txtDesc, "Description") == true &&
Validator.IsDecimal(txtBase, "Base Price") == true && Validator.IsNonNegativeDecimal(txtBase, "Base Price") == true &&
Validator.IsDecimal(txtCommission, "Agency Commission") == true && Validator.IsNonNegativeDecimal(txtCommission, "Agency Commission") == true &&
Convert.ToDecimal(txtBase.Text) > Convert.ToDecimal(txtCommission.Text)) //Everything is valid
//Neels Code
{
package = new Packages();
this.PutPackage(package);
try
{
package.PackageId = TravelExpertsDB.AddPackage(package);
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
Application.Restart();//You have to reload the form when you submit
}
else //Brandons Code
{
if (Validator.IsPresent(txtName, "Package Name") == false)//Package Name Error
{
lblErrorName.Text = "Package Name is Required";
ErrorBoxPackageName.Visible = true;
}
else
{
ErrorBoxPackageName.Visible = false;
lblErrorName.Text = "";
}
if (dtpStart.Value > dtpEnd.Value) //Date Error Start is > End
{
lblErrorDate.Text = "Start Date must be after End Date";
ErrorBoxStart.Visible = true;
ErrorBoxEnd.Visible = true;
}
else if (dtpStart.Value == dtpEnd.Value)//Date Error Start is = End
{
lblErrorDate.Text = "Start Date cannot be on End Date";
ErrorBoxStart.Visible = true;
ErrorBoxEnd.Visible = true;
}
else
{
lblErrorDate.Text = "";
ErrorBoxStart.Visible = false;
ErrorBoxEnd.Visible = false;
}
if (Validator.IsPresent(txtDesc, "Description") == false)//Description Error
{
lblErrorDesc.Text = "Please insert a description";
ErrorBoxDescription.Visible = true;
}
else
{
lblErrorDesc.Text = "";
ErrorBoxDescription.Visible = false;
}
if (Validator.IsDecimal(txtBase, "Base Price") == false) //Base Price Error
{
ErrorBoxBasePrice.Visible = true;
lblErrorBasePrice.Text = "Please insert a number that is greater than zero";
}
else if (Validator.IsDecimal(txtBase, "Base Price") == true && Validator.IsNonNegativeDecimal(txtBase, "Base Price") == false)
{
ErrorBoxBasePrice.Visible = true;
lblErrorBasePrice.Text = "Number inserted must be greater than zero";
}
else
{
ErrorBoxBasePrice.Visible = false;
lblErrorBasePrice.Text = "";
}
if (Validator.IsDecimal(txtCommission, "Agency Commission") == false)//commison error
{
ErrorBoxAgency.Visible = true;
lblErrorAgency.Text = "Please insert a number that is greater than zero ";
}
else if (Validator.IsDecimal(txtCommission, "Agency Commission") == true && Validator.IsNonNegativeDecimal(txtCommission, "Agency Commission") == false)
{
ErrorBoxAgency.Visible = true;
lblErrorAgency.Text = "Number inserted must be greater than zero";
}
else if (Validator.IsDecimal(txtCommission, "Agency Commission") == true &&
Validator.IsDecimal(txtBase, "Base Price") == true &&
Convert.ToDecimal(txtBase.Text) <= Convert.ToDecimal(txtCommission.Text))
{
ErrorBoxAgency.Visible = true;
ErrorBoxBasePrice.Visible = true;
lblErrorAgency.Text = "Agency Commission cannot be greater than or equal to the Base price";
}
else
{
ErrorBoxAgency.Visible = false;
lblErrorAgency.Text = "";
}
}
/*
*
*/
}
