public IHttpActionResult ViewEmployeeByPage(int max, int page, string sort_col, string sort_dir, string search = null) { Paged_EmployeeModel objList = new Paged_EmployeeModel(); try { objList = objEmployeeVM.ListEmployeeDetailsByPagination(max, page, search, sort_col, sort_dir); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(Ok(new { results = objList })); }
public Paged_EmployeeModel ListEmployeeDetailsByPagination(int max, int page, string search, string sort_col, string sort_dir) { Paged_EmployeeModel objPaged = new Paged_EmployeeModel(); List <Employee> objList = new List <Employee>(); try { if (search == null) { search = ""; } int startIndex = max * (page - 1); string sqlstr = "USP_GetEmployeeByPage"; var connection = gConnection.Connection(); connection.Open(); SqlCommand cmd = new SqlCommand(sqlstr, connection); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@startRowIndex", startIndex); cmd.Parameters.AddWithValue("@pageSize", max); cmd.Parameters.AddWithValue("@search", search); cmd.Parameters.AddWithValue("@sort_col", sort_col); cmd.Parameters.AddWithValue("@sort_dir", sort_dir); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { Employee tempobj = new Employee(); tempobj.ID = Convert.ToInt32(sdr["ID"].ToString()); tempobj.EmpID = Convert.ToInt32(sdr["EmpID"].ToString()); tempobj.Email = sdr["Email"].ToString(); tempobj.Name = sdr["Name"].ToString(); tempobj.ManagerName = sdr["ManagerName"].ToString(); tempobj.Profile = Convert.ToInt32(sdr["ProfileID"].ToString()); tempobj.ProjectID = Convert.ToInt32(sdr["ProjectID"].ToString()); tempobj.ProfilelName = sdr["Profile"].ToString(); tempobj.ProjectName = sdr["ProjectName"].ToString(); tempobj.Status = Convert.ToInt32(sdr["Status"]); tempobj.StatusName = sdr["StatusName"].ToString(); tempobj.Expertise = sdr["Experties"].ToString().Split(',').Select(x => int.Parse(x)).ToArray(); tempobj.stream = sdr["Stream"].ToString().Split(',').Select(x => int.Parse(x)).ToArray(); tempobj.ExpertiseDisplay = sdr["ExpertiseDisplay"].ToString(); tempobj.streamDisplay = sdr["streamDisplay"].ToString(); objList.Add(tempobj); } sdr.Close(); objPaged.EmployeeModelList = objList; sqlstr = "select count(*) as cnt from tbl_Employee inner join tbl_DCTEmployee on tbl_Employee.EmpID=tbl_DCTEmployee.EmpID where tbl_DCTEmployee.EmpName like @search "; cmd.Parameters.Clear(); cmd.CommandText = sqlstr; cmd.Connection = connection; cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@search", '%' + @search + '%'); sdr = cmd.ExecuteReader(); while (sdr.Read()) { objPaged.PageCount = Convert.ToInt32(sdr["cnt"].ToString()); } connection.Close(); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(objPaged); }