public static int AddProductPackage(Packages_products_suppliers pck_prod_sup)
{
int prodPckId = 0;
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Packages_Products_Suppliers (ProductSupplierId) " +
"VALUES(@ProductSupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProductSupplierId", pck_prod_sup.ProductSupplierId);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Packages_Products_Suppliers') FROM Packages_Products_Suppliers"; // Identity value
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
prodPckId = Convert.ToInt32(selectCommand.ExecuteScalar()); // single value
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return prodPckId;
}
public static int UpdateProductPackage(Packages_products_suppliers oldProductPackage, Packages_products_suppliers newProductPackage)
{
int count = 0;
string updateStatement = "UPDATE Packages_Products_Suppliers SET " +
"ProductSupplierId = @NewProductSupplierId " +
"WHERE PackageId = @OldPackageId " +
"AND ProductSupplierId = @OldProductSupplierId ";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
cmd.Parameters.AddWithValue("@NewProductSupplierId", newProductPackage.ProductSupplierId);
cmd.Parameters.AddWithValue("@PackageId", oldProductPackage.PackageId); // PK is not null
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldProductPackage.ProductSupplierId);
try
{
con.Open();
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
return count;
}
public static List<Packages_products_suppliers> GetPackagesProductsSuppliers()
{
List<Packages_products_suppliers> packages_products_suppliers = new List<Packages_products_suppliers>();
Packages_products_suppliers pck_prod_sup;
string selectQuery = "SELECT PackageId, ProductSupplierId " +
"FROM Packages_Products_Suppliers " +
"ORDER BY PackageId";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
pck_prod_sup = new Packages_products_suppliers();
pck_prod_sup.PackageId = (int)dr["PackageId"];
pck_prod_sup.ProductSupplierId = (int)dr["ProductSupplierId"];
packages_products_suppliers.Add(pck_prod_sup);
}
}
}
return packages_products_suppliers;
}
