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 = ""; } } /* * */ }