/// <summary>
/// 查询用户列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="paras"></param>
/// <param name="iCount"></param>
/// <returns></returns>
public IEnumerable <T> QryUsers <T>(Dictionary <string, object> paras, out int iCount)
{
iCount = 0;
WhereBuilder builder = new WhereBuilder();
builder.FromSql = "v_user_list";
GridData grid = new GridData()
{
PageIndex = Convert.ToInt32(paras["pi"]),
PageSize = Convert.ToInt32(paras["pageSize"]),
SortField = paras["sort"].ToString(),
SortDirection = paras["order"].ToString()
};
builder.AddWhereAndParameter(paras, "userid", "AccountName", "LIKE", "'%'+@userid+'%'");
builder.AddWhereAndParameter(paras, "username", "RealName", "LIKE", "'%'+@username+'%'");
builder.AddWhereAndParameter(paras, "IsAble");
builder.AddWhereAndParameter(paras, "IfChangePwd");
builder.AddWhereAndParameter(paras, "RoleID");
builder.AddWhereAndParameter(paras, "adddatestart", "CreateTime", ">");
builder.AddWhereAndParameter(paras, "adddateend", "CreateTime", "<");
return(SortAndPage <T>(builder, grid, out iCount));
}
void FormartSqlToSortAndPage(Pagination pagination, ref string sql, ref string countSql, ref WhereBuilder builder)
{
sql = "";
countSql = "";
if (!sql.StartsWith("select", StringComparison.CurrentCultureIgnoreCase))
{
//sql = string.Format("SELECT * FROM(SELECT row=ROW_NUMBER() OVER(ORDER BY {0} {1}),* FROM {2}", grid.SortField, grid.SortDirection, builder.FromSql); //
sql = "select * from " + builder.FromSql; //仅支持SQL Server 2012及以上
countSql = "select count(*) from " + builder.FromSql;
}
if (builder.Wheres.Count > 0)
{
string strWhere = " where " + String.Join(" and ", builder.Wheres);
sql += strWhere;
countSql += strWhere;
}
//sql += ") AS A WHERE row between @PageStartIndex AND @PageSize";
#region 仅支持SQL Server 2012及以上
sql += " order by " + pagination.sidx + " " + pagination.sord;
sql += " OFFSET @PageStartIndex ROWS FETCH NEXT @PageSize ROWS ONLY";
builder.Parameters.Add("PageSize", pagination.rows);
builder.Parameters.Add("PageStartIndex", pagination.rows * (pagination.page - 1));
#endregion
//builder.Parameters.Add("PageSize", grid.PageSize * grid.PageIndex);
//builder.Parameters.Add("PageStartIndex", grid.PageSize * (grid.PageIndex - 1) + 1);
}
