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

数据库  /  管理员 发布于 6年前   178

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