asp.net跨資料庫多表聯合動態條件查詢的程式碼範例

這篇文章主要介紹了asp.net實現的MVC跨資料庫多表聯合動態條件查詢功能,結合執行個體形式較為詳細分析了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();        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";      }    }  }}