首页 > 代码库 > TinyFrame升级之九：实现复杂的查询
TinyFrame升级之九：实现复杂的查询
本章我们主要讲解如何实现一个复杂的查询。由于目前TinyFrame框架已经投入到了实际的项目生产中，所以我很乐意将项目中遇到的任何问题做以记录并备忘。
这章中，我们提到的查询界面如下所示：
其中，涉及到联查，单查；对于联查部分，选中”所属区域“后，才会加载”所属基地“内容，选中”所属基地“后，才会加载”所属设备“内容，选中”所属设备“后才会加载”检索参数“内容。其他的项会在页面加载的时候就被填充了。由于复杂的查询要涉及很多表的联查，所以不可能做成通用的，所以在这里，利用代码生成器来生成复杂的查询操作是最靠谱的。这是后话，我们今天主要来看看利用TinyFrame框架如何来实现复杂的查询功能：
首先，我们定义一个查询接口：
1: namespace TinyFrame.Services
2: {3: public interface IMonitorDataQueryService
4: {5: /* string areaid
6: * ,string baseid
7: * ,string etypeid
8: * ,string equipmentid
9: * ,string paramid
10: * ,string start
11: * ,string end
12: * ,string value*/
13: IList<t_monitor_data> GetByPagger(int pageCount
14: , int currentIndex
15: , out int totalCount
16: , Expression<Func<t_monitor_data, bool>> where
17: , Expression<Func<t_monitor_data, DateTime?>> orderBy18: , string areaid = ""
19: , string baseid = ""
20: , string etypeid = ""
21: , string equipmentid = ""
22: , string paramid = ""
23: , string start = ""
24: , string end = ""
25: , string value = ""
26: ); 27: 28: }
然后，下面是其实现部分：
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Text;
5: using TinyFrame.Unitofwork;
6: using TinyFrame.Framework.Caching;
7: using TinyFrame.Framework.Logger;
8: using TinyFrame.Data.DomainModel;
9: using System.Linq.Expressions;
10: 11: namespace TinyFrame.Services
12: {13: public class MonitorDataQueryService:IMonitorDataQueryService
14: {15: public MonitorDataQueryService(
16: IUnitOfWork unitOfWork 17: , ICacheManager cacheManager 18: , ILoggerService logger 19: ) 20: {21: this.unitOfWork = unitOfWork;
22: this.cacheManager = cacheManager;
23: this.logger = logger;
24: }25: private readonly IUnitOfWork unitOfWork;
26: private readonly ICacheManager cacheManager;
27: private readonly ILoggerService logger;
28: 29: private readonly string monitoryDataCacheKey = "T_MONITOR_DATA_{0}-{1}-{2}-{3}-{4}-{5}-{6}-{7}-{8}-{9}-{10}";
30: 31: public IList<t_monitor_data> GetByPagger(int pageCount
32: , int currentIndex
33: , out int totalCount
34: , Expression<Func<t_monitor_data, bool>> where
35: , Expression<Func<t_monitor_data, DateTime?>> orderBy36: , string areaid = ""
37: , string baseid = ""
38: , string etypeid = ""
39: , string equipmentid = ""
40: , string paramid = ""
41: , string start = ""
42: , string end = ""
43: , string value = "")
44: {45: int skipRows = 0;
46: if (currentIndex > 0) skipRows = currentIndex * pageCount;
47: 48: var areaRepo = unitOfWork.Repository<t_base_area>(); 49: var baseRepo = unitOfWork.Repository<t_base>(); 50: var etypeRepo = unitOfWork.Repository<t_monitor_equipment_type>(); 51: var eRepo = unitOfWork.Repository<t_monitor_equipment>(); 52: var paramRepo = unitOfWork.Repository<t_monitor_param>(); 53: 54: var mapRepo = unitOfWork.Repository<t_monitor_map>(); 55: var dataRepo = unitOfWork.Repository<t_monitor_data>(); 56: 57: IQueryable<t_monitor_map> mapQueryable = null;
58: IQueryable<t_monitor_data> dataQueryable = null;
59: 60: if (!string.IsNullOrEmpty(areaid))
61: dataQueryable = dataRepo.GetMany(x=>x.Map.Base.Area_ID==areaid); 62: 63: if (!string.IsNullOrEmpty(baseid))
64: dataQueryable = dataQueryable.Where(x => x.Map.Base_ID == baseid); 65: 66: if (!string.IsNullOrEmpty(etypeid))
67: {68: if (dataQueryable == null)
69: dataQueryable = dataRepo.GetMany(x => x.Map.Equipment.Equipment_TypeID == etypeid);70: else
71: dataQueryable = dataQueryable.Where(x => x.Map.Equipment.Equipment_TypeID == etypeid); 72: } 73: 74: if (!string.IsNullOrEmpty(equipmentid))
75: dataQueryable = dataQueryable.Where(x => x.Map.Equipment.ID == equipmentid); 76: 77: if (!string.IsNullOrEmpty(paramid))
78: dataQueryable = dataQueryable.Where(x => x.Param_ID == paramid); 79: 80: if (!string.IsNullOrEmpty(start) && !string.IsNullOrEmpty(end))
81: { 82: var dtStart = DateTime.Parse(start); 83: var dtEnd = DateTime.Parse(end);84: if (dataQueryable == null)
85: dataQueryable = dataRepo.GetMany(x => x.UpdateTime >= dtStart && x.UpdateTime <= dtEnd);86: else
87: dataQueryable = dataQueryable.Where(x => x.UpdateTime >= dtStart && x.UpdateTime <= dtEnd); 88: }89: else if (!string.IsNullOrEmpty(start) && string.IsNullOrEmpty(end))
90: { 91: var dtStart = DateTime.Parse(start);92: if (dataQueryable == null)
93: dataQueryable = dataRepo.GetMany(x => x.UpdateTime >= dtStart);94: else
95: dataQueryable = dataQueryable.Where(x => x.UpdateTime >= dtStart); 96: }97: else if (string.IsNullOrEmpty(start) && !string.IsNullOrEmpty(end))
98: { 99: var dtEnd = DateTime.Parse(end);100: if (dataQueryable == null)
101: dataQueryable = dataRepo.GetMany(x => x.UpdateTime <= dtEnd);102: else
103: dataQueryable = dataQueryable.Where(x => x.UpdateTime <= dtEnd); 104: } 105: 106: if (!string.IsNullOrEmpty(value))
107: {108: if (dataQueryable != null)
109: dataQueryable = dataQueryable.Where(x => x.Data_Value =http://www.mamicode.com/= value);
110: else
111: dataQueryable = dataRepo.GetMany(x => x.Data_Value =http://www.mamicode.com/= value);
112: } 113: 114: if (IsAllNull(areaid, baseid, etypeid, equipmentid, paramid, start, end, value))
115: dataQueryable = dataRepo.GetMany(x => x.ID != string.Empty);
116: 117: totalCount = dataQueryable.Count(); 118: 119: string key = string.Format(monitoryDataCacheKey, pageCount, currentIndex, totalCount, areaid, baseid, etypeid, equipmentid, paramid, start, end, value);
120: return cacheManager.Get(key, () => dataQueryable.OrderByDescending(orderBy).Skip(skipRows).Take(pageCount).ToList());
121: //return dataQueryable.OrderByDescending(orderBy).Skip(skipRows).Take(pageCount).ToList();
122: } 123: 124: private bool IsAllNull(string areaid, string baseid, string etypeid
125: , string equipmentid, string paramid, string start
126: , string end, string value)
127: {128: if (string.IsNullOrEmpty(areaid) && string.IsNullOrEmpty(baseid) && string.IsNullOrEmpty(etypeid)
129: && string.IsNullOrEmpty(equipmentid) && string.IsNullOrEmpty(paramid) && string.IsNullOrEmpty(start)
130: && string.IsNullOrEmpty(end) && string.IsNullOrEmpty(value)
131: ) 132: {133: return true;
134: }135: return false;
136: } 137: } 138: }第48～55行，主要是获取数据对象操作。由于TinyFrame提供了丰富的数据对象获取行为，所以这里想拿什么对象，就拿什么对象。
第60行及其以后的行，主要是用来判断当前查询中有无值，如果有值，就进行过滤，如果没有值就不进行任何操作。由于Repository中的GetMany方法支持Linq操作，所以这里做过滤的时候，感觉非常轻松，同时由于EF的导航属性，使用起来就更加便捷了。
第114行主要是用来检测参数的，如果用户未选择任何项进行过滤，则默认加载全部数据。
第119行主要是用来定义缓存的Key 。
第120行主要是用来获取数据并返回，如果在Cache中存在数据，则直接命中返回；反之，则从数据库获取后，保存到缓存中，然后返回给前台页面。
最后，在Controller中，我们的调用就简单的多：
1: [HttpPost]2: public JsonResult GetQueryData()
3: {4: int page = 0;
5: int rows = 20;
6: if (!String.IsNullOrEmpty(Request.Form["page"]))
7: page = Int32.Parse(Request.Form["page"]) - 1;
8: if (!String.IsNullOrEmpty(Request.Form["rows"]))
9: rows = Int32.Parse(Request.Form["rows"]);
10: 11: string areaid = string.Empty;
12: string baseid = string.Empty;
13: string etypeid = string.Empty;
14: string equipmentid = string.Empty;
15: string paramid = string.Empty;
16: string start = string.Empty;
17: string end = string.Empty;
18: string value = string.Empty;
19: 20: if (!string.IsNullOrEmpty(Request.Form["areaid"])) areaid = Request.Form["areaid"];
21: if (!string.IsNullOrEmpty(Request.Form["baseid"])) baseid = Request.Form["baseid"];
22: if (!string.IsNullOrEmpty(Request.Form["etypeid"])) etypeid = Request.Form["etypeid"];
23: if (!string.IsNullOrEmpty(Request.Form["equipmentid"])) equipmentid = Request.Form["equipmentid"];
24: if (!string.IsNullOrEmpty(Request.Form["paramid"])) paramid = Request.Form["paramid"];
25: if (!string.IsNullOrEmpty(Request.Form["start"])) start = Request.Form["start"];
26: if (!string.IsNullOrEmpty(Request.Form["end"])) end = Request.Form["end"];
27: if (!string.IsNullOrEmpty(Request.Form["value"])) value = Request.Form["value"];
28: 29: int totalCount = 0;
30: List<t_monitor_data> dataList = null;
31: 32: dataList = dataService.GetByPagger(rows 33: , page34: , out totalCount
35: , c => c.ID != string.Empty
36: , c => c.UpdateTime37: , areaid, baseid, etypeid, equipmentid, paramid, start, end, value).ToList();
38: 39: var items = from p in dataList
40: select new
41: { 42: ID = p.ID, 43: Data_Code = p.Data_Code, 44: Data_Name = p.Data_Name, 45: Data_Order = p.Data_Order, 46: Data_Value = http://www.mamicode.com/p.Data_Value, 47: Map_ID = p.Map_ID, 48: BaseName = p.Map.Base.Base_Name, 49: AreaName = p.Map.Base.Base_Area.Area_Name, 50: Param_ID = p.Param_ID, 51: Param_Name = p.Param.Param_Name, 52: Param_min = p.Param.Param_Min, 53: Param_Max = p.Param.Param_Max, 54: UpdateTime = p.UpdateTime 55: }; 56: 57: var json = new
58: { 59: total = totalCount, 60: rows = items 61: }; 62: JsonResult result = Json(json, JsonRequestBehavior.AllowGet);63: logger.Info(string.Format("dataService.GetByPagger 方法返回了第{0}页的{1}条数据", page, rows));
64: return result;
65: }上面代码中：
第32行，主要是调用我们的方法来返回查询的结果集。
第39行，主要是将当前的实体类进行一下重组，以便于满足前台页面的显示。
第57行，主要是用来构造JSON字串。
第62行及其之后，返回JsonResult到前台。
这样做完之后，Controller就已经把实体类都准备好了，下面部分就是进行前台组织调用了：
1: @{ 2: Layout = "~/Views/Shared/_Layout.cshtml"; 3: } 4: 5: @section scripts{6: <script src="@Url.Content("~/Content/jqueryeasyui/DataGridNoBar.js")" type="text/javascript"></script>
7: <script type="text/javascript">
8: 9: var DataURL = "GetQueryData";
10: var ColumnData = http://www.mamicode.com/[[
11: { field: ‘ID‘, hidden: true },
12: { field: ‘AreaName‘, title: ‘区域名称‘, width: 100 },
13: { field: ‘BaseName‘, title: ‘基地名称‘, width: 100 },
14: { field: ‘Param_Name‘, title: ‘采集参数‘, width: 100 },
15: { field: ‘Param_min‘, title: ‘最小值‘, width: 100 },
16: { field: ‘Param_Max‘, title: ‘最大值‘, width: 100 },
17: { field: ‘Data_Value‘, title: ‘采集值‘, width: 100 },
18: { field: ‘UpdateTime‘, title: ‘更新时间‘, formatter: formatDateTimeJSON, width: 150 }
19: ]]; 20: 21: $(document).ready(function () {
22: AjaxForArea(); 23: AjaxForEquipmentType(); 24: 25: $("#submitQuery").click(function () {
26: AjaxSubmit(); 27: }); 28: }); 29: 30: var AjaxSubmit = function () {
31: var areaid = $("input:hidden[name=baseAreaQuery]").val();
32: var baseid = $("input:hidden[name=‘baseQuery‘]").val();
33: var etypeid = $("input:hidden[name=‘baseEquipmentTypeQuery‘]").val();
34: var equipmentid = $("input:hidden[name=‘baseEquipmentQuery‘]").val();
35: var paramid = $("input:hidden[name=‘baseParamQuery‘]").val();
36: var start = $("input:hidden[name=‘StartQuery‘]").val();
37: var end = $("input:hidden[name=‘EndQuery‘]").val();
38: var value = http://www.mamicode.com/$("input:text[name=‘ValueQuery‘]").val();
39: var query = { areaid: ‘‘ + areaid + ‘‘
40: , baseid: ‘‘ + baseid + ‘‘
41: , etypeid: ‘‘ + etypeid + ‘‘
42: , equipmentid: ‘‘ + equipmentid + ‘‘
43: , paramid: ‘‘ + paramid + ‘‘
44: , start: ‘‘ + start + ‘‘
45: , end: ‘‘ + end + ‘‘
46: , value: ‘‘ + value + ‘‘
47: }; 48: 49: $("#dg").datagrid(‘options‘).queryParams = query; //把查询条件赋值给datagrid内部变量
50: $("#dg").datagrid(‘reload‘); //重新加载
51: } 52: 53: var AjaxForArea = function () {
54: $("#baseAreaQuery").combobox({
55: url: ‘GetAreas‘,
56: method: ‘post‘,
57: valueField: ‘ID‘,
58: textField: ‘Area_Name‘,
59: onSelect: function (rec) {
60: AjaxForBase(rec.ID); 61: } 62: }); 63: } 64: 65: var AjaxForBase = function (areaID) {
66: $("#baseQuery").combobox({
67: url: ‘GetBases?ID=‘+areaID,
68: method: ‘post‘,
69: valueField: ‘ID‘,
70: textField: ‘Base_Name‘,
71: onSelect: function (rec) {
72: AjaxForEquipment(rec.ID); //点选所属基地的时候，出现设备
73: } 74: }); 75: } 76: 77: var AjaxForEquipmentType = function () {
78: $("#baseEquipmentTypeQuery").combobox({
79: url: ‘GetEquipmentTypes‘,
80: method: ‘post‘,
81: valueField: ‘ID‘,
82: textField: ‘Equipment_Type‘
83: 84: }); 85: } 86: 87: var AjaxForEquipment = function (baseID) {
88: $("#baseEquipmentQuery").combobox({
89: url: ‘GetEquipments?baseID=‘ + baseID,
90: method: ‘post‘,
91: valueField: ‘ID‘,
92: textField: ‘Equipment_Name‘,
93: onSelect: function (rec) {
94: AjaxForParam(rec.ID); //点选所属基地的时候，出现设备
95: } 96: }); 97: } 98: 99: var AjaxForParam = function (equipmentID) {
100: $("#baseParamQuery").combobox({
101: url: ‘GetParams?equipmentID=‘ + equipmentID,
102: method: ‘post‘,
103: valueField: ‘ID‘,
104: textField: ‘Param_Name‘
105: }); 106: }107: </script>
108: }109: <div class="GridWrapper">
110: <table title="文本采集数据检索" id="dg"></table>
111: 112: <div id="tb" style="padding:5px;height:auto">
113: <form id="queryForm" action="DaemonDataManagement" >
114: <table id="compositeQuery">
115: <tr>
116: <td>所属区域：</td>
117: <td><input type="" id="baseAreaQuery" name="baseAreaQuery" class="easyui-combobox" style="width:100px;" /></td>
118: <td>所属基地：</td>
119: <td><input type="text" id="baseQuery" name="baseQuery" class="easyui-combobox" style="width:100px;" /></td>
120: <td>设备类型：</td>
121: <td><input type="text" id="baseEquipmentTypeQuery" name="baseEquipmentTypeQuery" class="easyui-combobox" style="width:100px;" /></td>
122: <td>所属设备：</td>
123: <td><input type="text" id="baseEquipmentQuery" name="baseEquipmentQuery" class="easyui-combobox" style="width:100px;" /></td>
124: <td></td>
125: </tr>
126: <tr>
127: <td>检索参数：</td>
128: <td><input type="text" id="baseParamQuery" name="baseParamQuery" class="easyui-combobox" style="width:100px;" /></td>
129: <td>开始时间：</td>
130: <td><input type="text" name="StartQuery" class="easyui-datebox" style="width:100px;" /></td>
131: <td>结束时间：</td>
132: <td><input type="text" name="EndQuery" class="easyui-datebox" style="width:100px;" /></td>
133: <td>数据值：</td>
134: <td><input type="text" id="TValue" name="ValueQuery" style="width:100px;" /></td>
135: <td align="right">
136: <a href="#" id="submitQuery" class="easyui-linkbutton" data-options="iconCls:‘icon-search‘">查询</a>
137: </td></tr></table>
138: </form>
139: </div>
140: </div>
上面代码中：
第53行，主要用来动态加载”所属区域“下拉列表。
第65行，主要用来根据所选的”所属区域“值，来动态加载”所属基地“下拉列表。
第77行，主要用来加载”设备类型”下拉列表。
第87行，主要用来根据所选的“所属基地”值，来动态加载“所属设备”下拉列表。
第99行，主要用来根据所选的“所属设备”值，来动态加载“检索参数”下拉列表。
第30行，主要用来提交组合查询，通过将其组合成JSON串返回给Controller进行控制。
需要注意的是，在EasyUI中，如果想让DataGrid的自定义参数返回给Controller，只需要利用
1: var AjaxSubmit = function () {
2: var areaid = $("input:hidden[name=baseAreaQuery]").val();
3: var baseid = $("input:hidden[name=‘baseQuery‘]").val();
4: var etypeid = $("input:hidden[name=‘baseEquipmentTypeQuery‘]").val();
5: var equipmentid = $("input:hidden[name=‘baseEquipmentQuery‘]").val();
6: var paramid = $("input:hidden[name=‘baseParamQuery‘]").val();
7: var start = $("input:hidden[name=‘StartQuery‘]").val();
8: var end = $("input:hidden[name=‘EndQuery‘]").val();
9: var value = http://www.mamicode.com/$("input:text[name=‘ValueQuery‘]").val();
10: 11: var query = { areaid: ‘‘ + areaid + ‘‘
12: , baseid: ‘‘ + baseid + ‘‘
13: , etypeid: ‘‘ + etypeid + ‘‘
14: , equipmentid: ‘‘ + equipmentid + ‘‘
15: , paramid: ‘‘ + paramid + ‘‘
16: , start: ‘‘ + start + ‘‘
17: , end: ‘‘ + end + ‘‘
18: , value: ‘‘ + value + ‘‘
19: }; 20: 21: $("#dg").datagrid(‘options‘).queryParams = query; //把查询条件赋值给datagrid内部变量
22: $("#dg").datagrid(‘reload‘); //重新加载
23: }即可实现（注意上边代码中的黄色背景标注部分），在后台只需要通过：
1: int page = 0;
2: int rows = 20;
3: if (!String.IsNullOrEmpty(Request.Form["page"]))
4: page = Int32.Parse(Request.Form["page"]) - 1;
5: if (!String.IsNullOrEmpty(Request.Form["rows"]))
6: rows = Int32.Parse(Request.Form["rows"]);
7: 8: string areaid = string.Empty;
9: string baseid = string.Empty;
10: string etypeid = string.Empty;
11: string equipmentid = string.Empty;
12: string paramid = string.Empty;
13: string start = string.Empty;
14: string end = string.Empty;
15: string value = string.Empty;
16: 17: if (!string.IsNullOrEmpty(Request.Form["areaid"])) areaid = Request.Form["areaid"];
18: if (!string.IsNullOrEmpty(Request.Form["baseid"])) baseid = Request.Form["baseid"];
19: if (!string.IsNullOrEmpty(Request.Form["etypeid"])) etypeid = Request.Form["etypeid"];
20: if (!string.IsNullOrEmpty(Request.Form["equipmentid"])) equipmentid = Request.Form["equipmentid"];
21: if (!string.IsNullOrEmpty(Request.Form["paramid"])) paramid = Request.Form["paramid"];
22: if (!string.IsNullOrEmpty(Request.Form["start"])) start = Request.Form["start"];
23: if (!string.IsNullOrEmpty(Request.Form["end"])) end = Request.Form["end"];
24: if (!string.IsNullOrEmpty(Request.Form["value"])) value = Request.Form["value"];
即可获取。
最后看看我们的查询结果：
最后附上下载：
百度网盘
微云网盘
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。