如何通过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;
}

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

/// <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;
}

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