asp.net实现的MVC跨数据库多表联合动态条件查询功能示例

本文实例讲述了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（）;elsereturn "议价";}}/// <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程序设计有所帮助。