//DELET DATA
public static bool Delete(Packages_Products_Suppliers ppSupplier)
{
bool result = false;
SqlConnection con = Connection.GetConnection();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "DELETE FROM Packages_Products_Suppliers WHERE PackageId = @id";
// Primary Key Paramater
SqlParameter idPar = new SqlParameter("@id", SqlDbType.Int);
idPar.Value = ppSupplier.PackageId;
cmd.Parameters.Add(idPar);
cmd.ExecuteNonQuery();
result = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(result);
}
{// get suppliers as a list
public static List <Packages_Products_Suppliers> GetSuppliers()
{
Packages_Products_Suppliers ppSupplier = null;
SqlConnection conn = Connection.GetConnection();
string query = "SELECT PackageId, ProductSupplierId " +
"FROM Packages_Products_Suppliers;";
SqlCommand cmd = new SqlCommand(query, conn);
List <Packages_Products_Suppliers> ppsList = new List <Packages_Products_Suppliers>();
// use try and catch to execute command
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ppSupplier = new Packages_Products_Suppliers((int)reader["PackageId"], (int)reader["ProductSupplierId"]);
ppsList.Add(ppSupplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(ppsList);
}
// selects row of the producst suppleir packages table in the packages tab
private void SelectedRowProdSupPackages()
{
packProdSup = new Packages_Products_Suppliers();
int index = packages_Products_SuppliersDataGridView.CurrentCell.RowIndex;
DataGridViewRow selectedRow = packages_Products_SuppliersDataGridView.Rows[index];
packProdSup.ProductSupplerID = (int)selectedRow.Cells[0].Value;
packProdSup.ProdName = selectedRow.Cells[1].Value.ToString();
packProdSup.SupName = selectedRow.Cells[2].Value.ToString();
}
//modify button pressed for the product suppleir table on the package tab in the main form
private void btnModifyPackProdSup_Click(object sender, EventArgs e)
{
//gets the package products supplier from the list according to the row index form the table
Packages_Products_Suppliers packProdSup = packProdSups[packages_Products_SuppliersDataGridView.CurrentCell.RowIndex];
SelectedRowPackage();
AddModifyProdSup addModifyProdSup = new AddModifyProdSup();
addModifyProdSup.addPackageProdSup = false;
addModifyProdSup.package = package; //make add modify package equal package main form
addModifyProdSup.packProdSup = packProdSup; //make add modify package product supplier equal package product supplier main form
DialogResult result = addModifyProdSup.ShowDialog();
this.DisplayPackages();
this.DisplayPackageProdSup();
}
//UPDATE DATA
public static bool Update(Packages_Products_Suppliers oldPpSupplier, Packages_Products_Suppliers newPpSupplier)
{
bool result = false;
SqlConnection con = Connection.GetConnection();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "UPDATE Packages_Products_Suppliers SET PackageId = @newPid, ProductSupplierId = @newPsId Where PackageId = @oldPid and ProductSupplierId = @oldPsId";
// new Package Id Paramater
SqlParameter newPidPar = new SqlParameter("@newPid", SqlDbType.Int);
newPidPar.Value = newPpSupplier.PackageId;
cmd.Parameters.Add(newPidPar);
// new ProductSupplierId Paramater
SqlParameter newPsIdPar = new SqlParameter("@newPsId", SqlDbType.Int);
newPsIdPar.Value = newPpSupplier.ProductSupplierId;
cmd.Parameters.Add(newPsIdPar);
// oldId Package Paramater
SqlParameter oldIdPar = new SqlParameter("@oldPid", SqlDbType.Int);
oldIdPar.Value = oldPpSupplier.PackageId;
cmd.Parameters.Add(oldIdPar);
// old ProductSupplierId Paramater
SqlParameter oldNamePar = new SqlParameter("@oldPsId", SqlDbType.Int);
oldNamePar.Value = oldPpSupplier.ProductSupplierId;
cmd.Parameters.Add(oldNamePar);
cmd.ExecuteNonQuery();
result = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(result);
}
//INSERT items
public static bool Insert(Packages_Products_Suppliers ppSupplier)
{
bool result = false;
SqlConnection con = Connection.GetConnection();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "INSERT INTO Packages_Products_Suppliers (PackageId, ProductSupplierId) values (@pId, @psId)";
// insert the PackageId
SqlParameter pIdPar = new SqlParameter("@pId", SqlDbType.Int);
pIdPar.Value = ppSupplier.PackageId;
cmd.Parameters.Add(pIdPar);
// insert the ProductSupplierId
SqlParameter psIdPar = new SqlParameter("@psId", SqlDbType.Int);
psIdPar.Value = ppSupplier.ProductSupplierId;
cmd.Parameters.Add(psIdPar);
cmd.ExecuteNonQuery();
result = true;
}
catch (SqlException e)
{
switch (e.Number)
{
case 2627:
throw new DuplicateKeyException(string.Format("Duplicate ID entry, please enter a different ID and try again {0}", ppSupplier.PackageId));
default:
throw;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(result);
}
