C# sqlserver 分页查询

 C# sqlserver 分页查询

#region----商家列表查询请求类----
    /// <summary>
    /// 商家列表查询请求类
    /// </summary>
    public class SellerListRequest
    {
        /// <summary>
        /// 商家ID
        /// </summary>
        public string SellerID { get; set; }
        /// <summary>
        /// 商家名称
        /// </summary>
        public string SLName { get; set; }
        /// <summary>
        /// 每页大小
        /// </summary>
        public int PageSize { get; set; }
        /// <summary>
        /// 当前页
        /// </summary>
        public int PageNumber { get; set; }
    }
    #endregion

  #region----商家列表查询响应类----
    /// <summary>
    /// 商家列表查询响应类
    /// </summary>
    public class SellerListResponse
    {
        public List<Seller> SellerList { get; set; }

        public int PageCount { get; set; }

        public int Total { get; set; }
    }
    #endregion

#region----查询商家列表----
        public ActionResult QuerySellerListAjax()
        {
            //每页大小
            string pageSizeStr = Request["PageSize"];
            int pageSize;
            if (RegExp.IsNull(pageSizeStr) || !RegExp.IsNumbers(pageSizeStr))
            {
                pageSize = 10;
            }
            else
            {
                pageSize = Convert.ToInt32(pageSizeStr);
            }
            //当前页
            string pageNumberStr = Request["PageNumber"];
            int pageNumber;
            if (RegExp.IsNull(pageNumberStr) || !RegExp.IsNumbers(pageNumberStr))
            {
                pageNumber = 0;
            }
            else
            {
                pageNumber = Convert.ToInt32(pageNumberStr);
            }

//提示：商家ID(SellerID)字段类型是Int，为了方便，这里写成了string,
//要注意的是，如果sql里面的SellerID包含了非数字内容(例如：1aa)，就会报错，
//所以在这里就做了过滤，让它赋值为0。

            //商家ID
            string sellerID = Request["SellerID"];
            if (RegExp.IsNull(sellerID))
            {
                sellerID = string.Empty;
            }
            else
            {
                if (!RegExp.IsNumbers(sellerID))
                {
                    sellerID = "0";//按这个条件查询出的结果是：查无数据
                }
            }        
            //商家名称
            string sLName = Request["SLName"];
            if (RegExp.IsNull(sLName))
            {
                sLName = string.Empty;
            }
            SellerListRequest request = new SellerListRequest()
            {
                PageSize = pageSize,
                PageNumber = pageNumber,
                SellerID = sellerID,
                SLName = sLName
            };
            BaseResponse<SellerListResponse> responseSellerListObj = new BaseResponse<SellerListResponse>();
            string responseSellerListJson = HttpAjaxHelper.ActionPost(request, "QuerySellerList", "SellerService");
            responseSellerListObj = JsonConvert.DeserializeObject<BaseResponse<SellerListResponse>>(responseSellerListJson);
            if (
                responseSellerListObj == null
                || responseSellerListObj.Result == null
                || responseSellerListObj.Result.SellerList.Count <= 0)
            {
                return Json(JsonAjax.JsonResult("0", "查无数据"));
            }
            else
            {
                List<Seller> sellerLsit = responseSellerListObj.Result.SellerList;
                string data = JsonConvert.SerializeObject(sellerLsit);
                int pageCount = responseSellerListObj.Result.PageCount;
                int totalCount = responseSellerListObj.Result.Total;
                return Json(JsonAjax.JsonResult("1", pageCount, totalCount, data));
            }
        }
        #endregion

#region----商家列表查询响应类----
    /// <summary>
    /// 商家列表查询响应类
    /// </summary>
    public class SellerListResponse
    {
        public List<Seller> SellerList { get; set; }

        public int PageCount { get; set; }

        public int Total { get; set; }
    }
    #endregion

#region----查询商家列表----
        public BaseResponse<SellerListResponse> QuerySellerList(SellerListRequest request)
        {
            BaseResponse<SellerListResponse> response = new BaseResponse<SellerListResponse>();
            StringBuilder sbSqlPage2And = new StringBuilder();
            //商家ID
            if (!RegExp.IsNull(request.SellerID))
            {
                sbSqlPage2And.Append(" and SellerID=@SellerID ");
            }
            //商家名称
            if (!RegExp.IsNull(request.SLName))
            {
                sbSqlPage2And.Append(" and SLName like '%' + @SLName + '%' ");
            }
            StringBuilder sbSql = new StringBuilder();
            string sqlPage1 = " select * from ( ";
            //sqlPage2的作用是：可以查询总行数
            string sqlPage2 = @"
                select Row_number() over(order by UpdateDate desc) as rownum,*
                from Seller where SellerID=SellerID
                " + sbSqlPage2And;
            string sqlPage3 = @"
                ) as t1 where t1.rownum>=(@PageNumber)*@PageSize+1 and t1.rownum<=((@PageNumber+1)*@PageSize)
                ";
            sbSql.Append(sqlPage1);
            sbSql.Append(sqlPage2);
            sbSql.Append(sqlPage3);

            //执行查询 
            List<Seller> sellerList = _IDbConnection.Query<Seller>(sbSql.ToString(), request).ToList();
            //查询出结果后继续查询行数
            if (sellerList != null && sellerList.Count > 0)
            {
                response.Result = new SellerListResponse();
                response.Result.SellerList = sellerList;
                //总行数（totalCount）
                string totalCountSql = " select count(1) from ( " + sqlPage2 + " ) as A ";
                int totalCount = _IDbConnection.Query<int>(totalCountSql, request).FirstOrDefault();
                //总页数（pageCount）=总行数（totalCount）/每页大小（PageSize）
                decimal PageCountDecimal = Math.Ceiling((decimal)totalCount / request.PageSize);
                int pageCount = Convert.ToInt32(PageCountDecimal);

                response.Result.Total = totalCount;//总行数
                response.Result.PageCount = pageCount;//总页数
            }
            return response;
        }
        #endregion

搜索

复制