本文实例讲述了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();
        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程序设计有所帮助。

# asp.net  # MVC  # 跨数据库  # 多表  # 联合  # 动态条件  # 查询  # ASP.NET MVC把数据库中枚举项的数字转换成文字  # 使用EF Code First搭建简易ASP.NET MVC网站并允许  # asp.net mvc CodeFirst模式数据库迁移步骤详解  # asp.net mvc 从数据库中读取图片的实现代码  # asp.net MVC 根据菜单树类别不同动态加载视图的实现步骤  # ASP.NET MVC使用jQuery的Load方法加载静态页面及注意事项  # ASP.NET Mvc开发之EF延迟加载  # ASP.NET MVC懒加载如何逐步加载数据库信息  # 升序  # 降序  # 操作技巧  # 相关内容  # 感兴趣  # 给大家  # 分页  # 上架  # 更多关于  # 转换为  # 所述  # 程序设计  # 条数  # 查询功能  # 数据库查询  # 器中  # 可用于  # 讲述了  # jsonStr  # ViewData 

相关栏目： 【 网站优化151355 】 【 网络推广146373 】 【 网络技术251813 】 【 AI营销90571 】

相关推荐： Linux虚拟化技术教程_KVMQEMU虚拟机安装与调优  Windows11怎样设置电源计划_Windows11电源计划调整攻略【指南】  如何获取上海专业网站定制建站电话？  东莞专业网站制作公司有哪些,东莞招聘网站哪个好？  香港代理服务器配置指南：高匿IP选择、跨境加速与SEO优化技巧  齐河建站公司：营销型网站建设与SEO优化双核驱动策略  谷歌Google入口永久地址_Google搜索引擎官网首页永久入口  Win11怎么修改DNS服务器 Win11设置DNS加速网络【指南】  无锡营销型网站制作公司,无锡网选车牌流程？  在线ppt制作网站有哪些软件,如何把网页的内容做成ppt？  香港网站服务器数量如何影响SEO优化效果？  Laravel怎么使用Collection集合方法_Laravel数组操作高级函数pluck与map【手册】  QQ浏览器网页版登录入口 个人中心在线进入  在线教育网站制作平台,山西立德教育官网？  ChatGPT 4.0官网入口地址 ChatGPT在线体验官网  SQL查询语句优化的实用方法总结  大同网页,大同瑞慈医院官网？  JavaScript数据类型有哪些_如何准确判断一个变量的类型  奇安信“盘古石”团队突破 iOS 26.1 提权  Laravel如何监控和管理失败的队列任务_Laravel失败任务处理与监控  晋江文学城电脑版官网 晋江文学城网页版直接进入  JavaScript如何实现倒计时_时间函数如何精确控制  Win11怎么设置默认图片查看器_Windows11照片应用关联设置  Laravel如何实现本地化和多语言支持？（i18n教程）  如何快速查询域名建站关键信息？  Laravel Eloquent：优雅地将关联模型字段扁平化到主模型中  如何用低价快速搭建高质量网站？  Laravel怎么配置.env环境变量_Laravel生产环境敏感数据保护与读取【方法】  C++时间戳转换成日期时间的步骤和示例代码  如何在HTML表单中获取用户输入并用JavaScript动态控制复利计算循环  Laravel如何集成微信支付SDK_Laravel使用yansongda-pay实现扫码支付【实战】  如何制作一个表白网站视频,关于勇敢表白的小标题？  Laravel如何发送邮件和通知_Laravel邮件与通知系统发送步骤  Laravel如何使用Scope本地作用域_Laravel模型常用查询逻辑封装技巧【手册】  mc皮肤壁纸制作器,苹果平板怎么设置自己想要的壁纸我的世界？  网站制作软件免费下载安装,有哪些免费下载的软件网站？  JS中使用new Date(str)创建时间对象不兼容firefox和ie的解决方法(两种)  Python企业级消息系统教程_KafkaRabbitMQ高并发应用  Laravel如何优化应用性能？（缓存和优化命令）  Microsoft Edge如何解决网页加载问题 Edge浏览器加载问题修复  如何在宝塔面板创建新站点？  Laravel distinct去重查询_Laravel Eloquent去重方法  Claude怎样写约束型提示词_Claude约束提示词写法【教程】  Android滚轮选择时间控件使用详解  Windows Hello人脸识别突然无法使用  Laravel Blade组件怎么用_Laravel可复用视图组件的创建与使用  如何基于云服务器快速搭建网站及云盘系统？  Win11搜索栏无法输入_解决Win11开始菜单搜索没反应问题【技巧】  制作网站软件推荐手机版,如何制作属于自己的手机网站app应用？  弹幕视频网站制作教程下载,弹幕视频网站是什么意思？