本文实例讲述了asp.net实现的MVC跨数据库多表联合动态条件查询功能。分享给大家供大家参考，具体如下：
一、控制器中方法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
[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层方法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
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(' ').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; } }} |
注：注意观察由DataTable转换为可枚举的可用于Linq查询的方法方式。
DAL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
/// <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;} |
注：注意其中使用的跨数据库查询的方式和union的一种使用方式
Model
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
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"; } } }} |
希望本文所述对大家asp.net程序设计有所帮助。
