首页 > 代码库 > TinyFrame升级之九:实现复杂的查询

TinyFrame升级之九:实现复杂的查询

本章我们主要讲解如何实现一个复杂的查询。由于目前TinyFrame框架已经投入到了实际的项目生产中,所以我很乐意将项目中遇到的任何问题做以记录并备忘。

这章中,我们提到的查询界面如下所示:

image

其中,涉及到联查,单查;对于联查部分,选中”所属区域“后,才会加载”所属基地“内容,选中”所属基地“后,才会加载”所属设备“内容,选中”所属设备“后才会加载”检索参数“内容。其他的项会在页面加载的时候就被填充了。由于复杂的查询要涉及很多表的联查,所以不可能做成通用的,所以在这里,利用代码生成器来生成复杂的查询操作是最靠谱的。这是后话,我们今天主要来看看利用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?>> orderBy
  18:                              , 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:      }
<style></style>

 

然后,下面是其实现部分:

   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?>> orderBy
  36:                                                 , 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:  }
<style></style>

第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:                                                       , page
  34:                                                       , out totalCount
  35:                                                       , c => c.ID != string.Empty
  36:                                                       , c => c.UpdateTime
  37:                                                       , 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:          }
<style></style>

上面代码中:

第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>
<style></style>

 

上面代码中:

第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:                          }
<style></style>

即可实现(注意上边代码中的黄色背景标注部分),在后台只需要通过:

   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"];
<style></style>

即可获取。

最后看看我们的查询结果:

最后附上下载:

百度网盘

微云网盘