示例#1
0
        /// <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));
        }
示例#2
0
 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);
 }