本文实例讲述了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 "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" + 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 "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相关内容感兴趣的读者可查看本站专题:《asp.net优化技巧总结》、《asp.net字符串操作技巧汇总》、《asp.net操作XML技巧总结》、《asp.net文件操作技巧汇总》、《asp.net ajax技巧总结专题》及《asp.net缓存操作技巧总结》。
希望本文所述对大家asp.net程序设计有所帮助。
华山资源网 Design By www.eoogi.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
华山资源网 Design By www.eoogi.com
暂无评论...
P70系列延期,华为新旗舰将在下月发布
3月20日消息,近期博主@数码闲聊站 透露,原定三月份发布的华为新旗舰P70系列延期发布,预计4月份上市。
而博主@定焦数码 爆料,华为的P70系列在定位上已经超过了Mate60,成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢?
根据目前爆料的消息来看,华为P70系列将推出三个版本,其中P70和P70 Pro采用了三角形的摄像头模组设计,而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智,但辨识度绝对拉满。
更新日志
2024年09月26日
2024年09月26日
- 纯音入心系列纯音乐《中央民族乐团-古筝传奇》1CD[MP3][253.1MB]
- 江玲.1989-这样你才爱我,是吗?【新作有声】【WAV+CUE】
- 郭美美.2010-我是郭美美【华纳】【WAV+CUE】
- 黄心懋1991-轻忧郁【滚石】【WAV+CUE】
- 群星.2000-大地金曲世纪回顾2CD【大地】【WAV+CUE】
- 王菲.1996-浮躁(2024环球MQA-UHQCD限量版)【环球】【WAV+CUE】
- 群星.2022-传家电视剧原声带【东阳欢娱】【FLAC分轨】
- 《摇滚教父 伍佰黄金精选 2CD》 [WAV+CUE][1GB]
- 《凤凰传奇 我从草原来》[WAV/分轨][400MB]
- 《叶倩文 真心真意过一生》[WAV+CUE][400MB]
- 潘越云.1985-世间女子(滚石25周年经典复刻版)【滚石】【WAV+CUE】
- 陈柏宇.2009-CANT.BE.HALF【SONY】【WAV+CUE】
- 李明依.1989-小女生【滚石】【WAV+CUE】
- 《李荣浩 新时代创作新人王》[WAV分轨][310MB]
- 《黑鸭子与马头琴 史上最动听草原音乐》[WAV+CUE][320MB]