/// <summary>
/// method name:Grid_Fill
/// Description: Fill and bind the Gridview
/// Author:bhumi
/// Created On:10/6/2015
/// </summary>
public void Grid_Fill()
{
StringBuilder strBrSelectQuery;//string builder for query
try
{
objconstmsg = new ConstantMessages();
//insert SQLquery in String builder
strBrSelectQuery = new StringBuilder("SELECT EmployeeID,TerritoryID");
strBrSelectQuery.Append(" FROM");
strBrSelectQuery.Append(" EmployeeTerritories;");
//connection generation and read the data from reader
objcmnfunction = new CommanFunction();
//datatable
dtEmployees = new DataTable();
dtEmployees = objcmnfunction.ConnectionGenerate(strBrSelectQuery.ToString());
//store data in grid view
gvEmployeeTerritory.DataSource = dtEmployees;
gvEmployeeTerritory.DataBind();
}
catch (Exception)
{
Response.Redirect(objconstmsg.strerrorpage);
}
finally
{
strBrSelectQuery = null;
dtEmployees = null;
objconstmsg = null;
}
}
/// <summary>
/// method name:Grid_Fill
/// Description: Fill and bind the Gridview
/// Author:bhumi
/// Created On:9/6/2015
/// </summary>
public void Grid_Fill()
{
StringBuilder strBrSelectQuery;//string builder for query
try
{
objconstmsg = new ConstantMessages();
//insert SQLquery in String builder
strBrSelectQuery = new StringBuilder("SELECT CustomerID,CompanyName,ContactTitle,City,PostalCode,Phone,Fax");
strBrSelectQuery.Append(" FROM");
strBrSelectQuery.Append(" Customers;");
//connection generation and read the data from reader
objcmnfunction = new CommanFunction();
//datatable
dtCustomers = new DataTable();
dtCustomers = objcmnfunction.ConnectionGenerate(strBrSelectQuery.ToString());
//store data in grid view
gvCustomers.DataSource = dtCustomers;
gvCustomers.DataBind();
}
catch (Exception)
{
Response.Redirect(objconstmsg.strerrorpage, false);
}
finally
{
strBrSelectQuery = null;
}
}
/// <summary>
/// method name:Grid_Fill
/// Description: Fill and bind the Gridview
/// Author:bhumi
/// Created On:8/6/2015
/// </summary>
public void Grid_Fill()
{
StringBuilder strBrSelectQuery;//string builder for query
try
{
objconstmsg = new ConstantMessages();
//insert SQLquery in String builder
strBrSelectQuery = new StringBuilder("SELECT EmployeeID,TitleOfCourtesy,FirstName,LastName,");
strBrSelectQuery.Append("CONVERT(nvarchar,BirthDate,110) AS BirthDate,City,Country,HomePhone");
strBrSelectQuery.Append(" FROM");
strBrSelectQuery.Append(" Employees;");
//connection generation and read the data from reader
objcmnfunction = new CommanFunction();
//datatable
dtEmployees = new DataTable();
dtEmployees = objcmnfunction.ConnectionGenerate(strBrSelectQuery.ToString());
//store data in grid view
gvEmployees.DataSource = dtEmployees;
gvEmployees.DataBind();
}
catch (Exception)
{
Response.Redirect(objconstmsg.strerrorpage);
}
finally
{
strBrSelectQuery = null;
dtEmployees = null;
objconstmsg = null;
}
}
/// <summary>
/// Event Name:BtnHUpdate_Click
/// Description:Update the Customer Details
/// Author:Bhumi
/// Created on:9/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnHUpdate_Click(object sender, EventArgs e)
{
SqlConnection sqlconn;
SqlCommand sqlcmd;
SqlDataAdapter sqladapter;
SqlTransaction sqltransact;
sqlconn = new SqlConnection();
StringBuilder strBrUpdateQuery;
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open(); //open the connection
}
sqltransact = sqlconn.BeginTransaction("Transaction"); //Begin Transaction
try
{
customerid = Request.QueryString["CustomerID"];
//insert SQLquery in String builder
strBrUpdateQuery = new StringBuilder("UPDATE Customers");
strBrUpdateQuery.Append(" SET ");
strBrUpdateQuery.Append("CompanyName ='" + TxtCompanyName.Text + "',ContactTitle='" + TxtContactTitle.Text + "' ,City='" + TxtCity.Text + "' ,PostalCode='" + TxtPostalCode.Text + "',Phone='" + TxtPhoneNo.Text + "',Fax='" + TxtFax.Text + "'");
strBrUpdateQuery.Append(" Where CustomerID='" + customerid + "'");
sqlcmd = new SqlCommand(strBrUpdateQuery.ToString(), sqlconn, sqltransact);
sqladapter = new SqlDataAdapter(sqlcmd);
sqlcmd.ExecuteNonQuery();
sqltransact.Commit();//Commit
objconst = new ConstantMessages();
Response.Redirect(objconst.strCustomerPage, false);
TxtCustomerID.Text = "";
TxtCompanyName.Text = "";
TxtContactTitle.Text = "";
TxtCity.Text = "";
TxtPostalCode.Text = "";
TxtPhoneNo.Text = "";
TxtFax.Text = "";
}
catch (Exception ex)
{
Response.Write(ex.Message);
sqltransact.Rollback();//Roll back transaction
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlconn = null;
sqlcmd = null;
sqladapter = null;
sqltransact = null;
strBrUpdateQuery = null;
}
}
/// <summary>
/// Event Name: BtnHAdd_Click
/// Description: ADD Button Click Event
/// Author:Bhumi
/// Created On:9/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnHAdd_Click(object sender, EventArgs e)
{
//SQL CONNECTION
SqlConnection sqlconn;
ConstantMessages objconstmsg;
objconstmsg = new ConstantMessages();
SqlCommand sqlcmd;
sqlconn = new SqlConnection();
StringBuilder strBrInsertQuery;
try
{
if (Page.IsPostBack)
{
//connectionstring
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn != null && sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
strBrInsertQuery = new StringBuilder("insert");
strBrInsertQuery.Append(" into Employees ");
strBrInsertQuery.Append(" (TitleOfCourtesy,FirstName,LastName,BirthDate,City,Country,HomePhone)");
strBrInsertQuery.Append(" values");
strBrInsertQuery.Append(" ('" + ddlHSalutation.SelectedItem.Value.ToString() + "','" + TxtHFirstName.Text + "','" + TxtHLastName.Text + "','" + TxtHBirthDate.Text + "','" + TxtHCity.Text + "','" + TxtHCountry.Text + "','" + TxtHPhoneNo.Text + "')");
sqlcmd = new SqlCommand(strBrInsertQuery.ToString(), sqlconn);
sqlcmd.ExecuteNonQuery();
}
}
catch (Exception)
{
Response.Redirect(objconstmsg.strerrorpage);
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlconn = null;
sqlcmd = null;
strBrInsertQuery = null;
Server.Transfer(objconstmsg.strDefaultPage);
}
}
/// <summary>
/// Event Name:Page_Load
/// Description:Set focus on Control and initialize the webcontrols
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ConstantMessages objconst;
StringBuilder strBrInsert;
SqlConnection sqlconn;
sqlconn = new SqlConnection();
SqlCommand sqlcmd;
SqlDataAdapter sqladapter;
DataTable dttable;
strBrInsert = new StringBuilder("SELECT EmployeeID");
strBrInsert.Append(" FROM Employees");
try
{
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
sqlconn.Open();
sqlcmd = new SqlCommand(strBrInsert.ToString(), sqlconn);
sqladapter = new SqlDataAdapter(sqlcmd);
dttable = new DataTable();
sqladapter.Fill(dttable);
DdlEmployeeID.DataSource = dttable;
DdlEmployeeID.DataTextField = "EmployeeID";
DdlEmployeeID.DataValueField = "EmployeeID";
DdlEmployeeID.DataBind();
}
catch (Exception)
{
objconst = new ConstantMessages();
Response.Redirect(objconst.strerrorpage);
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
objconst = null;
strBrInsert = null;
sqlconn = null;
sqlcmd = null;
sqladapter = null;
dttable = null;
}
}
}
protected void LBtnDelete_Click(object sender, EventArgs e)
{
try
{
strcollect1 = new StringCollection();
strcollect2 = new StringCollection();
for (int i = 0; i < gvEmployeeTerritory.Rows.Count; i++)
{
CheckBox Chkbox = (CheckBox)gvEmployeeTerritory.Rows[i].Cells[0].FindControl("CbxDelete");
if (Chkbox.Checked)
{
Label empid = (Label)gvEmployeeTerritory.Rows[i].Cells[2].FindControl("LblEmployeeID");
Label trid = (Label)gvEmployeeTerritory.Rows[i].Cells[3].FindControl("LblTerritoryID");
strcollect1.Add(empid.Text);
strcollect2.Add(trid.Text);
count = count + 1;
}
}
if (count == 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.strcheckboxunchecked + "');", true);
}
else
{
Delete_Record(strcollect1, strcollect2);
Grid_Fill();
}
}
catch (Exception)
{
objconstmsg = new ConstantMessages();
Server.Transfer(objconstmsg.strerrorpage);//Exception
}
finally
{
strcollect1 = null;
strcollect2 = null;
objconstmsg = null;
}
}
/// <summary>
/// Event Name:BtnHADD_Click
/// Description:redirection to Add_Edit_Customer Page
/// Author:Bhumi
/// Created on:9/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnHADD_Click(object sender, EventArgs e)
{
objconstmsg = new ConstantMessages();
Response.Redirect(objconstmsg.strAdd_Edit_CustomerPage, false);
}
/// <summary>
/// EventName:gvEmployees_RowUpdating
/// Description:Update operation on Rows
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void gvEmployees_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
object id;
SqlCommand sqlcmd;
SqlDataAdapter sqladapter;
SqlDataReader sqlreader;
DataTable dtEmployees;
SqlConnection sqlconn;
StringBuilder strBrUpdatequery;
sqlconn = new SqlConnection();
//Get the data from gridview
id = gvEmployees.DataKeys[e.RowIndex].Value;
GridViewRow gvrow = gvEmployees.Rows[e.RowIndex] as GridViewRow;
TextBox TxtSalutation = gvrow.FindControl("TxtTitleOfCourtesy1") as TextBox;
TextBox Txtfname = gvrow.FindControl("TxtFirstName1") as TextBox;
TextBox Txtlname = gvrow.FindControl("TxtLastName1") as TextBox;
TextBox Txtbirthdy = gvrow.FindControl("TxtBirthDate1") as TextBox;
TextBox Txtcity = gvrow.FindControl("TxtCity1") as TextBox;
TextBox Txtcntry = gvrow.FindControl("TxtCountry1") as TextBox;
TextBox Txtcontactno = gvrow.FindControl("TxtHomePhone1") as TextBox;
try
{
//connectionstring
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn != null && sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
strBrUpdatequery = new StringBuilder("UPDATE Employees set ");
strBrUpdatequery.Append("TitleOfCourtesy=@TitleOfCourtesy,FirstName=@FirstName,LastName=@LastName,");
strBrUpdatequery.Append("BirthDate=@BirthDate,");
strBrUpdatequery.Append("City=@City,Country=@Country,HomePhone=@HomePhone");
strBrUpdatequery.Append(" WHERE EmployeeID=@EmployeeID;");
sqlcmd = new SqlCommand(strBrUpdatequery.ToString(), sqlconn);
//Update the data
sqlcmd.Parameters.AddWithValue("@EmployeeID", SqlDbType.Int).Value = id;
sqlcmd.Parameters.AddWithValue("@TitleOfCourtesy", SqlDbType.NVarChar).Value = TxtSalutation.Text;
sqlcmd.Parameters.AddWithValue("@FirstName", SqlDbType.NVarChar).Value = Txtfname.Text;
sqlcmd.Parameters.AddWithValue("@LastName", SqlDbType.NVarChar).Value = Txtlname.Text;
sqlcmd.Parameters.AddWithValue("@BirthDate", SqlDbType.DateTime).Value = Txtbirthdy.Text;
sqlcmd.Parameters.AddWithValue("@City", SqlDbType.NVarChar).Value = Txtcity.Text;
sqlcmd.Parameters.AddWithValue("@Country", SqlDbType.NVarChar).Value = Txtcntry.Text;
sqlcmd.Parameters.AddWithValue("@HomePhone", SqlDbType.NVarChar).Value = Txtcontactno.Text;
//Execute Query
sqladapter = new SqlDataAdapter(sqlcmd);
sqlcmd.ExecuteNonQuery();
sqlreader = sqlcmd.ExecuteReader();//Reading data
dtEmployees = new DataTable();
dtEmployees.Load(sqlreader);
gvEmployees.DataSource = dtEmployees;
gvEmployees.DataBind();
gvEmployees.EditIndex = -1;
Grid_Fill();//Display Gridview
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessage + "');", true);
}
catch (Exception ex)
{
Response.Write(ex.Message);//Exception
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlcmd = null;
sqladapter = null;
sqlreader = null;
dtEmployees = null;
strBrUpdatequery = null;
}
}
/// <summary>
/// Method Name:DdlHEmployeeID_SelectedIndexChanged
/// Description: According to selection of Dropdownlist Textboxes text set
/// Author:Bhumi
/// Created On:8/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DdlHEmployeeID_SelectedIndexChanged(object sender, EventArgs e)
{
//SQL objects
SqlConnection sqlconn;
SqlCommand sqlcmd;
SqlDataAdapter sqladapter;
SqlDataReader sqlreader;
sqlconn = new SqlConnection();
StringBuilder strbuider;
ConstantMessages objconstmsg;
try
{
if (DdlHEmployeeID.SelectedValue != "0")
{
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
//insert SQLquery in String builder
strbuider = new StringBuilder("SELECT EmployeeID,FirstName,LastName,HomePhone,City,PostalCode ");
strbuider.Append(" FROM");
strbuider.Append(" Employees");
strbuider.Append(" Where EmployeeID='" + DdlHEmployeeID.SelectedItem.Value.ToString() + "'");
sqlcmd = new SqlCommand(strbuider.ToString(), sqlconn);
sqladapter = new SqlDataAdapter(sqlcmd);
sqlreader = sqlcmd.ExecuteReader();//Reading data
while (sqlreader.Read())
{
//store the dat in textbox from sql reader
TxtHFirstName.Text = sqlreader[1].ToString();
TxtHLastName.Text = sqlreader[2].ToString();
TxtHHomePhone.Text = sqlreader[3].ToString();
TxtHCity.Text = sqlreader[4].ToString();
TxtHPostalCode.Text = sqlreader[5].ToString();
}
sqlreader = null;
}
else
{
TxtHFirstName.Text = "";
TxtHLastName.Text = "";
TxtHHomePhone.Text = "";
TxtHCity.Text = "";
TxtHPostalCode.Text = "";
}
}
catch (Exception)
{
objconstmsg = new ConstantMessages();
Server.Transfer(objconstmsg.strerrorpage);
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlconn = null;
sqlcmd = null;
sqladapter = null;
sqlreader = null;
strbuider = null;
}
}
/// <summary>
/// Event Name:ADD
/// Description:New Record Add in Datatable
/// Author:bhumi
/// Created On:10/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ADD(object sender, EventArgs e)
{
SqlCommand sqlcmd;
SqlCommand Check_TerritoryID;
SqlCommand Check_EmployeeID;
SqlCommand Check_EmployeeID_TerritoryID;
SqlDataAdapter sqladapter;
DataTable dtEmployeeTerritories;
SqlConnection sqlconn;
Control control;
StringBuilder strBrInsertquery;
StringBuilder strBrTerritoryID;
StringBuilder strBrEmployeeID;
StringBuilder strBrEmp_Terr_ID;
sqlconn = new SqlConnection();
int TerritoryID_Exist;
int EmployeeID_Exist;
int EmployeeID_TerritoryID_Exist;
//Get the data from gridview
if (gvEmployeeTerritory.FooterRow != null)
{
control = gvEmployeeTerritory.FooterRow;
}
else
{
control = gvEmployeeTerritory.Controls[0].Controls[0];
}
TextBox EmployeeID = control.FindControl("TxtEmployeeID1") as TextBox;
TextBox TerritoryID = control.FindControl("TxtTerritoryID1") as TextBox;
EmployeeID.Enabled = true;
TerritoryID.Enabled = true;
try
{
if ((!string.IsNullOrEmpty(EmployeeID.Text)) && (!string.IsNullOrEmpty(TerritoryID.Text)))
{
//connectionstring
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn != null && sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
//Existance checking of TerritoryID in Territory Table
strBrTerritoryID = new StringBuilder("SELECT TerritoryID ");
strBrTerritoryID.Append(" FROM Territories");
strBrTerritoryID.Append(" WHERE (TerritoryID='" + TerritoryID.Text + "')");
Check_TerritoryID = new SqlCommand(strBrTerritoryID.ToString(), sqlconn);
TerritoryID_Exist = Convert.ToInt32(Check_TerritoryID.ExecuteScalar());
//Existance Checking of EmployeeID in Employees Table
strBrEmployeeID = new StringBuilder("SELECT EmployeeID ");
strBrEmployeeID.Append(" FROM Employees");
strBrEmployeeID.Append(" WHERE (EmployeeID='" + EmployeeID.Text + "')");
Check_EmployeeID = new SqlCommand(strBrEmployeeID.ToString(), sqlconn);
EmployeeID_Exist = Convert.ToInt32(Check_EmployeeID.ExecuteScalar());
//Existance in Employeeterritories Table
strBrEmp_Terr_ID = new StringBuilder("SELECT EmployeeID,TerritoryID ");
strBrEmp_Terr_ID.Append(" FROM EmployeeTerritories");
strBrEmp_Terr_ID.Append(" WHERE EmployeeID='" + EmployeeID.Text + "' AND TerritoryID='" + TerritoryID.Text + "';");
Check_EmployeeID_TerritoryID = new SqlCommand(strBrEmp_Terr_ID.ToString(), sqlconn);
EmployeeID_TerritoryID_Exist = Convert.ToInt32(Check_EmployeeID_TerritoryID.ExecuteScalar());
if (EmployeeID_Exist <= 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageEmployeeID + "');", true);
}
else if (TerritoryID_Exist <= 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageTerritoryID + "');", true);
}
else if (EmployeeID_TerritoryID_Exist > 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageEmp_Terr_ID + "');", true);
}
else
{
strBrInsertquery = new StringBuilder("Insert INTO EmployeeTerritories ");
strBrInsertquery.Append("(EmployeeID,TerritoryID) ");
strBrInsertquery.Append("Values");
strBrInsertquery.Append("(@EmployeeID,@TerritoryID);");
sqlcmd = new SqlCommand(strBrInsertquery.ToString(), sqlconn);
//Update the data
sqlcmd.Parameters.AddWithValue("@EmployeeID", SqlDbType.Int).Value = EmployeeID.Text;
sqlcmd.Parameters.AddWithValue("@TerritoryID", SqlDbType.NVarChar).Value = TerritoryID.Text;
//Execute Query
sqladapter = new SqlDataAdapter(sqlcmd);
dtEmployeeTerritories = new DataTable();
sqladapter.Fill(dtEmployeeTerritories);
gvEmployeeTerritory.DataSource = dtEmployeeTerritories;
gvEmployeeTerritory.DataBind();
gvEmployeeTerritory.EditIndex = -1;
Grid_Fill();//Display Gridview
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertSuccessfulInsert + "');", true);
}
}
else
{
objconstmsg = new ConstantMessages();
LblMandatoryField.Visible = true;
}
}
catch (Exception ex)
{
Response.Write(ex.Message);//Exception
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlcmd = null;
sqladapter = null;
dtEmployeeTerritories = null;
dtEmployeeTerritories = null;
Check_TerritoryID = null;
Check_EmployeeID = null;
Check_EmployeeID_TerritoryID = null;
strBrInsertquery = null;
strBrTerritoryID = null;
strBrEmployeeID = null;
strBrEmp_Terr_ID = null;
}
}
/// <summary>
/// EventName:gvEmployeeTerritory_RowUpdating
/// Description:Update operation on Rows
/// Author:bhumi
/// Created On:10/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void gvEmployeeTerritory_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
object datakeyEmployeeID, datakeyTerritoryID;
SqlCommand sqlcmd;
SqlCommand Check_TerritoryID;
SqlCommand Check_EmployeeID;
SqlCommand Check_EmployeeID_TerritoryID;
SqlDataAdapter sqladapter;
DataTable dtEmployeeTerritories;
SqlConnection sqlconn;
StringBuilder strBrUpdatequery;
StringBuilder strBrTerritoryID;
StringBuilder strBrEmployeeID;
StringBuilder strBrEmp_Terr_ID;
//SqlCommand Check_TerritoryID;
int TerritoryID_Exist;
int EmployeeID_Exist;
int EmployeeID_TerritoryID_Exist;
sqlconn = new SqlConnection();
//Get the data from gridview
GridViewRow gvrow = gvEmployeeTerritory.Rows[e.RowIndex] as GridViewRow;
TextBox TxtEmployeeID = gvrow.FindControl("TxtEmployeeID") as TextBox;
TextBox TxtTerritoryID = gvrow.FindControl("TxtTerritoryID") as TextBox;
datakeyEmployeeID = gvEmployeeTerritory.DataKeys[e.RowIndex].Values["EmployeeID"];
datakeyTerritoryID = gvEmployeeTerritory.DataKeys[e.RowIndex].Values["TerritoryID"];
try
{
//connectionstring
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn != null && sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
//Existance checking of TerritoryID in Territory Table
strBrTerritoryID = new StringBuilder("SELECT TerritoryID ");
strBrTerritoryID.Append(" FROM Territories");
strBrTerritoryID.Append(" WHERE (TerritoryID='" + TxtTerritoryID.Text + "')");
Check_TerritoryID = new SqlCommand(strBrTerritoryID.ToString(), sqlconn);
TerritoryID_Exist = Convert.ToInt32(Check_TerritoryID.ExecuteScalar());
//Existance Checking of EmployeeID in Employees Table
strBrEmployeeID = new StringBuilder("SELECT EmployeeID ");
strBrEmployeeID.Append(" FROM Employees");
strBrEmployeeID.Append(" WHERE (EmployeeID='" + TxtEmployeeID.Text + "')");
Check_EmployeeID = new SqlCommand(strBrEmployeeID.ToString(), sqlconn);
EmployeeID_Exist = Convert.ToInt32(Check_EmployeeID.ExecuteScalar());
//Existance in Employeeterritories Table
strBrEmp_Terr_ID = new StringBuilder("SELECT EmployeeID,TerritoryID ");
strBrEmp_Terr_ID.Append(" FROM EmployeeTerritories");
strBrEmp_Terr_ID.Append(" WHERE EmployeeID='" + TxtEmployeeID.Text + "' AND TerritoryID='" + TxtTerritoryID.Text + "';");
Check_EmployeeID_TerritoryID = new SqlCommand(strBrEmp_Terr_ID.ToString(), sqlconn);
EmployeeID_TerritoryID_Exist = Convert.ToInt32(Check_EmployeeID_TerritoryID.ExecuteScalar());
if (EmployeeID_Exist <= 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageEmployeeID + "');", true);
}
else if (TerritoryID_Exist <= 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageTerritoryID + "');", true);
}
else if (EmployeeID_TerritoryID_Exist > 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageEmp_Terr_ID + "');", true);
}
else
{
strBrUpdatequery = new StringBuilder("UPDATE EmployeeTerritories set ");
strBrUpdatequery.Append("EmployeeID=@EmpID,TerritoryID=@TerID ");
strBrUpdatequery.Append(" WHERE EmployeeID='" + datakeyEmployeeID.ToString() + "' AND TerritoryID='" + datakeyTerritoryID.ToString() + "';");
sqlcmd = new SqlCommand(strBrUpdatequery.ToString(), sqlconn);
//Update the data
sqlcmd.Parameters.AddWithValue("@EmpID", SqlDbType.Int).Value = TxtEmployeeID.Text;
sqlcmd.Parameters.AddWithValue("@TerID", SqlDbType.NVarChar).Value = TxtTerritoryID.Text;
sqladapter = new SqlDataAdapter(sqlcmd);
dtEmployeeTerritories = new DataTable();
sqladapter.Fill(dtEmployeeTerritories);
gvEmployeeTerritory.DataSource = dtEmployeeTerritories;
gvEmployeeTerritory.DataBind();
gvEmployeeTerritory.EditIndex = -1;
Grid_Fill();//Display Gridview
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessage + "');", true);
}
}
catch (Exception)
{
objconstmsg = new ConstantMessages();
Server.Transfer(objconstmsg.strerrorpage);//Exception
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlcmd = null;
sqladapter = null;
dtEmployeeTerritories = null;
datakeyEmployeeID = null;
datakeyTerritoryID = null;
Check_TerritoryID = null;
Check_EmployeeID = null;
Check_EmployeeID_TerritoryID = null;
strBrUpdatequery = null;
strBrTerritoryID = null;
strBrEmployeeID = null;
strBrEmp_Terr_ID = null;
}
}
//Description:Whenever Error Occures in any page Execution redirect over here
protected void Page_Load(object sender, EventArgs e)
{
objconst = new ConstantMessages();
Response.Write(objconst.strerrormessage);//error message
}
/// <summary>
/// Event Name:Page_Load
/// Description:Fetch the querystring values
/// Author:Bhumi
/// Created On:9/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
TxtCustomerID.Focus();
SqlConnection sqlconn;
SqlCommand sqlcmd;
SqlDataAdapter sqladapter;
SqlDataReader sqlreader;
sqlconn = new SqlConnection();
StringBuilder strBrSelectQuery;
try
{
if (string.IsNullOrEmpty(Request.QueryString["CustomerID"]))
{
BtnHAdd.Visible = true;
}
else
{
customerid = Request.QueryString["CustomerID"];
TxtCustomerID.Text = customerid;
TxtCustomerID.Enabled = false;
BtnHUpdate.Visible = true;
BtnHCancel.Visible = true;
try
{
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
//insert SQLquery in String builder
strBrSelectQuery = new StringBuilder("SELECT CustomerID,CompanyName,ContactTitle,City,PostalCode,Phone,Fax");
strBrSelectQuery.Append(" FROM");
strBrSelectQuery.Append(" Customers");
strBrSelectQuery.Append(" Where CustomerID='" + customerid + "'");
sqlcmd = new SqlCommand(strBrSelectQuery.ToString(), sqlconn);
sqladapter = new SqlDataAdapter(sqlcmd);
sqlreader = sqlcmd.ExecuteReader();//Reading data
while (sqlreader.Read())
{
//store the dat in textbox from sql reader
TxtCompanyName.Text = sqlreader[1].ToString();
TxtContactTitle.Text = sqlreader[2].ToString();
TxtCity.Text = sqlreader[3].ToString();
TxtPostalCode.Text = sqlreader[4].ToString();
TxtPhoneNo.Text = sqlreader[5].ToString();
TxtFax.Text = sqlreader[6].ToString();
}
sqlreader = null;
}
catch (Exception)
{
Server.Transfer(objconst.strerrorpage);
}
}
}
catch (Exception)
{
objconst = new ConstantMessages();
Server.Transfer(objconst.strerrorpage);
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlconn = null;
sqlcmd = null;
sqladapter = null;
sqlreader = null;
strBrSelectQuery = null;
}
}
}
/// <summary>
/// Event Name:BtnHCancel_Click
/// Description:Cancel the updation
/// Author:Bhumi
/// Created on:9/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnHCancel_Click(object sender, EventArgs e)
{
objconst = new ConstantMessages();
Response.Redirect(objconst.strCustomerPage, false);
}
/// <summary>
/// Method Name:BtnHUpdate_Click
/// Description: Click Event Of Update Button
/// Author:Bhumi
/// Created On:8/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnHUpdate_Click(object sender, EventArgs e)
{
ConstantMessages objconstmsg;
Employee_Dataset dsEmployees;
//Sql Objects
SqlConnection sqlconn;
SqlCommand sqlcmd;
SqlDataAdapter sqladapter;
SqlTransaction sqltransact;
sqlconn = new SqlConnection();
StringBuilder strBrUpdateQuery;
if (DdlHEmployeeID.SelectedValue != "0")
{
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open(); //open the connection
}
sqltransact = sqlconn.BeginTransaction("Transaction"); //Begin Transaction
try
{
//insert SQLquery in String builder
strBrUpdateQuery = new StringBuilder("UPDATE Employees");
strBrUpdateQuery.Append(" SET ");
strBrUpdateQuery.Append(" FirstName='" + TxtHFirstName.Text + "',LastName='" + TxtHLastName.Text + "' ,HomePhone='" + TxtHHomePhone.Text + "' ,City='" + TxtHCity.Text + "' ,PostalCode='" + TxtHPostalCode.Text + "'");
strBrUpdateQuery.Append(" Where EmployeeID='" + DdlHEmployeeID.SelectedItem.Value.ToString() + "'");
sqlcmd = new SqlCommand(strBrUpdateQuery.ToString(), sqlconn, sqltransact);
sqladapter = new SqlDataAdapter(sqlcmd);
dsEmployees = new Employee_Dataset();
sqladapter.Fill(dsEmployees, "Employees");
sqladapter = new SqlDataAdapter(sqlcmd);
sqlcmd.ExecuteNonQuery();
sqltransact.Commit();//Commit
//Alert Message after data updation
DdlHEmployeeID.SelectedValue = "0";
TxtHFirstName.Text = "";
TxtHLastName.Text = "";
TxtHHomePhone.Text = "";
TxtHCity.Text = "";
TxtHPostalCode.Text = "";
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertSuccessfulInsert + "');", true);
}
catch (Exception ex)
{
Response.Write(ex.Message);
sqltransact.Rollback();//Roll back transaction
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlcmd = null;
sqladapter = null;
sqltransact = null;
strBrUpdateQuery = null;
}
}
else
{
//objconst = new ConstantMessages();
//Response.Write(objconst.strselectEmpId);
LblEmployeeID.Visible = true;
}
}
/// <summary>
/// Event Name:BtnInsert_Click
/// Description:Insert the data in EmployeeTerritories Table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnInsert_Click(object sender, EventArgs e)
{
//SQL CONNECTION
SqlConnection sqlconn;
ConstantMessages objconstmsg;
objconstmsg = new ConstantMessages();
SqlCommand sqlcmd;
SqlCommand Check_TerritoryID;
SqlCommand Check_EmployeeID_TerritoryID;
StringBuilder strBrTerritoryID;
StringBuilder strBrEmp_Terr_ID;
int TerritoryID_Exist;
int EmployeeID_TerritoryID_Exist;
sqlconn = new SqlConnection();
StringBuilder strBrInsertQuery;
try
{
if (Page.IsPostBack)
{
//connectionstring
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn != null && sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
//Existance checking of TerritoryID in Territory Table
strBrTerritoryID = new StringBuilder("SELECT TerritoryID ");
strBrTerritoryID.Append(" FROM Territories");
strBrTerritoryID.Append(" WHERE (TerritoryID='" + TxtTerritoryID.Text + "')");
Check_TerritoryID = new SqlCommand(strBrTerritoryID.ToString(), sqlconn);
TerritoryID_Exist = Convert.ToInt32(Check_TerritoryID.ExecuteScalar());
//Existance in Employeeterritories Table
strBrEmp_Terr_ID = new StringBuilder("SELECT EmployeeID,TerritoryID ");
strBrEmp_Terr_ID.Append(" FROM EmployeeTerritories");
strBrEmp_Terr_ID.Append(" WHERE EmployeeID='" + DdlEmployeeID.SelectedValue + "' AND TerritoryID='" + TxtTerritoryID.Text + "';");
Check_EmployeeID_TerritoryID = new SqlCommand(strBrEmp_Terr_ID.ToString(), sqlconn);
EmployeeID_TerritoryID_Exist = Convert.ToInt32(Check_EmployeeID_TerritoryID.ExecuteScalar());
if (TerritoryID_Exist <= 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageTerritoryID + "');", true);
}
else if (EmployeeID_TerritoryID_Exist > 0)
{
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertmessageEmp_Terr_ID + "');", true);
}
else
{
strBrInsertQuery = new StringBuilder("insert");
strBrInsertQuery.Append(" into EmployeeTerritories ");
strBrInsertQuery.Append(" (EmployeeID,TerritoryID)");
strBrInsertQuery.Append(" values");
strBrInsertQuery.Append(" ('" + DdlEmployeeID.SelectedItem.Value.ToString() + "','" + TxtTerritoryID.Text + "')");
sqlcmd = new SqlCommand(strBrInsertQuery.ToString(), sqlconn);
sqlcmd.ExecuteNonQuery();
objconstmsg = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconstmsg.stralertSuccessfulInsert + "');", true);
}
}
}
catch (Exception)
{
Response.Redirect(objconstmsg.strerrorpage);
}
finally
{
if (sqlconn.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 = "";
}
}
/// <summary>
/// Event Name:BtnHAdd_Click
/// Description:ADD the Customer Details
/// Author:Bhumi
/// Created on:9/6/2015
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnHAdd_Click(object sender, EventArgs e)
{
//SQL CONNECTION
SqlConnection sqlconn;
objconst = new ConstantMessages();
SqlCommand sqlcmd;
SqlCommand Check_CustomerID;
sqlconn = new SqlConnection();
StringBuilder strBrInsertQuery;
StringBuilder strBrCustomerID;
object CustomerID_Exist;
try
{
if (Page.IsPostBack)
{
//connectionstring
sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
if (sqlconn != null && sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();//open the connection
}
strBrCustomerID = new StringBuilder("SELECT CustomerID ");
strBrCustomerID.Append(" FROM Customers");
strBrCustomerID.Append(" WHERE (CustomerID='" + TxtCustomerID.Text + "')");
Check_CustomerID = new SqlCommand(strBrCustomerID.ToString(), sqlconn);
CustomerID_Exist = Check_CustomerID.ExecuteScalar();
if (CustomerID_Exist != null)
{
objconst = new ConstantMessages();
ClientScript.RegisterStartupScript(this.GetType(), "msgbox", "alert('" + objconst.stralertCustomerID + "');", true);
}
else
{
strBrInsertQuery = new StringBuilder("insert");
strBrInsertQuery.Append(" into Customers ");
strBrInsertQuery.Append(" (CustomerID,CompanyName,ContactTitle,City,PostalCode,Phone,Fax)");
strBrInsertQuery.Append(" values");
strBrInsertQuery.Append(" ('" + TxtCustomerID.Text + "','" + TxtCompanyName.Text + "','" + TxtContactTitle.Text + "','" + TxtCity.Text + "','" + TxtPostalCode.Text + "','" + TxtPhoneNo.Text + "','" + TxtFax.Text + "')");
sqlcmd = new SqlCommand(strBrInsertQuery.ToString(), sqlconn);
sqlcmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{
Response.Redirect(objconst.strerrorpage);
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlconn = null;
sqlcmd = null;
strBrInsertQuery = null;
objconst = new ConstantMessages();
Response.Redirect(objconst.strCustomerPage);
}
}
