//+++ METHOD WHICH RETRIEVES DATA FROM DATABASE AND RETURNS LIST PACK-PROD-SUPPLIERS TO A FORM
public static List <PackProdSupplier> getProdSuppliersForDGV(int packIdIndex)
{
string queryString = @"select Products.ProdName, Products.ProductId,
Suppliers.SupName, Suppliers.SupplierId,
Products_Suppliers.ProductSupplierId
from Packages_Products_Suppliers
Inner Join Packages on Packages.PackageId = Packages_Products_Suppliers.PackageId
Inner Join Products_Suppliers on Products_Suppliers.ProductSupplierId = Packages_Products_Suppliers.ProductSupplierId
Inner Join Products on Products.ProductId = Products_Suppliers.ProductId
Inner Join Suppliers on Suppliers.SupplierId = Products_Suppliers.SupplierId
where Packages.PackageId =";
// Store retreived data into datatable and create a list
DataTable packageProdSupplList = retreiveData(queryString, packIdIndex);
List <PackProdSupplier> prodSuppliersList = new List <PackProdSupplier>();
foreach (DataRow row in packageProdSupplList.Rows)
{
PackProdSupplier itemLine = new PackProdSupplier(row[0].ToString(), Convert.ToInt32(row[1]),
row[2].ToString(), Convert.ToInt32(row[3]), Convert.ToInt32(row[4]));
prodSuppliersList.Add(itemLine);
}
return(prodSuppliersList);
}
// METHOD WHICH RETRIEVES DATA FROM DATABASE AND RETURNS LIST PACK-PROD-SUPPLIERS TO A FORM
public static List <PackProdSupplier> getProdSuppliersForDGV(int packIdIndex)
{
string queryString = @"select Products.ProdName, Products.ProductId,
Suppliers.SupName, Suppliers.SupplierId,
Products_Suppliers.ProductSupplierId
from Packages_Products_Suppliers
Inner Join Packages on Packages.PackageId = Packages_Products_Suppliers.PackageId
Inner Join Products_Suppliers on Products_Suppliers.ProductSupplierId = Packages_Products_Suppliers.ProductSupplierId
Inner Join Products on Products.ProductId = Products_Suppliers.ProductId
Inner Join Suppliers on Suppliers.SupplierId = Products_Suppliers.SupplierId
where Packages.PackageId =";
DataTable dt = new DataTable();
SqlConnection conn = null;
// RETRIEVE DATA FROM DATABASE AND STORE IT IN DATATABLE
try
{
conn = DBConnection.getConnection();
using (SqlCommand command =
new SqlCommand((queryString + packIdIndex), conn))
{
conn.Open();
SqlDataReader reader = command.ExecuteReader();
dt.Load(reader);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
///+++++++++
// Store retreived data into datatable and create a list
List <PackProdSupplier> prodSuppliersList = new List <PackProdSupplier>();
foreach (DataRow row in dt.Rows)
{
PackProdSupplier itemLine = new PackProdSupplier(row[0].ToString(), Convert.ToInt32(row[1]),
row[2].ToString(), Convert.ToInt32(row[3]), Convert.ToInt32(row[4]));
prodSuppliersList.Add(itemLine);
}
return(prodSuppliersList);
}
// METHOD HANDLES CELL CHANGES IN DATAGRIDVIEW
private void dgvPackProdSuppl_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
// Clrear Suppliers combobox on change of SupplierID
if (e.ColumnIndex == 1)
{
//Make a Supplier
dgvPackProdSuppl.Rows[e.RowIndex].Cells[2].Value = null;
// Create a list of possiple values to populate a combox
List <Supplier> supplierIdPairs =
PackProdSupplier.createSupplierIdPairsList(Convert.ToInt32(dgvPackProdSuppl.Rows[e.RowIndex].Cells[1].Value));
((DataGridViewComboBoxCell)dgvPackProdSuppl.Rows[e.RowIndex].
Cells[2]).DataSource = supplierIdPairs;
((DataGridViewComboBoxCell)dgvPackProdSuppl.Rows[e.RowIndex].
Cells[2]).DisplayMember = "SupName";
((DataGridViewComboBoxCell)dgvPackProdSuppl.Rows[e.RowIndex].
Cells[2]).ValueMember = "SupplierId";
}
// Change SupplierID on change of Supplier
if (e.ColumnIndex == 2)
{
if (dgvPackProdSuppl.Rows[e.RowIndex].Cells[2].Value != null)
{
dgvPackProdSuppl.Rows[e.RowIndex].Cells[3].Value =
dgvPackProdSuppl.Rows[e.RowIndex].Cells[2].Value;
}
else
{
dgvPackProdSuppl.Rows[e.RowIndex].Cells[3].Value = null;
}
}
// Change ProdSupplierID on change of SupplierID
if (e.ColumnIndex == 3)
{
if (dgvPackProdSuppl.Rows[e.RowIndex].Cells[3].Value != null &&
dgvPackProdSuppl.Rows[e.RowIndex].Cells[1].Value != null)
{
int prodId = Convert.ToInt32(dgvPackProdSuppl.Rows[e.RowIndex].Cells[1].Value);
int supplierId = Convert.ToInt32(dgvPackProdSuppl.Rows[e.RowIndex].Cells[3].Value);
dgvPackProdSuppl.Rows[e.RowIndex].Cells[4].Value =
DBHandler.getNewProdSupplierId(prodId, supplierId);
}
else
{
dgvPackProdSuppl.Rows[e.RowIndex].Cells[4].Value = null;
}
}
}
//*******************************************************************************//
// END OF CUSTOM USER CONTROL PART
//*******************************************************************************//
private void PackagesForm_Load(object sender, EventArgs e)
{
Constants.packageIsChanged = false;
if (itIsNewForm == false)
{
// if form is opened in EDIT mode fill all exusting informartion
// Create a DGV table
FormHandler.createProdSupplTable(dgvPackProdSuppl);
// Create a list of PackProdSuppl objects to popuplate Data Grid View with
// existing data
List <PackProdSupplier> packProdSupList = PackProdSupplierDB.getProdSuppliersForDGV(Convert.ToInt32(txtId.Text));
// Loop through list generated from data table to populate DataGridView
string[] splitLine = new string[5];
foreach (PackProdSupplier line in packProdSupList)
{
// variable to break list line into array
splitLine = line.PackProdSupplierToString().Split(',');
// Create a list of possiple values to populate a combox
List <Supplier> supplierIdPairs =
PackProdSupplier.createSupplierIdPairsList(Convert.ToInt32(splitLine[1]));
// Find index of right element
int indOfRightElement = supplierIdPairs.IndexOf(supplierIdPairs.Where(p =>
p.SupplierId == Convert.ToInt32(splitLine[3])).FirstOrDefault());
// Store values from DataTable
DataGridViewRow dgvRow = new DataGridViewRow();
dgvRow.Cells.Add(new DataGridViewTextBoxCell());
dgvRow.Cells.Add(new DataGridViewTextBoxCell());
dgvRow.Cells.Add(new DataGridViewComboBoxCell());
dgvRow.Cells.Add(new DataGridViewTextBoxCell());
dgvRow.Cells.Add(new DataGridViewTextBoxCell());
dgvRow.Cells[0].Value = splitLine[0];
dgvRow.Cells[1].Value = splitLine[1];
((DataGridViewComboBoxCell)dgvRow.Cells[2]).FlatStyle = FlatStyle.Flat;
((DataGridViewComboBoxCell)dgvRow.Cells[2]).DataSource = supplierIdPairs;
((DataGridViewComboBoxCell)dgvRow.Cells[2]).DisplayMember = "SupName";
((DataGridViewComboBoxCell)dgvRow.Cells[2]).ValueMember = "SupplierId";
((DataGridViewComboBoxCell)dgvRow.Cells[2]).Value =
supplierIdPairs[indOfRightElement].SupplierId;
dgvRow.Cells[3].Value = splitLine[3];
dgvRow.Cells[4].Value = splitLine[4];
dgvPackProdSuppl.Rows.Add(dgvRow);
// add item to prodSuppliersIdForUpDate list
prodSuppliersIdForUpDate.Add(Convert.ToInt32(dgvRow.Cells[4].Value));
}
}
else
{
// if it is a new form it is needed to generate new Package ID
txtId.Text = Convert.ToString((PackageDB.getMaxPackIdValue() + 1));
}
//**************************************************************************
//****** ADD CUSTOM BUTTON CONTROL
//**************************************************************************
this.txtbtn = new TextAndButtonControlPackForm();
this.txtbtn.Visible = false;
this.dgvPackProdSuppl.Controls.Add(this.txtbtn);
//Handle the cellbeginEdit event to show the usercontrol in the cell while editing
this.dgvPackProdSuppl.CellBeginEdit += new DataGridViewCellCancelEventHandler(dgv_CellBeginEdit);
//Handle the cellEndEdit event to update the cell value
this.dgvPackProdSuppl.CellEndEdit += new DataGridViewCellEventHandler(dgv_CellEndEdit);
//**************************************************************************
//****** END OF CUSTOM BUTTON CONTROL
//**************************************************************************
FormHandler.captureChanges(this);
rtxtHint.SelectionAlignment = HorizontalAlignment.Center;
}
