跨数据库多表联合动态条件查询

一、控制器中方法

[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";            }        }    }}