突发奇想-关于列表页面查询条件自动组装的实现
此方法不仅使用于ASP.NET WEB FORM ,而且适用于ASP.NET MVC。
当列表页面需要大量的查询条件，你该怎么办？
你是否厌倦了一个个查询条件的拼接？
你是否在想能不能有一种工具让这些查询条件自动组装？
今天，联想到ASP.NET MVC的模型绑定以及前台验证的实现思想，突发奇想，想要开发一个能够自动组装查询条件并且自动拼接成sql查询条件的工具。
原始方法的实现
第一步:列表页面查询表单的建立
由于本人几乎不会拖拽服务端框架，以前在解决列表页面多条件搜索的的实现方法是，通过js将查询条件极其值通过js拼接成一个url后缀的ur，然后通过get方式提交到后台，然后再才拆卸url后缀，最后手动拼接sql语句，其过程真是十分繁琐无味。
以前的做法前台代码如下所示：
l
<script type="text/javascript"> $(function () { $("#search").click(function(){ //url的拼接 window.location.href="<%=ListUrl%>?ddlsuggestionType="+$("#ddlsuggestionType").val()+"&key="+$("#txtkey").val()+"&txtFrom="+$("#txtFrom").val()+"&txtTo="+$("#txtTo").val(); }); }) </script> </head> <body> <form id="form1" runat="server"> <div class="alert alert-info">当前位置<b class='tip'></b><%=ListTitle %></div> <table class="tb"> <tr height="45"> <th>意见分类:<select id="ddlsuggestionType" class="ipt width200 " runat="server"></select> 关键字:<input type="text" id="txtkey" runat="server" class="ipt width200 " /> 发布人登录名:<input type="text" id="txtFrom" runat="server" class="ipt width200 " /> 接收人登录名:<input type="text" id="txtTo" runat="server" class="ipt width200 " /> <input type="button" id="search" class="btn" value="搜索" /> </th> </tr> <tr height="45"> <th> <input type="button" class="btn btn-primary" onclick='window.location.href="<%=EditUrl %> ";' value="添加" /> <input type="button" class="btn" onclick='window.location.href=window.location.href;' value="刷新" /> </th> </tr> </table> <table class="tb" id="list">
第二步：url参数的拆卸以及sql条件的拼接
后台代码如下：
1 //拆卸url参数 2 private void BindData() 3 { 4 GetSuggestionTypeList(); 5 var suggestionType = Request.QueryString["ddlsuggestionType"]; 6 var key = Request.QueryString["key"]; 7 var txtFrom = Request.QueryString["txtFrom"]; 8 var txtTo = Request.QueryString["txtTo"]; 9 var userType = GetUser().UserType; 10 var entitys = BLL.BLLSession.Info_SuggestionBLL.GetSearchRptEntitys(suggestionType, key, userType,GetUser().Id,txtFrom,txtTo, Pager.CurrentPageIndex, Pager.PageSize, out RowCount, out PageCount); 11 BindRptData(rptList, entitys); 12 Pager.RecordCount = RowCount; 13 ddlsuggestionType.Value = suggestionType; 14 txtkey.Value = key; 15 } 16 17 //sql的拼接 18 public List<Info_Suggestion> GetSearchRptEntitys(string suggestionType,string key,int userType,int userid,string txtFrom,string txtTo,int PageIndex, int PageSize, out int RowCount, out int PageCount) 19 { 20 string where = " and Deleted=0"; 21 if (!string.IsNullOrEmpty(suggestionType) && suggestionType != "-1") 22 { 23 where += " and SuggestionTypeId=" + suggestionType; 24 } 25 if (!string.IsNullOrEmpty(key)) 26 { 27 where += " and KeyWord like '%" + key.Replace("'", "") + "%'"; 28 } 29 if (userType != 1)//管理员可以查看所有的 30 { 31 where += " and (FromUser=" + userid + " or ToUser=" + userid + ")"; 32 } 33 if (!string.IsNullOrEmpty(txtFrom)) 34 { 35 where += " and FromUsrName='" + txtFrom.Replace("'", "") + "'"; 36 } 37 if (!string.IsNullOrEmpty(txtTo)) 38 { 39 where += " and ToUserName='" + txtTo.Replace("'", "") + "'"; 40 } 41 List<Model.Info_Suggestion> entitys = null; 42 entitys = GetPagedEntitys(PageIndex, PageSize, out RowCount, out PageCount, null, where, "Id desc"); 43 return entitys; 44 }
现在我想做的是让前台url自动组装，后台自动拆卸url参数，并且自动拼接sql语句
列表页面查询条件自动组装的实现
的的
第一步：将查询条件放在请求方式为get 的form中
代码如下：
1 <form method="get"> 2 <table class="tb"> 3 <tr class="treven height45"> 4 <th class="thCss"> 5 标题: <input name="Title_string_like" type="text" id="Title_string_like" runat="server" class="ipt width200 " /> 6 来源: <input name="FromWhere_string_like" type="text" id="FromWhere_string_like" runat="server" class="ipt width200 " /> 7 <input type="submit" class="btn" value="搜索" /> 8 </th> 9 </tr> 10 <tr class="height45"> 11 <th> 12 <input type="button" class="btn btn-primary" onclick='window.location.href="<%=EditUrl %> ";' value="添加" /> 13 14 <input type="button" class="btn" onclick='window.location.href=window.location.href;' value="刷新" /> 15 </th> 16 </tr> 17 </table> 18 </form>
说明：上述代码会自动拼接url参数，也许你发现了表单的name很特别，这里使用的是约定大于配置的思想(如果你了解过mvc的话会发现其中到处都充斥着这种思想，比如mvc前台验证机制等等)。查询表单的那么格式为name_[int|string]_[like|equal]，name代表对应的数据库字段名称，int和string代表的是数据类型，datetime类型默认为int类型，like代表模糊查询(暂不支持左模糊和右模糊)，equal代表等值查询。我们约定以此方式为查询表单命名，方便在后台对此表单自动拆卸，组装sql条件。
第二步：建造url参数自动拆卸组装sql条件的工具
方法如下：
1 public string GetQueryString(string name) 2 { 3 string temp= Request.QueryString[name]; 4 return string.IsNullOrEmpty(temp) ? "" : temp; 5 } 6 /// <summary> 7 /// 自动组合拼接查询语句-使用约定大于配置的思想 8 /// 查询表单的那么格式为name_[int|string]_[like|equal]，name代表对应的数据库字段名称，int和string代表的是数据类型，datetime类型默认为int类型，like代表模糊查询(暂不支持左模糊和右模糊)，equal代表等值查询。 9 /// </summary> 10 /// <returns></returns> 11 public string GetQueryString() 12 { 13 string where = ""; 14 System.Collections.Specialized.NameValueCollection collection = Request.QueryString; 15 var keys= collection.AllKeys;//key 的格式为 name_[int|string]_[like|equal] 16 foreach (var key in keys) 17 { 18 string[] split = key.ToString().Split('_'); 19 string name = split[0]; 20 string type = split[1] == "int" ? "" : "'"; 21 string le = split[2] == "equal" ? "=" : "like"; 22 string l = le == "like" ? "%" : ""; 23 string value = GetQueryString(key.ToString()); 24 if (type=="'") 25 { 26 value = value.Replace("'","");//手动过滤掉',不足之处 应该使用参数化查询 27 } 28 if (!string.IsNullOrEmpty(value)) 29 { 30 string tempStr = string.Format(" and {0} {1} {2}{3}{4}{3}{2} ", name, le, type, l, value); 31 where += tempStr; 32 33 } 34 } 35 return where; 36 37 }
说明：我们知道sql的查询条件有等值查询和模糊查询(这里暂不考虑左模糊和右模糊)，sql里面的值类型大致可以分为整型和字符串类型，我们把bit类型和datetime类型看做是整型，因为它们在值的两端不会加 "'"。这样我们可以根据我们前面在查询列表页面以name_[int|string]_[like|equal]的方式命名表单。具体操作，在以上代码中可以看到，不作说明，关于数据非法性验证在这里没做说明。
数据查询方法如下
private void BindData() { string where=GetQueryString(); var entitys = bll.GetPagedEntitys(Pager.CurrentPageIndex, Pager.PageSize, out RowCount, out PageCount, where, null); BindRptData(rptList, entitys); Pager.RecordCount = RowCount; }
有些朋友该说，现在我们都使用的是强类型ORM框架处理数据的CURD。你怎么还在这里拼接sql语句，其实在这里我的底层操作数据库的也是强类型ORM框架EF。但是我个人认为针对于多条件查询的方法，使用Lamda作为条件显的特别臃肿。而我恰恰是利用了EF支持原生态的sql查询而且可以生成强类型model的特性拼接sql。这样还有一个好处是性能会有所提高。
但是经过使用sql server profiler跟踪发现了两个不足之处。
1.EF自动生成的sql分页语句并不是性能最好的分页语句(其实微软这样做是有原因的)。
2.如果直接使用EF提供的拼接sql语句结合分页查询时，跟踪发现对数据的分页是在内存中进行的(这实在太可怕了！！！)。
以上言论纯属本人言论，如有不妥或者错误的地方欢迎指正，同时在下篇博客我将给出以上两个问题的解决方案。
