Codebeispiel für eine gemeinsame dynamische Bedingungsabfrage über Datenbanken und mehrere Tabellen in asp.net

In diesem Artikel wird hauptsächlich die von asp.net implementierte datenbankübergreifende gemeinsame Abfragefunktion für mehrere Tabellen vorgestellt. Er analysiert detailliert die Implementierungsfähigkeiten für datenbankübergreifende gemeinsame Abfragefunktionen von asp.net basierend auf MVC Architektur in Form von Beispielen. Freunde können sich auf

beziehen. Dieser Artikel beschreibt die von asp.net implementierte datenbankübergreifende MVC-Funktion zur gemeinsamen dynamischen Bedingungsabfrage. Teilen Sie es als Referenz mit allen. Die Details lauten wie folgt:

1. Methoden in Controller

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

Hinweis: Sicherheit. HtmlHelper.GetQueryString (), StringFilter() ist eine eigene gekapselte Methode, die zum Filtern von Parameterwerten verwendet wird

2. BLL-Layer-Methode

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(&#39; &#39;).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;
    }
  }
}

Hinweis: Achten Sie auf die Konvertierung durch DataTable. Es handelt sich um eine aufzählbare Methode, die für Linq-Abfragen verwendet werden kann.

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

Hinweis: Achten Sie auf die Art der datenbankübergreifenden Abfrage und die Art der Verwendung von Union

Modell

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

Das obige ist der detaillierte Inhalt vonCodebeispiel für eine gemeinsame dynamische Bedingungsabfrage über Datenbanken und mehrere Tabellen in asp.net. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!