public IPagedList <T> GetRolePageList <T>(out string err, int page = 1, int pageSize = 10, string sortCol = "RoleId", string sortOrder = "Asc") { err = ""; List <T> result = new List <T>(); int count = default(int); int s = (page - 1) * pageSize + 1; int e = page * pageSize + 1; #region sql statment string sqlCount = string.Format(@"select count(RoleSeq) from tblRole where CompanySeq = {0};", CompanySeq); string sql = string.Format(@" SELECT Row_Number() OVER(ORDER BY $sortCol$ $sortOrder$) AS ROWNum, x.* FROM ( SELECT r.*, (select u.UserName+ ' ,' from tblUser u, tblUserRole ur where ur.RoleSeq = r.RoleSeq and u.UserSeq = ur.UserSeq FOR XML PATH('')) as UserNameList FROM tblRole r WHERE r.CompanySeq = {0} ) x", CompanySeq); sql = string.Format("select * from ({0}) as t where ROWNum between @s and @e ", sql.Replace("$sortCol$", sortCol).Replace("$sortOrder$", sortOrder)); #endregion sql statement #region dynamic params var p = new DynamicParameters(); p.Add("@s", s); p.Add("@e", e); #endregion params try { using (SqlConnection conn = new SqlConnection(dbConnStr)) { using (var m = conn.QueryMultiple(sqlCount + sql, p)) { count = m.Read <int>().Single(); result = m.Read <T>().ToList(); } } // foreach (var a in (result as List <RoleViewModel>)) { if (!string.IsNullOrEmpty(a.UserNameList)) { a.UserNameList = a.UserNameList.Substring(0, a.UserNameList.Length - 2); // remove last "space + ," } } } catch (Exception ex) { err = ex.Message; // write error log LOG.Fatal(ERRHD.BuildExceptionMessage(ex)); return(null); } return(result.ToPagedList(page - 1, pageSize, count)); }