Example of the MVC cross-database multi-table joint dynamic condition query function implemented by asp.net, asp. netmvc

Example of the MVC cross-database multi-table joint dynamic condition query function implemented by asp.net, asp. netmvc

The example in this article describes the MVC cross-database multi-table joint dynamic condition query function implemented by asp.net. We will share this with you for your reference. The details are as follows:

I. 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. parentId = '"+ StringFilter (" first ", true) +"' ";} if (Security. htmlHelper. getQueryString ("second", true )! = String. empty) whereText + = "and. 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. saleTitle like '%' "+ StringFilter (" searchKes ", true) +" '%' or. saleDes like '%' "+ StringFilter (" searchKes ", true) +" '%' or. saleAuthor like '%' "+ StringFilter (" searchKes ", true) +" '%' or. 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 ": // by default, searchInfo = Search (pageIndex, pageSize, whereText, 1); break; case "2 ": // sales searchInfo = Search (pageIndex, pageSize, whereText, 0, "saleTotal"); break; case "3": // Add searchInfo = Search (pageIndex, pageSize, whereText, 0, "favoritesTotal"); break; case "4": // searchInfo = Search (pageIn Dex, 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 ;}

Note: Security. HtmlHelper. GetQueryString () and StringFilter () are self-encapsulated methods used to filter parameter values.

Ii. 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 FotosayM All. MVC. models; namespace FotosayMall. BLL {public class Products {private readonly DAL. products dal = new DAL. products (); // <summary> // query by page, retrieve page data /// </summary> /// <param name = "pageIndex"> </param> /// <param name = "pageSize"> </param> /// <param name = "orderByPrice"> price sorting: 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); // The 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: Observe the way that the DataTable can be converted into an enumerative method that can be used for Linq query.

DAL

/// <Summary> /// obtain 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. saleId,. photoId, SaleTitle, SaleAuthor,. status,. categoryId, c. userID, c. userName, B. photo FilePathFlag, 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. saleId) rowsNum "); sqlText. append ("from fotosay .. photo_Sale a join fotosay .. photo_Basic B on. 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. saleId = d. saleId "); sqlText. append ("left join fotosay .. photo_Sale_Tangible e on. saleId = e. saleId "); sqlText. append ("join FotosayMall .. fotomall_Product_Relation f on f. saleId =. saleId "); sqlText. append ("join FotosayMall .. fotomall_Product_PropertyValue g on g. categoryId =. categoryId and g. valueId = f. valueId and g. propertyI D = f. propertyId "); sqlText. append ("join fotosay .. photo_Sale_Property h on. saleId = h. saleId "); sqlText. append ("where. status = 1 "+ whereText +" "); sqlText. append ("group by. saleId,. photoId, SaleTitle, SaleAuthor,. status,. categoryId, c. userID, c. userName, B. photoFilePathFlag, B. photoFileName, e. buildTime, c. place, usingPrice, SalePrice, h. saleTotal, h. favoritesTotal "); sqlText. append (") t where ro WsNum between @ PageSize * (@ PageIndex-1) + 1 and @ PageSize * @ PageIndex; "); sqlText. append ("select count (distinct. 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. photoId = B. photoID join f Otosay .. system_AccountsDescription c on B. userID = c. userID left join fotosay .. photo_Sale_Picture d on. saleId = d. saleId left join fotosay .. photo_Sale_Tangible e on. saleId = e. saleId join FotosayMall .. fotomall_Product_Relation f on f. saleId =. saleId join FotosayMall .. fotomall_Product_PropertyValue g on g. categoryId =. categoryId and g. valueId = f. valueId and g. propertyId = f. propertyId join Fotosay .. photo_Sale_Property h on. saleId = h. saleId where. 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). saleId from fotosay .. photo_Sale a join fotosay .. photo_Basic_History B on. photoId = B. photoID join fotosay .. system_AccountsDescription c on B. userID = c. userID left join fotosay .. photo_Sale_Picture d on. saleId = d. saleId left join fotosay .. photo_Sale_Tangible e on. saleId = e. saleId join FotosayMall .. fotomall_Product_Relation f on f. saleI D =. saleId join FotosayMall .. fotomall_Product_PropertyValue g on g. categoryId =. categoryId and g. valueId = f. valueId and g. propertyId = f. propertyId join fotosay .. photo_Sale_Property h on. saleId = h. saleId where. 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. saleId,. photoId, SaleTitle, SaleAuthor,. status,. 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. 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. photoId = B. photoID join fotosay .. system_AccountsDescription c on B. userID = c. userID "); sqlTextMore. append ("left join fotosay .. photo_Sale_Picture d on. saleId = d. saleId "); sqlTextMore. append ("left join fotosay .. photo_Sale_Tangible e on. saleId = e. saleId "); sqlTextMore. append ("join FotosayMall .. fotomall_Product_Relation f on f. saleId =. saleId "); sqlTextMore. append ("join FotosayMall .. fotomall_Product_PropertyValue g on g. categoryId =. categoryId and g. valueId = f. valueId and g. propertyId = f. propertyId "); sqlTextMore. append ("join fotosay .. photo_Sale_Property h on. saleId = h. saleId "); sqlTextMore. append ("where. status = 1 "+ whereText +" "); sqlTextMore. append ("group by. saleId,. photoId, SaleTitle, SaleAuthor,. status,. 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. 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. photoId = B. photoID join fotosay .. system_AccountsDescription c on B. userID = c. userID left join fotosay .. photo_Sale_Picture d on. saleId = d. saleId left join fotosay .. photo_Sale_Tangible e on. saleId = e. saleId join FotosayMall .. fotomall_Product_Relation f on f. saleId =. saleId join FotosayMall .. fotomall_Product_PropertyValue g on g. categoryId =. categoryId and g. valueId = f. valueId and g. propertyId = f. propertyId join fotosay .. photo_Sale_Property h on. saleId = h. saleId where. status = 1 "+ whereText +"; "); searchInfoList = Fotosay. executeQuery (CommandType. text, sqlTextMore. toString (), parameters) ;}return searchInfoList ;}

Note: The Cross-database query method and the union method are 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 image folder (for url address) /// </summary> private const string OriginImagesUrlFolder = "userimages/photos_origin "; /// <summary> /// link to the purchase page /// </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> /// image name /// </summary> public string PhotoFileName {get; set ;} /// <summary> /// product name /// </summary> public string Title {get; set ;} /// <summary> /// Province where the author is located /// </summary> public string Province {get; set ;} /// <summary> /// Author /// </summary> public string Author {get; set ;} /// <summary> /// creation Year /// </summary> public string Year {get; set ;}/// <summary> /// image: price per time /// </summary> public decimal UsingPrice {get; set ;}/// <summary> // physical object: 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 "price negotiation" ;}/// <summary >///// </summary> private string MasterSite {get {return ConfigurationManager. appSettings ["masterSite"]. toString ();}} /// <summary> /// complete image path /// </summary> public string Img {get {return MasterSite + "/" + OriginImagesUrlFolder + this. photoFileName + "B .jpg ";}}}}