public static List <Leaves_Count> Get_Data(string eid) { List <Leaves_Count> list_det = new List <Leaves_Count>(); DataTable dt = get_all_products(Convert.ToInt32(eid)); for (Int32 i = 0; i < dt.Rows.Count; i++) { Leaves_Count p = new Leaves_Count(); p.sl = Convert.ToInt32(dt.Rows[i]["sl"]); p.cl = Convert.ToInt32(dt.Rows[i]["cl"]); p.al = Convert.ToInt32(dt.Rows[i]["al"]); list_det.Add(p); } return(list_det); }
public void GetLeaves_Count(int iDisplayLength, int iDisplayStart, int iSortCol_0, string sSortDir_0, string sSearch) { int displayLength = iDisplayLength; int displayStart = iDisplayStart; int sortCol = iSortCol_0; string sortDir = sSortDir_0; string search = sSearch; string cs = ConfigurationManager.ConnectionStrings["DBMS"].ConnectionString; List <Leaves_Count> listEmployees = new List <Leaves_Count>(); int filteredCount = 0; using (SqlConnection con = new SqlConnection(cs)) { SqlCommand cmd = new SqlCommand("Sp_GetUpdate_Leave", con); cmd.CommandType = CommandType.StoredProcedure; SqlParameter paramDisplayLength = new SqlParameter() { ParameterName = "@DisplayLength", Value = displayLength }; cmd.Parameters.Add(paramDisplayLength); SqlParameter paramDisplayStart = new SqlParameter() { ParameterName = "@DisplayStart", Value = displayStart }; cmd.Parameters.Add(paramDisplayStart); SqlParameter paramSortCol = new SqlParameter() { ParameterName = "@SortCol", Value = sortCol }; cmd.Parameters.Add(paramSortCol); SqlParameter paramSortDir = new SqlParameter() { ParameterName = "@SortDir", Value = sortDir }; cmd.Parameters.Add(paramSortDir); SqlParameter paramSearchString = new SqlParameter() { ParameterName = "@Search", Value = string.IsNullOrEmpty(search) ? null : search }; cmd.Parameters.Add(paramSearchString); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); int sl, cl, al; SqlConnection con1 = new SqlConnection(cs); while (rdr.Read()) { Leaves_Count emp = new Leaves_Count(); emp.emp_id = Convert.ToInt32(rdr["emp_id"]); filteredCount = Convert.ToInt32(rdr["TotalCount"]); emp.emp_name = rdr["emp_name"].ToString(); emp.Desig = Convert.ToString(rdr["Designation"]); emp.sl = Convert.ToInt32(rdr["sl"]); emp.cl = Convert.ToInt32(rdr["cl"]); emp.al = Convert.ToInt32(rdr["al"]); emp.Img = Convert.ToString(rdr["Img"]); SqlCommand cmd1 = new SqlCommand("select ISNULL(Sum(l.days),0) from Leaves as l where emp_id='" + Convert.ToInt32(rdr["emp_id"]) + "' and leave_type='sl'", con1); con1.Open(); sl = Convert.ToInt32(cmd1.ExecuteScalar()); con1.Close(); SqlCommand cmd2 = new SqlCommand("select ISNULL(Sum(l.days),0) from Leaves as l where emp_id='" + Convert.ToInt32(rdr["emp_id"]) + "' and leave_type='cl'", con1); con1.Open(); cl = Convert.ToInt32(cmd2.ExecuteScalar()); con1.Close(); SqlCommand cmd3 = new SqlCommand("select ISNULL(Sum(l.days),0) from Leaves as l where emp_id='" + Convert.ToInt32(rdr["emp_id"]) + "' and leave_type='al'", con1); con1.Open(); al = Convert.ToInt32(cmd3.ExecuteScalar()); con1.Close(); emp.lsl = Convert.ToInt32(rdr["sl"]) - sl; emp.lcl = Convert.ToInt32(rdr["cl"]) - cl; emp.lal = Convert.ToInt32(rdr["al"]) - al; listEmployees.Add(emp); } } var result = new { iTotalRecords = GetUpdate_LeavesCount(), iTotalDisplayRecords = filteredCount, aaData = listEmployees }; JavaScriptSerializer js = new JavaScriptSerializer(); Context.Response.Write(js.Serialize(result)); }