public static bool UpdatePackages_Products_Suppliers(Package_Product_Supplier oldPps, Package_Product_Supplier newPps)
{
// Updates the Packages_Products_Suppliers Table
// parameter oldPos ... the old row as an instance of Package_Product_Supplier class
// parameter newPps ... the new row as an instance of Package_Product_Supplier class
// returns true row updated, false row not updated
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string updateStatement = "UPDATE Products SET PackageId=@newPackageId, ProductSupplierId=@newProductSupplierId, " +
"WHERE PackageId=@oldPackageId and ProductSupplierId=@oldProductSupplierId";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
// new product listing
updateCommand.Parameters.AddWithValue("@newnewPackageId", newPps.PackageId);
updateCommand.Parameters.AddWithValue("@newProductSupplierId", newPps.ProductSupplierId);
// old product listing
updateCommand.Parameters.AddWithValue("@oldPackageId", oldPps.PackageId);
updateCommand.Parameters.AddWithValue("@oldProductSupplierId", oldPps.ProductSupplierId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true); // rows updated
}
else
{
return(false); //rows not updated
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Add a package_product_supplier to the Packages_Products_Suppliers table
/// </summary>
/// <param name="prod">Package_Product_Supplier instance</param>
/// <returns>PackageId</returns>
public static int AddPackages_Products_Suppliers(Package_Product_Supplier pps)
{
// Add a package_product_supplier to the Packages_Products_Suppliers Table
// pps is the instance of Product class
// returns the ProductId of the row inserted or -1 if not added to table
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
String insertStatement = "INSERT INTO Products (PackageId, ProductSupplierId) VALUES (@PackageId, @ProductSupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PackageId", pps.PackageId);
insertCommand.Parameters.AddWithValue("@ProductSupplierId", pps.ProductSupplierId);
try
{
connection.Open();
int numRows = insertCommand.ExecuteNonQuery();
if (numRows > 0)
{
string selectStatement = "SELECT IDENT_CURRENT('PackageId') FROM Packages_Products_Suppliers";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int packageId = (int)(selectCommand.ExecuteScalar());
return(packageId);
}
else
{
return(-1);
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
public static bool UpdatePackages_Products_Suppliers(Package_Product_Supplier oldPps, Package_Product_Supplier newPps)
{
// Updates the Packages_Products_Suppliers Table
// parameter oldPos ... the old row as an instance of Package_Product_Supplier class
// parameter newPps ... the new row as an instance of Package_Product_Supplier class
// returns true row updated, false row not updated
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string updateStatement = "UPDATE Products SET PackageId=@newPackageId, ProductSupplierId=@newProductSupplierId, " +
"WHERE PackageId=@oldPackageId and ProductSupplierId=@oldProductSupplierId";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
// new product listing
updateCommand.Parameters.AddWithValue("@newnewPackageId", newPps.PackageId);
updateCommand.Parameters.AddWithValue("@newProductSupplierId", newPps.ProductSupplierId);
// old product listing
updateCommand.Parameters.AddWithValue("@oldPackageId", oldPps.PackageId);
updateCommand.Parameters.AddWithValue("@oldProductSupplierId", oldPps.ProductSupplierId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return true; // rows updated
}
else
{
return false; //rows not updated
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Add a package_product_supplier to the Packages_Products_Suppliers table
/// </summary>
/// <param name="prod">Package_Product_Supplier instance</param>
/// <returns>PackageId</returns>
public static int AddPackages_Products_Suppliers(Package_Product_Supplier pps)
{
// Add a package_product_supplier to the Packages_Products_Suppliers Table
// pps is the instance of Product class
// returns the ProductId of the row inserted or -1 if not added to table
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
String insertStatement = "INSERT INTO Products (PackageId, ProductSupplierId) VALUES (@PackageId, @ProductSupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PackageId", pps.PackageId);
insertCommand.Parameters.AddWithValue("@ProductSupplierId", pps.ProductSupplierId);
try
{
connection.Open();
int numRows = insertCommand.ExecuteNonQuery();
if (numRows > 0)
{
string selectStatement = "SELECT IDENT_CURRENT('PackageId') FROM Packages_Products_Suppliers";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int packageId = (int)(selectCommand.ExecuteScalar());
return packageId;
}
else
{
return -1;
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
