// retrieve all objects
public static List <Affiliations> GetAll()
{
// create SELECT command
string query =
"SELECT AffilitationId, AffName, AffDesc " +
"FROM Affiliations ";
SqlConnection connection = TravelExperts.GetConection();
List <Affiliations> dataList = new List <Affiliations>(); // epmty list
Affiliations data; // for reading
// create connection
SqlCommand cmd = new SqlCommand(query, connection);
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// build object list to return
while (reader.Read()) // if there is a object with this ID
{
data = new Affiliations();
data.AffilitationId = reader["AffilitationId"].ToString();
data.AffName = reader["AffName"].ToString();
data.AffDesc = reader["AffDesc"].ToString();
dataList.Add(data);
}
return(dataList);
}
// retrieve all objects
public static List <Customers_Rewards> GetAll()
{
// create SELECT command
string query =
"SELECT CustomerId, RewardId, RwdNumber " +
"FROM Customers_Rewards ";
SqlConnection connection = TravelExperts.GetConection();
List <Customers_Rewards> dataList = new List <Customers_Rewards>(); // epmty list
Customers_Rewards data; // for reading
// create connection
SqlCommand cmd = new SqlCommand(query, connection);
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// build object list to return
while (reader.Read()) // if there is a object with this ID
{
data = new Customers_Rewards();
data.CustomerId = Convert.ToInt32(reader["CustomerId"]);
data.RewardId = Convert.ToInt32(reader["RewardId"]);
data.RwdNumber = reader["RwdNumber"].ToString();
dataList.Add(data);
}
return(dataList);
}
// Update object
// return indicator of success
public static bool Update(Fees oldObj, Fees newObj)
{
bool success = false; // did not update
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create UPDATE command
string updateStatment =
"UPDATE Fees SET " +
"FeeId = @NewFeeId, " +
"FeeName = @NewFeeName, " +
"FeeAmt = @NewFeeAmt, " +
"FeeDesc = @NewFeeDesc " +
"WHERE FeeId = @OldFeeId " + // identifies
"AND FeeName = @OldFeeName " + // the rest - for optimistic concurrency
"AND FeeAmt = @OldFeeAmt " +
"AND FeeDesc = @OldFeeDesc ";
SqlCommand cmd = new SqlCommand(updateStatment, connection);
// suply perameter value
// New object Values
cmd.Parameters.AddWithValue("@NewFeeId", newObj.FeeId);
cmd.Parameters.AddWithValue("@NewFeeName", newObj.FeeName);
cmd.Parameters.AddWithValue("@NewFeeAmt", newObj.FeeAmt);
cmd.Parameters.AddWithValue("@NewFeeDesc", newObj.FeeDesc);
// ID
cmd.Parameters.AddWithValue("@OldFeeId", oldObj.FeeId);
// Old object Values
cmd.Parameters.AddWithValue("@OldFeeName", oldObj.FeeName);
cmd.Parameters.AddWithValue("@OldFeeAmt", oldObj.FeeAmt);
cmd.Parameters.AddWithValue("@OldFeeDesc", oldObj.FeeDesc);
// execute the UPDATE command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated object ID to return
return(success);
}
// retrieve all objects
public static List <Fees> GetAll()
{
// create SELECT command
string query =
"SELECT FeeId, FeeName, FeeAmt, FeeDesc " +
"FROM Fees ";
SqlConnection connection = TravelExperts.GetConection();
List <Fees> dataList = new List <Fees>(); // epmty list
Fees data; // for reading
// create connection
SqlCommand cmd = new SqlCommand(query, connection);
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// build object list to return
while (reader.Read()) // if there is a object with this ID
{
data = new Fees();
data.FeeId = reader["FeeId"].ToString();
data.FeeName = reader["FeeName"].ToString();
data.FeeAmt = Convert.ToDecimal(reader["FeeAmt"]);
data.FeeDesc = reader["FeeDesc"].ToString();
dataList.Add(data);
}
return(dataList);
}
// retrieve all objects
public static List <Packages_Products_Suppliers> GetAll()
{
// create SELECT command
string query =
"SELECT PackageId, ProductSupplierId " +
"FROM Packages_Products_Suppliers ";
SqlConnection connection = TravelExperts.GetConection();
List <Packages_Products_Suppliers> dataList = new List <Packages_Products_Suppliers>(); // epmty list
Packages_Products_Suppliers data; // for reading
// create connection
SqlCommand cmd = new SqlCommand(query, connection);
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// build object list to return
while (reader.Read()) // if there is a object with this ID
{
data = new Packages_Products_Suppliers();
data.PackageId = Convert.ToInt32(reader["PackageId"]);
data.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
dataList.Add(data);
}
return(dataList);
}
// retrieve all objects
public static List <TripTypes> GetAll()
{
// create SELECT command
string query =
"SELECT SupplierId, SupName " +
"FROM Suppliers ";
SqlConnection connection = TravelExperts.GetConection();
List <TripTypes> dataList = new List <TripTypes>(); // epmty list
TripTypes data; // for reading
// create connection
SqlCommand cmd = new SqlCommand(query, connection);
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// build object list to return
while (reader.Read()) // if there is a object with this ID
{
data = new TripTypes();
data.TripTypeId = reader["SupplierId"].ToString();
data.TTName = reader["SupName"].ToString();
dataList.Add(data);
}
return(dataList);
}
// retrieve object with given ID
public static SupplierContacts GetValue(int objID)
{
SupplierContacts obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL, AffiliationId, SupplierId " +
"FROM SupplierContacts " +
"WHERE SupplierContactId = @SupplierContactId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@SupplierContactId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new SupplierContacts();
obj.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]);
obj.SupConFirstName = reader["SupConFirstName"].ToString();
obj.SupConLastName = reader["SupConLastName"].ToString();
obj.SupConCompany = reader["SupConCompany"].ToString();
obj.SupConAddress = reader["SupConAddress"].ToString();
obj.SupConCity = reader["SupConCity"].ToString();
obj.SupConProv = reader["SupConProv"].ToString();
obj.SupConPostal = reader["SupConPostal"].ToString();
obj.SupConCountry = reader["SupConCountry"].ToString();
obj.SupConBusPhone = reader["SupConBusPhone"].ToString();
obj.SupConFax = reader["SupConFax"].ToString();
obj.SupConEmail = reader["SupConEmail"].ToString();
obj.SupConURL = reader["SupConURL"].ToString();
obj.AffiliationId = reader["AffiliationId"].ToString();
obj.SupplierId = Convert.ToInt32(reader["SupplierId"]);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// retrieve object with given ID
public static Bookings GetValue(int objID)
{
Bookings obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT BookingId, BookingDate, BookingNo, TravelerCount, CustomerId, TripTypeId, PackageId " +
"FROM Bookings " +
"WHERE TripTypeId = @TripTypeId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@TripTypeId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Bookings();
obj.BookingId = Convert.ToInt32(reader["BookingId"]);
obj.BookingDate = (DateTime)reader["BookingDate"];
obj.BookingNo = reader["BookingNo"].ToString();
obj.TravelerCount = Convert.ToSingle(reader["TravelerCount"]);
obj.CustomerId = Convert.ToInt32(reader["CustomerId"]);
obj.TripTypeId = reader["TripTypeId"].ToString();
if (reader["PackageId"].ToString() == "")
{
obj.PackageId = null;
}
else
{
obj.PackageId = Convert.ToInt32(reader["PackageId"].ToString());
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// Delete object
// return indicator of success
public static bool Delete(Agencies obj)
{
bool success = false;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create DELETE command
string deleteStatment =
"DELETE FROM Agencies " +
"WHERE AgencyId = @AgencyId " + // needed for identification of object
"AND AgncyAddress = @AgncyAddress " + // the rest - for optimistic concurrency
"AND AgncyCity = @AgncyCity " +
"AND AgncyProv = @AgncyProv " +
"AND AgncyPostal = @AgncyPostal " +
"AND AgncyCountry = @AgncyCountry " +
"AND AgncyPhone = @AgncyPhone " +
"AND AgncyFax = @AgncyFax ";
SqlCommand cmd = new SqlCommand(deleteStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@AgencyId", obj.AgencyId);
cmd.Parameters.AddWithValue("@AgncyAddress", obj.AgncyAddress);
cmd.Parameters.AddWithValue("@AgncyCity", obj.AgncyCity);
cmd.Parameters.AddWithValue("@AgncyProv", obj.AgncyProv);
cmd.Parameters.AddWithValue("@AgncyPostal", obj.AgncyPostal);
cmd.Parameters.AddWithValue("@AgncyCountry", obj.AgncyCountry);
cmd.Parameters.AddWithValue("@AgncyPhone", obj.AgncyPhone);
cmd.Parameters.AddWithValue("@AgncyFax", obj.AgncyFax);
// execute the command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(success);
}
// retrieve object with given ID
public static Customers GetValue(int objID)
{
Customers obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, AgentId, CustPassword " +
"FROM Customers " +
"WHERE CustomerId = @CustomerId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@CustomerId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Customers();
obj.CustomerId = Convert.ToInt32(reader["CustomerId"]);
obj.CustFirstName = reader["CustFirstName"].ToString();
obj.CustLastName = reader["CustLastName"].ToString();
obj.CustAddress = reader["CustAddress"].ToString();
obj.CustCity = reader["CustCity"].ToString();
obj.CustProv = reader["CustProv"].ToString();
obj.CustPostal = reader["CustPostal"].ToString();
obj.CustCountry = reader["CustCountry"].ToString();
obj.CustHomePhone = reader["CustHomePhone"].ToString();
obj.CustBusPhone = reader["CustBusPhone"].ToString();
obj.CustEmail = reader["CustEmail"].ToString();
obj.AgentId = Convert.ToInt32(reader["AgentId"]);
obj.CustPassword = reader["CustPassword"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// retrieve object with given ID
public static BookingDetails GetValue(int objID)
{
BookingDetails obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT BookingDetailId, ItineraryNo, TripStart, TripEnd, Description, Destination, BasePrice, AgencyCommission, BookingId, RegionId, ClassId, FeeId, ProductSupplierId " +
"FROM BookingDetails " +
"WHERE BookingDetailId = @BookingDetailId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@BookingDetailId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new BookingDetails();
obj.BookingDetailId = Convert.ToInt32(reader["BookingDetailId"]);
obj.ItineraryNo = Convert.ToSingle(reader["ItineraryNo"]);
obj.TripStart = (DateTime)reader["TripStart"];
obj.TripEnd = (DateTime)reader["TripEnd"];
obj.Description = reader["Description"].ToString();
obj.Destination = reader["Destination"].ToString();
obj.BasePrice = Convert.ToDecimal(reader["BasePrice"]);
obj.AgencyCommission = Convert.ToDecimal(reader["AgencyCommission"]);
obj.BookingId = Convert.ToInt32(reader["BookingId"]);
obj.RegionId = reader["RegionId"].ToString();
obj.ClassId = reader["ClassId"].ToString();
obj.FeeId = reader["FeeId"].ToString();
obj.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// Update object
// return indicator of success
public static bool UpdateCustomers_Reward(Customers_Rewards oldObj, Customers_Rewards newObj)
{
bool success = false; // did not update
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create UPDATE command
string updateStatment =
"UPDATE Customers_Rewards SET " +
"CustomerId = @NewCustomerId, " +
"RewardId = @NewRewardId, " +
"RwdNumber = @NewRwdNumber " +
"WHERE CustomerId = @OldCustomerId " + // identifies
"AND RewardId = @OldRewardId " + // the rest - for optimistic concurrency
"AND RwdNumber = @OldRwdNumber ";
SqlCommand cmd = new SqlCommand(updateStatment, connection);
// suply perameter value
// New object Values
cmd.Parameters.AddWithValue("@NewCustomerId", newObj.CustomerId);
cmd.Parameters.AddWithValue("@NewRewardId", newObj.RewardId);
cmd.Parameters.AddWithValue("@NewRwdNumber", newObj.RwdNumber);
// ID
cmd.Parameters.AddWithValue("@OldCustomerId", oldObj.CustomerId);
// Old object Values
cmd.Parameters.AddWithValue("@OldRewardId", oldObj.RewardId);
cmd.Parameters.AddWithValue("@OldRwdNumber", oldObj.RwdNumber);
// execute the UPDATE command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated object ID to return
return(success);
}
// Delete object
// return indicator of success
public static bool Delete(Employees obj)
{
bool success = false;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create DELETE command
string deleteStatment =
"DELETE FROM Employees " +
"WHERE EmpEmail = @EmpEmail " + // needed for identification of object
"AND EmpFirstName = @EmpFirstName " + // the rest - for optimistic concurrency
"AND EmpMiddleInitial = @EmpMiddleInitial " +
"AND EmpLastName = @EmpLastName " +
"AND EmpBusPhone = @EmpBusPhone " +
"AND EmpPosition = @EmpPosition ";
SqlCommand cmd = new SqlCommand(deleteStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@EmpEmail", obj.EmpEmail);
cmd.Parameters.AddWithValue("@EmpFirstName", obj.EmpFirstName);
cmd.Parameters.AddWithValue("@EmpMiddleInitial", obj.EmpMiddleInitial);
cmd.Parameters.AddWithValue("@EmpLastName", obj.EmpLastName);
cmd.Parameters.AddWithValue("@EmpBusPhone", obj.EmpBusPhone);
cmd.Parameters.AddWithValue("@EmpPosition", obj.EmpPosition);
// execute the command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(success);
}
// insert new row to table
// return new object
public static int Add(SupplierContacts obj)
{
int custID = 0;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatment =
"INSERT INTO SupplierContacts(SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL, AffiliationId, SupplierId) " +
"OUTPUT inserted.[SupplierContactId] " +
"VALUES(@SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProv, @SupConPostal, @SupConCountry, @SupConBusPhone, @SupConFax, @SupConEmail, @SupConURL, @AffiliationId, @SupplierId) ";
SqlCommand cmd = new SqlCommand(insertStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@SupplierContactId", obj.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConFirstName", obj.SupConFirstName);
cmd.Parameters.AddWithValue("@SupConLastName", obj.SupConLastName);
cmd.Parameters.AddWithValue("@SupConCompany", obj.SupConCompany);
cmd.Parameters.AddWithValue("@SupConAddress", obj.SupConAddress);
cmd.Parameters.AddWithValue("@SupConCity", obj.SupConCity);
cmd.Parameters.AddWithValue("@SupConProv", obj.SupConProv);
cmd.Parameters.AddWithValue("@SupConPostal", obj.SupConPostal);
cmd.Parameters.AddWithValue("@SupConCountry", obj.SupConCountry);
cmd.Parameters.AddWithValue("@SupConBusPhone", obj.SupConBusPhone);
cmd.Parameters.AddWithValue("@SupConFax", obj.SupConFax);
cmd.Parameters.AddWithValue("@SupConEmail", obj.SupConEmail);
cmd.Parameters.AddWithValue("@SupConURL", obj.SupConURL);
cmd.Parameters.AddWithValue("@AffiliationId", obj.AffiliationId);
cmd.Parameters.AddWithValue("@SupplierId", obj.SupplierId);
// execute the INSERT command
try
{
// open the conection
connection.Open();
// execute insert command
custID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(custID);
}
// retrieve object with given ID
public static Agencies GetValue(int objID)
{
Agencies obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax " +
"FROM Agencies " +
"WHERE AgencyId = @AgencyId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@AgencyId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Agencies();
obj.AgencyId = Convert.ToInt32(reader["AgencyId"]);
obj.AgncyAddress = reader["AgncyAddress"].ToString();
obj.AgncyCity = reader["AgncyCity"].ToString();
obj.AgncyProv = reader["AgncyProv"].ToString();
obj.AgncyPostal = reader["AgncyPostal"].ToString();
obj.AgncyCountry = reader["AgncyCountry"].ToString();
obj.AgncyPhone = reader["AgncyPhone"].ToString();
obj.AgncyFax = reader["AgncyFax"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// Update object
// return indicator of success
public static bool Update(Packages_Products_Suppliers oldObj, Packages_Products_Suppliers newObj)
{
bool success = false; // did not update
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create UPDATE command
string updateStatment =
"UPDATE Packages_Products_Suppliers SET " +
"PackageId = @NewPackageId, " +
"ProductSupplierId = @NewProductSupplierId " +
"WHERE PackageId = @OldPackageId " + // identifies
"AND ProductSupplierId = @OldProductSupplierId "; // the rest - for optimistic concurrency
SqlCommand cmd = new SqlCommand(updateStatment, connection);
// suply perameter value
// New object Values
cmd.Parameters.AddWithValue("@NewPackageId", newObj.PackageId);
cmd.Parameters.AddWithValue("@NewProductSupplierId", newObj.ProductSupplierId);
// ID
cmd.Parameters.AddWithValue("@OldPackageId", oldObj.PackageId);
// Old object Values
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldObj.ProductSupplierId);
// execute the UPDATE command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated object ID to return
return(success);
}
// retrieve all objects
public static List <BookingHistory> GetAllByCustomer(int custid)
{
// create SELECT command
string query =
"SELECT b.BookingId, b.BookingDate, b.BookingNo, b.TravelerCount, b.CustomerId, b.TripTypeId, b.PackageId, " +
" p.PkgName, p.PkgStartDate, p.PkgEndDate, p.PkgDesc, p.PkgBasePrice, p.PkgImg " +
" FROM Bookings b join Packages p on b.PackageId =p.PackageId " +
" where b.CustomerId=@CustomerId";
SqlConnection connection = TravelExperts.GetConection();
List <BookingHistory> dataList = new List <BookingHistory>(); // epmty list
BookingHistory data; // for reading
// create connection
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@CustomerId", custid);
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// build object list to return
while (reader.Read()) // if there is a object with this ID
{
data = new BookingHistory();
data.BookingId = Convert.ToInt32(reader["BookingId"]);
data.BookingDate = (DateTime)reader["BookingDate"];
data.BookingNo = reader["BookingNo"].ToString();
data.TravelerCount = Convert.ToSingle(reader["TravelerCount"]);
data.CustomerId = Convert.ToInt32(reader["CustomerId"]);
data.TripTypeId = reader["TripTypeId"].ToString();
if (reader["PackageId"].ToString() == "")
{
data.PackageId = null;
}
else
{
data.PackageId = Convert.ToInt32(reader["PackageId"].ToString());
}
data.PkgName = reader["PkgName"].ToString();
data.PkgStartDate = (DateTime)reader["PkgStartDate"];
data.PkgEndDate = (DateTime)reader["PkgEndDate"];
data.PkgDesc = reader["PkgDesc"].ToString();
data.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
data.PkgImg = reader["PkgImg"].ToString();
dataList.Add(data);
}
return(dataList);
}
// retrieve object with given ID
public static Agents GetValue(int objID)
{
Agents obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT AgentId, AgtFirstName, AgtMiddleInitial, AgtLastName, AgtBusPhone, AgtEmail, AgtPosition, AgencyId " +
"FROM Agents " +
"WHERE AgentId = @AgentId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@AgentId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Agents();
obj.AgentId = Convert.ToInt32(reader["AgentId"]);
obj.AgtFirstName = reader["AgtFirstName"].ToString();
obj.AgtMiddleInitial = reader["AgtMiddleInitial"].ToString();
obj.AgtLastName = reader["AgtLastName"].ToString();
obj.AgtBusPhone = reader["AgtBusPhone"].ToString();
obj.AgtEmail = reader["AgtEmail"].ToString();
obj.AgtPosition = reader["AgtPosition"].ToString();
obj.AgencyId = Convert.ToInt32(reader["AgencyId"]);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// Delete object
// return indicator of success
public static bool Delete(Bookings obj)
{
bool success = false;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create DELETE command
string deleteStatment =
"DELETE FROM Bookings " +
"WHERE TripTypeId = @TripTypeId " + // needed for identification of object
"AND TTName = @TTName "; // the rest - for optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@BookingId", obj.BookingId);
cmd.Parameters.AddWithValue("@BookingDate", obj.BookingDate);
cmd.Parameters.AddWithValue("@BookingNo", obj.BookingNo);
cmd.Parameters.AddWithValue("@TravelerCount", obj.TravelerCount);
cmd.Parameters.AddWithValue("@TripTypeId", obj.TripTypeId);
cmd.Parameters.AddWithValue("@CustomerId", obj.CustomerId);
cmd.Parameters.AddWithValue("@TripTypeId", obj.TripTypeId);
cmd.Parameters.AddWithValue("@PackageId", obj.PackageId);
// execute the command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(success);
}
// Delete object
// return indicator of success
public static bool Delete(CreditCards obj)
{
bool success = false;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create DELETE command
string deleteStatment =
"DELETE FROM CreditCards " +
"WHERE CreditCardId = @CreditCardId " + // needed for identification of object
"AND CCName = @CCName " + // the rest - for optimistic concurrency
"AND CCNumber = @CCNumber " +
"AND CCExpiry = @CCExpiry " +
"AND CustomerId = @CustomerId ";
SqlCommand cmd = new SqlCommand(deleteStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@CreditCardId", obj.CreditCardId);
cmd.Parameters.AddWithValue("@CCName", obj.CCName);
cmd.Parameters.AddWithValue("@CCNumber", obj.CCNumber);
cmd.Parameters.AddWithValue("@CCExpiry", obj.CCExpiry);
cmd.Parameters.AddWithValue("@CustomerId", obj.CustomerId);
// execute the command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(success);
}
// Delete object
// return indicator of success
public static bool Delete(Packages obj)
{
bool success = false;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create DELETE command
string deleteStatment =
"DELETE FROM Packages " +
"WHERE PackageId = @PackageId " + // needed for identification of object
"AND PkgName = @PkgName " + // the rest - for optimistic concurrency
"AND PkgStartDate = @PkgStartDate " +
"AND PkgEndDate = @PkgEndDate " +
"AND PkgDesc = @PkgDesc " +
"AND PkgBasePrice = @PkgBasePrice " +
"AND PkgAgencyCommission = @PkgAgencyCommission ";
SqlCommand cmd = new SqlCommand(deleteStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@PackageId", obj.PackageId);
cmd.Parameters.AddWithValue("@PkgName", obj.PkgName);
cmd.Parameters.AddWithValue("@PkgStartDate", obj.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", obj.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", obj.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", obj.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", obj.PkgAgencyCommission);
// execute the command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0) success = true;
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return success;
}
// insert new row to table
// return new object
public static int Add(Customers obj)
{
int custID = 0;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatment =
"INSERT INTO Customers(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, /*AgentId, */CustPassword) " +
"OUTPUT inserted.[CustomerId] " +
"VALUES(@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail,/* @AgentId,*/ @CustPassword) ";
SqlCommand cmd = new SqlCommand(insertStatment, connection);
// suply perameter value
//cmd.Parameters.AddWithValue("@CustomerId", obj.CustomerId);
cmd.Parameters.AddWithValue("@CustFirstName", obj.CustFirstName);
cmd.Parameters.AddWithValue("@CustLastName", obj.CustLastName);
cmd.Parameters.AddWithValue("@CustAddress", obj.CustAddress);
cmd.Parameters.AddWithValue("@CustCity", obj.CustCity);
cmd.Parameters.AddWithValue("@CustProv", obj.CustProv);
cmd.Parameters.AddWithValue("@CustPostal", obj.CustPostal);
cmd.Parameters.AddWithValue("@CustCountry", obj.CustCountry);
cmd.Parameters.AddWithValue("@CustHomePhone", obj.CustHomePhone);
cmd.Parameters.AddWithValue("@CustBusPhone", obj.CustBusPhone);
cmd.Parameters.AddWithValue("@CustEmail", obj.CustEmail);
//cmd.Parameters.AddWithValue("@AgentId", obj.AgentId);
cmd.Parameters.AddWithValue("@CustPassword", obj.CustPassword);
// execute the INSERT command
try
{
// open the conection
connection.Open();
// execute insert command
custID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(custID);
}
// retrieve object with given ID
public static Employees GetValue(int objID)
{
Employees obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT EmpFirstName, EmpMiddleInitial, EmpLastName, EmpBusPhone, EmpEmail, EmpPosition " +
"FROM Employees " +
"WHERE EmpEmail = @EmpEmail ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@EmpEmail", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Employees();
obj.EmpFirstName = reader["EmpFirstName"].ToString();
obj.EmpMiddleInitial = reader["EmpMiddleInitial"].ToString();
obj.EmpLastName = reader["EmpLastName"].ToString();
obj.EmpBusPhone = reader["EmpBusPhone"].ToString();
obj.EmpEmail = reader["EmpEmail"].ToString();
obj.EmpPosition = reader["EmpPosition"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// retrieve object with given ID
public static Packages GetValue(int objID)
{
Packages obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = @PackageId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@PackageId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Packages();
obj.PackageId = Convert.ToInt32(reader["PackageId"]);
obj.PkgName = reader["PkgName"].ToString();
obj.PkgStartDate = (DateTime)reader["PkgStartDate"];
obj.PkgEndDate = (DateTime)reader["PkgEndDate"];
obj.PkgDesc = reader["PkgDesc"].ToString();
obj.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
obj.PkgAgencyCommission = Convert.ToDecimal(reader["PkgAgencyCommission"]);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return obj;
}
// insert new row to table
// return new object
public static int Add(BookingDetails obj)
{
int custID = 0;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatment =
"INSERT INTO BookingDetails(BookingDetailId, ItineraryNo, TripStart, TripEnd, Description, Destination, BasePrice, AgencyCommission, BookingId, RegionId, ClassId, FeeId, ProductSupplierId) " +
"OUTPUT inserted.[BookingDetailId] " +
"VALUES(@BookingDetailId, @ItineraryNo, @TripStart, @TripEnd, @Description, @Destination, @BasePrice, @AgencyCommission, @BookingId, @RegionId, @ClassId, @FeeId, @ProductSupplierId) ";
SqlCommand cmd = new SqlCommand(insertStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@BookingDetailId", obj.BookingDetailId);
cmd.Parameters.AddWithValue("@ItineraryNo", obj.ItineraryNo);
cmd.Parameters.AddWithValue("@TripStart", obj.TripStart);
cmd.Parameters.AddWithValue("@TripEnd", obj.TripEnd);
cmd.Parameters.AddWithValue("@Description", obj.Description);
cmd.Parameters.AddWithValue("@Destination", obj.Destination);
cmd.Parameters.AddWithValue("@BasePrice", obj.BasePrice);
cmd.Parameters.AddWithValue("@AgencyCommission", obj.AgencyCommission);
cmd.Parameters.AddWithValue("@BookingId", obj.BookingId);
cmd.Parameters.AddWithValue("@RegionId", obj.RegionId);
cmd.Parameters.AddWithValue("@ClassId", obj.ClassId);
cmd.Parameters.AddWithValue("@FeeId", obj.FeeId);
cmd.Parameters.AddWithValue("@ProductSupplierId", obj.ProductSupplierId);
// execute the INSERT command
try
{
// open the conection
connection.Open();
// execute insert command
custID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(custID);
}
// retrieve object with given ID
public static CreditCards GetValue(int objID)
{
CreditCards obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT CreditCardId, CCName, CCNumber, CCExpiry, CustomerId " +
"FROM CreditCards " +
"WHERE CreditCardId = @CreditCardId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@CreditCardId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new CreditCards();
obj.CreditCardId = Convert.ToInt32(reader["CreditCardId"]);
obj.CCName = reader["CCName"].ToString();
obj.CCNumber = reader["CCNumber"].ToString();
obj.CCExpiry = (DateTime)reader["CCExpiry"];
obj.CustomerId = Convert.ToInt32(reader["CustomerId"]);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// retrieve object with given ID
public static Fees GetValue(int objID)
{
Fees obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT FeeId, FeeName, FeeAmt, FeeDesc " +
"FROM Fees " +
"WHERE FeeId = @FeeId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@FeeId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Fees();
obj.FeeId = reader["FeeId"].ToString();
obj.FeeName = reader["FeeName"].ToString();
obj.FeeAmt = Convert.ToDecimal(reader["FeeAmt"]);
obj.FeeDesc = reader["FeeDesc"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// Get one package
public static Packages GetPackage(int PackageID)
{
Packages package = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query = "SELECT PackageID, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages WHERE PackageID = @PackageID";
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@PackageID", PackageID);
// run the SELECT query
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build customer object to return
if (reader.Read()) // if there is a customer with this ID
{
package = new Packages();
package.PackageId = (int)reader["PackageID"]; // Primary Key and thus not null
package.PkgName = reader["PkgName"].ToString();
package.PkgStartDate = (DateTime)reader["PkgStartDate"];
package.PkgEndDate = (DateTime)reader["PkgEndDate"];
package.PkgDesc = reader["PkgDesc"].ToString();
package.PkgBasePrice = (decimal)reader["PkgBasePrice"];
package.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return package;
}
// retrieve object with given ID
public static Products_Suppliers GetValue(int objID)
{
Products_Suppliers obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@ProductSupplierId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Products_Suppliers();
obj.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
obj.ProductId = Convert.ToInt32(reader["ProductId"]);
obj.SupplierId = Convert.ToInt32(reader["SupplierId"]);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// retrieve object with given ID
public static Regions GetValue(int objID)
{
Regions obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT RegionId, RegionName " +
"FROM Regions " +
"WHERE RegionId = @RegionId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@RegionId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new Regions();
obj.RegionId = reader["RegionId"].ToString();
obj.RegionName = reader["RegionName"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}