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;
}
}
