asp.net实现的MVC跨数据库多表联合动态条件查询功能示例
本文实例讲述了asp.net实现的MVC跨数据库多表联合动态条件查询功能。分享给大家供大家参考,具体如下:
一、控制器中方法
[HttpGet] publicActionResultSearch() { ViewBag.HeadTitle="搜索"; ViewBag.MetaKey="\"123\""; ViewBag.MetaDes="\"456\""; stringwhereText=""; if(Security.HtmlHelper.GetQueryString("first",true)!=string.Empty) { whereText+="anda.ParentId='"+StringFilter("first",true)+"'"; } if(Security.HtmlHelper.GetQueryString("second",true)!=string.Empty) whereText+="anda.categoryId='"+StringFilter("second",true)+"'"; stringvalueStr=""; if(Security.HtmlHelper.GetQueryString("theme",true)!=string.Empty) valueStr+=StringFilter("theme",true)+","; if(Security.HtmlHelper.GetQueryString("size",true)!=string.Empty) valueStr+=StringFilter("size",true)+","; if(Security.HtmlHelper.GetQueryString("font",true)!=string.Empty) valueStr+=StringFilter("font",true)+","; if(Security.HtmlHelper.GetQueryString("shape",true)!=string.Empty) valueStr+=StringFilter("shape",true)+","; if(Security.HtmlHelper.GetQueryString("technique",true)!=string.Empty) valueStr+=StringFilter("technique",true)+","; if(Security.HtmlHelper.GetQueryString("category",true)!=string.Empty) valueStr+=StringFilter("category",true)+","; if(Security.HtmlHelper.GetQueryString("place",true)!=string.Empty) valueStr+=StringFilter("place",true)+","; if(Security.HtmlHelper.GetQueryString("price",true)!=string.Empty) valueStr+=StringFilter("price",true)+","; if(valueStr!="") { valueStr=valueStr.Substring(0,valueStr.Length-1); whereText+="andf.valueIdin("+valueStr+")"; } if(Security.HtmlHelper.GetQueryString("searchKeys",true)!=string.Empty) whereText+="anda.SaleTitlelike'%'"+StringFilter("searchKes",true)+"'%'ora.SaleDeslike'%'"+StringFilter("searchKes",true)+"'%'ora.SaleAuthorlike'%'"+StringFilter("searchKes",true)+"'%'ora.KeyWordslike'%'"+StringFilter("searchKes",true)+"'%'org.valuePropertylike'%'"+StringFilter("searchKes",true)+"'%'"; intpageSize=50; intpageIndex=HttpContext.Request.QueryString["pageIndex"].Toint(1); List<string>searchInfo=Search(pageIndex,pageSize,whereText,1); if(Security.HtmlHelper.GetQueryString("sort",true)!=string.Empty) { stringsort=StringFilter("sort",true); switch(sort) { case"1"://综合即默认按照上架时间降序排列即按照id降序 searchInfo=Search(pageIndex,pageSize,whereText,1); break; case"2"://销量 searchInfo=Search(pageIndex,pageSize,whereText,0,"saleTotal"); break; case"3"://收藏 searchInfo=Search(pageIndex,pageSize,whereText,0,"favoritesTotal"); break; case"4"://价格升序 searchInfo=Search(pageIndex,pageSize,whereText,1); break; case"5"://价格降序 searchInfo=Search(pageIndex,pageSize,whereText,2); break; } } stringjsonStr=searchInfo[0]; ViewData["jsondata"]=jsonStr; intallCount=Utility.Toint(searchInfo[1],0); ViewBag.AllCount=allCount; ViewBag.MaxPages=allCount%pageSize==0?allCount/pageSize:(allCount/pageSize+1).Toint(1); returnView(); } [NonAction] publicList<string>Search(intpageIndex,intpageSize,stringwhereText,intorderByPrice,stringorderBy="SaleId") { BLL.ProductssearchInfoBLL=newBLL.Products(); List<string>searchInfo=searchInfoBLL.GetSearchInfo(pageIndex,pageSize,whereText,orderByPrice,orderBy); returnsearchInfo; }
注:Security.HtmlHelper.GetQueryString(),StringFilter()为自己封装的方法,用于过滤参数值
二、BLL层方法
usingSystem; usingSystem.Web; usingSystem.Web.Caching; usingSystem.Collections; usingSystem.Collections.Generic; usingSystem.Linq; usingSystem.Text; usingSystem.Data; usingSystem.Data.Common; usingSystem.Web.Script.Serialization; usingFotosayMall.Model; usingFotosayMall.Common; usingSystem.Text.RegularExpressions; usingSystem.IO; usingNewtonsoft.Json; usingNewtonsoft.Json.Converters; usingFotosayMall.MVC.Models; namespaceFotosayMall.BLL { publicclassProducts { privatereadonlyDAL.Productsdal=newDAL.Products(); ///<summary> ///分页查询,检索页数据 ///</summary> ///<paramname="pageIndex"></param> ///<paramname="pageSize"></param> ///<paramname="orderByPrice">价格排序:0默认,1升序,2降序</param> ///<returns></returns> publicList<string>GetSearchInfo(intpageIndex,intpageSize,stringwhereText,intorderByPrice,stringorderBy="SaleId") { DataSetsearchInfoTables=dal.GetSearchInfo(pageIndex,pageSize,whereText); //总记录数 intallCount=Utility.Toint(searchInfoTables.Tables[1].Rows[0]["rowsTotal"],0); varsearchInfo=fromlistinsearchInfoTables.Tables[0].AsEnumerable().OrderByDescending(x=>x.Table.Columns[orderBy]) selectnewSearchModel { Url="/home/products?saleId="+list.Field<int>("SaleId"), Author=list.Field<string>("SaleAuthor"), PhotoFileName=list.Field<string>("PhotoFileName"), PhotoFilePathFlag=list.Field<int>("PhotoFilePathFlag"), Province=list.Field<string>("Place").Split('').First(), SalePrice=list.Field<decimal>("SalePrice"), UsingPrice=list.Field<decimal>("usingPrice"), Title=list.Field<string>("SaleTitle").Length>30?list.Field<string>("SaleTitle").Substring(0,30):list.Field<string>("SaleTitle"), Year=list.Field<DateTime>("BuildTime").ToString("yyyy")=="1900"?"":list.Field<DateTime>("BuildTime").ToString("yyyy年") }; if(orderByPrice==2) searchInfo=searchInfo.OrderByDescending(x=>x.Price); elseif(orderByPrice==1) searchInfo=searchInfo.OrderBy(x=>x.Price); stringjsonStr=JsonConvert.SerializeObject(searchInfo); List<string>dataList=newList<string>(); dataList.Add(jsonStr); dataList.Add(allCount.ToString()); returndataList; } } }
注:注意观察由DataTable转换为可枚举的可用于Linq查询的方法方式。
DAL
///<summary> ///获取检索页数据 ///</summary> ///<paramname="pageIndex"></param> ///<paramname="pageSize"></param> ///<returns></returns> publicDataSetGetSearchInfo(intpageIndex,intpageSize,stringwhereText) { StringBuildersqlText=newStringBuilder(); sqlText.Append("select*from("); sqlText.Append("selecta.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(orderbya.saleId)rowsNum"); sqlText.Append("fromfotosay..Photo_Saleajoinfotosay..Photo_Basicbona.PhotoId=b.PhotoID"); sqlText.Append("joinfotosay..System_AccountsDescriptionconb.UserID=c.UserID"); sqlText.Append("leftjoinfotosay..Photo_Sale_Picturedona.SaleId=d.SaleId"); sqlText.Append("leftjoinfotosay..Photo_Sale_Tangibleeona.saleId=e.saleId"); sqlText.Append("joinFotosayMall..Fotomall_Product_Relationfonf.saleId=a.SaleId"); sqlText.Append("joinFotosayMall..Fotomall_Product_PropertyValuegong.categoryId=a.categoryIdandg.valueId=f.valueIdandg.propertyId=f.propertyId"); sqlText.Append("joinfotosay..Photo_Sale_Propertyhona.saleId=h.saleId"); sqlText.Append("wherea.Status=1"+whereText+""); sqlText.Append("groupbya.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(")twhererowsNumbetween@PageSize*(@PageIndex-1)+1and@PageSize*@PageIndex;"); sqlText.Append("selectcount(distincta.saleId)rowsTotalfromfotosay..Photo_Saleajoin(selectb1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoIDfromfotosay..Photo_Basicb1unionselectb2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoIDfromfotosay..Photo_Basic_Historyb2)bona.PhotoId=b.PhotoIDjoinfotosay..System_AccountsDescriptionconb.UserID=c.UserIDleftjoinfotosay..Photo_Sale_Picturedona.SaleId=d.SaleIdleftjoinfotosay..Photo_Sale_Tangibleeona.saleId=e.saleIdjoinFotosayMall..Fotomall_Product_Relationfonf.saleId=a.SaleIdjoinFotosayMall..Fotomall_Product_PropertyValuegong.categoryId=a.categoryIdandg.valueId=f.valueIdandg.propertyId=f.propertyIdjoinfotosay..Photo_Sale_Propertyhona.saleId=h.saleIdwherea.Status=1"+whereText+";"); DbParameter[]parameters={ Fotosay.CreateInDbParameter("@PageIndex",DbType.Int32,pageIndex), Fotosay.CreateInDbParameter("@PageSize",DbType.Int32,pageSize) }; DataSetsearchInfoList=Fotosay.ExecuteQuery(CommandType.Text,sqlText.ToString(),parameters); //记录条数不够一整页,则查历史库 if(searchInfoList.Tables[0].Rows.Count<pageSize) { stringsql="selecttop(1)a.saleIdfromfotosay..Photo_Saleajoinfotosay..Photo_Basic_Historybona.PhotoId=b.PhotoIDjoinfotosay..System_AccountsDescriptionconb.UserID=c.UserIDleftjoinfotosay..Photo_Sale_Picturedona.SaleId=d.SaleIdleftjoinfotosay..Photo_Sale_Tangibleeona.saleId=e.saleIdjoinFotosayMall..Fotomall_Product_Relationfonf.saleId=a.SaleIdjoinFotosayMall..Fotomall_Product_PropertyValuegong.categoryId=a.categoryIdandg.valueId=f.valueIdandg.propertyId=f.propertyIdjoinfotosay..Photo_Sale_Propertyhona.saleId=h.saleIdwherea.Status=1"+whereText+";"; DataSetds=Fotosay.ExecuteQuery(CommandType.Text,sql.ToString(),parameters); if(ds!=null&&ds.Tables[0].Rows.Count>0) { StringBuildersqlTextMore=newStringBuilder(); sqlTextMore.Append("select*from("); sqlTextMore.Append("selecta.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(orderbya.saleId)rowsNum"); sqlTextMore.Append("fromfotosay..Photo_Salea"); sqlTextMore.Append("join(selectb1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoIDfromfotosay..Photo_Basicb1unionselectb2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoIDfromfotosay..Photo_Basic_Historyb2)bona.PhotoId=b.PhotoIDjoinfotosay..System_AccountsDescriptionconb.UserID=c.UserID"); sqlTextMore.Append("leftjoinfotosay..Photo_Sale_Picturedona.SaleId=d.SaleId"); sqlTextMore.Append("leftjoinfotosay..Photo_Sale_Tangibleeona.saleId=e.saleId"); sqlTextMore.Append("joinFotosayMall..Fotomall_Product_Relationfonf.saleId=a.SaleId"); sqlTextMore.Append("joinFotosayMall..Fotomall_Product_PropertyValuegong.categoryId=a.categoryIdandg.valueId=f.valueIdandg.propertyId=f.propertyId"); sqlTextMore.Append("joinfotosay..Photo_Sale_Propertyhona.saleId=h.saleId"); sqlTextMore.Append("wherea.Status=1"+whereText+""); sqlTextMore.Append("groupbya.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(")twhererowsNumbetween@PageSize*(@PageIndex-1)+1and@PageSize*@PageIndex;"); sqlTextMore.Append("selectcount(distincta.saleId)rowsTotalfromfotosay..Photo_Saleajoin(selectb1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoIDfromfotosay..Photo_Basicb1unionselectb2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoIDfromfotosay..Photo_Basic_Historyb2)bona.PhotoId=b.PhotoIDjoinfotosay..System_AccountsDescriptionconb.UserID=c.UserIDleftjoinfotosay..Photo_Sale_Picturedona.SaleId=d.SaleIdleftjoinfotosay..Photo_Sale_Tangibleeona.saleId=e.saleIdjoinFotosayMall..Fotomall_Product_Relationfonf.saleId=a.SaleIdjoinFotosayMall..Fotomall_Product_PropertyValuegong.categoryId=a.categoryIdandg.valueId=f.valueIdandg.propertyId=f.propertyIdjoinfotosay..Photo_Sale_Propertyhona.saleId=h.saleIdwherea.Status=1"+whereText+";"); searchInfoList=Fotosay.ExecuteQuery(CommandType.Text,sqlTextMore.ToString(),parameters); } } returnsearchInfoList; }
注:注意其中使用的跨数据库查询的方式和union的一种使用方式
Model
usingSystem; usingSystem.Collections.Generic; usingSystem.Configuration; usingSystem.Linq; usingSystem.Web; namespaceFotosayMall.MVC.Models { publicclassSearchModel { ///<summary> ///原始图片文件夹(用于url地址) ///</summary> privateconststringOriginImagesUrlFolder="userimages/photos_origin"; ///<summary> ///购买页链接 ///</summary> publicstringUrl{get;set;} ///<summary> ///所属域名(1为fotosay,2为img,3为img1) ///</summary> publicintPhotoFilePathFlag{get;set;} ///<summary> ///图片名称 ///</summary> publicstringPhotoFileName{get;set;} ///<summary> ///商品名称 ///</summary> publicstringTitle{get;set;} ///<summary> ///作者所在省份 ///</summary> publicstringProvince{get;set;} ///<summary> ///作者 ///</summary> publicstringAuthor{get;set;} ///<summary> ///创作年份 ///</summary> publicstringYear{get;set;} ///<summary> ///图片:单次价格 ///</summary> publicdecimalUsingPrice{get;set;} ///<summary> ///实物:定价 ///</summary> publicdecimalSalePrice{get;set;} ///<summary> ///售价 ///</summary> publicstringPrice { get { if(this.UsingPrice>0) returnthis.UsingPrice.ToString(); elseif(this.SalePrice>0) returnthis.SalePrice.ToString(); else return"议价"; } } ///<summary> /// ///</summary> privatestringMasterSite { get{returnConfigurationManager.AppSettings["masterSite"].ToString();} } ///<summary> ///图片完整路径 ///</summary> publicstringImg { get { returnMasterSite+"/"+OriginImagesUrlFolder+this.PhotoFileName+"b.jpg"; } } } }
更多关于asp.net相关内容感兴趣的读者可查看本站专题:《asp.net优化技巧总结》、《asp.net字符串操作技巧汇总》、《asp.net操作XML技巧总结》、《asp.net文件操作技巧汇总》、《asp.netajax技巧总结专题》及《asp.net缓存操作技巧总结》。
希望本文所述对大家asp.net程序设计有所帮助。