// Method to add a new product to the Suppliers table of Travel Experts
// and return the auto-generated SupplierId. (T. Leslie)
public static bool AddSupplier(Supplier supplier)
{
bool success = true;
SqlConnection conn = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Suppliers (SupplierId, SupName) " +
"VALUES(@SupplierId, @SupName)";
SqlCommand insertCommand = new SqlCommand(insertStatement, conn);
insertCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
insertCommand.Parameters.AddWithValue("@SupName", supplier.SupName);
try
{
conn.Open();
insertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(success);
}
// Method to delete a SupplierContact record for a given supplier. (T. Leslie)
public static bool DeleteSupplierContacts(Supplier supplier)
{
bool success = true;
SqlConnection conn = TravelExpertsDB.GetConnection();
string deleteStatement = "DELETE FROM SupplierContacts " +
"WHERE SupplierId = @SupplierId";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, conn);
deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
try
{
conn.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count == 0)
{
success = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(success);
}
// Update Products Supplier Table
public static bool UpdateProductsSupplier(ProductsSupplier oldProdSuppObj, ProductsSupplier newProdSuppObj)
{
bool updateSuccess = true;
string updateStatement = "UPDATE Products_Suppliers " +
"SET ProductId = @newProductId, " +
"SupplierId = @newSupplierId " +
"WHERE ProductSupplierId = @oldProductSupplierId " +
"AND (ProductId = @oldProductId " +
"OR ProductId IS NULL AND @oldProductId IS NULL) " +
"AND (SupplierId = @oldSupplierId " +
"OR SupplierId IS NULL AND @oldSupplierId IS NULL)";
// Get connection to Travel Experts DB
SqlConnection connection = TravelExpertsDB.GetConnection();
// Create a select command object
SqlCommand updateCmd = new SqlCommand(updateStatement, connection);
// Assign value to parameter(s)
// Verify if newProdSuppObj.ProductId is null
if (newProdSuppObj.ProductId == null)
{
updateCmd.Parameters.AddWithValue("@newProductId", DBNull.Value);
}
else
{
updateCmd.Parameters.AddWithValue("@newProductId", newProdSuppObj.ProductId);
}
// Verify if newProdSuppObj.SupplierId is null
if (newProdSuppObj.SupplierId == null)
{
updateCmd.Parameters.AddWithValue("@newSupplierId", DBNull.Value);
}
else
{
updateCmd.Parameters.AddWithValue("@newSupplierId", newProdSuppObj.SupplierId);
}
// Execute the update command
try {
connection.Open();
int rowsUpdated = updateCmd.ExecuteNonQuery();
// Check for concurrency, another user might have updated or deleted in the meantime
if (rowsUpdated == 0)
{
updateSuccess = false;
}
} catch (Exception ex) {
throw ex;
} finally {
connection.Close();
}
return(updateSuccess);
}
// Get Product Suppliers by product id and supplier id
public static ProductsSupplier GetProductsSupplierByProductIdAndSupplierId(int productId, int supplierId)
{
ProductsSupplier prodSuppObj = null;
string selectStatement = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductId = @productId " +
"AND SupplierId = @supplierId";
// Get connection to Travel Experts DB
SqlConnection connection = TravelExpertsDB.GetConnection();
// Create a select command object
SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
// Assign value to parameter(s)
selectCmd.Parameters.AddWithValue("@productId", productId);
selectCmd.Parameters.AddWithValue("@supplierId", supplierId);
// Execute the select command and start the reading process from DB
try {
connection.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
if (dr.Read()) // if exists
{
prodSuppObj = new ProductsSupplier();
prodSuppObj.ProductSupplierId = (int)dr["ProductSupplierId"];
// Both Product ID and Supplier ID can be null, need to verify while reading
// from DB
if (dr["ProductId"] is DBNull)
{
prodSuppObj.ProductId = null;
}
else
{
prodSuppObj.ProductId = (int)dr["ProductId"];
}
if (dr["SupplierId"] is DBNull)
{
prodSuppObj.SupplierId = null;
}
else
{
prodSuppObj.SupplierId = (int)dr["SupplierId"];
}
}
} catch (Exception ex) {
throw ex;
} finally {
connection.Close();
}
return(prodSuppObj);
}
// Get All products suppliers from the DB
public static List <ProductsSupplier> GetAllProductsSuppliers()
{
List <ProductsSupplier> prodSuppList = new List <ProductsSupplier>();
string selectStatement = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"ORDER BY ProductSupplierId";
// Get connection to Travel Experts DB
SqlConnection connection = TravelExpertsDB.GetConnection();
// Create a select command object
SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
// Execute the select command and start the reading process from DB
try {
connection.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
while (dr.Read())
{
ProductsSupplier prodSuppObj = new ProductsSupplier();
prodSuppObj.ProductSupplierId = (int)dr["ProductSupplierId"];
// Both Product ID and Supplier ID can be null, need to verify while reading
// from DB
if (dr["ProductId"] is DBNull)
{
prodSuppObj.ProductId = null;
}
else
{
prodSuppObj.ProductId = (int)dr["ProductId"];
}
if (dr["SupplierId"] is DBNull)
{
prodSuppObj.SupplierId = null;
}
else
{
prodSuppObj.SupplierId = (int)dr["SupplierId"];
}
// Add to product suppliers list
prodSuppList.Add(prodSuppObj);
}
} catch (Exception ex) {
throw ex;
} finally {
connection.Close();
}
return(prodSuppList);
}
// This method returns a list of Supplier objects from the database. (T. Leslie)
public static List <Supplier> GetSuppliers()
{
List <Supplier> suppliers = new List <Supplier>();
SqlConnection conn = TravelExpertsDB.GetConnection();
// create a sql select statement
string selectStatement =
"SELECT SupplierId, SupName " +
"FROM Suppliers";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();// open connection
SqlDataReader sr = selectCommand.ExecuteReader();
while (sr.Read()) // while product record exists
{
Supplier supplier = new Supplier(); // instantiate Supplier object
// set properties of new object from database record
supplier.SupplierId = (int)sr["SupplierId"];
if (sr["SupName"] is DBNull)
{
supplier.SupName = null;
}
else
{
supplier.SupName = (string)(sr["SupName"]);
}
suppliers.Add(supplier);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(suppliers);
}
// Method to return a List of SupplierContacts. (T. Leslie)
private static List <SupplierContact> GetSupplierContacts()
{
List <SupplierContact> suppliercontacts = new List <SupplierContact>();
SqlConnection conn = TravelExpertsDB.GetConnection();
// create a sql select statement
string selectStatement =
"SELECT SupplierContactId, SupplierId " +
"FROM SupplierContacts";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();// open connection
SqlDataReader sr = selectCommand.ExecuteReader();
while (sr.Read()) // product record exists
{
SupplierContact suppliercontact = new SupplierContact();
suppliercontact.SupplierContactId = (int)sr["SupplierContactId"];
suppliercontact.SupplierId = (int)sr["SupplierId"];
if (sr["SupplierId"] is DBNull)
{
suppliercontact.SupplierId = null;
}
else
{
suppliercontact.SupplierId = (int)(sr["SupplierId"]);
}
suppliercontacts.Add(suppliercontact);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(suppliercontacts);
}
// Insert a new product
public static int AddProductsSupplier(ProductsSupplier prodSuppObj)
{
int prodSuppId = 0;
string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " +
"OUTPUT Inserted.ProductSupplierId " +
"VALUES (@ProductId, @SupplierId)";
// Get connection to Travel Experts DB
SqlConnection connection = TravelExpertsDB.GetConnection();
// Create an insert command object
SqlCommand insertCmd = new SqlCommand(insertStatement, connection);
// Assign value to parameter(s)
// Verify if Product ID from object is null
if (prodSuppObj.ProductId == null)
{
insertCmd.Parameters.AddWithValue("@ProductId", DBNull.Value);
}
else
{
insertCmd.Parameters.AddWithValue("@ProductId", prodSuppObj.ProductId);
}
// Verify if Supplier ID from object is null
if (prodSuppObj.SupplierId == null)
{
insertCmd.Parameters.AddWithValue("@SupplierId", DBNull.Value);
}
else
{
insertCmd.Parameters.AddWithValue("@SupplierId", prodSuppObj.SupplierId);
}
// Execute the insert command
try {
connection.Open();
// Returns the auto generated ProductSupplierId
prodSuppId = (int)insertCmd.ExecuteScalar();
} catch (Exception ex) {
throw ex;
} finally {
connection.Close();
}
return(prodSuppId);
}
// Method to return a Supplier object for the given supplierid. (T. Leslie)
public static Supplier GetSupplier(int supplierid)
{
Supplier supplier = null;
SqlConnection conn = TravelExpertsDB.GetConnection();
// create a sql select statement
string selectStatement =
"SELECT SupplierId, SupName " +
"FROM Suppliers " +
"WHERE SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
selectCommand.Parameters.AddWithValue("@SupplierId", supplierid);
try
{
conn.Open();// open connection
SqlDataReader sr = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (sr.Read()) // product record exists
{
supplier = new Supplier();
supplier.SupplierId = (int)sr["SupplierId"];
if (sr["SupName"] is DBNull)
{
supplier.SupName = null;
}
else
{
supplier.SupName = (string)(sr["SupName"]);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(supplier);
}
public static Package GetPackage(int packageId)
{
Package package = null;
SqlConnection connection = TravelExpertsDB.GetConnection();// create the connection
// create a command string
string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = @PackageId";
// connect to the database and execute the command
SqlCommand cmd = new SqlCommand(selectQuery, connection);
//define the command's object value
cmd.Parameters.AddWithValue("@PackageId", packageId);
try
{
connection.Open();// open connection
//read one row from database with the specific value
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) // we have a package
{
//create the Package array
package = new Package();
package.PackageId = (int)reader["PackageId"];
package.PkgName = reader["PkgName"].ToString();
package.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
package.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
package.PkgDesc = reader["PkgDesc"].ToString();
package.PkgBasePrice = (decimal)reader["PkgBasePrice"];
package.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
} // end of if
} // end of try
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();//close connection
}
return(package);
}// end of GetPackage
// Method to delete the passed supplier from the database. (T. Leslie)
public static bool DeleteSupplier(Supplier supplier)
{
bool success = true;
// Delete SupplierContacts record for the supplier first
if (IsInSupplierContacts(supplier))
{
DeleteSupplierContacts(supplier);
}
SqlConnection conn = TravelExpertsDB.GetConnection();
// prepare a delete statement
string deleteStatement = "DELETE FROM Suppliers " +
"WHERE SupplierId = @SupplierId " +
"AND SupName = @SupName";
// prepare a delete command
SqlCommand deleteCommand = new SqlCommand(deleteStatement, conn);
deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
deleteCommand.Parameters.AddWithValue("@SupName", supplier.SupName);
try
{
conn.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count == 0)
{
success = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(success);
}
// Method to return a Product object for the given ProductId. (T. Leslie)
public static Product GetProduct(int productid)
{
Product product = null;
SqlConnection conn = TravelExpertsDB.GetConnection();
// create a sql select statement
string selectStatement =
"SELECT ProductId, ProdName " +
"FROM Products " +
"WHERE ProductId = @ProductId";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
selectCommand.Parameters.AddWithValue("@ProductId", productid);
try
{
conn.Open();// open connection
SqlDataReader pr = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (pr.Read()) // product record exists
{
product = new Product();
product.ProductId = (int)pr["ProductId"];
product.ProdName = pr["ProdName"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(product);
}
}//List of PPS end
public static PackagesProductsSuppliers GetPackagesProductsSuppliersByPkgIdAndProductSupplierId(int pkgId, int prodSuppId)
{
PackagesProductsSuppliers pps = null;
SqlConnection connection = TravelExpertsDB.GetConnection(); //Connecting to TRavel Experts Database
string select = "Select * FROM Packages_Products_Suppliers " + //selecting PackageId and Product Supplier ID
"WHERE PackageId = @pkgId " +
"AND ProductSupplierId = @prodSuppId";
SqlCommand sqlCommand = new SqlCommand(select, connection);
sqlCommand.Parameters.AddWithValue("@pkgId", pkgId);
sqlCommand.Parameters.AddWithValue("@prodSuppId", prodSuppId);
try
{
connection.Open(); //opening connection
SqlDataReader read = sqlCommand.ExecuteReader();
if (read.Read())
{
pps = new PackagesProductsSuppliers();
pps.PackageId = (int)read["PackageId"];
pps.ProductSupplierId = (int)read["ProductSupplierId"];
}
}
catch (Exception ex) //catching all exeptions
{
throw ex;
}
finally
{
connection.Close(); //closing connection
}
return(pps); //returning List
}
//Updating DataBase
public static bool UpdatePackagesProductsSuppliers(PackagesProductsSuppliers oldPPS, PackagesProductsSuppliers newPPS)
{
SqlConnection connection = TravelExpertsDB.GetConnection();//connection to DB
bool success = true;
//finding record it needs to update "old" and replacing it with the "new" PPS info
string update = "UPDATE Packages_Products_Suppliers SET " +
"ProductSupplierId = @NewProductSupplierId " +
"WHERE PackageId = @OldPackageId " +
"AND ProductSupplierId = @OldProductSupplierId";
SqlCommand updateCmd = new SqlCommand(update, connection);
updateCmd.Parameters.AddWithValue("@NewProductSupplierId", newPPS.ProductSupplierId);
updateCmd.Parameters.AddWithValue("@OldPackageId", oldPPS.PackageId);
updateCmd.Parameters.AddWithValue("@OldProductSupplierId", oldPPS.ProductSupplierId);
try
{
connection.Open();
int rowsUpdated = updateCmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false; //if rows where not updated and success returns false
}
}
catch (Exception ex)//catching all exeptions
{
throw ex;
}
finally
{
connection.Close(); //closing connection
}
return(success); //returning updated Info if it was "true"
}//updating Method end
// This method will return a list of Product objects from the database. (T. Leslie)
public static List <Product> GetProducts()
{
List <Product> products = new List <Product>();
SqlConnection conn = TravelExpertsDB.GetConnection();
// create a sql select statement
string selectStatement =
"SELECT ProductId, ProdName " +
"FROM Products";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();// open connection
SqlDataReader sr = selectCommand.ExecuteReader();
while (sr.Read()) // product record exists
{
Product product = new Product();
product.ProductId = (int)sr["ProductId"];
product.ProdName = sr["ProdName"].ToString();
products.Add(product);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(products);
}
// Method to update an existing Product record in the database.
// This method compares the 'old' product which was 'SELECT'ed
// originally against the product record at the time of 'UPDATE'ing
// to ensure that no changes have occurred. In other words, this is
// a concurrency check prior to updating the record. (T. Leslie)
public static bool UpdateProduct(Product oldProduct, Product newProduct)
{
bool success = true;
SqlConnection conn = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Products SET " +
"ProdName = @NewProdName " +
"WHERE ProductId = @OldProductId " + // to identify record to update
"AND ProdName = @OldProdName";
SqlCommand updateCommand = new SqlCommand(updateStatement, conn);
updateCommand.Parameters.AddWithValue("@NewProdName", newProduct.ProdName);
updateCommand.Parameters.AddWithValue("@OldProductId", oldProduct.ProductId);
updateCommand.Parameters.AddWithValue("@OldProdName", oldProduct.ProdName);
try
{
conn.Open();
int rowsUpdated = updateCommand.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false; // did not update (another user updated or deleted)
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(success);
}
// Get All Packages
public static List <Package> GetAllPackages()
{
List <Package> packages = new List <Package>();
SqlConnection connection = TravelExpertsDB.GetConnection();// create the connection
// create a command string
string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages ";
// connect to the database and execute the command
SqlCommand cmd = new SqlCommand(selectQuery, connection);
try
{
connection.Open();// open connection
//read one row from database with the specific value
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // we have a package
{
//create the Package array
Package package = new Package();
package.PackageId = (int)reader["PackageId"];
package.PkgName = reader["PkgName"].ToString();
if (reader["PkgStartDate"] is DBNull)
{
package.PkgStartDate = null;
}
else
{
package.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
}
if (reader["PkgEndDate"] is DBNull)
{
package.PkgEndDate = null;
}
else
{
package.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
}
if (reader["PkgDesc"] is DBNull)
{
package.PkgDesc = null;
}
else
{
package.PkgDesc = reader["PkgDesc"].ToString();
}
package.PkgBasePrice = (decimal)reader["PkgBasePrice"];
if (reader["PkgAgencyCommission"] is DBNull)
{
package.PkgAgencyCommission = null;
}
else
{
package.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
}
packages.Add(package);
} // end of while
} // end of try
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();//close connection
}
return(packages);
}// end of GetPackage
//update(modify) function
public static bool UpdatePackage(Package oldPackage, Package newPackage)
{
bool success = true;
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Packages SET " +
"PkgName = @NewPkgName, " +
"PkgStartDate = @NewPkgStartDate, " +
"PkgEndDate = @NewPkgEndDate, " +
"PkgDesc = @NewPkgDesc, " +
"PkgBasePrice = @NewPkgBasePrice, " +
"PkgAgencyCommission = @NewPkgAgencyCommission " +
"WHERE PackageId = @OldPackageId " + // to identify record to update
"AND PkgName = @OldPkgName " + // remaining conditions for optimistic concurrency
"AND (PkgStartDate = @OldPkgStartDate " +
"OR PkgStartDate IS NULL AND @OldPkgStartDate IS NULL) " +
"AND (PkgEndDate = @OldPkgEndDate " +
"OR PkgEndDate IS NULL AND @OldPkgEndDate IS NULL) " +
"AND (PkgDesc = @OldPkgDesc " +
"OR PkgDesc IS NULL AND @OldPkgDesc IS NULL) " +
"AND PkgBasePrice = @OldPkgBasePrice " +
"AND (PkgAgencyCommission = @OldPkgAgencyCommission " +
"OR PkgAgencyCommission IS NULL AND @OldPkgAgencyCommission IS NULL)";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewPkgName", newPackage.PkgName);
if (newPackage.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", newPackage.PkgStartDate);
}
if (newPackage.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", newPackage.PkgEndDate);
}
if (newPackage.PkgDesc == null)
{
cmd.Parameters.AddWithValue("@NewPkgDesc", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgDesc", newPackage.PkgDesc);
}
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPackage.PkgBasePrice);
if (newPackage.PkgAgencyCommission == null)
{
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", newPackage.PkgAgencyCommission);
}
// OLD
cmd.Parameters.AddWithValue("@OldPackageId", oldPackage.PackageId);
cmd.Parameters.AddWithValue("@OldPkgName", oldPackage.PkgName);
if (oldPackage.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", oldPackage.PkgStartDate);
}
if (oldPackage.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", oldPackage.PkgEndDate);
}
if (oldPackage.PkgDesc == null)
{
cmd.Parameters.AddWithValue("@OldPkgDesc", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgDesc", oldPackage.PkgDesc);
}
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPackage.PkgBasePrice);
if (oldPackage.PkgAgencyCommission == null)
{
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", oldPackage.PkgAgencyCommission);
}
try
{
con.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false; // did not update (another user updated or deleted)
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(success);
}
// Delete function to delete a package from database
public static bool DeletePackage(Package package)
{
bool success = true;
SqlConnection con = TravelExpertsDB.GetConnection();
string deleteStatement = "DELETE FROM Packages " +
"WHERE PackageId = @PackageId " + // to identify record
"AND PkgName = @PkgName " + // remaining: for optimistic concurrency
"AND (PkgStartDate = @PkgStartDate " +
"OR PkgStartDate IS NULL AND @PkgStartDate IS NULL) " +
"AND (PkgEndDate = @PkgEndDate " +
"OR PkgEndDate IS NULL AND @PkgEndDate IS NULL) " +
"AND (PkgDesc = @PkgDesc " +
"OR PkgDesc IS NULL AND @PkgDesc IS NULL) " +
"AND PkgBasePrice = @PkgBasePrice " +
"AND (PkgAgencyCommission = @PkgAgencyCommission " +
"OR PkgAgencyCommission IS NULL AND @PkgAgencyCommission IS NULL)";
SqlCommand cmd = new SqlCommand(deleteStatement, con);
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
cmd.Parameters.AddWithValue("@PackageId", package.PackageId);
if (package.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
}
if (package.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
}
if (package.PkgDesc == null)
{
cmd.Parameters.AddWithValue("@PkgDesc", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
}
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
if (package.PkgAgencyCommission == null)
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
}
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count == 0) // optimistic concurrency violation
{
success = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(success);
}
// Get Agent details by Agent First Name and Last Name
public static List <Agent> GetAllAgents()
{
List <Agent> agents = new List <Agent>();
string selectStatement = "SELECT AgentId, AgtFirstName, AgtMiddleInitial, AgtLastName, " +
"AgtBusPhone, AgtEmail, AgtPosition, AgencyId " +
"FROM Agents";
// Get connection to Travel Experts DB
SqlConnection connection = TravelExpertsDB.GetConnection();
// Create a select command object
SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
// Execute command
try {
connection.Open();
SqlDataReader dr = selectCmd.ExecuteReader();
while (dr.Read())
{
Agent agent = new Agent();
agent.AgentId = (int)dr["AgentId"];
if (dr["AgtFirstName"] is DBNull)
{
agent.AgtFirstName = null;
}
else
{
agent.AgtFirstName = dr["AgtFirstName"].ToString();
}
if (dr["AgtMiddleInitial"] is DBNull)
{
agent.AgtMiddleInitial = null;
}
else
{
agent.AgtMiddleInitial = dr["AgtMiddleInitial"].ToString();
}
if (dr["AgtLastName"] is DBNull)
{
agent.AgtLastName = null;
}
else
{
agent.AgtLastName = dr["AgtLastName"].ToString();
}
if (dr["AgtBusPhone"] is DBNull)
{
agent.AgtBusPhone = null;
}
else
{
agent.AgtBusPhone = dr["AgtBusPhone"].ToString();
}
if (dr["AgtEmail"] is DBNull)
{
agent.AgtEmail = null;
}
else
{
agent.AgtEmail = dr["AgtEmail"].ToString();
}
if (dr["AgtPosition"] is DBNull)
{
agent.AgtPosition = null;
}
else
{
agent.AgtPosition = dr["AgtPosition"].ToString();
}
if (dr["AgencyId"] is DBNull)
{
agent.AgencyId = null;
}
else
{
agent.AgencyId = (int)dr["AgencyId"];
}
agents.Add(agent);
}
} catch (Exception ex) {
throw ex;
} finally {
connection.Close();
}
return(agents);
}