protected void ButtonDeleteRecord_Click(object sender, EventArgs e)
{
try
{
foreach (GridViewRow grow in GridView1.Rows)
{
//Searching CheckBox("CheckBoxDelete") in an individual row of Grid
CheckBox checkdel = (CheckBox)grow.FindControl("CheckBoxDelete");
//If CheckBox is checked than delete the record with particular empid and terid
if (checkdel.Checked)
{
int terID = Convert.ToInt32(GridView1.DataKeys[grow.RowIndex].Values["TerritoryID"]);
string cstring = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
MySqlConnection con = new MySqlConnection(cstring);
MySqlCommand cmd1 = new MySqlCommand(m.strEmTerDel + terID + "' ", con);
con.Open();
cmd1.ExecuteNonQuery();
MySqlCommand cmd2 = new MySqlCommand(m.strTerDel + terID + "'", con);
cmd2.ExecuteNonQuery();
con.Close();
BindGrid();
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('Record is deleted successfully');", true);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
m = null;
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
EmpDelID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["EmployeeID"]);
TerrDelID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["TerritoryID"]);
string cstring = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
MySqlConnection con = new MySqlConnection(cstring);
MySqlCommand cmd1 = new MySqlCommand(m.strEmTerDel + EmpDelID + "' and TerritoryID = '" + TerrDelID + "'", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
BindGrid();
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('Record is deleted successfully');", true);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
EmpDelID = null;
TerrDelID = null;
m = null;
}
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
object TerrUpID; //Object needed to fetch the current values of gridview rows
MySqlCommand verifyTerID;
MySqlCommand verifyInEmp;
int terIDValue;
int terInEmpValue;
TerrUpID = GridView1.DataKeys[e.RowIndex].Values["TerritoryID"];
GridViewRow row = GridView1.Rows[e.RowIndex];
string ter = ((TextBox)row.FindControl("textGridTerritoryID")).Text;
try
{
string cstring = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
MySqlConnection con = new MySqlConnection(cstring);
if (con != null && con.State == ConnectionState.Closed)
{
con.Open();
}
//Checking if territory id exists or not in territories Table
StringBuilder strter = new StringBuilder(m.strTerrCheck + ter + "'");
verifyTerID = new MySqlCommand(strter.ToString(), con);
terIDValue = Convert.ToInt32(verifyTerID.ExecuteScalar());
//Checking if territory id exists or not in employeeterritories Table
StringBuilder strTerInEmp = new StringBuilder(m.strTerInEmCheck + ter + "'");
verifyInEmp = new MySqlCommand(strTerInEmp.ToString(), con);
terInEmpValue = Convert.ToInt32(verifyInEmp.ExecuteScalar());
if (terIDValue > 0)
{
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('TerritoryID already exists in Territories Table');", true);
}
if (terInEmpValue > 0)
{
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('TerritoryID already exists in EmployeeTerritories Table');", true);
}
else
{
GridView1.EditIndex = -1;
MySqlCommand cmd = new MySqlCommand(m.strUpdate1 + ter + "' where TerritoryID = '" + TerrUpID + "'; " + m.strUpdate2 + ter + "' where TerritoryID = '" + TerrUpID + "'", con);
cmd.ExecuteNonQuery();
con.Close();
BindGrid();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
TerrUpID = null;
verifyInEmp = null;
verifyTerID = null;
m = null;
}
}
protected void AddButtonClick(object sender, EventArgs e)
{
AllMessage m = new AllMessage();
MySqlCommand verifyEmpID;
MySqlCommand verifyTerID;
MySqlCommand verifyEmpTerID;
int empIDvalue;
int terIDvalue;
int empTerIDvalue;
try
{
string cstring = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
MySqlConnection con = new MySqlConnection(cstring);
if (con != null && con.State == ConnectionState.Closed)
{
con.Open();
}
//Check if EmployeeID Present in in Employees Table
StringBuilder strEmpCheck = new StringBuilder(m.strEmpCheck + textEmployeeID.Text + "'");
verifyEmpID = new MySqlCommand(strEmpCheck.ToString(), con);
empIDvalue = Convert.ToInt32(verifyEmpID.ExecuteScalar());
//Check if TerritoryID Present in in Territory Table
StringBuilder strTerr = new StringBuilder(m.strTerrCheck + textTerritoryID.Text + "'");
verifyTerID = new MySqlCommand(strTerr.ToString(), con);
terIDvalue = Convert.ToInt32(verifyTerID.ExecuteScalar());
//Check if TerritoryID for corresponding EmployeeID are present in Employeeterritories Table
StringBuilder strEmpTerr = new StringBuilder(m.strEmpTerCheck + textEmployeeID.Text + "' AND TerritoryID='" + textTerritoryID.Text + "'");
verifyEmpTerID = new MySqlCommand(strEmpTerr.ToString(), con);
empTerIDvalue = Convert.ToInt32(verifyEmpTerID.ExecuteScalar());
if (empIDvalue > 0)
{
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('EmployeeID already exists in Employees Table');", true);
}
else if (terIDvalue > 0)
{
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('TerritoryID already exists in Territories Table');", true);
}
else if (empTerIDvalue > 0)
{
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('EmployeeID and TerritoryID already exists in Employees table');", true);
}
else
{
MySqlCommand cmd2 = new MySqlCommand(m.strInsertEmpTer + textEmployeeID.Text + "','" + textTerritoryID.Text + "');", con);
cmd2.ExecuteNonQuery();
con.Close();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
protected void Insert(object sender, EventArgs e)
{
MySqlCommand verifyBothAdd;
int bothExistsAdd;
string EmployeeIDAdd = textEmployeeID.Text;
string TerritoryIDAdd = textTerritoryID.Text;
textEmployeeID.Text = "";
textTerritoryID.Text = "";
try
{
string cstring = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
MySqlConnection con = new MySqlConnection(cstring);
if (con != null && con.State == ConnectionState.Closed)
{
con.Open();
}
//Checking if both ids exists or not in employeeterritories Table
StringBuilder strBothAdd = new StringBuilder(m.strEmTerCheck + EmployeeIDAdd + "' and TerritoryID='" + TerritoryIDAdd + "'");
verifyBothAdd = new MySqlCommand(strBothAdd.ToString(), con);
bothExistsAdd = Convert.ToInt32(verifyBothAdd.ExecuteScalar());
if (bothExistsAdd > 0)
{
ClientScript.RegisterStartupScript(GetType(), "msgbox", "alert('EmployeeID and TerritoryID already exists in EmployeeTerritories Table');", true);
}
else
{
MySqlCommand cmd1 = new MySqlCommand(m.strEmTerInsert + "'" + EmployeeIDAdd + "'" + "," + "'" + TerritoryIDAdd + "')", con);
cmd1.ExecuteNonQuery();
con.Close();
BindGrid();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
verifyBothAdd = null;
m = null;
}
}
protected void BindGrid()
{
try
{
string cstring = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
MySqlConnection con = new MySqlConnection(cstring);
MySqlCommand cmd = new MySqlCommand(m.strEmTer, con);
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
m = null;
}
}
protected void BtnInsert_Click(object sender, EventArgs e)
{
AllMessage m;
MySqlCommand Check_TerritoryID;
MySqlCommand Check_EmployeeID_TerritoryID;
int TerritoryID_Exist;
int EmployeeID_TerritoryID_Exist;
try
{
string cstring = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
MySqlConnection con = new MySqlConnection(cstring);
if (con != null && con.State == ConnectionState.Closed)
{
con.Open();
}
//Check if TerritoryID Present in in Territory Table
StringBuilder strTerr = new StringBuilder("select TerritoryID from Territories where (TerritoryID='" + TxtTerritoryID.Text + "')");
Check_TerritoryID = new MySqlCommand(strTerr.ToString(), con);
TerritoryID_Exist = Convert.ToInt32(Check_TerritoryID.ExecuteScalar());
//Check if TerritoryID for corresponding EmployeeID are present in Employeeterritories Table
StringBuilder strEmpTerr = new StringBuilder("select EmployeeID,TerritoryID from EmployeeTerritories WHERE EmployeeID='" + DdlEmployeeID.SelectedValue + "' AND TerritoryID='" + TxtTerritoryID.Text + "';");
Check_EmployeeID_TerritoryID = new MySqlCommand(strEmpTerr.ToString(), con);
EmployeeID_TerritoryID_Exist = Convert.ToInt32(Check_EmployeeID_TerritoryID.ExecuteScalar());
if (TerritoryID_Exist <= 0)
{
m = new AllMessage();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + m.stralertmessageTerritoryID + "');", true);
}
else if (EmployeeID_TerritoryID_Exist > 0)
{
m = new AllMessage();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + m.stralertmessageEmp_Terr_ID + "');", true);
}
else
{
MySqlCommand cmd2 = new MySqlCommand("insert into EmployeeTerritories (EmployeeID,TerritoryID) values ('" + DdlEmployeeID.SelectedItem.Value.ToString() + "','" + TxtTerritoryID.Text + "');", con);
cmd2.ExecuteNonQuery();
m = new AllMessage();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + m.stralertSuccessfulInsert + "');", true);
}
}
catch (Exception ex)
{
Response.Redirect(ex.Message);
}
finally
{
//if (con.State == ConnectionState.Open)
//{
// sqlconn.Close();
//}
//sqlcmd = null;
//strBrInsertQuery = null;
//objconstmsg = null;
//strBrInsertQuery = null;
//Check_TerritoryID = null;
//Check_EmployeeID_TerritoryID = null;
//strBrTerritoryID = null;
//strBrEmp_Terr_ID = null;
//TxtTerritoryID.Text = "";
}
}