}//END OF ADD METHOD
public static Supplier GetSupplierByID(int SupplierID)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement =
"SELECT SupplierId, SupName " +
"FROM Suppliers " +
"WHERE SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@SupplierId", SupplierID);
try
{
connection.Open();
SqlDataReader custReader = selectCommand.ExecuteReader
(CommandBehavior.SingleRow); // selecting by PK value
if (custReader.Read())
{ // we have a customer
Supplier supplier = new Supplier();
supplier.SupplierId = (int)custReader["SupplierID"];
supplier.Name = (string)custReader["SupName"];
return supplier;
}
else // no customer
{
return null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Package> GetPackageProducts(ListBox listBox, int packageId)
{
List <Package> packageProducts = new List <Package>();
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
string selectPackProdQuery = @"SELECT ProdName FROM Packages P " +
"INNER JOIN Packages_Products_Suppliers S ON P.PackageId = S.PackageId " +
"INNER JOIN Products_Suppliers O ON S.ProductSupplierId = O.ProductSupplierId " +
"INNER JOIN Products R ON O.ProductId = R.ProductId " +
"WHERE R.ProductId = O.ProductId " +
"AND O.ProductSupplierId = S.ProductSupplierId " +
"AND S.PackageId = @PackageId";
using (SqlCommand cmd = new SqlCommand(selectPackProdQuery, con))
{
cmd.Parameters.AddWithValue("@PackageId", packageId);
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
var item = new ListViewItem();
item.SubItems[0].Text = reader["ProdName"].ToString();
item.SubItems.Add(reader["ProdName"].ToString());
listBox.Items.Add(item);
}
}
}
return(packageProducts);
}
public void AddTravelPackage(string packageName, DateTime packageStartDate, DateTime packageEndDate, string packageDescription, double packageBasePrice, double packageCommission)
{
SqlConnection con = TravelExpertsDB.GetConnection();
try
{
string updatePkgQuery = @"INSERT INTO Packages " +
"(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"VALUES (@PackageName, @PackageStartDate, @PackageEndDate, @PackageDescription, @PackageBasePrice, @PackageCommission)";
SqlCommand sqlCommand = new SqlCommand(updatePkgQuery, con);
con.Open();
sqlCommand.Parameters.AddWithValue("@PackageName", packageName);
sqlCommand.Parameters.AddWithValue("@PackageStartDate", packageStartDate);
sqlCommand.Parameters.AddWithValue("@PackageEndDate", packageEndDate);
sqlCommand.Parameters.AddWithValue("@PackageDescription", packageDescription);
sqlCommand.Parameters.AddWithValue("@PackageBasePrice", packageBasePrice);
sqlCommand.Parameters.AddWithValue("@PackageCommission", packageCommission);
sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static bool InsertCustomerContactInfo(CustomerContact customer)
{
bool successful = false;
SqlConnection connection = TravelExpertsDB.GetConnection(); // instantiate the connection
string insertContactString = "INSERT INTO CustomerContact " +
"(CustFirstName, CustLastName, Email, CustSubject, CustMessage) " +
"values(@CustFirstName, @CustLastName, @Email, @CustSubject, @CustMessage)";
SqlCommand insertCommand = new SqlCommand(insertContactString, connection);
insertCommand.Parameters.AddWithValue("@CustFirstName", customer.CustFirstName);
insertCommand.Parameters.AddWithValue("@CustLastName", customer.CustLastName);
insertCommand.Parameters.AddWithValue("@Email", customer.Email);
insertCommand.Parameters.AddWithValue("@CustSubject", customer.CustSubject);
insertCommand.Parameters.AddWithValue("@CustMessage", customer.CustMessage);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
}
catch (Exception ex)
{
connection.Close();
}
return(successful);
}
public void EditTravelPackage(int packageId, string packageName, DateTime packageStartDate, DateTime packageEndDate, string packageDescription, double packageBasePrice, double packageCommission)
{
SqlConnection con = TravelExpertsDB.GetConnection();
try
{
string updatePkgQuery = @"UPDATE Packages " +
"SET PkgName = @PackageName, PkgStartDate = @PackageStartDate, PkgEndDate = @PackageEndDate, PkgDesc = @PackageDescription, PkgBasePrice = @PackageBasePrice, PkgAgencyCommission = @PackageCommission " +
"WHERE PackageId = @PackageId";
SqlCommand sqlCommand = new SqlCommand(updatePkgQuery, con);
con.Open();
sqlCommand.Parameters.AddWithValue("@PackageId", packageId);
sqlCommand.Parameters.AddWithValue("@PackageName", packageName);
sqlCommand.Parameters.AddWithValue("@PackageStartDate", packageStartDate);
sqlCommand.Parameters.AddWithValue("@PackageEndDate", packageEndDate);
sqlCommand.Parameters.AddWithValue("@PackageDescription", packageDescription);
sqlCommand.Parameters.AddWithValue("@PackageBasePrice", packageBasePrice);
sqlCommand.Parameters.AddWithValue("@PackageCommission", packageCommission);
sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <Booking> GetBookings(int CustomerId)
{
List <Booking> bookings = new List <Booking>(); // make an empty list
Booking book; // reference to new state object
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "select b.[BookingId],[BookingNo],[BookingDate]," +
"isnull([PkgName],'not selected') as 'PackageName',[TTName] as 'TripType',[TravelerCount]," +
"CAST(isnull([PkgBasePrice],0) AS DECIMAL(10,2)) as 'PackagePrice'," +
"[BasePrice] as 'ServicePrice'," +
"CAST(isnull([PkgBasePrice], 0) * [TravelerCount] + [BasePrice] AS DECIMAL(10,2)) as 'Total' " +
"from [Bookings] b " +
"inner join [TripTypes] t " +
"on t.TripTypeId = b.TripTypeId " +
"left join [Packages] pkg " +
"on pkg.PackageId = b.PackageId " +
"inner join [BookingDetails] d " +
"on b.[BookingId] = d.[BookingId] " +
"where[CustomerId] = @CustomerId " +
"order by[BookingDate] desc";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", CustomerId);
try
{
// open connection
connection.Open();
// run the select command and process the results adding states to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
book = new Booking();
book.BookingId = (int)reader["BookingId"];
book.BookingNo = reader["BookingNo"].ToString();
book.PackageName = reader["PackageName"].ToString();
book.TripType = reader["TripType"].ToString();
book.TravelerCount = (double)reader["TravelerCount"];
book.BookingDate = (DateTime)reader["BookingDate"];
book.PackagePrice = (decimal)reader["PackagePrice"];
book.ServicePrice = (decimal)reader["ServicePrice"];
book.Total = (decimal)reader["Total"];
bookings.Add(book);
}
reader.Close();
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(bookings);
}
public static void AddProduct(string prodName)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products (prodname) VALUES (@ProdName) ";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProdName", prodName);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void UpdateProduct(string newprodname, string oldprodname)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "UPDATE Products SET ProdName = @prodname WHERE @oldprodname = ProdName ; ";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@prodname", newprodname);
cmd.Parameters.AddWithValue("@oldprodname", oldprodname);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
//insert new customer record
public static int AddCustomer(Customers cust)
{
int custId = 0;
//prepare connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//prepare statement
string insertString = "INSERT INTO CUSTOMERS " +
"(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, CustPassword, CustActivated) " +
"values(@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, HASHBYTES('SHA1', @CustPassword), @CustActivated)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
insertCommand.Parameters.AddWithValue("@CustFirstName", cust.CustFirstName);
insertCommand.Parameters.AddWithValue("@CustLastName", cust.CustLastName);
insertCommand.Parameters.AddWithValue("@CustAddress", cust.CustAddress);
insertCommand.Parameters.AddWithValue("@CustCity", cust.CustCity);
insertCommand.Parameters.AddWithValue("@CustProv", cust.CustProv);
insertCommand.Parameters.AddWithValue("@CustPostal", cust.CustPostal);
insertCommand.Parameters.AddWithValue("@CustCountry", cust.CustCountry);
insertCommand.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone);
insertCommand.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone);
insertCommand.Parameters.AddWithValue("@CustEmail", cust.CustEmail);
insertCommand.Parameters.AddWithValue("@CustPassword", cust.CustPassword);
insertCommand.Parameters.AddWithValue("@CustActivated", cust.CustActivated);
try
{
//open connection
connection.Open();
//execute the statement
int i = insertCommand.ExecuteNonQuery();
if (i >= 1) //one record inserted
{
// retrieve customer id from the added record
//ident_current Returns the last identity value generated for the customers table
string selectString = "select ident_current('Customers') from Customers";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
custId = Convert.ToInt32(selectCommand.ExecuteScalar());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(custId);
}
//updates existing customer record and returns bool success flag
public static bool UpdateCustomer(Customers old_Customer, Customers customer)
{
bool successful = false;
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateString = "UPDATE CUSTOMERS SET CustFirstName = @CustNewFName, CustLastName = @CustNewLName, CustAddress = @CustNewAddress, CustCity = @CustNewCity, CustProv = @CustNewProv, CustPostal = @CustNewPostal, CustCountry = @CustNewCountry, CustHomePhone = @CustNewHomePhone, CustBusPhone = @CustNewBusPhone, CustEmail = @CustNewEmail WHERE CustFirstName = @CustOldFName AND CustLastName = @CustOldLName AND CustAddress = @CustOldAddress AND CustCity = @CustOldCity AND CustProv = @CustOldProv AND CustPostal = @CustOldPostal AND CustCountry = @CustOldCountry AND CustHomePhone = @CustOldHomePhone AND CustBusPhone = @CustOldBusPhone AND CustEmail = @CustOldEmail";
SqlCommand updateCommand = new SqlCommand(updateString, connection);
updateCommand.Parameters.AddWithValue("@CustOldFname", old_Customer.CustFirstName);
updateCommand.Parameters.AddWithValue("@CustOldLname", old_Customer.CustLastName);
updateCommand.Parameters.AddWithValue("@CustOldAddress", old_Customer.CustAddress);
updateCommand.Parameters.AddWithValue("@CustOldCity", old_Customer.CustCity);
updateCommand.Parameters.AddWithValue("@CustOldProv", old_Customer.CustProv);
updateCommand.Parameters.AddWithValue("@CustOldPostal", old_Customer.CustPostal);
updateCommand.Parameters.AddWithValue("@CustOldCountry", old_Customer.CustCountry);
updateCommand.Parameters.AddWithValue("@CustOldHomePhone", old_Customer.CustHomePhone);
updateCommand.Parameters.AddWithValue("@CustOldBusPhone", old_Customer.CustBusPhone);
updateCommand.Parameters.AddWithValue("@CustOldEmail", old_Customer.CustEmail);
updateCommand.Parameters.AddWithValue("@CustNewFname", customer.CustFirstName);
updateCommand.Parameters.AddWithValue("@CustNewLname", customer.CustLastName);
updateCommand.Parameters.AddWithValue("@CustNewAddress", customer.CustAddress);
updateCommand.Parameters.AddWithValue("@CustNewCity", customer.CustCity);
updateCommand.Parameters.AddWithValue("@CustNewProv", customer.CustProv);
updateCommand.Parameters.AddWithValue("@CustNewPostal", customer.CustPostal);
updateCommand.Parameters.AddWithValue("@CustNewCountry", customer.CustCountry);
updateCommand.Parameters.AddWithValue("@CustNewHomePhone", customer.CustHomePhone);
updateCommand.Parameters.AddWithValue("@CustNewBusPhone", customer.CustBusPhone);
updateCommand.Parameters.AddWithValue("@CustNewEmail", customer.CustEmail);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count >= 1)
{
successful = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(successful);
}
//get customer information
public static Customers GetCustomerbyPassword(string custPassword)
{
Customers cust = null;
//defineconnection
SqlConnection connection = TravelExpertsDB.GetConnection();
//define the select query command
string selectQuery = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail FROM Customers WHERE CustPassword = HASHBYTES('SHA1', @CustPassword)";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@CustPassword", custPassword);
try
{
//open the connection
connection.Open();
//execute the query
SqlDataReader reader = selectCommand.ExecuteReader();
//process the results
while (reader.Read()) //if there is a customer
{
cust = new Customers();
cust.CustomerId = (int)reader["CustomerId"];
cust.CustFirstName = reader["CustFirstName"].ToString();
cust.CustLastName = reader["CustLastName"].ToString();
cust.CustAddress = reader["CustAddress"].ToString();
cust.CustCity = reader["CustCity"].ToString();
cust.CustProv = reader["CustProv"].ToString();
cust.CustPostal = reader["CustPostal"].ToString();
cust.CustCountry = reader["CustCountry"].ToString();
cust.CustHomePhone = reader["CustHomePhone"].ToString();
cust.CustBusPhone = reader["CustBusPhone"].ToString();
cust.CustEmail = reader["CustEmail"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(cust);
}
public void ShowSelectedOrder(ListView listview, Label packageId, Label packageName, Label packageStartDate, Label packageEndDate, Label packageDescription, Label packageBasePrice, Label packageCommission)
{
SqlConnection con = TravelExpertsDB.GetConnection();
try
{
string selectPackageQuery = @"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission FROM Packages WHERE PackageId = @PackageId";
SqlCommand sqlCommand = new SqlCommand(selectPackageQuery, con);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
using (sqlDataAdapter)
{
if (listview != null && listview.SelectedItems.Count > 0)
{
sqlCommand.Parameters.AddWithValue("@PackageId", listview.FocusedItem.Text);
DataTable OrderDataTable = new DataTable();
sqlDataAdapter.Fill(OrderDataTable);
// variables
string pkgId = Convert.ToString(OrderDataTable.Rows[0]["PackageId"]);
string pkgName = Convert.ToString(OrderDataTable.Rows[0]["PkgName"]);
string pkgDesc = Convert.ToString(OrderDataTable.Rows[0]["PkgDesc"]);
DateTime startDate = Convert.ToDateTime(OrderDataTable.Rows[0]["PkgStartDate"]);
DateTime endDate = Convert.ToDateTime(OrderDataTable.Rows[0]["PkgEndDate"]);
double pkgPrice = Convert.ToDouble(OrderDataTable.Rows[0]["PkgBasePrice"]);
double pkgComm = Convert.ToDouble(OrderDataTable.Rows[0]["PkgAgencyCommission"]);
packageId.Text = pkgId;
packageName.Text = pkgName;
packageStartDate.Text = startDate.ToString("MM/dd/yyyy");
packageEndDate.Text = endDate.ToString("MM/dd/yyyy");
packageDescription.Text = pkgDesc;
packageBasePrice.Text = pkgPrice.ToString("C");
packageCommission.Text = pkgComm.ToString("C");
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <Package> GetPackages(ListView tableView)
{
List <Package> packages = new List <Package>();
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
string selectPackagesQuery = @"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission FROM Packages";
using (SqlCommand cmd = new SqlCommand(selectPackagesQuery, con))
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
tableView.View = View.Details;
tableView.FullRowSelect = true;
tableView.Columns.Add("ID").Width = 35;
tableView.Columns.Add("Name").Width = 125;
tableView.Columns.Add("Start Date").Width = 85;
tableView.Columns.Add("End Date").Width = 85;
tableView.Columns.Add("Description").Width = 270;
tableView.Columns.Add("Base Price").Width = 85;
tableView.Columns.Add("Commission").Width = 85;
while (reader.Read())
{
var item = new ListViewItem();
// variables
DateTime startDate = Convert.ToDateTime(reader["PkgStartDate"]);
DateTime endDate = Convert.ToDateTime(reader["PkgEndDate"]);
double basePrice = Convert.ToDouble(reader["PkgBasePrice"]);
double agencyCommission = Convert.ToDouble(reader["PkgAgencyCommission"]);
item.SubItems[0].Text = reader[0].ToString();
item.SubItems.Add(reader["PkgName"].ToString());
item.SubItems.Add(startDate.ToString("MM/dd/yyyy"));
item.SubItems.Add(endDate.ToString("MM/dd/yyyy"));
item.SubItems.Add(reader["PkgDesc"].ToString());
item.SubItems.Add(basePrice.ToString("C"));
item.SubItems.Add(agencyCommission.ToString("C"));
tableView.Items.Add(item);
}
}
}
return(packages);
}
public static ProductSupplier GetProductSupplierById(int productSupplierID)
{
int tempSupId;
int tempProdId;
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement =
"SELECT ProductSupplierId, ProductID,SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductSupplierId", productSupplierID);
try
{
connection.Open();
SqlDataReader Reader = selectCommand.ExecuteReader
(CommandBehavior.SingleRow); // selecting by PK value
if (Reader.Read())
{ // we have a customer
ProductSupplier productSupplier = new ProductSupplier();
productSupplier.ProductSupplierId = (int)Reader["ProductSupplierId"];
tempSupId = (int)Reader["SupplierId"];
tempProdId = (int)Reader["ProductId"];
connection.Close();
productSupplier.Supplier = SupplierDB.GetSupplierByID(tempSupId);
productSupplier.Product = ProductDB.GetEntityById(tempSupId);
return(productSupplier);
}
else // no customer
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public bool UpdateSupplier(int supplierID) //refreshes supplier list with updated supplier
{
//validate on the form
//string connectionString = "Data Source=localhost\\SAIT;Initial Catalog=TravelExperts;Integrated Security=True";
//SqlConnection connection = new SqlConnection(connectionString);
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Suppliers " +
"SET SupplierID = @NewSupplierID, " +
" SupName = @NewSupName " +
"WHERE SupplierID = @OldSupplierID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewSupplierID", txtSupplierID.Text);
updateCommand.Parameters.AddWithValue("@NewSupName", txtSupName.Text);
updateCommand.Parameters.AddWithValue("@OldSupplierID", selectedSupplierID);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery(); //count how many rows are affected
if (count > 0) //if update was successful
{
List <Supplier> suppliersList = SupplierDB.GetSuppliers();
updateListView(suppliersList);
pnlAddUpdate.Visible = false;
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("There was an error. Please try again.");
}
finally
{
connection.Close();
}
return(false);
}//END OF UpdateSupplier()
public void DeleteTravelPackage(ListView packageList, int packageID)
{
SqlConnection con = TravelExpertsDB.GetConnection();
try
{
string deletePkgQuery = @"DELETE FROM Packages WHERE PackageID = @PackageId";
SqlCommand sqlCommand = new SqlCommand(deletePkgQuery, con);
con.Open();
sqlCommand.Parameters.AddWithValue("@PackageId", packageID);
sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
// adds new customer record and returns the customer ID
public static int AddProductSupplier(Product product, Supplier supplier)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string insertStatement =
"INSERT INTO Products_Suppliers " +
"(ProductId, SupplierId) " +
"Values(@ProductId, @SupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@ProductId", product.ProductId);
insertCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
try
{
connection.Open();
int nr = insertCommand.ExecuteNonQuery();
if (nr > 0) // success
{
// find out what is the customer ID of the added record
string selectStatement = "SELECT IDENT_CURRENT('Products_Suppliers') " +
"FROM Products_Suppliers";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int custID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(custID);
}
else // not added
{
return(-1);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static void updateActivationStatus(string custEmail)
{
string updateActivation = "UPDATE Customers SET CustActivated = 'Yes' WHERE custEmail = @CustEmail";
SqlConnection connection = TravelExpertsDB.GetConnection();
SqlCommand updateCommand = new SqlCommand(updateActivation, connection);
updateCommand.Parameters.AddWithValue("@CustEmail", custEmail);
try
{
connection.Open();
int countU = updateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <BookingDetail> GetBookingDetails(int BookingId)
{
List <BookingDetail> bookingDetails = new List <BookingDetail>(); // make an empty list
BookingDetail bd; // reference to new state object
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "select b.[BookingId],[ItineraryNo],[TripStart],[TripEnd],[PkgName] as 'PackageName',[RegionName],[Description],[Destination]," +
"[ClassName],[ProdName] as 'ProductName',[SupName] as 'SupplierName',[BasePrice] as 'ServicePrice',[PkgBasePrice] as 'PackagePrice'," +
"CAST(isnull([PkgBasePrice], 0) * [TravelerCount] + [BasePrice] AS DECIMAL(10,2)) as 'Total' " +
"from [Bookings] b " +
"inner join [BookingDetails] d " +
"on b.[BookingId] = d.[BookingId] " +
"inner join [Products_Suppliers] ps " +
"on ps.[ProductSupplierId] = d.[ProductSupplierId] " +
"inner join [Products] p " +
"on p.[ProductId] = ps.ProductId " +
"inner join [Suppliers] s " +
"on s.SupplierId = ps.SupplierId " +
"inner join [Regions] r " +
"on r.RegionId = d.[RegionId] " +
"inner join [Classes] c " +
"on c.ClassId = d.ClassId " +
"left join [Packages] pkg " +
"on pkg.PackageId = b.PackageId " +
"where b.[BookingId] = @BookingId " +
"order by[ProdName]";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@BookingId", BookingId);
try
{
// open connection
connection.Open();
// run the select command and process the results adding states to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
bd = new BookingDetail();
bd.BookingId = (int)reader["BookingId"];
bd.ItineraryNo = (double)reader["ItineraryNo"];
bd.TripStart = (DateTime)reader["TripStart"];
bd.TripEnd = (DateTime)reader["TripEnd"];
bd.PackageName = reader["PackageName"].ToString();
bd.RegionName = reader["RegionName"].ToString();
bd.Description = reader["Description"].ToString();
bd.Destination = reader["Destination"].ToString();
bd.ClassName = reader["ClassName"].ToString();
bd.ProductName = reader["ProductName"].ToString();
bd.SupplierName = reader["SupplierName"].ToString();
bd.PackagePrice = (decimal)reader["PackagePrice"];
bd.ServicePrice = (decimal)reader["ServicePrice"];
bd.Total = (decimal)reader["Total"];
bookingDetails.Add(bd);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(bookingDetails);
}
