sqlserver跨库分页模糊查询

 sqlserver跨库分页模糊查询

============================================================

关键代码：

select * from PercenTageRt where goodsID in (1000001,1000002) 
--结果两条数据1000001,1000002
select * from PercenTageRt where goodsID in (1000001) and goodsid in (1000001,1000002)
--结果一条数据1000001

 

============================================================

#region----查询，根据GoodsID和Name分页查询PercenTageRt提成配置表单品配置列表----
        /// <summary>
        /// 查询，根据GoodsID和Name分页查询PercenTageRt提成配置表单品配置列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<BasePageResponse<PercenTageRtResponse>> GetRtGoodsPercenTagePageAsync(PercenTageRtGoodsPageRequest request)
        {
            var response = new BasePageResponse<PercenTageRtResponse>();
            string currentMethodLog = "[GetRtGoodsPercenTagePageAsync()]提成配置，";
            _mulDbBase.InitialiseConnectionString(request.SqlConn);
            try
            {
                StringBuilder sbSqlPage2And = new StringBuilder();
                //商品ID
                if (request.GoodsID != null)
                {
                    sbSqlPage2And.Append(" and pt.GoodsID like '%' + @GoodsID + '%' ");
                }
                //商品名字
                if (request.Name != null)
                {
                    string sqlRtName = @" 
                    declare 
                    @Str varchar(max)
                     set @Str=( select (','+ convert(varchar(20), goodsID)) from Goods where Name like '%' + @Name + '%' for xml path(''))
                    select substring( @Str,2,len(@Str)) ";
                    var resultRtName = await _mulDbBase.QueryFirstOrDefaultAsync<string>(sqlRtName, new { Name = request.Name });
                    sbSqlPage2And.Append(" and pt.goodsID in (" + resultRtName + ") ");
                }

                //string ipPort = string.Empty;
                //string loginName = string.Empty;
                //string loginPassword = string.Empty;
                //string sqlBuss = @" select SysURL+','+convert(varchar(50),SysURLComPort) as IpPort,DBUid as LoginName,DbPwd as LoginPassword 
                //                    from BusinessInfo where ID=@BID ";
                //var resultBuss = await _dbBase.QueryFirstOrDefaultAsync<GetBusinessInfoResponse>(sqlBuss, new { BID = request.BID });
                //if (resultBuss == null)
                //{
                //    response.Status = BaseResponseStatusEnum.Error;
                //    response.Msg = currentMethodLog + "没有查询到零售数据库登录数据";
                //    return response;
                //}
                //else
                //{
                //    ipPort = resultBuss.IpPort;
                //    loginName = resultBuss.LoginName;
                //    loginPassword = resultBuss.LoginPassword;
                //}

                StringBuilder sbSql = new StringBuilder();
                string ptTypeGoods = PercenTagePtTypeEnum.goods.ToString();
                string sqlPage1 = " select * from ( ";
                //sqlPage2的作用是：可以查询总行数
                string sqlPage2 = @"
                                    select Row_number() over(order by  pt.UpDateTime) as rownum,
                                    pt.GoodsID,pt.Percent1,pt.Percent2,pt.IsValite                                   
                                    from PercenTageRt as pt                            
                                    where pt.BID=@BID and pt.PtType='" + ptTypeGoods + "' "
                                  + 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);

                //执行查询
                var percenTageList = await _dbBase.QueryAsync<PercenTageRtResponse>(sbSql.ToString(),
                  new
                  {
                      //IpPort = ipPort,
                      //LoginName = loginName,
                      //LoginPassword = loginPassword,
                      BID = request.BID,
                      GoodsID = request.GoodsID,
                      Name = request.Name,
                      PageNumber = request.PageNumber,
                      PageSize = request.PageSize
                  });

                //查询出结果后继续查询行数
                if (percenTageList != null && percenTageList.Count() > 0)
                {
                    //查询零售系统产品
                    foreach (var item in percenTageList)
                    {
                        int index = percenTageList.ToList().IndexOf(item);
                        string sqlRtGoods = " select Name,Price,MemberPrice from Goods where GoodsID=@GoodsID ";
                        var resultRtGoods = await _mulDbBase.QueryFirstOrDefaultAsync<RetailGoods>(sqlRtGoods, new { GoodsID = item.GoodsID });
                        if (resultRtGoods == null)
                        {
                            resultRtGoods = new RetailGoods();
                            resultRtGoods.Name = null;
                            resultRtGoods.Price = 0;
                            resultRtGoods.MemberPrice = null;
                        }
                        percenTageList.ToList()[index].Name = resultRtGoods.Name;
                        percenTageList.ToList()[index].Price = resultRtGoods.Price;
                        percenTageList.ToList()[index].MemberPrice = resultRtGoods.MemberPrice;
                    }

                    response.Result = new List<PercenTageRtResponse>();
                    response.Result = percenTageList.ToList();
                    //总行数
                    string totalCountSql = " select count(1) from (" + sqlPage2 + ") as A ";
                    var totalCount = await _dbBase.QueryFirstOrDefaultAsync<int>(totalCountSql, request);
                    //总页数（pageCount）=总行数（totalCount）/每页大小（PageSize）
                    decimal PageCountDecimal = Math.Ceiling((decimal)totalCount / request.PageSize);
                    int pageCount = Convert.ToInt32(PageCountDecimal);

                    response.Total = totalCount;//总行数
                    response.PageCount = pageCount;//总页数
                }
                if (percenTageList == null || percenTageList.Count() == 0)
                {
                    response.Status = BaseResponseStatusEnum.Default;
                    response.Msg = "查询配置成功，数据为0";
                    response.Result = new List<PercenTageRtResponse>();
                    return response;
                }

                response.Status = BaseResponseStatusEnum.Success;
                response.Msg = "查询成功";
                return response;
            }
            catch (Exception ex)
            {
                Log4NetHelper.Log(LogTypeEnum.ServicesLog, LogLevelEnum.Error,
                  currentMethodLog + "查询分页报错", ex);
                response.Status = BaseResponseStatusEnum.Error;
                response.Msg = currentMethodLog + "查询分页报错:" + ex.Message;
                return response;
            }
        }
        #endregion

 

搜索

复制