private void dataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
var senderGrid = (DataGridView)sender;
if (senderGrid.Columns[e.ColumnIndex] is DataGridViewButtonColumn &&
e.RowIndex >= 0 && e.ColumnIndex == 2) // update record
{
//TODO - Button Clicked - Execute Code Here
Int64 cID = Int64.Parse(dataGridView.Rows[e.RowIndex].Cells[0].Value.ToString());
string cName = dataGridView.Rows[e.RowIndex].Cells[1].Value.ToString();
pCategoryUpdate pcUpdate = new pCategoryUpdate(cID, cName, 1);
pcUpdate.Show();
}
if (senderGrid.Columns[e.ColumnIndex] is DataGridViewButtonColumn &&
e.RowIndex >= 0 && e.ColumnIndex == 3) // delete record
{
Int64 cID = Int64.Parse(dataGridView.Rows[e.RowIndex].Cells[0].Value.ToString());
DialogResult dialogResult = MessageBox.Show("Are you sure want to Delete record?", "Delete", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
pcBLL = new ProductCategoryBLL();
PCategoryDTO DTO = new PCategoryDTO("", 0);
DTO.CATID = cID;
pcBLL.deleteCatRecord(DTO);
searchAll();
}
else if (dialogResult == DialogResult.No)
{
}
}
}
// ======================================== End of Product population in comboBox method ====================================
// ======================================== ProductListing method ====================================
public ArrayList pListingSearch(PCategoryDTO DTO)
{
pCategoryDAL = new ProductCategoryDAL();
ArrayList arr = pCategoryDAL.pListingSearch(DTO);
return(arr);
}
public void SearchRecordByCategory() // method to be called on search button click
{
string pCat = cmb_PL_PCategories.Text;
if (pCat != "")
{
int BAdmin = 1;
cDTO = new PCategoryDTO(pCat, BAdmin);
pCategoryBLL = new ProductCategoryBLL();
ArrayList arr = pCategoryBLL.pListingSearch(cDTO);
lv_ProductListing.Items.Clear();
foreach (object O in arr)
{
ProductDTO DTO = (ProductDTO)O;
string[] row = { DTO.PID + "", DTO.PNAME, DTO.PCODE, DTO.PCOMPANY, DTO.PSTOCKUNIT, DTO.PCATEGORY };
ListViewItem item = new ListViewItem(row);
lv_ProductListing.Items.Add(item);
}
}
else
{
MessageBox.Show("Please Select Category to Search", "Error");
}
}
// ======================================== End of Product population in comboBox method ====================================
// ============================================== ProductListing method ==========================================
public ArrayList pListingSearch(PCategoryDTO DTO)
{
arr = new ArrayList();
ProductManagementDAL DAL = new ProductManagementDAL();
try
{
con = new SqlConnection();
con.ConnectionString = conString;
con.Open();
string sqlQuery = "SELECT Product_Id,Product_Name,Product_Code,Company,Stock_Keeping_Unit,Business_Admin_Id,Product_Category_Id"
+ " FROM dbo.PRODUCT WHERE Product_Category_Id=@Product_Category_Id";
SqlCommand cm = new SqlCommand(sqlQuery, con);
cm.Parameters.AddWithValue("@Product_Category_Id", DAL.CatNametoID(DTO.CATNAME));
//MessageBox.Show(DAL.CatNametoID(DTO.CATNAME)+"");
DTO.CATNAME = "";
try
{
using (var reader = cm.ExecuteReader())
{
if (reader.HasRows)
{
//Check the reader has data:
while (reader.Read())
{
// Do something
Int64 PID = reader.GetInt64(reader.GetOrdinal("Product_Id"));
string PNAME = reader.GetString(reader.GetOrdinal("Product_Name"));
string PCODE = reader.GetString(reader.GetOrdinal("Product_Code"));
string PCOMPANY = reader.GetString(reader.GetOrdinal("Company"));
string PSTOCKUNIT = reader.GetString(reader.GetOrdinal("Stock_Keeping_Unit"));
Int64 PBADMIN = reader.GetInt64(reader.GetOrdinal("Business_Admin_Id"));
string PCATEGORY = DAL.CatIDtoName(reader.GetInt64(reader.GetOrdinal("Product_Category_Id")));
arr.Add(new ProductDTO(PID, PNAME, PCODE, PCOMPANY, PSTOCKUNIT, PBADMIN, PCATEGORY));
}
con.Close();
return(arr);
}
else
{
MessageBox.Show("Record Not Found", "Error");
return(arr);
}
}
}
catch (Exception)
{
MessageBox.Show("Query Execution error!", "Error");
return(arr);
}
}
catch (Exception)
{
MessageBox.Show("Connection Not Successful to Database!", "Error");
return(arr);
}
}
// =================================== PRODUCT CATEGORY UPDATE DELETE SEARCH INSERT METHODS ================================
// =========================================== category insertion in Database =====================================================
public void insertCatToDatabase(PCategoryDTO DTO)
{
try
{
con = new SqlConnection();
con.ConnectionString = conString;
con.Open();
string sqlQuery = "INSERT INTO dbo.PRODUCTCATEGORY (Category_Name,Business_Admin_Id)"
+ " VALUES (@Category_Name,@Business_Admin_Id)";
SqlCommand cm = new SqlCommand(sqlQuery, con);
cm.Parameters.AddWithValue("@Category_Name", DTO.CATNAME);
cm.Parameters.AddWithValue("@Business_Admin_Id", DTO.CATBADMIN);
try
{
cm.ExecuteNonQuery();
con.Close();
}
catch (Exception)
{
MessageBox.Show("Data Not Inserted!", "Error");
}
}
catch (Exception)
{
MessageBox.Show("Connection Not Successful to Database!", "Error");
}
}
// =========================================== end of update in Database =====================================================
// =========================================== delete in Database =====================================================
public void deleteCatToDatabase(PCategoryDTO DTO)
{
try
{
con = new SqlConnection();
con.ConnectionString = conString;
con.Open();
string sqlQuery = "DELETE FROM dbo.PRODUCTCATEGORY WHERE Product_Category_Id=@Product_Category_Id";
SqlCommand cm = new SqlCommand(sqlQuery, con);
cm.Parameters.AddWithValue("@Product_Category_Id", DTO.CATID);
try
{
cm.ExecuteNonQuery();
con.Close();
}
catch (Exception)
{
MessageBox.Show("Data Not Deleted!", "Error");
}
}
catch (Exception)
{
MessageBox.Show("Connection Not Successful to Database!", "Error");
}
}
// =========================================== end of category search in Database =====================================================
// =========================================== update in Database =====================================================
public void updateCatToDatabase(PCategoryDTO DTO)
{
try
{
con = new SqlConnection();
con.ConnectionString = conString;
con.Open();
string sqlQuery = "UPDATE dbo.PRODUCTCATEGORY SET Category_Name=@Category_Name,"
+ "Business_Admin_Id=@Business_Admin_Id WHERE Product_CAtegory_Id=@Product_Category_Id";
SqlCommand cm = new SqlCommand(sqlQuery, con);
cm.Parameters.AddWithValue("@Product_Category_Id", DTO.CATID);
cm.Parameters.AddWithValue("@Category_Name", DTO.CATNAME);
cm.Parameters.AddWithValue("@Business_Admin_Id", DTO.CATBADMIN);
try
{
cm.ExecuteNonQuery();
con.Close();
}
catch (Exception)
{
MessageBox.Show("Data Not Updated!", "Error");
}
}
catch (Exception)
{
MessageBox.Show("Connection Not Successful to Database!", "Error");
}
}
// =========================================== end of category insertion in Database =====================================================
// =========================================== category search in Database =====================================================
public void searchCatInDatabase(PCategoryDTO DTO)
{
try
{
con = new SqlConnection();
con.ConnectionString = conString;
con.Open();
string sqlQuery = "SELECT Product_Category_Id,Category_Name,Business_Admin_Id"
+ " FROM dbo.PRODUCTCATEGORY WHERE Category_Name=@Category_Name";
SqlCommand cm = new SqlCommand(sqlQuery, con);
cm.Parameters.AddWithValue("@Category_Name", DTO.CATNAME);
DTO.CATNAME = "";
try
{
using (var reader = cm.ExecuteReader())
{
if (reader.HasRows)
{
//Check the reader has data:
if (reader.Read())
{
DTO.CATID = reader.GetInt64(reader.GetOrdinal("Product_Category_Id"));
DTO.CATNAME = reader.GetString(reader.GetOrdinal("Category_Name"));
DTO.CATBADMIN = reader.GetInt64(reader.GetOrdinal("Business_Admin_Id"));
}
con.Close();
// If you need to use all rows returned use a loop:
/*while (reader.Read())
* {
* // Do something
* }*/
}
else
{
MessageBox.Show("Record Not Found");
}
}
}
catch (Exception)
{
MessageBox.Show("Query Execution Error!");
}
}
catch (Exception)
{
MessageBox.Show("Connection Not Successful to Database!");
}
}
void searchAll()
{
dataGridView.Rows.Clear();
dataGridView.Refresh();
pcBLL = new ProductCategoryBLL();
ArrayList arr = pcBLL.searchAll();
foreach (object o in arr)
{
pcDTO = (PCategoryDTO)o;
int a = dataGridView.Rows.Add();
dataGridView.Rows[a].Cells[0].Value = pcDTO.CATID;
dataGridView.Rows[a].Cells[1].Value = pcDTO.CATNAME;
}
}
private void insert_CATAdd_button_Click(object sender, EventArgs e)
{
pCategoryBLL = new ProductCategoryBLL();
string catName = insert_CATName_textBox.Text;
int catAdmin = 1;
if (catName != "")
{
PCategoryDTO DTO = new PCategoryDTO(catName, catAdmin);
pCategoryBLL.insertCatRecord(DTO);
clearAllFields();
}
else
{
MessageBox.Show("Please Fill All The Fields!");
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Are you sure want to update record?", "Update", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
pCategoryBLL = new ProductCategoryBLL();
string catName = txtCtName.Text;
Int64 catAdmin = Int64.Parse(cmbCtBAdmin.Text);
PCategoryDTO DTO = new PCategoryDTO(pCID, catName, catAdmin);
pCategoryBLL.updateCatRecord(DTO);
Close();
}
else if (dialogResult == DialogResult.No)
{
}
}
// ====================================== end of update in database ============================================
// ====================================== delete in database ============================================
public void deleteCatRecord(PCategoryDTO DTO)
{
pCategoryDAL = new ProductCategoryDAL();
pCategoryDAL.deleteCatToDatabase(DTO);
}
// ====================================== end of insertion in database ============================================
// ====================================== search in database ============================================
public void searchCatRecord(PCategoryDTO DTO)
{
pCategoryDAL = new ProductCategoryDAL();
pCategoryDAL.searchCatInDatabase(DTO);
}
// ============================== PRODUCT CATEGORY UPDATE DELETE SEARCH INSERT METHODS ==============================
// ====================================== category insertion in database ============================================
public void insertCatRecord(PCategoryDTO DTO)
{
pCategoryDAL = new ProductCategoryDAL();
pCategoryDAL.insertCatToDatabase(DTO);
}
