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