您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
小编给大家分享一下asp.net如何实现MVC跨数据库多表联合动态条件查询功能,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
具体如下:
一、控制器中方法
[HttpGet] public ActionResult Search() { ViewBag.HeadTitle = "搜索"; ViewBag.MetaKey = "\"123\""; ViewBag.MetaDes = "\"456\""; string whereText = ""; if (Security.HtmlHelper.GetQueryString("first", true) != string.Empty) { whereText += " and a.ParentId='" + StringFilter("first", true)+"'"; } if (Security.HtmlHelper.GetQueryString("second", true) != string.Empty) whereText += " and a.categoryId='" + StringFilter("second",true)+"'"; string valueStr = ""; 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 += " and f.valueId in("+valueStr+")"; } if (Security.HtmlHelper.GetQueryString("searchKeys", true) != string.Empty) whereText += " and a.SaleTitle like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleDes like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleAuthor like '%'" + StringFilter("searchKes", true) + "'%' or a.KeyWords like '%'" + StringFilter("searchKes", true) + "'%' or g.valueProperty like '%'" + StringFilter("searchKes", true) + "'%'"; int pageSize = 50; int pageIndex = HttpContext.Request.QueryString["pageIndex"].Toint(1); List<string> searchInfo = Search(pageIndex, pageSize, whereText, 1); if (Security.HtmlHelper.GetQueryString("sort", true) != string.Empty) { string sort = 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; } } string jsonStr = searchInfo[0]; ViewData["jsondata"] = jsonStr; int allCount = Utility.Toint(searchInfo[1], 0); ViewBag.AllCount = allCount; ViewBag.MaxPages = allCount % pageSize == 0 ? allCount / pageSize : (allCount / pageSize + 1).Toint(1); return View(); } [NonAction] public List<string> Search(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId") { BLL.Products searchInfoBLL = new BLL.Products(); List<string> searchInfo = searchInfoBLL.GetSearchInfo(pageIndex, pageSize, whereText, orderByPrice,orderBy); return searchInfo; }
注:Security.HtmlHelper.GetQueryString(),StringFilter()为自己封装的方法,用于过滤参数值
二、BLL层方法
using System; using System.Web; using System.Web.Caching; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.Common; using System.Web.Script.Serialization; using FotosayMall.Model; using FotosayMall.Common; using System.Text.RegularExpressions; using System.IO; using Newtonsoft.Json; using Newtonsoft.Json.Converters; using FotosayMall.MVC.Models; namespace FotosayMall.BLL { public class Products { private readonly DAL.Products dal = new DAL.Products(); /// <summary> /// 分页查询,检索页数据 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="orderByPrice">价格排序:0默认,1升序,2降序</param> /// <returns></returns> public List<string> GetSearchInfo(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId") { DataSet searchInfoTables = dal.GetSearchInfo(pageIndex, pageSize, whereText); //总记录数 int allCount = Utility.Toint(searchInfoTables.Tables[1].Rows[0]["rowsTotal"], 0); var searchInfo = from list in searchInfoTables.Tables[0].AsEnumerable().OrderByDescending(x => x.Table.Columns[orderBy]) select new SearchModel { 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); else if (orderByPrice == 1) searchInfo = searchInfo.OrderBy(x => x.Price); string jsonStr = JsonConvert.SerializeObject(searchInfo); List<string> dataList = new List<string>(); dataList.Add(jsonStr); dataList.Add(allCount.ToString()); return dataList; } } }
注:注意观察由DataTable转换为可枚举的可用于Linq查询的方法方式。
DAL
/// <summary> /// 获取检索页数据 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></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
using System; using System.Collections.Generic; using System.Configuration; using System.Linq; using System.Web; namespace FotosayMall.MVC.Models { public class SearchModel { /// <summary> /// 原始图片文件夹(用于url地址) /// </summary> private const string OriginImagesUrlFolder = "userimages/photos_origin"; /// <summary> /// 购买页链接 /// </summary> public string Url { get; set; } /// <summary> /// 所属域名(1为fotosay,2为img,3为img1) /// </summary> public int PhotoFilePathFlag { get; set; } /// <summary> /// 图片名称 /// </summary> public string PhotoFileName { get; set; } /// <summary> /// 商品名称 /// </summary> public string Title { get; set; } /// <summary> /// 作者所在省份 /// </summary> public string Province { get; set; } /// <summary> /// 作者 /// </summary> public string Author { get; set; } /// <summary> /// 创作年份 /// </summary> public string Year { get; set; } /// <summary> /// 图片:单次价格 /// </summary> public decimal UsingPrice { get; set; } /// <summary> /// 实物:定价 /// </summary> public decimal SalePrice { get; set; } /// <summary> /// 售价 /// </summary> public string Price { get { if (this.UsingPrice > 0) return this.UsingPrice.ToString(); else if (this.SalePrice > 0) return this.SalePrice.ToString(); else return "议价"; } } /// <summary> /// /// </summary> private string MasterSite { get { return ConfigurationManager.AppSettings["masterSite"].ToString(); } } /// <summary> /// 图片完整路径 /// </summary> public string Img { get { return MasterSite + "/" + OriginImagesUrlFolder + this.PhotoFileName + "b.jpg"; } } } }
以上是“asp.net如何实现MVC跨数据库多表联合动态条件查询功能”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。