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
搜索
复制
标签：string,QueryPageEntity,C#,Sqlserver,IsNull,null,response,商城,sqlPage2Where From： https://www.cnblogs.com/jankie1122/p/11118600.html