C# Sqlserver 分页查询-微商城
#region----分页查询订单流水状态---- /// <summary> /// 分页查询订单流水状态 /// </summary> /// <param name="context"></param> public void DoQueryStoredCardRechargeFlow(HttpContext context) { //商家BID int bID = obAdmin.BID; int pageSize;//每页大小 int pageNumber;//当前页是第几页，索引0就是第一页，索引1就是第二页 string pageSizeStr = context.Request["PageSize"]; string pageNumberStr = context.Request["PageNumber"]; if (RegExpHelper.IsNull(pageSizeStr) || !RegExpHelper.IsNumber(pageSizeStr)) { pageSize = 10; } if (RegExpHelper.IsNull(pageNumberStr) || !RegExpHelper.IsNumber(pageNumberStr)) { pageNumber = 0; } pageSize = Convert.ToInt32(pageSizeStr); pageNumber = Convert.ToInt32(pageNumberStr); //会员卡号 string cardID = context.Request["CardID"]; if (RegExpHelper.IsNull(cardID)) { cardID = null; } //订单号 string tradeID = context.Request["TradeID"]; if (RegExpHelper.IsNull(tradeID)) { tradeID = null; } //下单时间 DateTime? fromTime; DateTime? toTime; string fromTimeStr = context.Request["FromTime"]; string toTimeStr = context.Request["ToTime"]; if (RegExpHelper.IsNull(fromTimeStr)) { fromTime = null; } else { fromTime = Convert.ToDateTime(fromTimeStr); } if (RegExpHelper.IsNull(toTimeStr)) { toTime = null; } else { toTime = Convert.ToDateTime(toTimeStr); } //微信交易号 string outerID = context.Request["OuterID"]; if (RegExpHelper.IsNull(outerID)) { outerID = null; } //微信昵称 string nickName = context.Request["NickName"]; if (RegExpHelper.IsNull(nickName)) { nickName = null; } //订支付状态 string statusStr = context.Request["Status"];//订单支付状态，0待支付，1已支付，2已完成，3所有关闭的交易 int? status; if (RegExpHelper.IsNull(statusStr)) { status = null; } else { status = Convert.ToInt32(statusStr); } //decOpenID，2018-01-18统计新增 string decOPenID = context.Request["OpenID"]; if (RegExpHelper.IsNull(decOPenID)) { decOPenID = null; } DoQueryStoredCardRechargeFlow queryStoredCardEntity = new Entity.MembershipCard.DoQueryStoredCardRechargeFlow() { PageSize = pageSize, PageNumber = pageNumber, Status = status, CardID = cardID, TradeID = tradeID, FromTime = fromTime, ToTime = toTime, OuterID = outerID, NickName = nickName, BID = bID, decOpenID = decOPenID }; var response = _valueCardSaveTradeLogServices.QueryPageIsDealWithByIndexAndSize(queryStoredCardEntity); if (response.Status == BaseResponseStatusEnum.Error) { BaseJsonResult.PrintJson("-1", response.Msg); return; } else if (response.Status == BaseResponseStatusEnum.Default) { BaseJsonResult.PrintJson("0", response.Msg); return; } BaseJsonResult.PrintJson("1", response.PageCount, response.Total, JsonConvert.SerializeObject(response.Result)); return; } #endregion
#region ----后台，分页查询后台数据，根据当前页和每页大小，分页查询订单处理状态和支付状态---- /// <summary> /// 后台，根据当前页和每页大小，分页查询订单处理状态和支付状态。 /// </summary> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <returns></returns> public BasePageResponse<ValueCardSaveTradeLogWxUsers> QueryPageIsDealWithByIndexAndSize(DoQueryStoredCardRechargeFlow QueryPageEntity) { BasePageResponse<ValueCardSaveTradeLogWxUsers> response = new BasePageResponse<ValueCardSaveTradeLogWxUsers>(); string currentMethodLog = "[QueryPageIsDealWithByIndexAndSize()]分页查询储值订单，"; try { string sqlPage2Where = string.Empty; //支付状态 int? status = QueryPageEntity.Status; //0待支付，1已支付代充值，2已完成 , 其他数字是全部状态 if (status == (int)StoredCardTradeLogStatus.NotPay) { sqlPage2Where += " and status like '%" + StoredCardTradeLogStatus.NotPay.ToString() + "%'";//还没支付 } else if (status == (int)StoredCardTradeLogStatus.HadPayNotInsert) { sqlPage2Where += " and status like '%" + StoredCardTradeLogStatus.HadPayNotInsert.ToString() + "%'";//已经支付但是没有充值进去数据库 } else if (status == (int)StoredCardTradeLogStatus.FinishPay) { sqlPage2Where += " and status like '%" + StoredCardTradeLogStatus.FinishPay.ToString() + "%'";//已经完成支付 } else { sqlPage2Where += " "; } //会员卡号 if (!RegExp.IsNull(QueryPageEntity.CardID)) { sqlPage2Where += " and VCSTL.CardID like '%' + @CardID + '%'"; } //订单号 if (!RegExp.IsNull(QueryPageEntity.TradeID)) { sqlPage2Where += " and TradeId like '%' + @TradeID + '%'"; } //下单时间段 DateTime? fromTime = QueryPageEntity.FromTime;//开始时间 DateTime? toTime = QueryPageEntity.ToTime;//结束时间 if (fromTime == null && toTime == null) { sqlPage2Where += ""; } else if (fromTime != null && toTime == null) { sqlPage2Where += " and CreateDate between @FromTime and '5000-01-01'"; } else if (fromTime == null && toTime != null) { //因为默认时间2017-09-05 00:00:00:000后面的时钟数是0，所以加上一天，让当日的时间都匹配。 //前端细分到“日”才会写这个，如果时间细分到“时分”这里就不用了加1天了 //(QueryPageEntity.ToTime) = Convert.ToDateTime(ToTime).AddDays(1); sqlPage2Where += " and CreateDate between '1800-01-01' and @ToTime "; } else { //因为默认时间2017-09-05 00:00:00:000后面的时钟数是0，所以加上一天，让当日的时间都匹配。 //前端细分到“日”才会写这个，如果时间细分到“时分”这里就不用了加1天了 //(QueryPageEntity.ToTime) = Convert.ToDateTime(ToTime).AddDays(1); sqlPage2Where += " and CreateDate between @FromTime and @ToTime "; } //微信交易号 if (!RegExp.IsNull(QueryPageEntity.OuterID)) { sqlPage2Where += " and OuterID like '%' + @OuterID + '%'"; } //微信昵称 if (!RegExp.IsNull(QueryPageEntity.NickName)) { sqlPage2Where += " and VCSTL.OpenID in (select OpenID from wxUsers where NickName like '%'+ @NickName+ '%')"; } //解密OpenID if (!RegExp.IsNull(QueryPageEntity.decOpenID)) { sqlPage2Where += " and VCSTL.OpenID=@decOpenID "; } StringBuilder sbSql = new StringBuilder(); var sqlPage1 = @" select * from ( "; //sqlPage2的作用是：可以查询总行数 var sqlPage2 = @" select Row_number() over(order by VCSTL.CreateDate desc ) as rownum, VCSTL.* , WU.NickName,WU.PicUrl,WU.RealName from ValueCardSaveTradeLog as VCSTL left join WxUsers as WU on VCSTL.CardID=WU.CardID and VCSTL.BID=WU.BID where VCSTL.TradeID=VCSTL.TradeID and VCSTL.BID=convert(varchar,@BID) " + sqlPage2Where; //------------中间位置插入条件语句 var 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<ValueCardSaveTradeLogWxUsers> valueCardSaveTradeLogList = _dbBase.Query<ValueCardSaveTradeLogWxUsers>(sbSql.ToString(), QueryPageEntity).ToList(); //查询出结果后继续查行数 if (valueCardSaveTradeLogList != null && valueCardSaveTradeLogList.Count > 0) { response.Result = valueCardSaveTradeLogList; //总行数 recordCount string recordCountSql = "select count(1) from (" + sqlPage2 + ") as A"; int recordCount = _dbBase.Query<int>(recordCountSql, QueryPageEntity).FirstOrDefault(); //总页数(pageCount) = 总行数 / PageSize decimal pageCountDecimal = Math.Ceiling((decimal)recordCount / (decimal)QueryPageEntity.PageSize); int pageCount = Convert.ToInt32(pageCountDecimal); response.Total = recordCount;//总行数 response.PageCount = pageCount;//总页数 response.Status = BaseResponseStatusEnum.Success; response.Msg = "查询成功"; return response; } else { response.Status = BaseResponseStatusEnum.Default; response.Msg = "查询数据为0"; return response; } } catch (Exception ex) { Log4NetHelper.Log(LogTypeEnum.ServicesLog, LogLevelEnum.Error, currentMethodLog + "查询报错", ex); response.Status = BaseResponseStatusEnum.Error; response.Msg = currentMethodLog + "查询报错"; return response; } } #endregion
