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;