code example of an ASP. NET cross-database multi-table federated dynamic Condition query

This article mainly introduces the MVC cross-database Multi-table federated dynamic Condition query, which is based on the example form, and analyzes the implementation technique of the multi-database multiple-table joint query function of ASP.

This paper describes the MVC Multi-table federated dynamic Condition query function implemented by ASP. Share to everyone for your reference, as follows:

First, the method 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 to sort in descending order by default, ie to sort in descending order by default

      searchInfo = Search (pageIndex, pageSize, whereText, 1);

      break;

    case "2": // Sales

      searchInfo = Search (pageIndex, pageSize, whereText, 0, "saleTotal");

      break;

    case "3": // Favorite

      searchInfo = Search (pageIndex, pageSize, whereText, 0, "favoritesTotal");

      break;

    case "4": // Price in ascending order

      searchInfo = Search (pageIndex, pageSize, whereText, 1);

      break;

    case "5": // Descending price

      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 () is a method of its own encapsulation for filtering parameter values

Two, the 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>

  /// Pagination query, retrieve page data

  /// </ summary>

  /// <param name = "pageIndex"> </ param>

  /// <param name = "pageSize"> </ param>

  /// <param name = "orderByPrice"> Price order: 0 default, 1 ascending, 2 descending </ 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

    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 ("yyyyyear")

      };

    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: Observe how the DataTable is converted to an enumerable method that can be used for LINQ queries.

DAL

/// <summary>

/// Get the retrieved 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 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);

// The number of records is not enough for a full page, then 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 = 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.categoryry 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: Notice how cross-database queries are used and how union is used

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 picture folder (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 is fotosay, 2 is img, 3 is img1)

  /// </ summary>

  public int PhotoFilePathFlag {get; set;}

  /// <summary>

  /// picture name

  /// </ summary>

  public string PhotoFileName {get; set;}

  /// <summary>

  /// product 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>

  /// Year of creation

  /// </ summary>

  public string Year {get; set;}

  /// <summary>

  /// Picture: Single price

  /// </ summary>

  public decimal UsingPrice {get; set;}

  /// <summary>

  /// kind: pricing

  /// </ summary>

  public decimal SalePrice {get; set;}

  /// <summary>

  /// 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 "negotiate";

    }

  }

  /// <summary>

  ///

  /// </ summary>

  private string MasterSite

  {

    get {return ConfigurationManager.AppSettings ["masterSite"]. ToString ();}

  }

  /// <summary>

  /// full picture path

  /// </ summary>

  public string Img

  {

    get

    {

      return MasterSite + "/" + OriginImagesUrlFolder + this.PhotoFileName + "b.jpg";

    }

  }

}

}