Commonlogic objCl = new Commonlogic(); //ObjCl is object of commonlogic #region Page_Load /// <summary> /// Name:Page_Load /// Description:"Add code to fetch data from the Employees table and show it on the page in a datagridview." /// Author:Monal Shah /// Created Date:2010/09/20 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Page_Load(object sender, EventArgs e) { try { if (!IsPostBack) { //string strTableName = "Employees"; // objDTable = objCl.SelectQuery(strTableName); objCl.GetSqlStmt = "SELECT EmployeeID," + "TitleOfCourtesy+' '+FirstName+' '+LastName As Name," + "Title,Convert(varchar,BirthDate,111)As BirthDate," + "Convert(varchar,HireDate,111) As HireDate," + "Address, City, Region, PostalCode, Country, HomePhone," + "Extension, Photo, ReportsTo, Notes," + "PhotoPath FROM Employees"; objDTable = objCl.DataTables(); this.GrdEmployee.DataSource = objDTable; this.GrdEmployee.DataBind(); //Binding grid } } catch (Exception exException) { Response.Redirect("Error.aspx"); // Response.Write("ERROR:" + exException.Message); } finally { objDTable = null; objCl = null; } }
/// <summary> /// Name:GrdEmpTerritories_RowDataBound /// Description:Bind the listbox /// Author:monal shah /// Created Date:2010/10/01 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void GrdEmpTerritories_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { string strEmployeeID = GrdEmpTerritories.DataKeys[e.Row.RowIndex].Values[0].ToString(); ListBox LstListBox = (ListBox)e.Row.FindControl("LstTerritoryDescription"); DataTable objDt = new DataTable(); try { objQuery = new StringBuilder(); objQuery.Append("Select "); objQuery.Append("TerritoryDescription "); objQuery.Append("From "); objQuery.Append("Territories "); objQuery.Append("where "); objQuery.Append("TerritoryID "); objQuery.Append("IN "); objQuery.Append("("); objQuery.Append("Select "); objQuery.Append("TerritoryID "); objQuery.Append("From "); objQuery.Append("EmployeeTerritories "); objQuery.Append("where "); objQuery.Append("EmployeeID"); objQuery.Append("="); objQuery.Append("'"); objQuery.Append(strEmployeeID); objQuery.Append("'"); objQuery.Append(")"); string strQuery = objQuery.ToString(); SqlDataAdapter adpapter = new SqlDataAdapter(strQuery, Commonlogic.GetConnectionString); adpapter.Fill(objDt); if (objDt.Rows.Count == 0) { LblErrorMessage.Visible = true; LblErrorMessage.Text = objConstant.NoTableRecordFound;// Does Not Contain Any Row"; LnkAddNewUser.Visible = false; } else { LstListBox.DataSource = objDt; LstListBox.DataBind(); } } catch (Exception) { Response.Redirect("Error.aspx"); } finally { objDTable = null; objDt = null; objCl = null; objQuery = null; } } }
/// <summary> /// Name:Page_Load /// Description:"Add code to fetch data from the Employees table and show it on the page in a datagridview.and store the typed dataset into session." /// Author:Monal Shah /// Created Date:2010/09/21 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Page_Load(object sender, EventArgs e) { try { if (!IsPostBack) { string strTable = "Employees"; objSelect = new StringBuilder(); objSelect.Append("SELECT "); objSelect.Append("EmployeeID"); objSelect.Append(","); objSelect.Append("TitleOfCourtesy+' '+FirstName+' '+LastName As Name"); objSelect.Append(","); objSelect.Append("Title"); objSelect.Append(","); objSelect.Append("Convert(varchar,BirthDate,111) As BirthDate"); objSelect.Append(","); objSelect.Append("Convert(varchar,HireDate,111) As HireDate"); objSelect.Append(","); objSelect.Append("Address"); objSelect.Append(","); objSelect.Append("City"); objSelect.Append(","); objSelect.Append("Region"); objSelect.Append(","); objSelect.Append("PostalCode"); objSelect.Append(","); objSelect.Append("Country"); objSelect.Append(","); objSelect.Append("HomePhone"); objSelect.Append(","); objSelect.Append("Extension"); objSelect.Append(","); objSelect.Append("ReportsTo"); objSelect.Append(","); objSelect.Append("Notes"); objSelect.Append(","); objSelect.Append("PhotoPath "); objSelect.Append("From Employees"); string strQuery = objSelect.ToString(); objEmpDataSet = objCl.EmpDataSet(strQuery, strTable);//here EmpDataSet method is called with the table name SessionMethod.CurrentSession.StoreTypedDataSet = objEmpDataSet; // Session["DataSetSession"] = objEmpDataSet;//stores the dataset into session this.GrdEmployee.DataSource = objEmpDataSet; this.GrdEmployee.DataBind();//here binding data to gridview } } catch (Exception) { Response.Redirect("Error.aspx", false); // Response.Write("ERROR:" + exException.Message); } finally { objEmpDataSet = null; objCl = null; } }
/// <summary> ///Name:btnSubmit_Click ///Description:In below code update the table row. ///Author:monal shah ///Created Date:2010/09/23 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnSubmit_Click(object sender, EventArgs e) { objCon.ConnectionString = Commonlogic.GetConnectionString; try { int intIdValue = SessionMethod.CurrentSession.EmployeeID; //get the session value if (intIdValue != 0) { string strQuery = "Select * from Employees"; objCmd = new SqlCommand(); if (objCon.State == ConnectionState.Closed) { objCon.Open(); } objTransaction = objCon.BeginTransaction(IsolationLevel.ReadCommitted); objCmd.Connection = objCon; objCmd.CommandType = CommandType.Text; objCmd.CommandText = strQuery; objCmd.Transaction = objTransaction; objAdp = new SqlDataAdapter(objCmd); objAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey; objSqlBuilder = new SqlCommandBuilder(objAdp); objAdp.Fill(objEmpDataSet, "Employees"); DataRow objRow = objEmpDataSet.Tables["Employees"].Rows.Find(intIdValue); objRow["LastName"] = TxtLname.Text; objRow["FirstName"] = TxtFname.Text; objRow["Title"] = TxtTitle.Text; objRow["HireDate"] = System.Convert.ToDateTime(TxtHDate.Text); objRow["Address"] = TxtAddress.Text; objRow["City"] = TxtCity.Text; objRow["Region"] = TxtRegion.Text; objRow["PostalCode"] = TxtPostalCode.Text; objRow["Country"] = TxtCountry.Text; objRow["HomePhone"] = TxtHomePhone.Text; objRow["Extension"] = TxtExtension.Text; objRow["Notes"] = TxtNotes.Text; objRow["ReportsTo"] = Convert.ToInt32(TxtReportsTo.Text); objAdp.Update(objEmpDataSet, "Employees"); objTransaction.Commit(); Response.Redirect("TransactionUpdate.aspx", false); } } catch (Exception) { objTransaction.Rollback(); Response.Redirect("Error.aspx", false); } finally { objTransaction = null; objCl = null; objCmd.Dispose(); objAdp.Dispose(); objDTable = null; objCon.Close(); } }
/// <summary> /// Name:Page_Load /// Description:Bind the grid with emplyee table using typed dataset and perform DML operatio(Insert,Update,Delete) /// Author:monal shah /// Created Date:2010/09/23 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Page_Load(object sender, EventArgs e) { try { if (!IsPostBack) { //objCl.GetSqlStmt = str; //objDTable = objCl.DataTables(); string strTable = "Employees"; objSelect = new StringBuilder(); objSelect.Append("SELECT "); objSelect.Append("EmployeeID"); objSelect.Append(","); objSelect.Append("TitleOfCourtesy+' '+FirstName+' '+LastName As Name"); objSelect.Append(","); objSelect.Append("Title"); objSelect.Append(","); objSelect.Append("Convert(varchar,BirthDate,111) As BirthDate"); objSelect.Append(","); objSelect.Append("Convert(varchar,HireDate,111) As HireDate"); objSelect.Append(","); objSelect.Append("Address"); objSelect.Append(","); objSelect.Append("City"); objSelect.Append(","); objSelect.Append("Region"); objSelect.Append(","); objSelect.Append("PostalCode"); objSelect.Append(","); objSelect.Append("Country"); objSelect.Append(","); objSelect.Append("HomePhone"); objSelect.Append(","); objSelect.Append("Extension"); objSelect.Append(","); objSelect.Append("ReportsTo"); objSelect.Append(","); objSelect.Append("Notes"); objSelect.Append(","); objSelect.Append("PhotoPath "); objSelect.Append("From Employees"); string strQuery = objSelect.ToString(); objEmpDataSet = objCl.EmpDataSet(strQuery, strTable); this.GrdEmployee.DataSource = objEmpDataSet.Employees; this.GrdEmployee.DataBind();//here binding data to gridview } } catch (SqlException) { Response.Redirect("Error.aspx", false); // Response.Write("ERROR:" + exException.Message); } finally { objEmpDataSet = null; objCl = null; } }
/// <summary> /// Name:Bindgrid /// Description:Bind Grid with employee details /// Author:monal shah /// Created Date:2010/09/23 /// </summary> public void BindGrid() { try { string strTable = "Employees"; objSelect = new StringBuilder(); objSelect.Append("SELECT "); objSelect.Append("EmployeeID"); objSelect.Append(","); objSelect.Append("TitleOfCourtesy+' '+FirstName+' '+LastName As Name"); objSelect.Append(","); objSelect.Append("Title"); objSelect.Append(","); objSelect.Append("Convert(varchar,BirthDate,111) As BirthDate"); objSelect.Append(","); objSelect.Append("Convert(varchar,HireDate,111) As HireDate"); objSelect.Append(","); objSelect.Append("Address"); objSelect.Append(","); objSelect.Append("City"); objSelect.Append(","); objSelect.Append("Region"); objSelect.Append(","); objSelect.Append("PostalCode"); objSelect.Append(","); objSelect.Append("Country"); objSelect.Append(","); objSelect.Append("HomePhone"); objSelect.Append(","); objSelect.Append("Extension"); objSelect.Append(","); objSelect.Append("ReportsTo"); objSelect.Append(","); objSelect.Append("Notes"); objSelect.Append(","); objSelect.Append("PhotoPath "); objSelect.Append("From Employees"); string strQuery = objSelect.ToString(); ////"Title,Convert(varchar,BirthDate,111) As BirthDate," + ////"Convert(varchar,HireDate,111) As HireDate," + ////"Address, City, Region, PostalCode, Country," + ////"HomePhone, Extension, ReportsTo, Notes," + ////"PhotoPath FROM Employees"; objEmpDataSet = objCl.EmpDataSet(strQuery, strTable); //here EmpDataSet method is called with the table name this.GrdEmployee.DataSource = objEmpDataSet; this.GrdEmployee.DataBind(); //here binding data to gridview } catch (Exception) { Response.Redirect("Error.aspx", false); } finally { objEmpDataSet = null; objCl = null; } }
/// <summary> /// Name:Bindgrid /// Description:Bind Grid with Customer details /// Author:monal shah /// Created Date:2010/09/23 /// </summary> public void BindGrid() { try { objSelect = new StringBuilder(); objSelect.Append("SELECT "); objSelect.Append("CustomerID"); objSelect.Append(","); objSelect.Append("CompanyName"); objSelect.Append(","); objSelect.Append("ContactName"); objSelect.Append(","); objSelect.Append("ContactTitle"); objSelect.Append(","); objSelect.Append("Address"); objSelect.Append(","); objSelect.Append("City"); objSelect.Append(","); objSelect.Append("Region"); objSelect.Append(","); objSelect.Append("PostalCode"); objSelect.Append(","); objSelect.Append("Country"); objSelect.Append(","); objSelect.Append("Phone"); objSelect.Append(","); objSelect.Append("Fax "); objSelect.Append("From Customers"); objCl.GetSqlStmt = objSelect.ToString(); objDt = objCl.DataTables();//here EmpDataSet method is called with the table name if (objDt.Rows.Count == 0) { LblErrorMessage.Visible = true; LblErrorMessage.Text = objConstant.NoTableRecordFound;// "Datatable Does Not Contain Any Row"; LnkAddNewUser.Visible = false; //Response.Write(LblErrorMessage.Text); } else { this.GrdCustomer.DataSource = objDt; this.GrdCustomer.DataBind();//here binding data to gridview } } catch (Exception) { Response.Redirect("Error.aspx", false); } finally { objDt = null; objCl = null; } }
/// <summary> /// Name:DeleteEmployeeTerritories /// Description:in this method fire the delete query using stringbuilder /// Author:Monal shah /// created date:2010/10/06 /// </summary> /// <returns>bool</returns> public void DeleteMultipleRecords(StringCollection idCollection) { //Create sql Connection and Sql Command string IDs = string.Empty; foreach (string id in idCollection) { IDs += id.ToString() + ","; } try { string strIDs = IDs.Substring(0, IDs.LastIndexOf(",")); sbMultipleDelete = new StringBuilder(); sbMultipleDelete.Append("DELETE "); sbMultipleDelete.Append("FROM "); sbMultipleDelete.Append("Employees "); sbMultipleDelete.Append("WHERE "); sbMultipleDelete.Append("EmployeeID "); sbMultipleDelete.Append("IN "); sbMultipleDelete.Append("("); sbMultipleDelete.Append(strIDs); sbMultipleDelete.Append(")"); sbMultipleDelete.Append("DELETE "); sbMultipleDelete.Append("FROM "); sbMultipleDelete.Append("EmployeeTerritories "); sbMultipleDelete.Append("WHERE "); sbMultipleDelete.Append("EmployeeID "); sbMultipleDelete.Append("IN "); sbMultipleDelete.Append("("); sbMultipleDelete.Append(strIDs); sbMultipleDelete.Append(")"); objcl.GetSqlStmt = sbMultipleDelete.ToString(); objcl.TransactionExecuteNonQuery(); //call ExecuteNonQuery method } catch (SqlException ex) { string errorMsg = "Error in Deletion"; errorMsg += ex.Message; } finally { objcl = null; sbMultipleDelete = null; } }
/// <summary> /// Name:BindListBox /// Description:"Add a gridview control to the page and bind it to the EmployeeTerritories table" /// Author:Monal Shah /// Created Date:2010/10/04 /// </summary> public void BindListBox() { ValueOfListBox(); try { int intIdValue = SessionMethod.CurrentSession.EmployeeID; //fetch id value from session //if id valueis '0'then btnSubmit.Text = "Insert" else btnSubmit.Text = "Update"; //and setch data from table and display in textbox objSelect = new StringBuilder(); objSelect.Append("Select "); objSelect.Append("DISTINCT "); objSelect.Append("TerritoryDescription "); objSelect.Append("From "); objSelect.Append("Territories"); objCl.GetSqlStmt = objSelect.ToString(); objDTable = objCl.DataTables(); if (objDTable.Rows.Count == 0) { LblErrorMessage.Text = objConstant.NoTableRecordFound; // "Datatable Does Not Contain Any Row"; } else { LblErrorMessage.Visible = false; LstListBox.DataSource = objDTable; LstListBox.DataBind(); for (int i = 0; i < LstListBox.Items.Count; i++) { for (int j = 0; j < lstValue.Items.Count; j++) { if (LstListBox.Items[i].Equals(lstValue.Items[j])) { LstListBox.Items[i].Selected = true; } } } } } catch (Exception) { Response.Redirect("Error.aspx"); } finally { objSelect = null; objDTable = null; objCl = null; } }
/// <summary> /// Name:Page_Load /// Description:"Add a gridview control to the page and bind it to the EmployeeTerritories table of the typed dataset" /// Author:Monal Shah /// Created Date:2010/09/21 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Page_Load(object sender, EventArgs e) { try { if (!IsPostBack) { int intIdValue = SessionMethod.CurrentSession.EmployeeID; //if session is not '0' then clicked linkbutton's record will be displayed //else all the employeeterritories record will be displayed. if (intIdValue != 0) { objDTable = objCl.SelectNWhereQuery("EmployeeTerritories", "EmployeeID", intIdValue); this.GrdEmpTerritories.DataSource = objDTable; this.GrdEmpTerritories.DataBind();//here binding data to gridview } else { string strTable = "EmployeeTerritories"; objEmpTerroristDataSet = objCl.EmpDataSet(strTable); //here EmpDataSet method is called with the table name this.GrdEmpTerritories.DataSource = objEmpTerroristDataSet.EmployeeTerritories; this.GrdEmpTerritories.DataBind(); //here binding data to gridview } } } catch (Exception) { Response.Redirect("Error.aspx", false); // Response.Write("ERROR:" + exException.Message); } finally { objEmpTerroristDataSet = null; objCl = null; objDTable = null; } }
Commonlogic objCl = new Commonlogic(); //ObjCl is object of commonlogic #region Page_Load /// <summary> /// Name:Page_Load /// Description:"Add code to fetch data from the Territories table and show it on the page in a datagridview." /// Author:Monal Shah /// Created Date:2010/09/21 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { try { objCl.GetSqlStmt = "Select * From Territories"; objDTable = objCl.DataTables();//through the common logic object datatable function is call and assign vale to the datatable object //string strTableName = "Territories"; //objDTable = objCl.DataTables();// SelectQuery(strTableName);//SelectQuery Method is called with tableName this.GrdTerritories.DataSource = objDTable; this.GrdTerritories.DataBind();//binding grid } catch (Exception) { Response.Redirect("Error.aspx", false); } finally { objDTable = null; objCl = null; } } }
/// <summary> /// Name:Page_Load /// Description:Here in below code fetch data from employee territories table and disaplay in textbox. /// Author:Monal Shah /// Created Date:2010/10/4 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { try { int intIdValue = SessionMethod.CurrentSession.EmployeeID; //fetch id value from session //if id valueis '0'then btnSubmit.Text = "Insert" else btnSubmit.Text = "Update"; //and setch data from table and display in textbox if (intIdValue == 0) { btnSubmit.Text = "Insert"; BindListBox(); } else { btnSubmit.Text = "Update"; objSelect = new StringBuilder(); objSelect.Append("SELECT "); objSelect.Append("EMP.EmployeeID"); objSelect.Append(","); objSelect.Append("EMP.TitleOfCourtesy"); objSelect.Append(","); objSelect.Append("EMP.FirstName"); objSelect.Append(","); objSelect.Append("EMP.LastName"); // As Name"); objSelect.Append(","); objSelect.Append("EMP.Title"); objSelect.Append(","); objSelect.Append("Convert(varchar,EMP.BirthDate,111) As BirthDate"); objSelect.Append(","); objSelect.Append("Convert(varchar,EMP.HireDate,111) As HireDate"); objSelect.Append(","); objSelect.Append("EMP.Address"); objSelect.Append(","); objSelect.Append("EMP.City"); objSelect.Append(","); objSelect.Append("EMP.Region"); objSelect.Append(","); objSelect.Append("EMP.PostalCode"); objSelect.Append(","); objSelect.Append("EMP.Country"); objSelect.Append(","); objSelect.Append("EMP.HomePhone"); objSelect.Append(","); objSelect.Append("EMP.Extension"); objSelect.Append(","); objSelect.Append("EMP.ReportsTo"); objSelect.Append(","); objSelect.Append("EMP.Notes"); objSelect.Append(","); objSelect.Append("EMP.PhotoPath "); objSelect.Append("From Employees EMP"); objSelect.Append(" Where "); objSelect.Append("EMP.EmployeeId"); objSelect.Append("="); objSelect.Append("'"); objSelect.Append(intIdValue); objSelect.Append("'"); objCl.GetSqlStmt = objSelect.ToString(); objDTable = objCl.DataTables(); if (objDTable.Rows.Count > 0) { DdlSalutation.Enabled = false; TxtBdate.Enabled = false; BtnCal1.Enabled = false; TxtLname.Text = Convert.ToString(objDTable.Rows[0]["LastName"]); TxtFname.Text = Convert.ToString(objDTable.Rows[0]["FirstName"]); TxtTitle.Text = Convert.ToString(objDTable.Rows[0]["Title"]); DdlSalutation.SelectedItem.Text = Convert.ToString(objDTable.Rows[0]["TitleOfCourtesy"]); TxtBdate.Text = Convert.ToString(objDTable.Rows[0]["BirthDate"]); TxtHDate.Text = Convert.ToString(objDTable.Rows[0]["HireDate"]); TxtAddress.Text = Convert.ToString(objDTable.Rows[0]["Address"]); TxtCity.Text = Convert.ToString(objDTable.Rows[0]["City"]); TxtRegion.Text = Convert.ToString(objDTable.Rows[0]["Region"]); TxtPostalCode.Text = Convert.ToString(objDTable.Rows[0]["PostalCode"]); TxtCountry.Text = Convert.ToString(objDTable.Rows[0]["Country"]); TxtHomePhone.Text = Convert.ToString(objDTable.Rows[0]["HomePhone"]); TxtExtension.Text = Convert.ToString(objDTable.Rows[0]["Extension"]); TxtNotes.Text = Convert.ToString(objDTable.Rows[0]["Notes"]); TxtReportsTo.Text = Convert.ToString(objDTable.Rows[0]["ReportsTo"]); BindListBox(); } else { LblErrorMessage.Text = objConstant.NoTableRecordFound; // "Datatable Does Not Contain Any Row"; } } } catch (Exception) { Response.Redirect("Error.aspx", false); } finally { objCl = null; objDTable = null; objSelect = null; } } }
/// <summary> /// Name:btnSubmit_Click /// Description:Here in below code update row and also insert row /// Author:Monal Shah /// Created Date:2010/10/4 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnSubmit_Click(object sender, EventArgs e) { int intIdValue = SessionMethod.CurrentSession.EmployeeID; try { if (intIdValue == 0) { objEmpTerritories.LName = TxtLname.Text; objEmpTerritories.FName = TxtFname.Text; objEmpTerritories.Title = TxtTitle.Text; if (DdlSalutation.SelectedIndex != 0) { objEmpTerritories.Salutation = DdlSalutation.SelectedItem.Value; } else { objEmpTerritories.Salutation = null; } objEmpTerritories.BirthDate = TxtBdate.Text; objEmpTerritories.HireDate = TxtHDate.Text; objEmpTerritories.Address = TxtAddress.Text; objEmpTerritories.City = TxtCity.Text; objEmpTerritories.Region = TxtRegion.Text; objEmpTerritories.PostalCode = TxtPostalCode.Text; objEmpTerritories.Country = TxtCountry.Text; objEmpTerritories.Homephone = TxtHomePhone.Text; objEmpTerritories.Extension = TxtExtension.Text; objEmpTerritories.Notes = TxtNotes.Text; if (TxtReportsTo.Text != "") { objEmpTerritories.ReportsTo = Convert.ToInt32(TxtReportsTo.Text); } else { objEmpTerritories.ReportsTo = Convert.ToInt32(null); } objEmpTerritories.InsertEmployeeTerritories().ToString(); for (int i = 0; i < LstListBox.Items.Count; i++)// in LstListBox.Items) { if (LstListBox.Items[i].Selected) { sbInsertValueEmployee = new StringBuilder(); sbInsertValueEmployee.Append("Select "); sbInsertValueEmployee.Append("TOP "); sbInsertValueEmployee.Append("1"); sbInsertValueEmployee.Append(" EmployeeID "); sbInsertValueEmployee.Append("From "); sbInsertValueEmployee.Append("Employees"); sbInsertValueEmployee.Append(" Order By"); sbInsertValueEmployee.Append(" EmployeeID "); sbInsertValueEmployee.Append("DESC "); objCl.GetSqlStmt = sbInsertValueEmployee.ToString(); objDTable = objCl.DataTables(); //string strquery = sbInsertValueEmployee.ToString(); //SqlDataAdapter adp = new SqlDataAdapter(strquery, Commonlogic.GetConnectionString); //adp.Fill(dt); if (objDTable.Rows.Count > 0) { objEmpTerritories.EmpID = Convert.ToInt32(objDTable.Rows[0]["EmployeeID"]); } else { LblErrorMessage.Text = objConstant.NoTableRecordFound;// "Datatable Does Not Contain Any Row"; } } } ListBoxValues(objEmpTerritories.EmpID); Response.Redirect("EmployeeTerritoriesGrid.aspx", false); } else { objEmpTerritories.EmpID = intIdValue; objEmpTerritories.LName = TxtLname.Text; objEmpTerritories.FName = TxtFname.Text; objEmpTerritories.Title = TxtTitle.Text; objEmpTerritories.Salutation = DdlSalutation.SelectedItem.Value; objEmpTerritories.HireDate = TxtHDate.Text; objEmpTerritories.Address = TxtAddress.Text; objEmpTerritories.City = TxtCity.Text; objEmpTerritories.Region = TxtRegion.Text; objEmpTerritories.PostalCode = TxtPostalCode.Text; objEmpTerritories.Country = TxtCountry.Text; objEmpTerritories.Homephone = TxtHomePhone.Text; objEmpTerritories.Extension = TxtExtension.Text; objEmpTerritories.Notes = TxtNotes.Text; objEmpTerritories.ReportsTo = Convert.ToInt32(TxtReportsTo.Text); sbDeleteValue = new StringBuilder(); sbDeleteValue.Append("DELETE "); sbDeleteValue.Append("FROM "); sbDeleteValue.Append("EmployeeTerritories "); sbDeleteValue.Append("WHERE "); sbDeleteValue.Append("EmployeeID"); sbDeleteValue.Append("="); sbDeleteValue.Append("'"); sbDeleteValue.Append(intIdValue); sbDeleteValue.Append("'"); objCl.GetSqlStmt = sbDeleteValue.ToString();//"Delete from EmployeeTerritories where EmployeeID='" + intIdValue + "'"; objCl.TransactionExecuteNonQuery(); ListBoxValues(intIdValue); objEmpTerritories.UpdateEmployeeTerritories().ToString(); Response.Redirect("EmployeeTerritoriesGrid.aspx", false); } } catch (Exception) { Response.Redirect("Error.aspx", false); } finally { objCl = null; sbInsertValue = null; sbInsertValueEmployee = null; aryLstBoxValue = null; objEmpTerritories = null; } }
/// <summary> /// Name:BindGrid /// Description:"Add a gridview control to the page and bind it to the EmployeeTerritories table of the typed dataset" /// Author:Monal Shah /// Created Date:2010/10/01 /// </summary> public void BindGrid() { try { objSelect = new StringBuilder(); objSelect.Append("SELECT "); objSelect.Append("EmployeeID"); objSelect.Append(","); objSelect.Append("TitleOfCourtesy+' '+FirstName+' '+LastName As Name"); objSelect.Append(","); objSelect.Append("Title"); objSelect.Append(","); objSelect.Append("Convert(varchar,BirthDate,111) As BirthDate"); objSelect.Append(","); objSelect.Append("Convert(varchar,HireDate,111) As HireDate"); objSelect.Append(","); objSelect.Append("Address"); objSelect.Append(","); objSelect.Append("City"); objSelect.Append(","); objSelect.Append("Region"); objSelect.Append(","); objSelect.Append("PostalCode"); objSelect.Append(","); objSelect.Append("Country"); objSelect.Append(","); objSelect.Append("HomePhone"); objSelect.Append(","); objSelect.Append("Extension"); objSelect.Append(","); objSelect.Append("ReportsTo"); objSelect.Append(","); objSelect.Append("Notes"); objSelect.Append(","); objSelect.Append("PhotoPath "); objSelect.Append("From Employees"); objCl.GetSqlStmt = objSelect.ToString();// "SELECT EmployeeID, TitleOfCourtesy + ' ' + FirstName + ' ' + LastName AS Name, Title,BirthDate,HireDate,Address,City,Region,PostalCode,Country,HomePhone,Extension,Notes,ReportsTo,PhotoPath from Employees"; //Territories.TerritoryDescription FROM Employees INNER JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID INNER JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID where Employees.EmployeeID='1'"; objDTable = objCl.DataTables(); if (objDTable.Rows.Count == 0) { LblErrorMessage.Visible = true; LblErrorMessage.Text = objConstant.NoTableRecordFound;// "Datatable Does Not Contain Any Row"; LnkAddNewUser.Visible = false; } else { this.GrdEmpTerritories.DataSource = objDTable; this.GrdEmpTerritories.DataBind();//here binding data to gridview } } catch (Exception) { Response.Redirect("Error.aspx"); } finally { objSelect = null; objDTable = null; objCl = null; } }
/// <summary> /// Name:Bindgrid /// Description:Bind Grid with employee details /// Author:monal shah /// Created Date:2010/09/23 /// </summary> public void BindGrid() { try { string strTable = "Employees"; objSelect = new StringBuilder(); objSelect.Append("SELECT "); objSelect.Append("EmployeeID"); objSelect.Append(","); objSelect.Append("TitleOfCourtesy+' '+FirstName+' '+LastName As Name"); objSelect.Append(","); objSelect.Append("Title"); objSelect.Append(","); objSelect.Append("Convert(varchar,BirthDate,111) As BirthDate"); objSelect.Append(","); objSelect.Append("Convert(varchar,HireDate,111) As HireDate"); objSelect.Append(","); objSelect.Append("Address"); objSelect.Append(","); objSelect.Append("City"); objSelect.Append(","); objSelect.Append("Region"); objSelect.Append(","); objSelect.Append("PostalCode"); objSelect.Append(","); objSelect.Append("Country"); objSelect.Append(","); objSelect.Append("HomePhone"); objSelect.Append(","); objSelect.Append("Extension"); objSelect.Append(","); objSelect.Append("ReportsTo"); objSelect.Append(","); objSelect.Append("Notes"); objSelect.Append(","); objSelect.Append("PhotoPath "); objSelect.Append("From Employees"); string strQuery = objSelect.ToString(); objEmpDataSet = objCl.EmpDataSet(strQuery, strTable);//here EmpDataSet method is called with the table name if (objEmpDataSet.Tables[0].Rows.Count > 0) { this.GrdEmployee.DataSource = objEmpDataSet; this.GrdEmployee.DataBind();//here binding data to gridview } else { LblErrorMessage.Visible = true; LblErrorMessage.Text = objConstant.NoTableRecordFound;// "Datatable Does Not Contain Any Row"; LnkAddNewUser.Visible = false; } } catch (Exception) { Response.Redirect("Error.aspx", false); } finally { objEmpDataSet = null; objCl = null; } }