// Get ProductSupplierID, Product Name, and Supplier Name table information from the database public static DataTable GetProducts() { SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT ProductSupplierId AS ID,ProdName AS Product,SupName AS Supplier " + "FROM Products p,Suppliers s,Products_Suppliers ps " + "WHERE p.ProductId = ps.ProductId AND s.SupplierId = ps.SupplierId " + "ORDER BY Product,Supplier"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); SqlDataAdapter adapter = new SqlDataAdapter(selectCommand); DataTable itemTable = new DataTable(); // Store the information in a Data Table try { connection.Open(); adapter.Fill(itemTable); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(itemTable); }
// gets info from database and puts in list public static List <Supplier> ListSupplier() { List <Supplier> supplier = new List <Supplier>(); SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT SupplierID, SupName " + "FROM Suppliers " + "ORDER BY SupName"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Supplier s = new Supplier(); s.SupplierId = Convert.ToInt32(reader["SupplierID"]); s.SupName = reader["SupName"].ToString(); supplier.Add(s); } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(supplier); }
// Find ProductSupplierIds, Product Names, or Supplier Names that contain the search string public static DataTable SearchProductSupplier(string searchString) { SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "Select ProductSupplierId AS ID,ProdName AS Product,SupName AS Supplier " + "FROM Products p,Suppliers s,Products_Suppliers ps " + "WHERE (p.ProductId = ps.ProductId AND s.SupplierId = ps.SupplierId) AND " + "(ProdName LIKE '%" + searchString.Trim() + "%' " + "OR SupName LIKE '%" + searchString.Trim() + "%'" + "OR ProductSupplierId LIKE '" + searchString.Trim() + "%')"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); SqlDataAdapter adapter = new SqlDataAdapter(selectCommand); DataTable searchTable = new DataTable(); // Return the search string in a data table try { connection.Open(); adapter.Fill(searchTable); } catch (Exception ex) { throw ex; } return(searchTable); }
public static int GetNextAvailableID() { SqlConnection connection = TravelExpertsDB.GetConnection(); string select = "SELECT MAX(SupplierId) AS ID FROM Suppliers"; SqlCommand command = new SqlCommand(select, connection); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow); // reads the data into the object reader.Read(); int nextid = Convert.ToInt32(reader["ID"]); nextid++; return(nextid); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
// Add a new product + supplier combination information to the database public static void AddProduct(string prodName, string supName) { SqlConnection connection = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " + "SELECT ProductId, SupplierId " + "FROM Products, Suppliers " + "WHERE ProdName = @prodName AND SupName = @supName"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@prodName", prodName); insertCommand.Parameters.AddWithValue("@supName", supName); try { connection.Open(); insertCommand.ExecuteNonQuery(); } catch (SqlException ex) { MessageBox.Show("Database error # " + ex.Number + ": " + ex.Message, ex.GetType().ToString()); } finally { connection.Close(); } }
// class to delete a prodoctsupplier from the database public static bool DeleteSup(Supplier sup) { SqlConnection connection = TravelExpertsDB.GetConnection(); string deleteStatement = "DELETE FROM Suppliers WHERE SupplierId = @SupplierId"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@SupplierId", sup.SupplierId); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
/// <summary> /// Using a SupplierID input, this method finds the associated details and stores it in a SupplierDetails object /// </summary> public static SupplierDetails GetSupplierDetailInfo(string name) { SupplierDetails supd = null; SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT SupConFirstName, " + "SupConLastName, " + "SupConCompany, " + "SupConAddress, " + "SupConCity, " + "SupConProv, " + "SupConPostal, " + "SupConCountry, " + "SupConBusPhone, " + "SupConFax, " + "SupConEmail, " + "SupConURL, " + "sc.SupplierId " + "FROM SupplierContacts sc " + "JOIN Suppliers s on sc.SupplierId = s.SupplierId " + "WHERE SupName = @SupName"; SqlCommand cmd = new SqlCommand(selectStatement, con); // Value comes from the method's argument cmd.Parameters.AddWithValue("@SupName", name); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow); if (reader.Read()) { supd = new SupplierDetails(); supd.SupConFirstName = reader["SupConFirstName"].ToString(); supd.SupConLastName = reader["SupConLastName"].ToString(); supd.SupConCompany = reader["SupConCompany"].ToString(); supd.SupConAddress = reader["SupConAddress"].ToString(); supd.SupConCity = reader["SupConCity"].ToString(); supd.SupConProv = reader["SupConProv"].ToString(); supd.SupConPostal = reader["SupConPostal"].ToString(); supd.SupConCountry = reader["SupConCountry"].ToString(); supd.SupConBusPhone = reader["SupConBusPhone"].ToString(); supd.SupConFax = reader["SupConFax"].ToString(); supd.SupConEmail = reader["SupConEmail"].ToString(); supd.SupConURL = reader["SupConURL"].ToString(); supd.SupplierId = reader["SupplierId"] as int? ?? default(int); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(supd); }
public static List <string[]> GetPackageProductSupplier(int pkgID) { List <string[]> records = new List <string[]>(); //create connection SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "select Packages_Products_Suppliers.productsupplierid, " + "prodname, supname " + "from Packages_Products_Suppliers, Products_Suppliers, Products, Suppliers " + "where Packages_Products_Suppliers.ProductSupplierId=Products_Suppliers.ProductSupplierId and " + "Products.ProductId=Products_Suppliers.ProductId and " + "Suppliers.SupplierId=Products_Suppliers.SupplierId and " + "Packages_Products_Suppliers.PackageId=" + pkgID.ToString(); SqlCommand selectCommand = new SqlCommand(selectStatement, connection); //open connection try { connection.Open(); } catch (Exception ex) { throw ex; } //create reader try { SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { string[] record = new string[3]; record[0] = reader["ProductSupplierId"].ToString(); record[1] = reader["ProdName"].ToString(); record[2] = reader["SupName"].ToString(); records.Add(record); } } catch (Exception ex) { throw ex; } //close connection try { connection.Close(); } catch (Exception ex) { throw ex; } return(records); }
public static SupplierContacts GetSupplierbySupID(int Supplierid) { // set up the connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string selectStatement = "SELECT * FROM SupplierContacts WHERE SupplierId = @SupplierId"; // creates the sql command and parameters SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@SupplierId", Supplierid); // use a try catch to attampt to get the data try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); // reads the data into the object if (reader.Read()) { SupplierContacts s = new SupplierContacts(); s.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]); s.SupConFirstName = reader["SupConFirstName"].ToString(); s.SupConLastName = reader["SupConLastName"].ToString(); s.SupConCompany = reader["SupConCompany"].ToString(); s.SupConAddress = reader["SupConAddress"].ToString(); s.SupConCity = reader["SupConCity"].ToString(); s.SupConProv = reader["SupConProv"].ToString(); s.SupConPostal = reader["SupConPostal"].ToString(); s.SupConCountry = reader["SupConCountry"].ToString(); s.SupConBusPhone = reader["SupConBusPhone"].ToString(); s.SupConFax = reader["SupConFax"].ToString(); s.SupConEmail = reader["SupConEmail"].ToString(); s.SupConURL = reader["SupConURL"].ToString(); s.AffiliationId = reader["AffiliationId"].ToString(); s.SupplierId = Convert.ToInt32(reader["SupplierID"]); return(s); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//method to update the supplier data to the db public static bool UpdateSupplier(SupplierContacts oldSup, SupplierContacts newSup) { // set up connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string updateStatement = "UPDATE SupplierContacts SET " + "SupplierContactId = @newSupplierContactId, SupConFirstName = @newSupConFirstName, SupConLastName = @newSupConLastName, " + "SupConCompany = @newSupConCompany, SupConAddress = @newSupConAddress, SupConCity = @newSupConCity, SupConProv = @newSupConProv, " + "SupConPostal = @newSupConPostal, SupConCountry = @newSupConCountry, SupConBusPhone = @newSupConBusPhone, SupConFax = @newSupConFax, " + "SupConEmail = @newSupConEmail, SupConURL = @newSupConURL" + " WHERE SupplierContactId = @oldSupplierContactId"; // creates the sql command and parameters SqlCommand command = new SqlCommand(updateStatement, connection); command.Parameters.AddWithValue("@newSupplierContactId", newSup.SupplierContactId); command.Parameters.AddWithValue("@newSupConFirstName", newSup.SupConFirstName); command.Parameters.AddWithValue("@newSupConLastName", newSup.SupConLastName); command.Parameters.AddWithValue("@newSupConCompany", newSup.SupConCompany); command.Parameters.AddWithValue("@newSupConAddress", newSup.SupConAddress); command.Parameters.AddWithValue("@newSupConCity", newSup.SupConCity); command.Parameters.AddWithValue("@newSupConProv", newSup.SupConProv); command.Parameters.AddWithValue("@newSupConPostal", newSup.SupConPostal); command.Parameters.AddWithValue("@newSupConCountry", newSup.SupConCountry); command.Parameters.AddWithValue("@newSupConBusPhone", newSup.SupConBusPhone); command.Parameters.AddWithValue("@newSupConFax", newSup.SupConFax); command.Parameters.AddWithValue("@newSupConEmail", newSup.SupConEmail); command.Parameters.AddWithValue("@newSupConURL", newSup.SupConURL); command.Parameters.AddWithValue("@newAffiliationId", newSup.AffiliationId); command.Parameters.AddWithValue("@newSupplierId", newSup.SupplierId); command.Parameters.AddWithValue("@oldSupplierContactId", oldSup.SupplierContactId); // use a try catch to attampt to update the data try { connection.Open(); int count = command.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
// method to return a list based on a filter from the db public static List <SupplierContacts> listSuppliersByFilter(string field, string filter) { List <SupplierContacts> supplier = new List <SupplierContacts>(); // set up the connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string selectStatement = "SELECT * FROM SupplierContacts WHERE " + field + " LIKE @filter ORDER BY SupConCompany"; // creates the sql command and parameters SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@filter", "%" + filter + "%"); // use a try catch to attampt to get the data try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); // reads the data into the object while (reader.Read()) { SupplierContacts s = new SupplierContacts(); s.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]); s.SupConFirstName = reader["SupConFirstName"].ToString(); s.SupConLastName = reader["SupConLastName"].ToString(); s.SupConCompany = reader["SupConCompany"].ToString(); s.SupConAddress = reader["SupConAddress"].ToString(); s.SupConCity = reader["SupConCity"].ToString(); s.SupConProv = reader["SupConProv"].ToString(); s.SupConPostal = reader["SupConPostal"].ToString(); s.SupConCountry = reader["SupConCountry"].ToString(); s.SupConBusPhone = reader["SupConBusPhone"].ToString(); s.SupConFax = reader["SupConFax"].ToString(); s.SupConEmail = reader["SupConEmail"].ToString(); s.SupConURL = reader["SupConURL"].ToString(); s.AffiliationId = reader["AffiliationId"].ToString(); s.SupplierId = Convert.ToInt32(reader["SupplierID"]); supplier.Add(s); } reader.Close(); return(supplier); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <Package> GetPackagesByCustomerID(int id) { List <Package> packages = new List <Package>(); SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT " + "Packages.PackageId, Packages.PkgName, " + "Packages.PkgStartDate, Packages.PkgEndDate, " + "Packages.PkgDesc, Packages.PkgBasePrice, " + "Packages.PkgAgencyCommission, " + "Bookings.BookingDate, Bookings.BookingNo " + "FROM Packages, Bookings " + "WHERE Packages.PackageID=Bookings.PackageID " + "AND Bookings.CustomerId=@CustomerId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@CustomerId", id); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Package package = new Package(); package.PackageID = (int)reader["PackageID"]; package.PkgName = (string)reader["PkgName"]; package.PkgStartDate = (DateTime)reader["PkgStartDate"]; package.PkgEndDate = (DateTime)reader["PkgEndDate"]; package.PkgDesc = (string)reader["PkgDesc"]; package.PkgBasePrice = (decimal)reader["PkgBasePrice"]; package.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; package.BookingNo = (string)reader["BookingNo"]; package.BookingDate = (DateTime)reader["BookingDate"]; packages.Add(package); } return(packages); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static string[] GetProductSupplier(int prodID, int supID) { string[] record = new string[3]; //create connection SqlConnection connection = TravelExpertsDB.GetConnection(); //search for something //string selectStatement = "SELECT * FROM Products_Suppliers "+ // "WHERE ProductId="+prodID.ToString()+" AND "+ // "SupplierId="+supID.ToString(); string selectStatement = "Select ProductSupplierId, ProdName, SupName " + "FROM " + "(SELECT * FROM Products_Suppliers WHERE ProductId=" + prodID.ToString() + " and SupplierId=" + supID.ToString() + ")as x, Products, Suppliers " + "WHERE x.ProductId=Products.ProductId AND x.SupplierId=Suppliers.SupplierId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); //open connection try { connection.Open(); } catch (Exception ex) { throw ex; } //create reader try { SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { //getID record[0] = reader["ProductSupplierId"].ToString(); record[1] = reader["ProdName"].ToString(); record[2] = reader["SupName"].ToString(); } } catch (Exception ex) { throw ex; } //close connection try { connection.Close(); } catch (Exception ex) { throw ex; } return(record); }
//get a package public static Package GetPackage(int id) { Package pkg = new Package(); //create connection SqlConnection connection = TravelExpertsDB.GetConnection(); //create sql command string selectStatement = "SELECT * FROM Packages " + "WHERE PackageId =" + id.ToString(); SqlCommand selectCommand = new SqlCommand(selectStatement, connection); //open connection try { connection.Open(); } catch (Exception ex) { throw ex; } //create reader try { SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); while (reader.Read()) { //add package details pkg.ID = (int)reader["PackageId"]; pkg.Name = reader["PkgName"].ToString(); pkg.Start_Date = Convert.ToDateTime(reader["PkgStartDate"]); pkg.End_Date = Convert.ToDateTime(reader["PkgEndDate"]); pkg.Description = reader["PkgDesc"].ToString(); pkg.Base_Price = (decimal)reader["PkgBasePrice"]; pkg.Agency_Commission = (decimal)reader["PkgAgencyCommission"]; } } catch (Exception ex) { throw ex; } //close connection try { connection.Close(); } catch (Exception ex) { throw ex; } return(pkg); }
public static int AddCustomer(Customer customer) { SqlConnection connection = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT Customers " + "(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, " + "CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, AgentID) " + "VALUES (@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, " + "@CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, 1 )"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@CustFirstName", customer.CustFirstName); insertCommand.Parameters.AddWithValue( "@CustLastName", customer.CustLastName); insertCommand.Parameters.AddWithValue( "@CustAddress", customer.CustAddress); insertCommand.Parameters.AddWithValue( "@CustCity", customer.CustCity); insertCommand.Parameters.AddWithValue( "@CustProv", customer.CustProv); insertCommand.Parameters.AddWithValue( "@CustPostal", customer.CustPostal); insertCommand.Parameters.AddWithValue( "@CustCountry", customer.CustCountry); insertCommand.Parameters.AddWithValue( "@CustHomePhone", customer.CustHomePhone); insertCommand.Parameters.AddWithValue( "@CustBusPhone", customer.CustBusPhone); insertCommand.Parameters.AddWithValue( "@CustEmail", customer.CustEmail); insertCommand.Parameters.AddWithValue( "@AgentId", customer.AgentID); try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(customer.CustomerID); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(-1); }
public static int DeleteCustomer(Customer customer) { SqlConnection connection = TravelExpertsDB.GetConnection(); string deleteStatement = "DELETE FROM Customers " + "WHERE " + "CustomerID = @CustomerID AND " + "CustFirstName = @CustFirstName AND " + "CustLastName = @CustLastName AND " + "CustAddress = @CustAddress AND " + "CustCity = @CustCity AND " + "CustProv = @CustProv AND " + "CustPostal = @CustPostal AND " + "CustCountry = @CustCountry AND " + "CustHomePhone = @CustHomePhone AND " + "CustBusPhone = @CustBusPhone AND " + "CustEmail = @CustEmail AND " + "AgentID = @AgentID "; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@CustomerID", customer.CustomerID); deleteCommand.Parameters.AddWithValue("@CustFirstName", customer.CustFirstName); deleteCommand.Parameters.AddWithValue("@CustLastName", customer.CustLastName); deleteCommand.Parameters.AddWithValue("@CustAddress", customer.CustAddress); deleteCommand.Parameters.AddWithValue("@CustCity", customer.CustCity); deleteCommand.Parameters.AddWithValue("@CustProv", customer.CustProv); deleteCommand.Parameters.AddWithValue("@CustPostal", customer.CustPostal); deleteCommand.Parameters.AddWithValue("@CustCountry", customer.CustCountry); deleteCommand.Parameters.AddWithValue("@CustHomePhone", customer.CustHomePhone); deleteCommand.Parameters.AddWithValue("@CustBusPhone", customer.CustBusPhone); deleteCommand.Parameters.AddWithValue("@CustEmail", customer.CustEmail); deleteCommand.Parameters.AddWithValue("@AgentID", customer.AgentId); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(customer.CustomerID); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(-1); }
public static bool UpdatePackage(Package editPackage) { SqlConnection connection = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Packages SET " + "PkgName = @PkgName, " + "PkgStartDate = @PkgStartDate, " + "PkgEndDate = @PkgEndDate, " + "PkgDesc = @PkgDesc, " + "PkgBasePrice = @PkgBasePrice, " + "PkgAgencyCommission = @PkgAgencyCommission " + "WHERE PackageId = @PackageId "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@PackageId", editPackage.ID); updateCommand.Parameters.AddWithValue( "@PkgName", editPackage.Name); updateCommand.Parameters.AddWithValue( "@PkgStartDate", editPackage.Start_Date); updateCommand.Parameters.AddWithValue( "@PkgEndDate", editPackage.End_Date); updateCommand.Parameters.AddWithValue( "@PkgDesc", editPackage.Description); updateCommand.Parameters.AddWithValue( "@PkgBasePrice", editPackage.Base_Price); updateCommand.Parameters.AddWithValue( "@PkgAgencyCommission", editPackage.Agency_Commission); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool UpdateAgent(Agent oldAgent, Agent newAgent) { SqlConnection connection = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Agents SET " + "AgtFirstName = @NewAgtFirstName, " + "AgtMiddleInitial = @NewAgtMiddleInitial, " + "AgtLastName = @NewAgtLastName, " + "AgtBusPhone = @NewAgtBusPhone, " + "AgtEmail = @NewAgtEmail, " + "AgtPosition = @NewAgtPosition, " + "AgencyId = @NewAgencyId, " + "AgtPassword = @NewAgtPassword " + "WHERE AgentId = @NewAgentId "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@NewAgentId", newAgent.AgentId); updateCommand.Parameters.AddWithValue("@NewAgtFirstName", newAgent.AgtFirstName); updateCommand.Parameters.AddWithValue("@NewAgtMiddleInitial", newAgent.AgtMiddleInitial); updateCommand.Parameters.AddWithValue("@NewAgtLastName", newAgent.AgtLastName); updateCommand.Parameters.AddWithValue("@NewAgtBusPhone", newAgent.AgtBusPhone); updateCommand.Parameters.AddWithValue("@NewAgtEmail", newAgent.AgtEmail); updateCommand.Parameters.AddWithValue("@NewAgtPosition", newAgent.AgtPosition); updateCommand.Parameters.AddWithValue("@NewAgencyId", newAgent.AgencyId); updateCommand.Parameters.AddWithValue("@NewAgtPassword", newAgent.AgtPassword); //Exception handling try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static Customer GetCustomerByEmail(string custEmail) { Customer customer = null; SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT * " + "FROM Customers " + "WHERE CustEmail = @CustEmail"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@CustEmail", custEmail); try { connection.Open(); SqlDataReader custReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (custReader.Read()) { customer = new Customer(); customer.CustomerID = (int)custReader["CustomerID"]; customer.CustFirstName = (string)custReader["CustFirstName"]; customer.CustLastName = (string)custReader["CustLastName"]; customer.CustAddress = (string)custReader["CustAddress"]; customer.CustCity = (string)custReader["CustCity"]; customer.CustProv = (string)custReader["CustProv"]; customer.CustPostal = (string)custReader["CustPostal"]; customer.CustCountry = (string)custReader["CustCountry"]; customer.CustHomePhone = (string)custReader["CustHomePhone"]; customer.CustBusPhone = (string)custReader["CustBusPhone"]; customer.CustEmail = (string)custReader["CustEmail"]; customer.AgentId = (int)custReader["AgentId"]; customer.CustPassword = (string)custReader["CustPassword"]; return(customer); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
private void LoadComboBox() { tableNames = TravelExpertsDB.GetTableNames(); if (tableNames.Count > 0) // if there are tables { cboTableNames.DataSource = tableNames; cboTableNames.SelectedIndex = 0; // triggers SelectedIndexChanged } else // no members { MessageBox.Show("There are no data tables. " + "Add some data tables in the database, and restart the application ", "Empty Load"); Application.Exit(); } }
public static Agent GetAgent(int agentId) { SqlConnection connection = TravelExpertsDB.GetConnection();//Define conection //Build select statement string selectStatement = "SELECT * FROM Agents " + "WHERE AgentId = @AgentId"; //Get the data into a temp table SqlCommand selectCommand = new SqlCommand(selectStatement, connection); //Build SQL command selectCommand.Parameters.AddWithValue("@AgentId", agentId); //Patch previous statement //Exception handling try { //Executes if agent exist connection.Open(); SqlDataReader custReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (custReader.Read()) { Agent agent = new Agent(); agent.AgentId = Convert.ToInt32(custReader["AgentId"]);//Fill with data from reader agent.AgtFirstName = custReader["AgtFirstName"].ToString(); agent.AgtMiddleInitial = custReader["AgtMiddleInitial"].ToString(); agent.AgtLastName = custReader["AgtLastName"].ToString(); agent.AgtBusPhone = custReader["AgtBusPhone"].ToString(); agent.AgtEmail = custReader["AgtEmail"].ToString(); agent.AgtPosition = custReader["AgtPosition"].ToString(); agent.AgencyId = Convert.ToInt32(custReader["AgencyId"]); agent.AgtPassword = custReader["AgtPassword"].ToString(); return(agent);//Returns agent } else //agent does not exist { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
private void btnDelete_Click(object sender, EventArgs e) { try { SqlConnection connection = TravelExpertsDB.GetConnection(); connection.Open(); SqlCommand cmd = new SqlCommand("Delete Suppliers where SupplierId = @SupplierId", connection); cmd.Parameters.AddWithValue("@SupplierId", int.Parse(txtSupplierId.Text)); cmd.ExecuteNonQuery(); connection.Close(); MessageBox.Show("Successfully Deleted"); } catch (Exception p) { MessageBox.Show(p.Message); } }
// Andy Gao public static List <Supplier> GetSupplier() { // Prepares a empty list of Supplier List <Supplier> supplierlist = new List <Supplier>(); Supplier sup; // Connect to Sql Server SqlConnection con = TravelExpertsDB.GetConnection(); // Define the select command in alphabetical order string selectStatement = "SELECT * " + "FROM Suppliers " + "ORDER BY SupName"; // Prepare for executing select query SqlCommand cmd = new SqlCommand(selectStatement, con); // Try and catch any unspecified errors try { // Open connection con.Open(); // Read and execute Sql command SqlDataReader reader = cmd.ExecuteReader(); //Supplier supp; // While reading - add each supplier into the list while (reader.Read()) { sup = new Supplier(); sup.SupplierId = Convert.ToInt32(reader["SupplierId"]); sup.SupName = reader["SupName"].ToString(); supplierlist.Add(sup); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(supplierlist); }
public static bool DeleteAgent(Agent agent) { SqlConnection connection = TravelExpertsDB.GetConnection(); string deleteStatement = "DELETE FROM Agents " + "WHERE AgtFirstName = @AgtFirstName " + "AND AgtMiddleInitial = @AgtMiddleInitial " + "AND AgtLastName = @AgtLastName " + "AND AgtBusPhone = @AgtBusPhone " + "AND AgtEmail = @AgtEmail " + "AND AgtPosition = @AgtPosition " + "AND AgencyId = @AgencyId " + "AND AgtPassword = @AgtPassword "; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@AgtFirstName", agent.AgtFirstName); deleteCommand.Parameters.AddWithValue("@AgtMiddleInitial", agent.AgtMiddleInitial); deleteCommand.Parameters.AddWithValue("@AgtLastName", agent.AgtLastName); deleteCommand.Parameters.AddWithValue("@AgtBusPhone", agent.AgtBusPhone); deleteCommand.Parameters.AddWithValue("@AgtEmail", agent.AgtEmail); deleteCommand.Parameters.AddWithValue("@AgtPosition", agent.AgtPosition); deleteCommand.Parameters.AddWithValue("@AgencyId", agent.AgencyId); deleteCommand.Parameters.AddWithValue("@AgtPassword", agent.AgtPassword); //Exception handling try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool CheckPassword(string enteredCustEmail, string enteredCustPassword) { SqlConnection connection = TravelExpertsDB.GetConnection();//Define conection connection.Open(); SqlCommand command = new SqlCommand("SELECT ISNULL(CustEmail, '') AS CustEmail, " + "ISNULL(CustPassword,'') AS CustPassword " + "FROM Customers WHERE CustEmail = @CustEmail and CustPassword = @CustPassword", connection); command.Parameters.Add(new SqlParameter("CustEmail", enteredCustEmail)); command.Parameters.Add(new SqlParameter("CustPassword", enteredCustPassword)); SqlDataReader dataReader = command.ExecuteReader(); try { dataReader.Read(); if (dataReader.HasRows) { if (dataReader["CustEmail"].ToString().Trim() == enteredCustEmail && dataReader["CustPassword"].ToString().Trim() == enteredCustPassword) { return(true); } else { return(false); } } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); dataReader.Close(); } }
//delete records that are not in the list public static bool DeletePackageProductSupplier(int pkgID, List <int> prodSupID) { SqlConnection connection = TravelExpertsDB.GetConnection(); string stringProdSupId = ""; foreach (int n in prodSupID) { stringProdSupId += n.ToString() + ","; } if (stringProdSupId.Length > 0) { stringProdSupId = stringProdSupId.Remove(stringProdSupId.Length - 1); } string deleteStatement = "IF EXISTS (SELECT * FROM Packages_Products_Suppliers " + "WHERE PackageId = " + pkgID.ToString() + " AND ProductSupplierId NOT in " + "(" + stringProdSupId + ")) " + "delete from Packages_Products_Suppliers " + "where PackageID=" + pkgID.ToString() + " AND ProductSupplierId NOT in " + "(" + stringProdSupId + ")"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//method to update the supplier data to the db public static bool UpdatePackage(Package oldPack, Package newPack) { // set up connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string updateStatement = "UPDATE Packages SET " + "PkgName = @newPkgName, PkgStartDate = @newPkgStartDate, PkgEndDate = @newPkgEndDate, " + "PkgDesc = @newPkgDesc, PkgBasePrice = @newPkgBasePrice, PkgAgencyCommission = @newPkgAgencyCommission" + " WHERE PackageId = @oldPackageId"; // creates the sql command and parameters SqlCommand command = new SqlCommand(updateStatement, connection); command.Parameters.AddWithValue("@newPkgName", newPack.PkgName); command.Parameters.AddWithValue("@newPkgStartDate", newPack.PkgStartDate); command.Parameters.AddWithValue("@newPkgEndDate", newPack.PkgEndDate); command.Parameters.AddWithValue("@newPkgDesc", newPack.PkgDesc); command.Parameters.AddWithValue("@newPkgBasePrice", newPack.PkgBasePrice); command.Parameters.AddWithValue("@newPkgAgencyCommission", newPack.PkgAgencyCommission); command.Parameters.AddWithValue("@oldPackageId", oldPack.PackageId); // use a try catch to attampt to update the data try { connection.Open(); int count = command.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//delete package public static bool DeletePackage(int pkgID) { SqlConnection connection = TravelExpertsDB.GetConnection(); string deleteLinkingTableStatement = "delete from Packages_Products_Suppliers " + "where PackageId= " + pkgID.ToString(); string deletePkgStatement = "DELETE FROM Packages " + "WHERE PackageId = " + pkgID.ToString(); SqlCommand deleteLinkingTableCommand = new SqlCommand(deleteLinkingTableStatement, connection); SqlCommand deletePkgCommand = new SqlCommand(deletePkgStatement, connection); try { connection.Open(); deleteLinkingTableCommand.ExecuteNonQuery(); int count = deletePkgCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException) { MessageBox.Show("You do not have enough permission to delete this product", "Error Deleting", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } finally { connection.Close(); } }
public static List <ProductSupplier> GetProductSupplierByPackageID(int id) { List <ProductSupplier> productsSuppliers = new List <ProductSupplier>(); SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT ps.ProductSupplierId, pro.ProdName, s.SupName, bd.BasePrice " + "FROM Packages pac, Packages_Products_Suppliers pps, Products_Suppliers ps, " + "Products pro, Suppliers s, BookingDetails bd " + "WHERE pac.PackageId='" + id + "' " + "AND pac.PackageId=pps.PackageId " + "AND pps.ProductSupplierId=ps.ProductSupplierId " + "AND ps.ProductId=pro.ProductId " + "AND ps.SupplierId=s.SupplierId " + "AND bd.ProductSupplierId=ps.ProductSupplierId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { ProductSupplier productSupplier = new ProductSupplier(); productSupplier.ProductSupplierId = (int)reader["ProductSupplierId"]; productSupplier.ProdName = (string)reader["ProdName"]; productSupplier.SupName = (string)reader["SupName"]; productSupplier.BasePrice = (decimal)reader["BasePrice"]; productsSuppliers.Add(productSupplier); } return(productsSuppliers); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
// method to add data to the db public static void AddSupp(SupplierContacts sup) { // set up the connection SqlConnection connection = TravelExpertsDB.GetConnection(); // sql statement string insertStatement = "INSERT INTO SupplierContacts VALUES " + "(@SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProv, @SupConPostal, " + "@SupConCountry, @SupConBusPhone, @SupConFax, @SupConEmail, @SupConURL, NULL, @SupplierId)"; // creates the sql command and parameters SqlCommand command = new SqlCommand(insertStatement, connection); command.Parameters.AddWithValue("@SupplierContactId", sup.SupplierContactId); command.Parameters.AddWithValue("@SupConFirstName", sup.SupConFirstName); command.Parameters.AddWithValue("@SupConLastName", sup.SupConLastName); command.Parameters.AddWithValue("@SupConCompany", sup.SupConCompany); command.Parameters.AddWithValue("@SupConAddress", sup.SupConAddress); command.Parameters.AddWithValue("@SupConCity", sup.SupConCity); command.Parameters.AddWithValue("@SupConProv", sup.SupConProv); command.Parameters.AddWithValue("@SupConPostal", sup.SupConPostal); command.Parameters.AddWithValue("@SupConCountry", sup.SupConCountry); command.Parameters.AddWithValue("@SupConBusPhone", sup.SupConBusPhone); command.Parameters.AddWithValue("@SupConFax", sup.SupConFax); command.Parameters.AddWithValue("@SupConEmail", sup.SupConEmail); command.Parameters.AddWithValue("@SupConURL", sup.SupConURL); //command.Parameters.AddWithValue("@AffiliationId", sup.AffiliationId); command.Parameters.AddWithValue("@SupplierId", sup.SupplierId); // use a try catch to attampt to add the data try { connection.Open(); command.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }