public static List <ProductsSuppliers> GetProductsSupplierId()
{
List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string selectStatement = "select ProductSupplierId, ProductId, SupplierId "
+ "from Products_Suppliers "
+ "ORDER BY ProductSupplierId";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
ProductsSuppliers p = new ProductsSuppliers();
p.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
p.SupplierId = Convert.ToInt32(reader["SupplierId"]);
p.ProductId = Convert.ToInt32(reader["ProductId"]);
productssupplierid.Add(p);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(productssupplierid);
}
// Delete operation returns bool success flag
static public bool DeleteProduct(Product product)
{
bool successful = false;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string deleteString = "delete from Products " +
"where " + // checking for optimistic concurrency
// "ProductId = @ProductId and " +
"ProdName = @ProdName";
SqlCommand deleteCommand = new SqlCommand(deleteString, connection);
// deleteCommand.Parameters.AddWithValue("@ProductId", product.ProductId);
deleteCommand.Parameters.AddWithValue("@ProdName", product.ProdName);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
}
catch (Exception ex)
{
MessageBox.Show("the existing product is connected with packages, delete the product from packages first");
}
finally
{
connection.Close();
}
return(successful);
}
public static ProductsSuppliers GetSelected(int pId)
{
ProductsSuppliers selected = new ProductsSuppliers();
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string selectStatement = "select p.ProductId, ProdName, ProductSupplierId, ps.SupplierId, s.SupName " +
"from Products p, Products_Suppliers ps, suppliers s " +
"where p.ProductId = ps.ProductId and ps.SupplierId = s.SupplierId and ProductSupplierId = @ProductSupplierId";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductSupplierId", pId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.Read())
{
selected.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
selected.SupplierId = Convert.ToInt32(reader["SupplierId"]);
selected.ProductId = Convert.ToInt32(reader["ProductId"]);
selected.SupName = reader["SupName"].ToString();
selected.ProdName = reader["ProdName"].ToString();
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(selected);
}
public static bool UpdateProductSupplier(ProductsSuppliers oldProd, ProductsSuppliers newProd)
{
bool successful = false;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string updateString = "update Products_Suppliers set ProductId = @newProd, SupplierId = @newSup " +
"where ProductSupplierId = @oldProdSupId and ProductId = @oldProdId and SupplierId = @oldSupId";
SqlCommand updateCommand = new SqlCommand(updateString, connection);
updateCommand.Parameters.AddWithValue("@newProd", newProd.ProductId);
updateCommand.Parameters.AddWithValue("@newSup", newProd.SupplierId);
updateCommand.Parameters.AddWithValue("@oldProdSupId", oldProd.ProductSupplierId);
updateCommand.Parameters.AddWithValue("@oldProdId", oldProd.ProductId);
updateCommand.Parameters.AddWithValue("@oldSupId", oldProd.SupplierId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//connection.Close();
}
return(successful);
}
//method to add product supplier Alex
public static void AddProductSupplier(int PackageId, int ProductSupplierId)
{
//int prodID = 0;
// prepare connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// prepare the statement
string insertString = " insert into Packages_Products_Suppliers(PackageId, ProductSupplierId) Values(@PackageId, @ProductSupplierId)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
insertCommand.Parameters.AddWithValue("@PackageId", PackageId);
insertCommand.Parameters.AddWithValue("@ProductSupplierId", ProductSupplierId);
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
}
}
catch (Exception ex)
{
MessageBox.Show("The selected PackageId and Products Suppliers Id already exists!"); // pass the buck
}
finally
{
connection.Close();
}
}
public static List <ProductsSuppliers> GetSupplierName(int selectedprodid)
{
List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string selectStatement = "select SupName, ps.SupplierId " +
"from Products_Suppliers ps, Suppliers s " +
"where ps.SupplierId = s.SupplierId and ProductId = @ProductId " +
"order by SupName";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", selectedprodid);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
ProductsSuppliers p = new ProductsSuppliers();
p.SupName = reader["SupName"].ToString();
p.SupplierId = Convert.ToInt32(reader["SupplierId"]);
productssupplierid.Add(p);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(productssupplierid);
}
public static bool Delete(int ps)
{
bool successful = false;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string deleteString = "delete from Packages where PackageId =@PackageId";
SqlCommand deleteCommand = new SqlCommand(deleteString, connection);
deleteCommand.Parameters.AddWithValue("@PackageId", ps);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
else
{
return(false);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
return(successful);
}
// Delete operation returns bool success flag
static public bool DeleteSupplier(Supplier supplier)
{
bool successful = false;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string deleteString = "delete from Suppliers " +
"where " + // checking for optimistic concurrency
// "SupplierId = @SupplierId and " +
"SupplierId = @SupplierId";
SqlCommand deleteCommand = new SqlCommand(deleteString, connection);
// deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
deleteCommand.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
}
catch (Exception ex)
{
//throw ex;
MessageBox.Show("Delete supplier from other tables first");
}
finally
{
connection.Close();
}
return(successful);
}
public static List <ProductsSuppliers> GetProductSupplierId2Inputs(int selectedprodid, int?selectedsupplier)
{
List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string selectStatement = "select ProductSupplierId from Products_Suppliers " +
"where ProductId= @ProductId and SupplierId=@SupplierId";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", selectedprodid);
selectCommand.Parameters.AddWithValue("@SupplierId", selectedsupplier);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
ProductsSuppliers p = new ProductsSuppliers();
p.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
productssupplierid.Add(p);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(productssupplierid);
}
public static List <package> GetPackages()
{
//make empty list
List <package> packages = new List <package>();
//new Order object
package pkg;
//create connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
//create selectcommand
string selectString = "select PackageId,PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, Products from packages " +
"order by PackageId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
connection.Open();
//run the selectCommand and process the results adding Orders to the list
SqlDataReader reader = selectCommand.ExecuteReader();
//process one row at a time
while (reader.Read())
{
pkg = new package();
//define the OrderID and CustomerID of thew new object by using the reader
pkg.PackageId = (int)reader["PackageId"];
pkg.PkgName = (string)reader["PkgName"];
DateTime tripEnd = (DateTime)reader["PkgEndDate"];
pkg.PkgEndDate = tripEnd.ToString("d");
DateTime tripStart = (DateTime)reader["PkgStartDate"];
pkg.PkgStartDate = tripStart.ToString("d");
pkg.PkgDesc = (string)reader["PkgDesc"];
decimal cost = (decimal)reader["PkgAgencyCommission"];
pkg.PkgAgencyCommission = cost.ToString("C");
decimal price = (decimal)reader["PkgBasePrice"];
pkg.PkgBasePrice = price.ToString("C");
packages.Add(pkg);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages);
}
public static List <ProductsSuppliers> GetUniqueProSupId(string logic)
{
List <ProductsSuppliers> productssupplierid = new List <ProductsSuppliers>();
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string selectStatement;
if (logic == "p")
{
selectStatement = "select ProductId, ProdName from Products ";
}
else
{
selectStatement = "select SupplierId, SupName from Suppliers ";
}
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
ProductsSuppliers p = new ProductsSuppliers();
if (logic == "p")
{
p.ProductId = Convert.ToInt32(reader["ProductId"]);
p.ProdName = reader["ProdName"].ToString();
}
else
{
p.SupplierId = Convert.ToInt32(reader["SupplierId"]);
p.SupName = reader["SupName"].ToString();
}
productssupplierid.Add(p);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(productssupplierid);
}
public static int AddPackage(package pkg) // returns generated customer id
{
int PackageId = 0;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// prepare the statement
string insertString = "insert into packages " +
"(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"values(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
// insertCommand.Parameters.AddWithValue("@PackageId", pkg.PackageId);
insertCommand.Parameters.AddWithValue("@PkgName", pkg.PkgName);
insertCommand.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate);
insertCommand.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate);
insertCommand.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc);
insertCommand.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);
insertCommand.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission);
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
// string selectString = "select @PackageId " +
// "from packages";
// SqlCommand selectCommand = new SqlCommand(selectString, connection);
// PackageId = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!!
}
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(PackageId);
}
public static List <Packages_Products_Suppliers> GetPackages_Products_Supplierss()
{
List <Packages_Products_Suppliers> prods = new List <Packages_Products_Suppliers>(); // product found
// define the connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// define the select query command
string selectQuery = "select PackageId, ps.ProductId, ProdName,pps.ProductSupplierId, SupplierId " +
"from Packages_Products_Suppliers pps, Products_Suppliers ps, Products p " +
"where pps.ProductSupplierId = ps.ProductSupplierId and ps.ProductId = p.ProductId " +
"order by PackageId, ProductSupplierId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
try
{
// open the connection
connection.Open();
// exectute the select query
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result if any
while (reader.Read()) // if there is an oerder
{
Packages_Products_Suppliers prod = new Packages_Products_Suppliers();
prod.PackageId = Convert.ToInt32(reader["PackageId"]);
prod.ProductId = Convert.ToInt32(reader["ProductId"]);
prod.ProductName = Convert.ToString(reader["ProdName"]);
prod.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
prod.SupplierId = Convert.ToInt32(reader["SupplierId"]);
prods.Add(prod);
}
}
catch (Exception ex)
{
throw ex; // let the form deal with it
}
finally
{
// close the connection not matter what
connection.Close();
}
return(prods);
}
// retrieves the order with given id
public static Product GetProduct(int ProductId)
{
Product prod = null; // order found
// define the connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// define the select query command
string selectQuery = "select ProductId, ProdName " +
"from products " +
"where ProductId = @ProductId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@ProductId", ProductId);
try
{
// open the connection
connection.Open();
// exectute the select query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result if any
if (reader.Read()) // if there is an oerder
{
prod = new Product();
prod.ProductId = (int)reader["ProductId"];
prod.ProdName = reader["ProdName"].ToString();
}
}
catch (Exception ex)
{
throw ex; // let the form deal with it
}
finally
{
// close the connection not matter what
connection.Close();
}
return(prod);
}
// retrieves the supplier with given id
public static Supplier GetSupplier(int SupplierId)
{
Supplier sup = null; // supplier found
// define the connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// define the select query command
string selectQuery = "select SupplierId, SupName " +
"from suppliers " +
"where SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@SupplierId", SupplierId);
try
{
// open the connection
connection.Open();
// exectute the select query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result if any
if (reader.Read()) // if there is a supplier
{
sup = new Supplier();
sup.SupplierId = (int)reader["SupplierId"];
sup.SupName = reader["SupName"].ToString();
}
}
catch (Exception ex)
{
throw ex; // let the form deal with it
}
finally
{
// close the connection not matter what
connection.Close();
}
return(sup);
}
public static List <Product> GetProducts()
{
List <Product> prods = new List <Product>(); // product found
// define the connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// define the select query command
string selectQuery = "select ProductId, ProdName " +
"from products";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
try
{
// open the connection
connection.Open();
// exectute the select query
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result if any
while (reader.Read()) // if there is an oerder
{
Product prod = new Product();
prod.ProductId = Convert.ToInt32(reader["ProductId"]);
prod.ProdName = reader["ProdName"].ToString();
prods.Add(prod);
}
}
catch (Exception ex)
{
throw ex; // let the form deal with it
}
finally
{
// close the connection not matter what
connection.Close();
}
return(prods);
}
public static List <Supplier> GetSuppliers()
{
List <Supplier> sups = new List <Supplier>(); // supplier found
// define the connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// define the select query command
string selectQuery = "select SupplierId, SupName " +
"from suppliers";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
try
{
// open the connection
connection.Open();
// exectute the select query
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result if any
while (reader.Read()) // if there is a supplier
{
Supplier sup = new Supplier();
sup.SupplierId = Convert.ToInt32(reader["SupplierId"]);
sup.SupName = reader["SupName"].ToString();
sups.Add(sup);
}
}
catch (Exception ex)
{
throw ex; // let the form deal with it
}
finally
{
// close the connection not matter what
connection.Close();
}
return(sups);
}
// insert order
public static int AddProduct(Product prod) // returns the generate order id
{
int prodId = 0;
// prepare connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// prepare the statement
string insertString = "insert into Products " +
"(ProdName) " +
"values(@ProdName)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
insertCommand.Parameters.AddWithValue("@ProductId", prod.ProductId);
insertCommand.Parameters.AddWithValue("@ProdName", prod.ProdName);
try
{
// open connection
connection.Open();
// execute the statement
int i = insertCommand.ExecuteNonQuery();
if (i == 1) // one record is inserted
{
}
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(prodId);
}
// updates existing suppliers record and returns bool success flag
public static bool UpdateSupplier(Supplier oldSupplier, Supplier newSupplier)
{
bool successful = false;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string updateString = "update Suppliers set " +
// "SupplierId = @NewSupplierId, " +
"SupName = @NewSupName " +
"where " + // update succeeds only if record not changed by other users
// "SupplierId = @OldSupplierId and " +
"SupName = @OldSupName";
SqlCommand updateCommand = new SqlCommand(updateString, connection);
// updateCommand.Parameters.AddWithValue("@OldSupplierId", oldSupplier.SupplierId);
updateCommand.Parameters.AddWithValue("@OldSupName", oldSupplier.SupName);
// updateCommand.Parameters.AddWithValue("@NewSupplierId", newSupplier.SupplierId);
updateCommand.Parameters.AddWithValue("@NewSupName", newSupplier.SupName);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(successful);
}
//method to delete Alex
public static bool Delete(package psup, ProductsSuppliers pps)
{
bool successful = false;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string deleteString = "delete from Packages_Products_Suppliers where PackageId = @PackageId and ProductSupplierId=@ProductSupplierId";
SqlCommand deleteCommand = new SqlCommand(deleteString, connection);
deleteCommand.Parameters.AddWithValue("@PackageId", psup.PackageId);
deleteCommand.Parameters.AddWithValue("@ProductSupplierId", pps.ProductSupplierId);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
else
{
return(false);
}
}
catch (Exception ex)
{
//throw ex;
MessageBox.Show("Delete supplier from other tables first");
}
finally
{
connection.Close();
}
return(successful);
}
public static int AddProductSupplier(ProductsSuppliers productsupplier)
{
int prodID = 0;
// prepare connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// prepare the statement
string insertString = "insert into Products_Suppliers(ProductId, SupplierId) Values(@ProductId, @SupplierId)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
insertCommand.Parameters.AddWithValue("@ProductId", productsupplier.ProductId);
insertCommand.Parameters.AddWithValue("@SupplierId", productsupplier.SupplierId);
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
string selectString = "select ident_current('Products_Suppliers') from Products_Suppliers";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
prodID = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!!
}
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(prodID);
}
// insert supplier
public static int AddSupplier(Supplier sup) // returns the generate supplier id
{
// prepare connection
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// prepare the statement
string insertString = "insert into Suppliers " +
"(SupName, SupplierId) " +
"values(@SupName, @SupplierId)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
insertCommand.Parameters.AddWithValue("@SupplierId", sup.SupplierId);
insertCommand.Parameters.AddWithValue("@SupName", sup.SupName);
try
{
// open connection
connection.Open();
// execute the statement
int i = insertCommand.ExecuteNonQuery();
if (i == 1) // one record is inserted
{
}
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(3);
}
//the get order method returns an individual order object defined by the OrderID passed
//by order form
public static package GetPackage(int PackageID)
{
// reference to new (null) order object
package pkg = null;
//create connection to Northwinds database
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
// create select command that pulls specific values from Orders table for 1 Order
string selectQuery = "select * " +
"from Packages " +
"where PackageId = @PackageId";
//Represents a stored procedure to execute against a SQL Server database
//info from website: msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
//use the store procedure to select the order using the parameter OrderID passed from the main form
selectCommand.Parameters.AddWithValue("@PackageId", PackageID);
try
{
// open connection
connection.Open();
//DataReader provides a way of reading a forward-only stream of rows from a SQL Server database
//Info from msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx
SqlDataReader reader = selectCommand.ExecuteReader();
//this if statements processes one row at a time
if (reader.Read())
{
//each row is a new object during importing
pkg = new package();
//define the OrderID and CustomerID of thew new object by using the reader
pkg.PackageId = (int)reader["PackageId"];
pkg.PkgName = (string)reader["PkgName"];
DateTime tripEnd = (DateTime)reader["PkgEndDate"];
pkg.PkgEndDate = tripEnd.ToString("d");
DateTime tripStart = (DateTime)reader["PkgStartDate"];
pkg.PkgStartDate = tripStart.ToString("d");
pkg.PkgDesc = (string)reader["PkgDesc"];
decimal cost = (decimal)reader["PkgAgencyCommission"];
pkg.PkgAgencyCommission = cost.ToString("C");
decimal price = (decimal)reader["PkgBasePrice"];
pkg.PkgBasePrice = price.ToString("C");
//pkg.Products = (string)reader["Products"];
}
//this catches the null exceptions on import
}
catch (Exception ex)
{
// throw exception to the form to handle
throw ex;
}
finally
{
connection.Close();
}
//if no exceptions return the imported order object
return(pkg);
}
public static bool UpdatePackage(package oldPackage, package newPackage)
{
bool successful = false;
SqlConnection connection = TRAVELEXPERTSDB.GetConnection();
string updateString = "update packages set " +
// "PackageId = @newPackageId, " +
"PkgName = @newPkgName, " +
"PkgStartDate = @newPkgStartDate, " +
"PkgEndDate = @newPkgEndDate, " +
"PkgDesc = @newPkgDesc, " +
"PkgBasePrice = @newPkgBasePrice, " +
"PkgAgencyCommission = @newPkgAgencyCommission " +
//"Products = @newProducts " +
"where " + // update succeeds only if record not changed by other users
"PackageId = @oldPackageId and " +
"PkgName = @oldPkgName and " +
"PkgStartDate = @oldPkgStartDate and " +
"PkgEndDate = @oldPkgEndDate and " +
"PkgDesc = @oldPkgDesc and " +
"PkgBasePrice = @oldPkgBasePrice and " +
"PkgAgencyCommission = @oldPkgAgencyCommission";
// "Products = @oldProducts";
SqlCommand updateCommand = new SqlCommand(updateString, connection);
// updateCommand.Parameters.AddWithValue("@oldPackageId", oldPackage.PackageId);
updateCommand.Parameters.AddWithValue("@oldPkgName", oldPackage.PkgName);
updateCommand.Parameters.AddWithValue("@oldPkgStartDate", oldPackage.PkgStartDate);
updateCommand.Parameters.AddWithValue("@oldPkgEndDate", oldPackage.PkgEndDate);
updateCommand.Parameters.AddWithValue("@oldPkgDesc", oldPackage.PkgDesc);
updateCommand.Parameters.AddWithValue("@oldPkgBasePrice", oldPackage.PkgBasePrice);
updateCommand.Parameters.AddWithValue("@oldPkgAgencyCommission", oldPackage.PkgAgencyCommission);
// updateCommand.Parameters.AddWithValue("@oldProducts", oldPackage.Products);
// updateCommand.Parameters.AddWithValue("@newPackageId", newPackage.PackageId);
updateCommand.Parameters.AddWithValue("@newPkgName", newPackage.PkgName);
updateCommand.Parameters.AddWithValue("@newPkgStartDate", newPackage.PkgStartDate);
updateCommand.Parameters.AddWithValue("@newPkgEndDate", newPackage.PkgEndDate);
updateCommand.Parameters.AddWithValue("@newPkgDesc", newPackage.PkgDesc);
updateCommand.Parameters.AddWithValue("@newPkgBasePrice", newPackage.PkgBasePrice);
updateCommand.Parameters.AddWithValue("@newPkgAgencyCommission", newPackage.PkgAgencyCommission);
//updateCommand.Parameters.AddWithValue("@newProducts", newPackage.Products);
updateCommand.Parameters.AddWithValue("@OldPackageId", oldPackage.PackageId);
// updateCommand.Parameters.AddWithValue("@NewProductId", newProduct.ProductId);
updateCommand.Parameters.AddWithValue("@NewPackageId", newPackage.PackageId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(successful);
}
