// insert new row to table Product Supplier Packages
public static int AddProducts_suppliers_packages(Products_suppliers_packages pSpack)
{
int pSpackID = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create INSERT command
// PacketID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Packages_Products_Suppliers (PackageId, ProductSupplierId) " +
"VALUES(@PackageId, @ProductSupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@PackageId", pSpack.packageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", pSpack.productSupplierId);
try
{
connection.Open();
// execute insert command and get PackageID
cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(pSpackID);
}
// 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);
}
// delete customer
// return indicator of success
public static bool DeleteProducts_suppliers_packages(Products_suppliers_packages pSpack)
{
bool success = false;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create DELETE command
string deleteStatement =
"DELETE FROM Products_suppliers_packages " +
"WHERE PackageId = @PackageId " + // needed for identification
"AND ProductSupplierId = @ProductSupplierId"; // the rest - for optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@PackageId", pSpack.packageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", pSpack.productSupplierId);
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);
}
// 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 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);
}
