Of Stack

Example of MVC cross database multi table joint dynamic conditional query function implemented by asp. net

  • 2021-09-11 19:56:42
  • OfStack

In this paper, an example of asp. net implementation of MVC cross-database multi-table joint dynamic condition query function. Share it for your reference, as follows:

1. Methods in the controller 


[HttpGet]
public ActionResult Search()
{
  ViewBag.HeadTitle = " Search ";
  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":  // Comprehensive means that it is arranged in descending order according to the shelf time by default, that is, according to id Descending order 
        searchInfo = Search(pageIndex, pageSize, whereText, 1);
        break;
      case"2":  // Sales volume 
        searchInfo = Search(pageIndex, pageSize, whereText,0, "saleTotal");
        break;
      case "3":  // Collection 
        searchInfo = Search(pageIndex, pageSize, whereText,0, "favoritesTotal");
        break;
      case "4":  // Price ascending order 
        searchInfo = Search(pageIndex, pageSize, whereText,1);
        break;
      case "5":  // Price descending order 
        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;
}

Note: Security. HtmlHelper. GetQueryString (), StringFilter () are self-encapsulated methods for filtering parameter values

2. BLL layer method 


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>
    ///  Paging query to retrieve page data 
    /// </summary>
    /// <param name="pageIndex"></param>
    /// <param name="pageSize"></param>
    /// <param name="orderByPrice"> Price ordering: 0 By default, 1 Ascending order, 2 Descending order </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);
      // Total number of records 
      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 Year ")
        };
      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;
    }
  }
}

Note: Note how DataTable is converted to enumerable methods that can be used in Linq queries.

DAL 


/// <summary>
///  Get retrieval page data 
/// </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);
  // Insufficient number of records 1 Full page , Check the history library 
  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;
}

Note: Pay attention to the cross-database query method and one usage method of 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>
    ///  Original Pictures Folder ( Used for url Address )
    /// </summary>
    private const string OriginImagesUrlFolder = "userimages/photos_origin";
    /// <summary>
    ///  Purchase page link 
    /// </summary>
    public string Url { get; set; }
    /// <summary>
    ///  Domain name (1 For fotosay , 2 For img , 3 For img1)
    /// </summary>
    public int PhotoFilePathFlag { get; set; }
    /// <summary>
    ///  Picture name 
    /// </summary>
    public string PhotoFileName { get; set; }
    /// <summary>
    ///  Commodity name 
    /// </summary>
    public string Title { get; set; }
    /// <summary>
    ///  Author's province 
    /// </summary>
    public string Province { get; set; }
    /// <summary>
    ///  Author 
    /// </summary>
    public string Author { get; set; }
    /// <summary>
    ///  Creative year 
    /// </summary>
    public string Year { get; set; }
    /// <summary>
    ///  Picture: Single price 
    /// </summary>
    public decimal UsingPrice { get; set; }
    /// <summary>
    ///  In kind: pricing 
    /// </summary>
    public decimal SalePrice { get; set; }
    /// <summary>
    ///  Selling price 
    /// </summary>
    public string Price
    {
      get
      {
        if (this.UsingPrice > 0)
          return this.UsingPrice.ToString();
        else if (this.SalePrice > 0)
          return this.SalePrice.ToString();
        else
          return " Bargaining ";
      }
    }
    /// <summary>
    ///
    /// </summary>
    private string MasterSite
    {
      get { return ConfigurationManager.AppSettings["masterSite"].ToString(); }
    }
    /// <summary>
    ///  Picture full path 
    /// </summary>
    public string Img
    {
      get
      {
        return MasterSite + "/" + OriginImagesUrlFolder + this.PhotoFileName + "b.jpg";
      }
    }
  }
}

For more readers interested in asp. net, please check the topics on this site: "Summary of asp. net Optimization Skills", "Summary of asp. net String Operation Skills", "Summary of XML Operation Skills", "Summary of asp. net File Operation Skills", "Summary of asp. net ajax Skills" and "Summary of asp. net Cache Operation Skills".

I hope this paper is helpful to everyone's asp.net programming.

Related articles: