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程序设计有所帮助。