/// <summary> /// 获取检索页数据 /// </summary> /// <param></param> /// <param></param> /// <returns></returns> public DataSet GetSearchInfo(int pageIndex, int pageSize, string whereText) { StringBuilder sqlText = new StringBuilder(); sqlText.Append("select * from ("); sqlText.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum "); sqlText.Append("from fotosay..Photo_Sale a join fotosay..Photo_Basic b on a.PhotoId = b.PhotoID "); sqlText.Append("join fotosay..System_AccountsDescription c on b.UserID = c.UserID "); sqlText.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId "); sqlText.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId "); sqlText.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId "); sqlText.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId "); sqlText.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId "); sqlText.Append("where a.Status=1 " + whereText + " "); sqlText.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal "); sqlText.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex;"); sqlText.Append("select count(distinct a.saleId) rowsTotal from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";"); DbParameter[] parameters = { Fotosay.CreateInDbParameter("@PageIndex", DbType.Int32,pageIndex), Fotosay.CreateInDbParameter("@PageSize", DbType.Int32,pageSize) }; DataSet searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlText.ToString(), parameters); //记录条数不够一整页,则查历史库 if (searchInfoList.Tables[0].Rows.Count < pageSize) { string sql = "select top(1) a.saleId from fotosay..Photo_Sale a join fotosay..Photo_Basic_History b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";"; DataSet ds = Fotosay.ExecuteQuery(CommandType.Text, sql.ToString(), parameters); if (ds != null && ds.Tables[0].Rows.Count > 0) { StringBuilder sqlTextMore = new StringBuilder(); sqlTextMore.Append("select * from ("); sqlTextMore.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum "); sqlTextMore.Append("from fotosay..Photo_Sale a "); sqlTextMore.Append("join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID "); sqlTextMore.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId "); sqlTextMore.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId "); sqlTextMore.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId "); sqlTextMore.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId "); sqlTextMore.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId "); sqlTextMore.Append("where a.Status=1 " + whereText + " "); sqlTextMore.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal"); sqlTextMore.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex;"); sqlTextMore.Append("select count(distinct a.saleId) rowsTotal from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";"); searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlTextMore.ToString(), parameters); } } return searchInfoList; }
注：注意其中使用的跨数据库查询的方式和union的一种使用方式
Model