public static List <Products_Suppliers> GetProductsSuppliersIdFromPackageId(int PackageId)
{
List <Products_Suppliers> products_supplier = new List <Products_Suppliers>();
Products_Suppliers prodsupp;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT a.ProductSupplierId, a.ProductId, a.SupplierId " +
"FROM Products_Suppliers a INNER JOIN Packages_Products_Suppliers b " +
"ON a.ProductSupplierId = b.ProductSupplierId " +
"WHERE b.PackageId = @PackageId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", PackageId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prodsupp = new Products_Suppliers();
prodsupp.ProductSupplierId = (int)dr["ProductSupplierId"];
prodsupp.ProductId = (int)dr["ProductId"];
prodsupp.SupplierId = (int)dr["SupplierId"];
products_supplier.Add(prodsupp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products_supplier);
}
public static List <Products_Suppliers> GetProductsSuppliers()
{
List <Products_Suppliers> products_supplier = new List <Products_Suppliers>();
Products_Suppliers prodsupp;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT ProductSupplierId, ps.ProductId, ps.SupplierId,p.ProdName,s.SupName " +
"FROM Products_Suppliers ps, Products p, Suppliers s where p.ProductId = ps.ProductId and s.SupplierId = ps.SupplierId ORDER BY ProductSupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prodsupp = new Products_Suppliers();
prodsupp.ProductSupplierId = (int)dr["ProductSupplierId"];
prodsupp.ProductId = (int)dr["ProductId"];
prodsupp.SupplierId = (int)dr["SupplierId"];
prodsupp.ProductName = dr["ProdName"].ToString();
prodsupp.SupplierName = dr["SupName"].ToString();
products_supplier.Add(prodsupp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products_supplier);
}
public static int AddProducts(Products prod)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO Products " +
"VALUES(@ProdName)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Products') FROM Products";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int ProductSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar());
return(ProductSupplierId);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static int AddProductsSuppliers(Products_Suppliers prodsupp)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " +
"VALUES(@ProductId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProductId", prodsupp.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", prodsupp.SupplierId);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int ProductSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar());
return(ProductSupplierId);
}
catch (SqlException ex)
{
throw ex; //IDENTITY_INSERT is left on.. i dont know but it says i need to turn it off
}
finally
{
con.Close();
}
}
public static List <Products> GetProductsFromProductD(int ProductSupplierId)
{
List <Products> products = new List <Products>();
Products prod;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT p.ProductId, p.ProdName " +
"FROM Products p INNER JOIN Products_Suppliers ps " +
"ON p.ProductId = ps.ProductId " +
"WHERE ProductSupplierId = @ProductSupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductSupplierId", ProductSupplierId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod = new Products();
prod.ProductId = (int)dr["ProductId"];
prod.ProdName = (string)dr["ProdName"];
products.Add(prod);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products);
}
public static List <Products> GetProducts()
{
List <Products> products = new List <Products>();
Products prod;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT ProductId, ProdName " +
"FROM Products ORDER BY ProductId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod = new Products();
prod.ProductId = (int)dr["ProductId"];
prod.ProdName = (string)dr["ProdName"];
products.Add(prod);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products);
}
public static bool AddProductToPackage(int packageID, int productsSuppliersId)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO Packages_Products_Suppliers(PackageId, ProductSupplierId) " +
"VALUES(@packageId, @productsSuppliersId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@packageId", packageID);
cmd.Parameters.AddWithValue("@productsSuppliersId", productsSuppliersId);
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static int AddPackage(Package pack)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, DeparturePlnId, ReturnPlnId) " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission,@DeparturePlnId,@ReturnPlnId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@PkgName", pack.PkgName);
cmd.Parameters.AddWithValue("@PkgStartDate", pack.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", pack.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", pack.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", pack.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", pack.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@DeparturePlnId", pack.DepartureFlight);
cmd.Parameters.AddWithValue("@ReturnPlnId", pack.ReturnFlight);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Packages') FROM Packages";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int packageID = Convert.ToInt32(selectCmd.ExecuteScalar());
return(packageID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <Supplier> GetSuppliers()
{
List <Supplier> suppliers = new List <Supplier>();
Supplier nextSupplier;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectstatement = "SELECT * FROM [Suppliers]";
SqlCommand selectCommand = new SqlCommand(selectstatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
nextSupplier = new Supplier();
nextSupplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
nextSupplier.SupName = Convert.ToString(reader["SupName"]);
suppliers.Add(nextSupplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(suppliers);
}
public static List <SupplierContact> GetSupplierContact(int SupplierContactId)
{
List <SupplierContact> supplierContacts = new List <SupplierContact>();
SupplierContact nextSupplierContact;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectstatement = "SELECT * FROM [SupplierContacts] where SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectstatement, connection);
// selectCommand.Parameters.AddWithValue("@SupplierId", SupplierId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
nextSupplierContact = new SupplierContact();
nextSupplierContact.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]);
nextSupplierContact.SupConFirstName = Convert.ToString(reader["SupConFirstName"]);
nextSupplierContact.SupConLastName = Convert.ToString(reader["SupConLastName"]);
nextSupplierContact.SupConCompany = Convert.ToString(reader["SupConCompany"]);
nextSupplierContact.SupConAddress = Convert.ToString(reader["SupConAddress"]);
nextSupplierContact.SupConCity = Convert.ToString(reader["SupConCity"]);
nextSupplierContact.SupConProvince = Convert.ToString(reader["SupConProvince"]);
nextSupplierContact.SupConPostal = Convert.ToString(reader["SupConPostal"]);
nextSupplierContact.SupConCountry = Convert.ToString(reader["SupConCountry"]);
nextSupplierContact.SupConBusPhone = Convert.ToString(reader["SupConBusPhone"]);
nextSupplierContact.SupConFax = Convert.ToString(reader["SupConFax"]);
nextSupplierContact.SupConEmail = Convert.ToString(reader["SupConEmail"]);
nextSupplierContact.SupConURL = Convert.ToString(reader["SupConURL"]);
nextSupplierContact.AffiliationId = Convert.ToString(reader["AffiliationId"]);
nextSupplierContact.SupplierId = Convert.ToInt32(reader["SupplierId"]);
supplierContacts.Add(nextSupplierContact);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(supplierContacts);
}
public static int AddSupplierContact(SupplierContact nextContact)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO SupplierContacts (SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProvince, SupConPostal, SupConCountry, SupConBusPhone, SupConEmail, SupConURL, AffiliationId, SupplierId) " +
"VALUES(@SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProvince, @SupConPostal, @SupConCountry, @SupConBusPhone, @SupConEmail, @SupConURL, @AffiliationId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@SupplierContactId", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConFirstName", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConLastName", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConCompany", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConAddress", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConCity", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConProvince", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConPostal", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConCountry", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConBusPhone", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConEmail", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConURL", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@AffiliationId", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupplierId", nextContact.SupplierContactId);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('SupplierContacts') FROM SupplierContacts";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int SupplierContactID = Convert.ToInt32(selectCmd.ExecuteScalar());
return(SupplierContactID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <Package> GetPackagesById(int PackageId)
{
List <Package> packages = new List <Package>(); // empty list
Package pack; // for reading
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission,DeparturePlnId, ReturnPlnId " +
"FROM Packages " +
"WHERE PackageId = @PackageId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", PackageId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
pack = new Package();
pack.PackageId = (int)dr["PackageId"];
pack.PkgName = dr["PkgName"].ToString();
pack.PkgStartDate = (DateTime)dr["PkgStartDate"];
pack.PkgEndDate = (DateTime)dr["PkgEndDate"];
pack.PkgDesc = dr["PkgDesc"].ToString();
pack.PkgBasePrice = Convert.ToDouble(dr["PkgBasePrice"]);
pack.PkgAgencyCommission = Convert.ToDouble(dr["PkgAgencyCommission"]);
pack.DepartureFlight = Convert.ToInt32(dr["DeparturePlnId"]);
pack.ReturnFlight = Convert.ToInt32(dr["ReturnPlnId"]);
packages.Add(pack);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages);
}
public static bool UpdateProductsSuppliers(Products_Suppliers oldProdSupp, Products_Suppliers newProdSupp)
{
SqlConnection con = UnversalDBControls.GetConnection();
string updateStatement = "SET IDENTITY_INSERT Products_Suppliers ON " +
"UPDATE Products_Suppliers " +
"SET ProductId = @NewProductId, " +
"SupplierId = @NewSupplierId " +
"WHERE ProductSupplierId = @OldProductSupplierId " +
"AND ProductId = @OldProductId " +
"AND SupplierId = @OldSupplierId " +
"SET IDENTITY_INSERT Products_Suppliers OFF";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewProductId", newProdSupp.ProductId);
cmd.Parameters.AddWithValue("@NewSupplierId", newProdSupp.SupplierId);
cmd.Parameters.AddWithValue("@OldProductId", oldProdSupp.ProductId);
cmd.Parameters.AddWithValue("@OldSupplierId", oldProdSupp.SupplierId);
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldProdSupp.ProductSupplierId);
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <Package> GetPackages()
{
List <Package> packages = new List <Package>();
Package pack;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission,DeparturePlnId, ReturnPlnId " +
"FROM Packages ORDER BY PackageId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
pack = new Package();
pack.PackageId = (int)dr["PackageId"];
pack.PkgName = dr["PkgName"].ToString();
pack.PkgStartDate = (DateTime)dr["PkgStartDate"];
pack.PkgEndDate = (DateTime)dr["PkgEndDate"];
pack.PkgDesc = dr["PkgDesc"].ToString();
pack.PkgBasePrice = Convert.ToDouble(dr["PkgBasePrice"]);
pack.PkgAgencyCommission = Convert.ToDouble(dr["PkgAgencyCommission"]);
pack.DepartureFlight = Convert.ToInt32(dr["DeparturePlnId"]);
pack.ReturnFlight = Convert.ToInt32(dr["ReturnPlnId"]);
packages.Add(pack);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages);
}
public static bool UpdateSupplier(Supplier oldSup, Supplier newSup)
{
SqlConnection con = UnversalDBControls.GetConnection();
string updateStatement = "UPDATE Suppliers " +
"SET SupName=@NewSupName " +
"WHERE SupplierId=@OldSupplierId " +
"AND SupName=@OldSupName";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewSupName", newSup.SupName);
cmd.Parameters.AddWithValue("@OldSupName", oldSup.SupName);
cmd.Parameters.AddWithValue("@OldSupplierId", oldSup.SupplierId);
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static int AddSupplier(Supplier nextSuplier)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO Suppliers (SupplierId, SupName) " +
"VALUES(@SupplierId, @SupName)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@SupplierId", nextSuplier.SupplierId);
cmd.Parameters.AddWithValue("@SupName", nextSuplier.SupName);
try
{
int SupplierID;
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Suppliers') FROM Suppliers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
if (selectCmd.ExecuteScalar() == DBNull.Value)
{
SupplierID = Convert.ToInt32(null);
}
else
{
SupplierID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returning with DBNULL value.. assumption is that this doesnt need to be called just a simple insert but iunno
}
return(SupplierID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static bool UpdateSupplierContact(SupplierContact oldSup, SupplierContact newSup)
{
SqlConnection con = UnversalDBControls.GetConnection();
string updateStatement = "UPDATE SupplierContacts " +
"SET SupConFirstName = @NewSupConFirstName, " +
"SET SupConLastName = @NewSupConLastName, " +
"SET SupConCompany = @NewSupConCompany, " +
"SET SupConAddress = @NewSupConAddress, " +
"SET SupConCity = @NewSupConCity, " +
"SET SupConProvince = @NewSupConProvince " +
"SET SupConPostal = @NewSupConPostal " +
"SET SupConCountry = @NewSupConCountry " +
"SET SupConBusPhone = @NewSupConBusPhone " +
"SET SupConFax = @NewSupConFax " +
"SET SupConEmail = @NewSupConEmail " +
"SET SupConURL = @NewSupConURL " +
"SET AffiliationId = @NewAffiliationId " +
"WHERE SupplierId = @OldSupplierId " +
"AND SupConFirstName = @OldSupConFirstName " +
"AND SupConLastName = @OldSupConLastName " +
"AND SupConCompany = @OldSupConCompany " +
"AND SupConAddress = @OldSupConAddress " +
"AND SupConCity = @OldSupConCity " +
"AND SupConProvince = @OldSupConProvince " +
"AND SupConPostal = @OldSupConPostal " +
"AND SupConCountry = @OldSupConCountry " +
"AND SupConBusPhone = @OldSupConBusPhone " +
"AND SupConFax = @OldSupConFax " +
"AND SupConEmail = @OldSupConEmail " +
"AND SupConURL = @OldSupConURL " +
"AND AffiliationId = @OldAffiliationId";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewSupConFirstName", newSup.SupConFirstName);
cmd.Parameters.AddWithValue("@NewSupConLastName", newSup.SupConLastName);
cmd.Parameters.AddWithValue("@NewSupConCompany", newSup.SupConCompany);
cmd.Parameters.AddWithValue("@NewSupConAddress", newSup.SupConAddress);
cmd.Parameters.AddWithValue("@NewSupConCity", newSup.SupConCity);
cmd.Parameters.AddWithValue("@NewSupConProvince", newSup.SupConProvince);
cmd.Parameters.AddWithValue("@NewSupConPostal", newSup.SupConPostal);
cmd.Parameters.AddWithValue("@NewSupConCountry", newSup.SupConCountry);
cmd.Parameters.AddWithValue("@NewSupConBusPhone", newSup.SupConBusPhone);
cmd.Parameters.AddWithValue("@NewSupConFax", newSup.SupConFax);
cmd.Parameters.AddWithValue("@NewSupConEmail", newSup.SupConEmail);
cmd.Parameters.AddWithValue("@NewSupConURL", newSup.SupConURL);
cmd.Parameters.AddWithValue("@NewAffiliationId", newSup.AffiliationId);
cmd.Parameters.AddWithValue("@OldSupplierId", oldSup.SupplierId);
cmd.Parameters.AddWithValue("@OldSupConFirstName", oldSup.SupConFirstName);
cmd.Parameters.AddWithValue("@OldSupConLastName", oldSup.SupConLastName);
cmd.Parameters.AddWithValue("@OldSupConCompany", oldSup.SupConCompany);
cmd.Parameters.AddWithValue("@OldSupConAddress", oldSup.SupConAddress);
cmd.Parameters.AddWithValue("@OldSupConCity", oldSup.SupConCity);
cmd.Parameters.AddWithValue("@OldSupConProvince", oldSup.SupConProvince);
cmd.Parameters.AddWithValue("@OldSupConPostal", oldSup.SupConPostal);
cmd.Parameters.AddWithValue("@OldSupConCountry", oldSup.SupConCountry);
cmd.Parameters.AddWithValue("@OldSupConBusPhone", oldSup.SupConBusPhone);
cmd.Parameters.AddWithValue("@OldSupSupConFax", oldSup.SupConFax);
cmd.Parameters.AddWithValue("@OldSupConEmail", oldSup.SupConEmail);
cmd.Parameters.AddWithValue("@OldSupConURL", oldSup.SupConURL);
cmd.Parameters.AddWithValue("@OldAffiliationId", oldSup.AffiliationId);
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static bool UpdatePackage(Package oldPack, Package newPack)
{
SqlConnection con = UnversalDBControls.GetConnection();
string updateStatement =
"UPDATE Packages " +
"SET PkgName = @NewPkgName, " +
"PkgStartDate = @NewPkgStartDate, " +
"PkgEndDate = @NewPkgEndDate, " +
"PkgDesc = @NewPkgDesc, " +
"PkgBasePrice = @NewPkgBasePrice, " +
"PkgAgencyCommission = @NewPkgAgencyCommission, " +
"DeparturePlnId = @NewDeparturePlnId, " +
"ReturnPlnId = @NewReturnPlnId " +
"WHERE PackageId = @OldPackageId " +
"AND PkgName = @OldPkgName " +
"AND PkgStartDate = @OldPkgStartDate " +
"AND PkgEndDate = @OldPkgEndDate " +
"AND PkgDesc = @OldPkgDesc " +
"AND PkgBasePrice = @OldPkgBasePrice " +
"AND PkgAgencyCommission = @OldPkgAgencyCommission " +
"AND DeparturePlnId = @OldDeparturePlnId " +
"AND ReturnPlnId = @OldReturnPlnId ";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewPkgName", newPack.PkgName);
cmd.Parameters.AddWithValue("@NewPkgStartDate", newPack.PkgStartDate);
cmd.Parameters.AddWithValue("@NewPkgEndDate", newPack.PkgEndDate);
cmd.Parameters.AddWithValue("@NewPkgDesc", newPack.PkgDesc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@NewDeparturePlnId", newPack.DepartureFlight);
cmd.Parameters.AddWithValue("@NewReturnPlnId", newPack.ReturnFlight);
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", newPack.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@OldPackageId", oldPack.PackageId);
cmd.Parameters.AddWithValue("@OldPkgName", oldPack.PkgName);
cmd.Parameters.AddWithValue("@OldPkgStartDate", oldPack.PkgStartDate);
cmd.Parameters.AddWithValue("@OldPkgEndDate", oldPack.PkgEndDate);
cmd.Parameters.AddWithValue("@OldPkgDesc", oldPack.PkgDesc);
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", oldPack.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@OldDeparturePlnId", oldPack.DepartureFlight);
cmd.Parameters.AddWithValue("@OldReturnPlnId", oldPack.ReturnFlight);
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}