如何使用asp.net跨数据库多表联合动态条件查询
如何使用asp.net跨数据库多表联合动态条件查询?针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
成都网络公司-成都网站建设公司创新互联建站十多年经验成就非凡,专业从事网站建设、成都做网站,成都网页设计,成都网页制作,软文发稿,广告投放平台等。十多年来已成功提供全面的成都网站建设方案,打造行业特色的成都网站建设案例,建站热线:18980820575,我们期待您的来电!
本文实例讲述了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); ListsearchInfo = 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 Search(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId") { BLL.Products searchInfoBLL = new BLL.Products(); List 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(); ////// 分页查询,检索页数据 /// /// /// /// 价格排序:0默认,1升序,2降序 ///public List 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 ("SaleId"), Author = list.Field ("SaleAuthor"), PhotoFileName = list.Field ("PhotoFileName"), PhotoFilePathFlag = list.Field ("PhotoFilePathFlag"), Province = list.Field ("Place").Split(' ').First(), SalePrice = list.Field ("SalePrice"), UsingPrice = list.Field ("usingPrice"), Title = list.Field ("SaleTitle").Length > 30 ? list.Field ("SaleTitle").Substring(0, 30) : list.Field ("SaleTitle"), Year = list.Field ("BuildTime").ToString("yyyy") == "1900" ? "" : list.Field ("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 dataList = new List (); dataList.Add(jsonStr); dataList.Add(allCount.ToString()); return dataList; } } }
注:注意观察由DataTable转换为可枚举的可用于Linq查询的方法方式。
DAL
////// 获取检索页数据 /// /// /// ///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 { ////// 原始图片文件夹(用于url地址) /// private const string OriginImagesUrlFolder = "userimages/photos_origin"; ////// 购买页链接 /// public string Url { get; set; } ////// 所属域名(1为fotosay,2为img,3为img1) /// public int PhotoFilePathFlag { get; set; } ////// 图片名称 /// public string PhotoFileName { get; set; } ////// 商品名称 /// public string Title { get; set; } ////// 作者所在省份 /// public string Province { get; set; } ////// 作者 /// public string Author { get; set; } ////// 创作年份 /// public string Year { get; set; } ////// 图片:单次价格 /// public decimal UsingPrice { get; set; } ////// 实物:定价 /// public decimal SalePrice { get; set; } ////// 售价 /// public string Price { get { if (this.UsingPrice > 0) return this.UsingPrice.ToString(); else if (this.SalePrice > 0) return this.SalePrice.ToString(); else return "议价"; } } ////// /// private string MasterSite { get { return ConfigurationManager.AppSettings["masterSite"].ToString(); } } ////// 图片完整路径 /// public string Img { get { return MasterSite + "/" + OriginImagesUrlFolder + this.PhotoFileName + "b.jpg"; } } } }
关于如何使用asp.net跨数据库多表联合动态条件查询问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注创新互联行业资讯频道了解更多相关知识。
名称栏目:如何使用asp.net跨数据库多表联合动态条件查询
网站链接:http://scyanting.com/article/jojgdh.html