public static List <PacProSup> GetPacProSup()
{
List <PacProSup> newPacProSup = new List <PacProSup>(); // empty list object
try
{
PacProSup currentPacProSup; // for reading
string selectJoinQuery = "select pp.ProductSupplierId, p.PackageId ,PkgName,pt.ProductId, ProdName,ss.SupplierId, ss.SupName " +
"from Packages_Products_Suppliers pps " +
"inner join Packages p " +
"on p.PackageId = pps.PackageId " +
"inner join Products_Suppliers pp " +
"on pps.ProductSupplierId = pp.ProductSupplierId " +
"inner join Products pt " +
"on pt.ProductId = pp.ProductId " +
"inner join Suppliers ss " +
"on ss.SupplierId = pp.SupplierId";
// block code style
using (SqlConnection con = GetConnection())
{
// use con anywhere in this block
using (SqlCommand cmd = new SqlCommand(selectJoinQuery, con))
{
// use cmd anywhere in this block
// any exception will be thrown to the place wher this method was called
con.Open();
// this will auto close connection when done
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read()) // while there is still data in the datareader
{
/*PackageId
* ProductSupplierId*/
currentPacProSup = new PacProSup();
currentPacProSup.PackageId = Convert.ToInt32(dr["PackageId"]);
currentPacProSup.ProductId = Convert.ToInt32(dr["ProductId"]);
currentPacProSup.ProdName = dr["ProdName"] as string;
currentPacProSup.SupplierId = Convert.ToInt64(dr["SupplierId"]);
currentPacProSup.SupName = dr["SupName"] as string;
currentPacProSup.ProductSupplierId = Convert.ToInt32(dr["ProductSupplierId"]);
newPacProSup.Add(currentPacProSup);
}
}
} // the object gets recycled
}
catch (Exception)
{
MessageBox.Show("Connection Error. Failed to retrieve PacProSup information");
}
return(newPacProSup);
}
/// <summary>
/// INSERTING new pacprosup ids combo
/// </summary>
/// <param name="inputPPS"></param>
/// <returns></returns>
public static bool AddPacProSup(PacProSup inputPPS)
{
bool success = true;
// block code style
string InsertStatement = "DECLARE @PackageId int = @inputPkgID " +
"DECLARE @ProductId int = @inputProID " +
"DECLARE @SupplierId int = @inputSupID " +
"DECLARE @ProductSupplierId int " +
"SELECT @ProductSupplierId = ProductSupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductId = @ProductId AND SupplierId = @SupplierId " +
"IF (@ProductSupplierId IS null) " +
"BEGIN " +
"INSERT INTO Products_Suppliers(ProductId, SupplierId) VALUES(@ProductId, @SupplierId) " +
"SELECT @ProductSupplierId = SCOPE_IDENTITY() " +
"END " +
"INSERT INTO Packages_Products_Suppliers(PackageId, ProductSupplierId) VALUES (@PackageId, @ProductSupplierId)";
SqlConnection con = GetConnection();
SqlCommand cmd = new SqlCommand(InsertStatement, con);
cmd.Parameters.AddWithValue("@inputPkgID", inputPPS.PackageId);
cmd.Parameters.AddWithValue("@inputProID", inputPPS.ProductId);
cmd.Parameters.AddWithValue("@inputSupID", inputPPS.SupplierId);
try
{
// use cmd anywhere in this block
// any exception will be thrown to the place where this method was called
con.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false;
}
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
return(success);
}
