public void ShowData()
{
SqlDataAdapter adapDA; DataSetEmp ds; StringBuilder strBl; string strcons; // declaration of object
Common connObj = new Common();
try
{
connObj.OpenConnection(); //open connection from common class object
ds = new DataSetEmp(); //create object of typed data set
DataSetEmp ds1 = (DataSetEmp)Session[Constant.strBuil6]; // reterive session
strBl = new StringBuilder(); //Create object of string builder class
strBl.Append(Constant.strBuil1); //Append string
strBl.Append(Constant.strBuil2); //Append string
strBl.Append(Constant.strBuil3);
strBl.Append(Constant.strBuil4);
strcons = strBl.ToString(); //store all string into a string
adapDA = new SqlDataAdapter(strcons, connObj.connC); //pass connection and query into data adapter
adapDA.Fill(ds, Constant.strBuil5); //fill dataset from employee table
gvEmpTer.DataSource = ds.Employees;
gvEmpTer.DataBind(); //Bind data
}
catch (Exception ex)
{
Response.Redirect(Constant.strCatcExceptn); //throw exception on error page
}
finally //reallocate memory
{
connObj.CloseConnection();
connObj = null;
adapDA = null; strcons = null;
}
}
public void BindGrid()
{
SqlDataAdapter adapDA; DataSetEmp ds; StringBuilder strBl; string strcons; // declaration of object
connObj = new Common();
try
{
connObj.OpenConnection(); //open connection from common class object
ds = new DataSetEmp(); //create object of typed data set
DataSetEmp ds1 = (DataSetEmp)Session[Constant.strBuil6]; // reterive session
strBl = new StringBuilder(); //Create object of string builder class
strBl.Append(Constant.strBuil1); //Append string
// strBl.Append(Constant.strBuil2); //Append string
strBl.Append(Constant.strEmp_ID);
strBl.Append(Constant.strLastnm);
strBl.Append(Constant.strFrstnm);
strBl.Append(Constant.strETitle);
strBl.Append(Constant.strttlcur);
strBl.Append(Constant.strBrthdate);
strBl.Append(Constant.strHaredate);
strBl.Append(Constant.strEAddress);
strBl.Append(Constant.strECity);
strBl.Append(Constant.strERegion);
strBl.Append(Constant.strEPostlCode);
strBl.Append(Constant.strECountry);
strBl.Append(Constant.strEHmphn);
strBl.Append(Constant.strEExtensn);
strBl.Append(Constant.strENotes);
strBl.Append(Constant.strEReportsTo);
strBl.Append(Constant.strBuil3);
strBl.Append(Constant.strBuil4);
//strBl.Append(Constant.strBuil3);
//strBl.Append(Constant.strBuil4);
strcons = strBl.ToString(); //store all string into a string
adapDA = new SqlDataAdapter(strcons, connObj.connC); //pass connection and query into data adapter
adapDA.Fill(ds, Constant.strBuil5); //fill dataset from employee table
gvEmpTer.DataSource = ds.Employees;
gvEmpTer.DataBind(); //Bind data
}
catch (Exception ex)
{
Response.Redirect(Constant.strCatcExceptn); //throw exception on error page
}
finally //reallocate memory
{
connObj.CloseConnection();
connObj = null;
adapDA = null; strcons = null;
}
}
protected void Page_Load(object sender, EventArgs e)
{
SqlDataAdapter adapDA; DataSetEmp ds; StringBuilder strBl; string strcons; // declaration of object
Common connObj = new Common();
try
{
connObj.OpenConnection(); //open connection from common class object
ds = new DataSetEmp(); //create object of typed data set
// DataSetEmp ds1 = (DataSetEmp)Session[Constant.strBuil6]; // reterive session
strBl = new StringBuilder(); //Create object of string builder class
if (Session[Constant.strempid] != null)
{
string stre = Session[Constant.strempid].ToString();
int EmployeeID = Int32.Parse(stre);
strBl.Append(Constant.strQueryEmpTerr);
strBl.Append(stre);
}
else
{
strBl.Append(Constant.strBuil1); //Append string
strBl.Append(Constant.strBuil2); //Append string
strBl.Append(Constant.strBuil3);
strBl.Append(Constant.strBuil8);
}
strcons = strBl.ToString(); //store all string into a string
adapDA = new SqlDataAdapter(strcons, connObj.connC); //pass connection and query into data adapter
adapDA.Fill(ds, Constant.strBuil9); //fill dataset from employee table
gvEmpTer.DataSource = ds.EmployeeTerritories;
gvEmpTer.DataBind(); //Bind data
if (gvEmpTer.Rows.Count == 0)
{
LblMsgET.Visible = true;
LblMsgET.Text = Constant.strNotFound;
}
}
catch (Exception ex)
{
Response.Redirect(Constant.strCatcExceptn); //throw exception on error page
}
finally //reallocate memory
{
connObj.CloseConnection();
connObj = null;
adapDA = null; strcons = null;
}
}
private void showReport()
{
rprt.Load(Server.MapPath("~/CrystalReport1.rpt"));
rprt.FileName = Server.MapPath("~/CrystalReport1.rpt");
SqlCommand cmd = new SqlCommand("select * from me where StudentID='" + txtSID.Text.Trim().ToString() + "'", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSetEmp ds = new DataSetEmp();
sda.Fill(ds, "me");
rprt.SetDataSource(ds);
rprt.SetParameterValue("stdname", txtform.Text.Trim().ToString());
CrystalReportViewer1.ReportSource = rprt;
CrystalReportViewer1.DataBind();
pdfdemo();
}
private void showReport()
{
rprt.Load(Server.MapPath("~/CRVFiles/CrystalReport2.rpt"));
rprt.FileName = Server.MapPath("~/CRVFiles/CrystalReport2.rpt");
SqlCommand cmd = new SqlCommand("select concat(FisrtName,' ',LastName)FullName,sum(p.PaymentAmount)Balance,s.StudentID,t.AcademicYear,sum(p.PaymentAmount)-t.TuitionAmount Balance from studentsmaster s ,tuitions t ,tuitionpayments p where t.FormID=s.ClassNumber and p.StudentID=s.StudentID and s.StudentID='" + txtSID.Text.Trim().ToString() + "' group by s.StudentID ,t.AcademicYear,FisrtName,LastName,TuitionAmount", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSetEmp ds = new DataSetEmp();
sda.Fill(ds, "DsOSbals");
rprt.SetDataSource(ds);
// rprt.SetParameterValue("sid", txtSID.Text.Trim().ToString());
// rprt.SetParameterValue("formid", txtform.Text.Trim().ToString());
CrystalReportViewer1.ReportSource = rprt;
CrystalReportViewer1.DataBind();
pdfdemo();
Response.ClearContent();
}
protected void Page_Load(object sender, EventArgs e)
{
DataTable dtSql; SqlDataAdapter adapDA; StringBuilder strBl; string strcons;
Common connObj = new Common(); //declaration of objects
try
{
DataSetEmp ds = new DataSetEmp();
Session[Constant.strBuil6] = ds; //Store dataset into session
connObj.OpenConnection(); //open connection
dtSql = new DataTable(); //create data table
strBl = new StringBuilder(); //create string builder object
strBl.Append(Constant.strBuil1); //Append string
strBl.Append(Constant.strBuil2);
strBl.Append(Constant.strBuil3);
strBl.Append(Constant.strBuil7);
strcons = strBl.ToString();
adapDA = new SqlDataAdapter(strcons, connObj.connC); // Pass the connection to a Data adapter object
adapDA.Fill(dtSql); // fill Data from table
gvS.DataSource = dtSql;
gvS.DataBind(); //Bind data
if (gvS.Rows.Count == 0)
{
LblMsgT.Visible = true;
LblMsgT.Text = Constant.strNotFound;
}
}
catch (Exception)
{
Response.Redirect(Constant.strCatcExceptn); //throw Exception
}
finally //Reallocate memory
{
connObj.CloseConnection();
connObj = null;
adapDA = null;
}
}
public void Show_Data()
{
if (Session[Constant.strEmpid] != null)
{
connObj = new Common(); //INITIALIZATION OF OBJECT OF CLASS COMMON
StringBuilder strBl; //DECLARATION FOR OBJECT OF STRING BUILDER
SqlDataAdapter adapDA; //DECLARATION FOR OBJECT OF SQL DATAADAPTOR
DataSetEmp dsEdit; //DECLARATION FOR OBJECT OF DATASET
try
{
dsEdit = new DataSetEmp(); //INITIALIZING THE OBJECT OF DATASET
connObj.OpenConnection(); //OPEN THE DATABASE
strBl = new StringBuilder(); //INITIALIZATION OF STRING BUILDER
if (Session[Constant.strEmpid] != null) //IF SESSION HAS SOME VALUES
{
string stre = Session[Constant.strEmpid].ToString();
int id = int.Parse(stre);
strBl.Append(Constant.strQryEmp);//APPENDING THE STRING BUILDER
strBl.Append(id);
}
adapDA = new SqlDataAdapter(strBl.ToString(), connObj.connC); //INITIALIZATOIN OF ADAPTER
adapDA.Fill(dsEdit.Employees); //FILLING THE ADAPTOR INTO DATASET
txtEmployeeID.Text = dsEdit.Tables[0].Rows[0][Constant.strEmpID].ToString(); //ASSIGN THE EMPLOYEEID TO ITS REGARD FIELD
txtFirstName.Text = dsEdit.Tables[0].Rows[0][Constant.strFstName].ToString(); //ASSIGN THE FIRSTNAME TO ITS REGARD FIELD
txtLastName.Text = dsEdit.Tables[0].Rows[0][Constant.strLstName].ToString(); //ASSIGN THE LASTNAME TO ITS REGARD FIELD
ddlTitle.SelectedValue = dsEdit.Tables[0].Rows[0]["Title"].ToString();
ddlTitleOfCourtesy.SelectedValue = dsEdit.Tables[0].Rows[0]["TitleOfCourtesy"].ToString();
txtBirthDate.Text = dsEdit.Tables[0].Rows[0][Constant.strBirthDate].ToString(); //ASSIGN THE BIRTHDATE TO ITS REGARD FIELD
txtHireDate.Text = dsEdit.Tables[0].Rows[0][Constant.strHireDate].ToString(); //ASSIGN THE HIREDATE TO ITS REGARD FIELD
txtAddress.Text = dsEdit.Tables[0].Rows[0][Constant.strAddress].ToString(); //ASSIGN THE ADDRESS TO ITS REGARD FIELD
txtCity.Text = dsEdit.Tables[0].Rows[0][Constant.strCity].ToString(); //ASSIGN THE CITY TO ITS REGARD FIELD
txtRegion.Text = dsEdit.Tables[0].Rows[0][Constant.strRegion].ToString(); //ASSIGN THE REGION TO ITS REGARD FIELD
txtPostalCode.Text = dsEdit.Tables[0].Rows[0][Constant.strPostalCode].ToString(); //ASSIGN THE POSTALCODE TO ITS REGARD FIELD
txtCountry.Text = dsEdit.Tables[0].Rows[0][Constant.strCountry].ToString(); //ASSIGN THE COUNTRY TO ITS REGARD FIELD
txtHomePhone.Text = dsEdit.Tables[0].Rows[0][Constant.strHomePhone].ToString(); //ASSIGN THE HOMEPHONE TO ITS REGARD FIELD
txtExtension.Text = dsEdit.Tables[0].Rows[0][Constant.strExtension].ToString(); //ASSIGN THE EXTENSION TO ITS REGARD FIELD
txtPhoto.Text = dsEdit.Tables[0].Rows[0][Constant.strPhoto].ToString();
txtNotes.Text = dsEdit.Tables[0].Rows[0][Constant.strNotes].ToString(); //ASSIGN THE NOTES TO ITS REGARD FIELD
txtReportsTo.Text = dsEdit.Tables[0].Rows[0][Constant.strReportsTo].ToString(); //ASSIGN THE REPORTSTO TO ITS REGARD FIELD
txtPhotoPath.Text = dsEdit.Tables[0].Rows[0][Constant.strPhotoPath].ToString();
if (connObj.connC.State == System.Data.ConnectionState.Open)
{
connObj.CloseConnection(); //TO CLOSE THE DATABASE
}
}
catch (Exception ex)
{
Response.Redirect(Constant.strCatcExceptn); //REDIRECT TO ERROR.ASPX IF ANY EXCEPTION IS OCCURED
}
finally
{
connObj = null; //DESTROYING THE OBJECT OF SQL CONNECTION
strBl = null; //DESTROYING THE OBJECT OF STRING BUILDER
adapDA = null; //DESTROYING THE OBJECT OF SQL DATAADAPTOR
dsEdit = null; //DESTROYING THE OBJECT OF DATA SET
}
}
else
{
txtEmployeeID.Visible = false; //MAKING TEXTBOX VISIBLE FALSE
txtFirstName.Text = null; //ASSIGN NULL TO FIRSTNAME
txtLastName.Text = null; //ASSIGN NULL TO LASTNAME
ddlTitle.SelectedValue = null; //ASSIGN NULL TO TITLE
ddlTitleOfCourtesy.SelectedValue = null; //ASSIGN NULL TO TITLEOFCOURTESY
txtBirthDate.Text = null; //ASSIGN NULL TO BIRTHDATE
txtHireDate.Text = null; //ASSIGN NULL TO HIREDATE
txtAddress.Text = null; //ASSIGN NULL TO ADDRESS
txtCity.Text = null; //ASSIGN NULL TO CITY
txtRegion.Text = null; //ASSIGN NULL TO REGION
txtPostalCode.Text = null; //ASSIGN NULL TO POSTALCODE
txtCountry.Text = null; //ASSIGN NULL TO COUNTRY
txtHomePhone.Text = null; //ASSIGN NULL TO HOMEPHONE
txtExtension.Text = null; //ASSIGN NULL TO EXTENSION
txtPhoto.Text = null;
txtNotes.Text = null; //ASSIGN NULL TO NOTES
txtReportsTo.Text = null; //ASSIGN NULL TO REPORTSTO
txtPhotoPath.Text = null;
}
}
protected void btnADD_Click(object sender, EventArgs e)
{
SqlCommand Cmd; //DECLARATION OF SQL COMMAND
StringBuilder strB1; Common connObj = new Common();
if (Session[Constant.strEmpid] == null)
{
try
{
connObj.OpenConnection();
strB1 = new StringBuilder(); //INITIALIZATION OF STRING BUILDER
strB1.Append(Constant.strInsertEmp);
// strB1.Append(" (EmployeeID , ");
strB1.Append(" (FirstName , ");
strB1.Append(" LastName , ");
strB1.Append(" Title , ");
strB1.Append(" TitleOfCourtesy , ");
strB1.Append(" BirthDate , ");
strB1.Append(" HireDate , ");
strB1.Append(" Address , ");
strB1.Append(" City , ");
strB1.Append(" Region , ");
strB1.Append(" PostalCode , ");
strB1.Append(" Country , ");
strB1.Append(" HomePhone , ");
strB1.Append(" Extension, ");
strB1.Append(" Notes, ");
strB1.Append(" ReportsTo ) ");
strB1.Append(" Values( ");
//strB1.Append(".");
//strB1.Append(" @EmployeeID , ");
strB1.Append(" @FirstName , ");
strB1.Append(" @LastName , ");
strB1.Append(" @Title , ");
strB1.Append(" @TitleOfCourtesy , ");
strB1.Append(" @BirthDate , ");
strB1.Append(" @HireDate , ");
strB1.Append(" @Address , ");
strB1.Append(" @City , ");
strB1.Append(" @Region , ");
strB1.Append(" @PostalCode , ");
strB1.Append(" @Country , ");
strB1.Append(" @HomePhone , ");
strB1.Append(" @Extension, ");
strB1.Append(" @Notes, ");
strB1.Append(" @ReportsTo ) ");
Cmd = new SqlCommand(strB1.ToString(), connObj.connC); //INITIALIZATION OF COMMAND
//Cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 20).Value= txtEmployeeID.Text.Trim();
Cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = txtFirstName.Text.Trim();
Cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = txtLastName.Text.Trim();
Cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = ddlTitle.SelectedValue.ToString();
Cmd.Parameters.Add("@TitleOfCourtesy", SqlDbType.NVarChar, 25).Value = ddlTitleOfCourtesy.SelectedValue.ToString();
Cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime, 8).Value = txtBirthDate.Text.Trim();
Cmd.Parameters.Add("@HireDate", SqlDbType.DateTime, 8).Value = txtHireDate.Text.Trim();
Cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 60).Value = txtAddress.Text.Trim();
Cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = txtCity.Text.Trim();
Cmd.Parameters.Add("@Region", SqlDbType.NVarChar, 15).Value = txtRegion.Text.Trim();
Cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 10).Value = txtPostalCode.Text.Trim();
Cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = txtCountry.Text.Trim();
Cmd.Parameters.Add("@HomePhone", SqlDbType.NVarChar, 24).Value = txtHomePhone.Text.Trim();
Cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 4).Value = txtExtension.Text.Trim();
// Cmd.Parameters.Add("@Photo", SqlDbType.NVarChar, 20).Value = txtPhoto.Text.Trim();
Cmd.Parameters.Add("@Notes", SqlDbType.NText, 16).Value = txtNotes.Text.Trim();
Cmd.Parameters.Add("@ReportsTo", SqlDbType.Int, 4).Value = txtReportsTo.Text.Trim();
//Cmd.Parameters.Add("@PhotoPath", SqlDbType.NVarChar, 255).Value= txtPhotoPath.Text.Trim();
Cmd.ExecuteNonQuery(); //EXECUTE THE COMMAND
lblInsert.Visible = true; //MAKING LABEL VISIBLE
lblInsert.Text = Constant.strMsgInsert;
Show_Data();
if (connObj.connC.State == System.Data.ConnectionState.Open)
{
connObj.connC.Close(); //CLOSE THE CONNECTION
}
}
catch (Exception ex)
{
Response.Redirect(Constant.strCatcExceptn);
}
finally
{
if (connObj.connC.State == System.Data.ConnectionState.Open)
{
connObj.connC.Close(); //CLOSE THE CONNECTION
}
Cmd = null;
strB1 = null;
}
}
else
{
SqlTransaction Trans;
try
{
DataSetEmp ds = new DataSetEmp();
connObj.OpenConnection();
Trans = connObj.connC.BeginTransaction();
string strA = Session[Constant.strEmpid].ToString();
int id = int.Parse(strA);
strB1 = new StringBuilder();
strB1.Append(Constant.strQryUpdate);
//strB1.Append(" (EmployeeID , ");
strB1.Append(" FirstName =@FirstName, ");
strB1.Append(" LastName = @LastName, ");
strB1.Append(" Title = @Title, ");
strB1.Append(" TitleOfCourtesy = @TitleOfCourtesy , ");
strB1.Append(" BirthDate= @BirthDate , ");
strB1.Append(" HireDate= @HireDate , ");
strB1.Append(" Address= @Address , ");
strB1.Append(" City= @City , ");
strB1.Append(" Region=@Region , ");
strB1.Append(" PostalCode= @PostalCode , ");
strB1.Append(" Country= @Country , ");
strB1.Append(" HomePhone= @HomePhone , ");
strB1.Append(" Extension= @Extension, ");
strB1.Append(" Notes= @Notes ");
strB1.Append(Constant.strWhereEid);
strB1.Append(id);
Cmd = new SqlCommand(strB1.ToString(), connObj.connC, Trans); //INITIALIZATION OF COMMAND
//Cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 20).Value= txtEmployeeID.Text.Trim();
Cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = txtFirstName.Text.Trim();
Cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = txtLastName.Text.Trim();
Cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = ddlTitle.SelectedValue.ToString();
Cmd.Parameters.Add("@TitleOfCourtesy", SqlDbType.NVarChar, 25).Value = ddlTitleOfCourtesy.SelectedValue.ToString();
Cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime, 8).Value = txtBirthDate.Text.Trim();
Cmd.Parameters.Add("@HireDate", SqlDbType.DateTime, 8).Value = txtHireDate.Text.Trim();
Cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 60).Value = txtAddress.Text.Trim();
Cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = txtCity.Text.Trim();
Cmd.Parameters.Add("@Region", SqlDbType.NVarChar, 15).Value = txtRegion.Text.Trim();
Cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 10).Value = txtPostalCode.Text.Trim();
Cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = txtCountry.Text.Trim();
Cmd.Parameters.Add("@HomePhone", SqlDbType.NVarChar, 24).Value = txtHomePhone.Text.Trim();
Cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 4).Value = txtExtension.Text.Trim();
// Cmd.Parameters.Add("@Photo", SqlDbType.NVarChar, 20).Value = txtPhoto.Text.Trim();
Cmd.Parameters.Add("@Notes", SqlDbType.NText, 16).Value = txtNotes.Text.Trim();
Cmd.ExecuteNonQuery();
//EXECUTE THE COMMAND
Trans.Commit();
lblUpdate.Visible = true; //MAKING LABEL VISIBLE
lblUpdate.Text = Constant.strMsgUpdate;
Show_Data();
if (connObj.connC.State == System.Data.ConnectionState.Open)
{
connObj.connC.Close(); //CLOSE THE CONNECTION
}
}
catch (Exception ex)
{
// Trans.Rollback();
Response.Redirect(Constant.strCatcExceptn);
}
finally
{
if (connObj.connC.State == System.Data.ConnectionState.Open)
{
connObj.connC.Close(); //CLOSE THE CONNECTION
}
Cmd = null;
strB1 = null;
Response.Redirect(Constant.strDefault);
}
}
}
