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