public static List <Product> GetAllProducts()
{
List <Product> products = new List <Product>();
Product P;
SqlConnection con = TravelExpertsDB.GetConnection();
string Statement = "SELECT * FROM Products ORDER BY ProdName";
SqlCommand cmd = new SqlCommand(Statement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // while there are products
{
P = new Product();
P.ProductID = (int)reader["ProductID"];
P.ProdName = reader["ProdName"].ToString();
products.Add(P);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(products);
}
public static bool UpdateProduct(Product newprodname, Product oldprodname)
{
bool success = false;
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Products SET ProdName = @newprodname WHERE ProductID = @oldprodID; ";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@newprodname", newprodname.ProdName);
cmd.Parameters.AddWithValue("@oldprodname", oldprodname.ProdName);
cmd.Parameters.AddWithValue("@oldprodID", oldprodname.ProductID);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(success);
}
public static Product GetAllProducts(string Id)
{
Product product;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = "SELECT ProductID, ProdName FROM Products WHERE ProductId = @ProductID ";
//"ProdName = @ProdName;";
SqlCommand cmd = new SqlCommand(query, connection);
product = new Product();
cmd.Parameters.AddWithValue("@ProductId", int.Parse(Id));
try
{
connection.Open();
// execute the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// check if successful
if (reader.Read())
{
product.ProductID = (int)reader["ProductId"];
product.ProdName = reader["ProdName"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(product);
}
public static int AddProductSupplier(suppliers prosup)
{
int prosupID = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create INSERT command
// ProductSupplierID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Products_Suppliers(ProductId, SupplierId) OUTPUT inserted.ProductSupplierId VALUES(@ProductId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);
try
{
connection.Open();
// execute insert command and get inserted ID
prosupID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(prosupID);
}
public static bool UpdatePackage(Packages oldPack, Packages newPack)
{
bool success = false; // did not update
// connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// update command
string updateStatement =
"UPDATE Packages SET " +
"PkgName = @NewPName, " +
"PkgStartDate = @NewPSD, " +
"PkgEndDate = @NewPED, " +
"PkgDesc = @NewPdesc, " +
"PkgBasePrice = @NewPBP, " +
"PkgAgencyCommission = @NewPAC " +
"WHERE PackageId = @OldPackageID " + // identifies ccustomer
"AND PkgName = @OldPName " + // remaining - for otimistic concurrency
"AND PkgStartDate = @OldPSD " +
"AND PkgEndDate = @OldPED " +
"AND PkgDesc = @OldPdesc " +
"AND PkgBasePrice = @OldPBP " +
"AND PkgAgencyCommission = @OldPAC";
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewPName", newPack.PkgName);
cmd.Parameters.AddWithValue("@NewPSD", newPack.PkgStartDate);
cmd.Parameters.AddWithValue("@NewPED", newPack.PkgEndDate);
cmd.Parameters.AddWithValue("@NewPdesc", newPack.PkgDesc);
cmd.Parameters.AddWithValue("@NewPBP", newPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@NewPAC", newPack.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@OldPackageID", oldPack.PackageId);
cmd.Parameters.AddWithValue("@OldPName", oldPack.PkgName);
cmd.Parameters.AddWithValue("@OldPSD", oldPack.PkgStartDate);
cmd.Parameters.AddWithValue("@OldPED", oldPack.PkgEndDate);
cmd.Parameters.AddWithValue("@OldPdesc", oldPack.PkgDesc);
cmd.Parameters.AddWithValue("@OldPBP", oldPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@OldPAC", oldPack.PkgAgencyCommission);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
public static void AddProduct(string prodName)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products (prodname) VALUES (@ProdName) ";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProdName", prodName);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void UpdateProduct(string newprodname, string oldprodname)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "UPDATE Products SET ProdName = @prodname WHERE @oldprodname = ProdName ; ";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@prodname", newprodname);
cmd.Parameters.AddWithValue("@oldprodname", oldprodname);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static List <Packages> GetPackages()
{
List <Packages> packages = new List <Packages>(); //empty list
Packages pk; //just for reading variables expressed before the commands!
//create the connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT PackageId, PkgName, PkgStartDate, " +
"PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages ";
SqlCommand cmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
pk = new Packages();
pk.PackageId = (int)reader["PackageId"]; //[] indexer from chapter 13
pk.PkgName = reader["PkgName"].ToString();
pk.PkgStartDate = ((DateTime)reader["PkgStartDate"]).Date;
pk.PkgEndDate = ((DateTime)reader["PkgEndDate"]).Date;
pk.PkgDesc = reader["PkgDesc"].ToString();
pk.PkgBasePrice = (decimal)reader["PkgBasePrice"];
pk.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
packages.Add(pk);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of states
return(packages);
}
// retrieve customer with given ID
public static List <Products_suppliers_packages> GetProducts_suppliers_packages()
{
//Products_suppliers_packages pSpack = null;
List <Products_suppliers_packages> products_suppliers_packages_list = new List <Products_suppliers_packages>();
Products_suppliers_packages pSpack = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = "SELECT PackageId, ProductSupplierId " +
"FROM Packages_Products_Suppliers "; //+
//"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();
// build customer object to return
while (reader.Read()) // if there is a package with this ID
{
pSpack = new Products_suppliers_packages();
pSpack.packageId = (int)reader["PackageId"];
pSpack.productSupplierId = (int)reader["ProductSupplierId"];
products_suppliers_packages_list.Add(pSpack);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products_suppliers_packages_list);
}
public static suppliers GetProductSupplierId(int prosupID)
{
suppliers prosup = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = " SELECT * FROM Products_Suppliers WHERE ProductSupplierId = @ProductSupplierId";
// string query =
//"SELECT p.ProductId, p.ProdName, s.SupplierId, s.SupName, ps.ProductSupplierId FROM Products as p JOIN Products_Suppliers as ps on p.ProductId = ps.ProductId join Suppliers s on ps.SupplierId = s.SupplierId WHERE ProductSupplierId = @ProductSupplierId";
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@ProductSupplierId", prosupID);
// run the SELECT query
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
// build customer object to return
if (reader.Read()) // if there is a customer with this ID
{
prosup = new suppliers
{
ProductSupplierId = (int)reader["ProductSupplierId"],
ProductId = (int)reader["ProductId"],
SupplierId = (int)reader["SupplierId"]
//ProdName = (string)reader["ProdName"],
//SupName = (string)reader["SupName"]
};
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(prosup);
}
public static int AddPackage(Packages pack)
{
int packID = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Packages(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"OUTPUT inserted.PackageId " +
"VALUES(@PName, @PSD, @PED, @PDesc, @PBP, @PAC)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@PName", pack.PkgName);
cmd.Parameters.AddWithValue("@PSD", pack.PkgStartDate);
cmd.Parameters.AddWithValue("@PED", pack.PkgEndDate);
cmd.Parameters.AddWithValue("@PDesc", pack.PkgDesc);
cmd.Parameters.AddWithValue("@PBP", pack.PkgBasePrice);
cmd.Parameters.AddWithValue("@PAC", pack.PkgAgencyCommission);
try
{
connection.Open();
// execute insert command and get inserted ID
packID = (int)cmd.ExecuteScalar();
//cmd.ExecuteNonQuery();
// retrieve generate customer ID to return
//string selectStatement =
// "SELECT IDENT_CURRENT('Customers')";
//SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
//custID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returns single value
// // (int) does not work in this case
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packID);
}
//public static int AddSupplier(global::TravelExperts.Supplier sup)
//{
// throw new NotImplementedException();
//}
//public static int AddSupplier(global::TravelExperts.Supplier sup)
//{
// throw new NotImplementedException();
//}
public static int AddSuppliers(Suppliers sup)
{
int supID = 0;
//object TravelExpertDB = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Suppliers(SupplierId, SupName) " +
"OUTPUT inserted.SupplierId " +
"VALUES(@SupplierId, @SupName)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@SupplierId", sup.SuppliersId);
cmd.Parameters.AddWithValue("@SupName", sup.SupName);
try
{
connection.Open();
// execute insert command and get inserted ID
supID = (int)cmd.ExecuteScalar();
//cmd.ExecuteNonQuery();
// retrieve generate customer ID to return
//string selectStatement =
// "SELECT IDENT_CURRENT('Customers')";
//SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
//custID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returns single value
// // (int) does not work in this case
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(supID);
}
//public static class Products_suppliersDB
//{
public static List <suppliers> GetSuppliers()
{
List <suppliers> productSuppliers = new List <suppliers>(); //empty list
suppliers pro; //just for reading
//create the connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create the command for SELECT query to get the product suppliers
string query = "SELECT * FROM Products_Suppliers ";
SqlCommand selectCmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = selectCmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
pro = new suppliers
{
ProductSupplierId = (int)reader["ProductSupplierId"],
ProductId = (int)reader["ProductId"],
SupplierId = (int)reader["SupplierId"]
};
productSuppliers.Add(pro);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of product
return(productSuppliers);
}
public static bool DeleteProductSupplier(suppliers prosup)
{
bool success = false;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create DELETE command
string deleteStatement =
"DELETE FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId " + // needed for identification
"AND ProductId = @ProductId " + // the rest - for optimistic concurrency
"AND SupplierId = @SupplierId ";
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@ProductSupplierId", prosup.ProductSupplierId);
cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);
try
{
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
// retrieve supplier with given ID
public static List <Suppliers> GetSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>();
Suppliers pSpack = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = "SELECT SupplierId, SupName " +
"FROM Suppliers ";
SqlCommand cmd = new SqlCommand(query, connection);
// run the SELECT query
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
// build supplier object to return
while (reader.Read()) // if there is a supplier with this ID
{
pSpack = new Suppliers();
pSpack.SuppliersId = (int)reader["SupplierId"];
pSpack.SupName = reader["SupName"].ToString();
suppliers.Add(pSpack);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(suppliers);
}
public static Packages GetPackage(string id)
{
Packages package;
SqlConnection connection = TravelExpertsDB.GetConnection();
string query = "SELECT PackageId, PkgName, PkgStartDate, " +
"PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission FROM Packages WHERE PackageId =@Pid;";
SqlCommand cmd = new SqlCommand(query, connection);
package = new Packages();
cmd.Parameters.AddWithValue("@Pid", int.Parse(id));
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); //built-in
//each state data returned, make state object and add to the list
if (reader.Read())
{
package.PackageId = (int)reader["PackageId"]; //[] indexer from chapter 13
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"];
}
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
return(package);
}
public static List <Suppliers> GetSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>(); //empty list
Suppliers s; //just for reading variables expressed before the commands!
//create the connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT SupplierId, SupName FROM Suppliers ";
SqlCommand cmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
s = new Suppliers();
s.SuppliersId = (int)reader["SupplierId"]; //[] indexer from chapter 13
s.SupName = reader["SupName"].ToString();
suppliers.Add(s);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of Suppliers
return(suppliers);
}
public static bool UpdateProductSupplier(suppliers oldprosup, suppliers newprosup)
{
bool success = false; // did not update
// connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// update command
string updateStatement =
"UPDATE Products_Suppliers SET ProductId = @NewProductId, SupplierId = @NewSupplierId " +
"WHERE ProductSupplierId = @OldProductSupplierId " +
"AND ProductId = @OldProductId " +
"AND SupplierId = @OldSupplierId";
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewProductId ", newprosup.ProductId);
cmd.Parameters.AddWithValue("@NewSupplierId", newprosup.SupplierId);
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldprosup.ProductSupplierId);
cmd.Parameters.AddWithValue("@OldProductId", oldprosup.ProductId);
cmd.Parameters.AddWithValue("@OldSupplierId", oldprosup.SupplierId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
// update product supplier package
// return indicator of success
public static bool UpdateProducts_suppliers_packages(Products_suppliers_packages oldPack, Products_suppliers_packages newPack)
{
bool success = false; // did not update
// connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// update command
string updateStatement =
"UPDATE Packages_Products_Suppliers SET " +
"PackageId = @NewPackageId, " +
"ProductSupplierId = @NewProductSupplierId " +
"WHERE PackageId = @OldPackageId " + // identifies packages
"AND ProductSupplierId = @OldProductSupplierId "; // remaining - for optimistic concurrency
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewPackageId", newPack.packageId);
cmd.Parameters.AddWithValue("@NewProductSupplierId", newPack.productSupplierId);
cmd.Parameters.AddWithValue("@OldPackageId", oldPack.packageId);
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldPack.productSupplierId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
public static int AddPackage(Packages pack)
{
int packID = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Packages(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"OUTPUT inserted.PackageId " +
"VALUES(@PName, @PSD, @PED, @PDesc, @PBP, @PAC)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@PName", pack.PkgName);
cmd.Parameters.AddWithValue("@PSD", pack.PkgStartDate);
cmd.Parameters.AddWithValue("@PED", pack.PkgEndDate);
cmd.Parameters.AddWithValue("@PDesc", pack.PkgDesc);
cmd.Parameters.AddWithValue("@PBP", pack.PkgBasePrice);
cmd.Parameters.AddWithValue("@PAC", pack.PkgAgencyCommission);
try
{
connection.Open();
// execute insert command and get inserted ID
packID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packID);
}
public static suppliers GetSuppliers(string Id, string Id1)
{
suppliers productSuppliers;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = "SELECT ProductId, SupplierId FROM Products_Suppliers WHERE ProductId = @ProductId AND SupplierId = @SupplierId ";
SqlCommand cmd = new SqlCommand(query, connection);
productSuppliers = new suppliers();
cmd.Parameters.AddWithValue("@ProductId", int.Parse(Id));
cmd.Parameters.AddWithValue("@SupplierId", int.Parse(Id1));
try
{
connection.Open();
// execute the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// check if successful
if (reader.Read())
{
productSuppliers.ProductId = (int)reader["ProductId"];
productSuppliers.SupplierId = (int)reader["SupplierId"];
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(productSuppliers);
}
//public static bool UpdateSupplier(Suppliers supplier, suppliers newSupplier)
//{
// throw new NotImplementedException();
//}
//public static int AddSupplier(Suppliers supplier)
//{
// throw new NotImplementedException();
//}
//public static object GetSupplier(string rowId)
//{
// throw new NotImplementedException();
//}
public static bool UpdateSuppliers(Suppliers oldSup, Suppliers newSup)
{
bool success = false; // did not update
// connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// update command
string updateStatement =
"UPDATE Suppliers SET " +
"SupplierId = @NewSupID, " +
"SupName = @SupName, " +
"WHERE SupplierId = @OldSupplierId " + // identifies Suppliers
"AND SupName = @OldSupName "; // remaining - for otimistic concurrency
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewSupID", newSup.SuppliersId);
cmd.Parameters.AddWithValue("@NewSupName", newSup.SupName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
public static Products_suppliers_packages GetProducts_suppliers_package(string id, string id1)
{
Products_suppliers_packages products_suppliers_packages;
SqlConnection connection = TravelExpertsDB.GetConnection();
string query = "SELECT PackageId," +
"ProductSupplierId FROM Packages_Products_Suppliers WHERE PackageId =@Pid AND ProductSupplierId =@PSid ;";
SqlCommand cmd = new SqlCommand(query, connection);
products_suppliers_packages = new Products_suppliers_packages();
cmd.Parameters.AddWithValue("@Pid", int.Parse(id));
cmd.Parameters.AddWithValue("@PSid", int.Parse(id1));
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); //built-in
//each state data returned, make state object and add to the list
if (reader.Read())
{
products_suppliers_packages.packageId = (int)reader["PackageId"];
products_suppliers_packages.productSupplierId = (int)reader["ProductSupplierId"];
}
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
return(products_suppliers_packages);
}
public static Suppliers GetSuppliers(string id)
{
Suppliers suppliers;
SqlConnection connection = TravelExpertsDB.GetConnection();
string query = "SELECT SupplierId," +
"SupName FROM Suppliers WHERE SupplierId =@Pid;";
SqlCommand cmd = new SqlCommand(query, connection);
suppliers = new Suppliers();
cmd.Parameters.AddWithValue("@Pid", int.Parse(id));
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); //built-in
//each state data returned, make state object and add to the list
if (reader.Read())
{
suppliers.SuppliersId = (int)reader["SupplierId"];
suppliers.SupName = reader["SupName"].ToString();
}
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
return(suppliers);
}
public static int AddProduct(Product pro)
{
int proID = 0;
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products (prodname) Output Inserted.ProductID VALUES (@ProdName) ";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProdName", pro.ProdName);
try
{
con.Open();
proID = (int)cmd.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(proID);
}
