/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(Hashtable ht, string order = null, Mod_Com_Pager pager = null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select a.*,b.Name as RoleStatusName ");
strSql.Append(" FROM Sys_Role a ");
strSql.Append(" LEFT JOIN Sys_Role_Status b on a.RoleStatus=b.ID ");
strSql.Append(" Where 1=1 ");
if (ht != null)
{
foreach (DictionaryEntry de in ht)
{
strSql.AppendLine(de.Value.ToString());
}
}
string strPageSql = string.Empty;
if (pager != null)
{
Dal_Common.GetPageSql(ref strPageSql, strSql.ToString(), order, pager);
}
if (!string.IsNullOrEmpty(strPageSql))
{
return(SqlEasy.ExecuteDataSet(strPageSql));
}
else
{
return(SqlEasy.ExecuteDataSet(strSql.ToString()));
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List <Mod_Sys_User> GetList(Hashtable ht, string order, Mod_Com_Pager pager = null)
{
DataSet dataSet = null;
List <Mod_Sys_User> li_Sys_User = new List <Mod_Sys_User>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * ");
strSql.Append(" FROM Sys_User ");
strSql.Append(" Where 1=1 ");
if (ht != null)
{
foreach (DictionaryEntry de in ht)
{
strSql.AppendLine(de.Value.ToString());
}
}
string strPageSql = string.Empty;
if (pager != null)
{
Dal_Common.GetPageSql(ref strPageSql, strSql.ToString(), order, pager);
}
if (!string.IsNullOrEmpty(strPageSql))
{
dataSet = SqlEasy.ExecuteDataSet(strPageSql);
return(ConvertToListData(dataSet));
//return dataSet;
}
else
{
dataSet = SqlEasy.ExecuteDataSet(strSql.ToString());
return(ConvertToListData(dataSet));
//return dataSet;
}
}
public void getBtnList()
{
int pageindex = int.Parse(HttpContext.Current.Request.Params["page"]);
int pagesize = int.Parse(HttpContext.Current.Request.Params["rows"]);
string MenuID = HttpContext.Current.Request.Params["MenuID"].ToString();
Mod_Com_Pager pager = new Mod_Com_Pager(pagesize, pageindex);
Hashtable ht = new Hashtable();
if (!string.IsNullOrEmpty(MenuID))
{
ht.Add("MenuID", " and MenuID=" + MenuID);
}
var li_sys_Users = Bll_Sys_Btn.Instance.getSysBtnList(ht, "BtnID", pager);
List <Mod_Sys_Btn> _Sys_User = new List <Mod_Sys_Btn>();
string result = "";
if (li_sys_Users != null && li_sys_Users.Count > 0)
{
result = $"\"total\":\"{pager.RowCount.ToString()}\",\"rows\":{JSONhelper.ToJson(li_sys_Users)}";
}
else
{
result = $"\"total\":\"{pager.RowCount.ToString()}\",\"rows\":[]";
}
result = "{" + result + "}";
//string result = $"\"total\": \"{ pager.RowCount.ToString()} \",\"rows\":\"{JSONhelper.ToJson(dt)}\"";
HttpContext.Current.Response.Write(result);
}
public void getUserList()
{
int pageindex = int.Parse(HttpContext.Current.Request.Params["page"]);
int pagesize = int.Parse(HttpContext.Current.Request.Params["rows"]);
Mod_Com_Pager pager = new Mod_Com_Pager(pagesize, pageindex);
Hashtable ht = new Hashtable();
var li_sys_Users = Bll_Sys_User.Instance.getSysUserList(ht, "UserId", pager);
DataTable dt = null;
List <Mod_Sys_User> _Sys_User = new List <Mod_Sys_User>();
string result = $"\"total\":\"{pager.RowCount.ToString()}\",\"rows\":{JSONhelper.ToJson(li_sys_Users)}";
result = "{" + result + "}";
//string result = $"\"total\": \"{ pager.RowCount.ToString()} \",\"rows\":\"{JSONhelper.ToJson(dt)}\"";
HttpContext.Current.Response.Write(result);
}
private void getRoleList()
{
int pageindex = int.Parse(HttpContext.Current.Request.Params["page"]);
int pagesize = int.Parse(HttpContext.Current.Request.Params["rows"]);
Mod_Com_Pager pager = new Mod_Com_Pager(pagesize, pageindex);
Hashtable ht = new Hashtable();
DataSet ds = Bll_Sys_Role.Instance.getSysRoleList(ht, "RoleID", pager);
DataTable dt = null;
List <Mod_Sys_User> _Sys_User = new List <Mod_Sys_User>();
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
string result = $"\"total\":\"{pager.RowCount.ToString()}\",\"rows\":{JSONhelper.ToJson(dt)}";
result = "{" + result + "}";
HttpContext.Current.Response.Write(result);
}
/// <summary>
/// 获得数据列表
/// </summary>
/// <returns></returns>
public List <Mod_Sys_User> getSysUserList(Hashtable ht, string order = null, Mod_Com_Pager pager = null)
{
return(Dal_Sys_User.Instance.GetList(ht, order, pager));
}
public List <Mod_Sys_Author> GetList(Hashtable ht, string order, Mod_Com_Pager pager = null)
{
return(Dal_Sys_Author.Instance.GetList(ht, order, pager));
}
public static bool GetPageSql(ref string strPageSql, string sql, string orderColumns, Mod_Com_Pager pager)
{
int pageSize = 0;
int getPageNum = 0;
int outTotalPage = 0;
int outTotalRows = 0;
if (pager != null)
{
pageSize = pager.PageSize;
getPageNum = pager.PageNum;
outTotalPage = pager.PageCount;
outTotalRows = pager.RowCount;
}
//先计算总的数量，总记录
string sqlRowCountTmp = string.Empty;
sqlRowCountTmp = $"SELECT COUNT(1) AS Rc FROM ({sql}) PageData";
object o = new object();
o = SqlEasy.ExecuteScalar(sqlRowCountTmp);
if (o != DBNull.Value)
{
outTotalRows = (int)o;
}
//计算页码
if (outTotalRows % pageSize == 0)
{
outTotalPage = outTotalRows / pageSize;
}
else
{
outTotalPage = outTotalRows / pageSize + 1;
}
//校验取的页码
if (getPageNum < 1)//比页面小就去第一页
{
getPageNum = 1;
}
if (outTotalPage < getPageNum)//比页面大就取最大页面
{
getPageNum = outTotalPage;
}
//开始行
int startRow = (getPageNum - 1) * pageSize;
//结束行
int endRow = getPageNum * pageSize;
pager.PageCount = outTotalPage;
pager.RowCount = outTotalRows;
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append($" SELECT ROW_NUMBER() OVER ( order by {orderColumns} desc) AS Rw,T.*");
strSql.Append($" FROM ({sql}) T");
strSql.Append(" )TT ");
strSql.AppendFormat(" WHERE TT.Rw > {0} and TT.Rw<={1}", startRow, endRow);
strPageSql = strSql.ToString();
return(false);
}
/// <summary>
/// 获得数据列表
/// </summary>
/// <returns></returns>
public DataSet getSysRoleList(Hashtable ht, string order = null, Mod_Com_Pager pager = null)
{
return(Dal_Sys_Role.Instance.GetList(ht, order, pager));
}
