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