用扩展方法优化多条件查询(不定条件查询)

在我们开发过程中，特别是管理系统的开发，经常会遇到多条件查询(或者叫不定条件查询)的案例，就是提供给User输入的查询条件有多个不同的查询栏位，而且，在实际使用中并不能确定User会使用哪些条件来当做搜索条件。

下图就是我们实际项目中一个查询页面的截图，
 
User在实际操作中，有可能会只根据[扣帐编号]查询，那么，只要在[扣帐编号]栏位输入号码，其他栏位留空即可，那么查询语句就只卡[扣帐编号]这条条件也有可能直接根据日前范围查询，只要输入起始日期即可。当然，在实际开发的时候我们是不能预判User的行为的，因此，正常情况下我们都是用Sql拼接的方法来应对这个问题：
代码如下:

<br>StringBulider sbSql=new StringBulider(); <br>sbSql.Append("select * from V_view1 where 1=1 "); <br>/*"注意，这里为了确保拼接Sql语句的语法正确，要加上“1=1”，因为可能后面所有的查询条件都为空，这个语句 要以 "where 1=1" 结尾。 以前也有在园子里看到文章说加上“1=1”对查询效率有一定影响，这个没有深入研究过，对此持保留态度鉴于我们这里只针对一般开发，数据量不是很大，所以对于这个问题暂且不做讨论*/ <br>if(!string.IsNullorEmpty(varGRNO)) <br>sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO); <br> <br>这样，就在生成Sql语句之前对User的输入行为做了判断：对于某个查询条件，如果User有输入，则加入Sql的Where条件中，有个没有输入，则不予考虑。 <br>对于日期范围的判断，可以这样写： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>StringBulider sbSql=new StringBulider(); <br>sbSql.Append("select * from V_view1 where 1=1 "); <br>if(!string.IsNullorEmpty(varGRNO)) <br>sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO); <br><br>if(!string.IsNullorEmpty(vardtFrom)) <br>{ <br>sbSql.AppendFormat(" and CRDate >= '{0}' ",Convert.ToDateTime(vardtFrom)); <br>if(!string.IsNullorEmpty(vardtTo)) <br>{ <br>sbSql.AppendFormat(" and CRDate <= '{0}' ",Convert.ToDateTime(vardtTo)); <br>} <br>} <br> <br>下面是我们实际开发中的完整代码(省略了一些无关的逻辑): <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>public DataTable GetGRCollections(string varShipto, string varGRNO, string varGRNOto, string varMaterialNO, string varPL, string varPLto, string varCustomerID, string varCustomerID1, string varCustomerPN, string varDateFrom, string varDateTo, string varChecked,string varSupplierPN) <br>{ <br>try <br>{ <br>#region Code Here................ <br><br>DataTable dtResult = new DataTable(); <br><br>StringBuilder sbSql = new StringBuilder(); <br>sbSql.Append(" SELECT * ") <br>.Append(" FROM V_QueryGR") <br>.Append(" WHERE (GRTime>= '" + varDateFrom + " 'and GRTime<='" + varDateTo + "')"); <br>if (!string.IsNullOrEmpty(varShipto)) <br>{ <br>sbSql.Append(" and Plant='"+varShipto+"'"); <br>} <br><br>if (!string.IsNullOrEmpty(varGRNO)) <br>{ <br>if (!string.IsNullOrEmpty(varGRNOto)) <br>sbSql.Append(" and (GRNO>='" + varGRNO +"' and GRNO<='"+varGRNOto+ "')"); <br>else <br>sbSql.Append(" and GRNO='" + varGRNO + "'"); <br>} <br>if (!string.IsNullOrEmpty(varMaterialNO)) <br>{ <br>sbSql.Append(" and MaterialNO='"+varMaterialNO+"'"); <br>} <br><br>if (!string.IsNullOrEmpty(varPL)) <br>{ <br>if (!string.IsNullOrEmpty(varPLto)) <br>sbSql.Append(" and (PackingNO>='" + varPL + "' and PackingNO<='"+varPLto+"')"); <br>else <br>sbSql.Append(" and PackingNO='" + varPL + "'"); <br>} <br>if (!string.IsNullOrEmpty(varCustomerID)) <br>{ <br>sbSql.Append(" and CustomID='" + varCustomerID + "'"); <br>} <br>if (string.IsNullOrEmpty(varCustomerID)) <br>{ <br>ClsCommon ObjCommon = new ClsCommon(userData); <br>sbSql.Append(" and CustomID in (" + ObjCommon.GetVendorPermissionString() + ")"); <br>} <br>if (!string.IsNullOrEmpty(varCustomerID1)) <br>{ <br>sbSql.Append(" and CustomID2='" + varCustomerID1 + "'"); <br>} <br>if (!string.IsNullOrEmpty(varCustomerPN)) <br>{ <br>sbSql.Append(" and CustomerPN='" + varCustomerPN + "'"); <br>} <br>if (!string.IsNullOrEmpty(varDateFrom)) <br>{ <br>if (!string.IsNullOrEmpty(varDateTo)) <br>sbSql.Append(" and (GRTime>= '" + varDateFrom + "' and GRTime<='" + varDateTo + "')"); <br>else <br>sbSql.Append(" and PackingNO='" + varDateFrom + "'"); <br>} <br>if (varChecked == "Checked") <br>{ <br>sbSql.Append(" and CheckPrice=1 "); <br>} <br>if (varChecked == "UnChecked") <br>{ <br>sbSql.Append(" and CheckPrice=0"); <br>} <br>if (!string.IsNullOrEmpty(varSupplierPN)) <br>{ <br>sbSql.Append(" and SuplierPN='" + varSupplierPN + "'"); <br>} <br><br>try <br>{ <br>ControlHandleDB(); <br>dtResult = ControlSqlAccess.GetDataTable(sbSql.ToString()); <br>} <br>catch <br>{ <br>throw; <br>} <br>finally <br>{ <br>ControlSqlAccess.CloseConnection(); <br>} <br><br>return dtResult; <br><br>#endregion <br>} <br>catch (CommonObjectsException ex) <br>{ <br><br>} <br>catch (Exception ex) <br>{ <br><br>} <br>} <br> <br>这样一来，如果参数多一点的话，一个简单的Get方法就要写50行以上的代码，虽然不能以代码的行数来评定开发效率，但这种方法无疑增加了代码量， <br>也降低的代码的可读性和可维护性。 <br>以前，为了给这种情况找到一种更“优雅”，更简洁的方法，也有在网上找了一些资料，发现其他人的方法也是大同小异，差不多都是这样按条件拼接。 <br>园子里有一位同学(现在忘记是哪位了O(∩_∩)O哈！)提出了一种解决方案就是把判断的逻辑直接写到Sql语句或者存储过程中： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select * from V_view1 where ((ISNULL(@varGRNO,'')<>'' and BOLNR=@varGRNO ) or (1=1)) <br> <br>这个方法虽然一定程度上减少了代码量，但是把业务逻辑混杂在Sql语句中，个人感觉不是太好的方法，而且大大增加了维护的难度。当然，有兴趣的同学可以 <br>自己去研究。 <br>既然，以上方法都有弊端，那么有没有更好一点的解放方案呢？答案是肯定的，上次用EF的时候突然想到.Net中的扩展方法能够对这个问题进行优化。 <br>首先，来看一下什么事扩展方法，一下是来做MSDN的解释： <br>扩展方法使您能够向现有类型“添加”方法，而无需创建新的派生类型、重新编译或以其他方式修改原始类型。 扩展方法是一种特殊的静态方法，但可以像扩展类型上的实例方法一样进行调用。 <br>我们常用的Linq中引用的 using System.Linq 其实就是一个扩展方法库，更详细的内容可以参照MSDN和c# 扩展方法奇思妙用(鹤冲天)。在这里，我只举一个简单的例子： <br>比如，正常情况下判断一个字符串是否为空是这样写： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>string.IsNullOrEmpty(str); <br>如果加上一个我们自己扩展的方法： <br>/// <summary> <br>/// 检查字符串是否是空(IsNullOrEmpty) <br>/// </summary> <br>/// <param name="str"></param> <br>/// <returns></returns> <br>public static bool IsNullOrEmpty(this string str) <br>{ <br>return string.IsNullOrEmpty(str); <br>} <br> <br>那么以后判断字符串是否为空就可以这样： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>str.IsNullOrEmpty(); <br> <br>是不是简洁、优雅了许多呢？ <br>好，回到正题，看看如何用扩展方法的特性来优化Sql语句的拼接问题。既然扩展方法允许我们以实例方法的方式来调用静态方法，那么我们是否可以给Sql语句的字符串实例扩展一个方法来对其操作呢？ <br>比如这个Sql: <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>StringBulider sbSql=new StringBulider(); <br>sbSql.Append("select * from V_view1 where 1=1 "); <br>if(!string.IsNullorEmpty(varGRNO)) <br>sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO); <br> <br>实际上就是对一个变量进行判断，然后操作字符串实例。 <br>那么，我们就加行一个这样的扩展： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>public static string strEquals(this string strSql, string strValue, string ColName) <br>{ <br>if (!string.IsNullOrEmpty(strValue)) <br>return string.Format(strSql + " and {0}='{1}' ", ColName, strValue); <br>else <br>return strSql; <br>} <br> <br>看到没有，在方法内部进行参数的非空判断，那么，上面的代码就可以这样写： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>String strSql="select * from V_view1 where 1=1" <br>strSql=strSql.strEquals(varGRNO,BOLNR) <br> <br>是不是少了很多代码？ <br>如果有更多的参数，我们可以写的想Linq一样优雅： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>String strSql="select * from V_view1 where 1=1" <br>.strEquals(varGRNO,BOLNR) <br>.strEquals(varPLNO,VBELN) <br>.strEquals(varPONO,EBELN) <br> <br>对于like语句，进行下面的扩展 <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>public static string strLike(this string strSql, string strValue, string ColName) <br>{ <br>if (!string.IsNullOrEmpty(strValue)) <br>return string.Format(strSql + " and {0} like '%{1}%' ", ColName, strValue); <br>else <br>return strSql; <br>} <br><br> <br>和范围的扩展： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>public static string strEqualsOrBetween(this string strSql, string strStart, string strEnd, string ColName) <br>{ <br>if (string.IsNullOrEmpty(strStart) && string.IsNullOrEmpty(strEnd)) <br>return strSql; <br>else if (!string.IsNullOrEmpty(strStart) && !string.IsNullOrEmpty(strEnd)) <br>{ <br>return strSql.strBigger(strStart, ColName).strSmaller(strEnd, ColName); <br>} <br>else if (string.IsNullOrEmpty(strStart) && !string.IsNullOrEmpty(strEnd)) <br>return strSql.strEquals(strEnd, ColName); <br>else <br>return strSql.strEquals(strStart, ColName); <br>} <br> <br>这样一来，上面一大段的代码就可以写成这样： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>public DataTable GetGRCollections(string varShipto, string varGRNO, string varGRNOto, string varMaterialNO, string varPL, string varPLto, string varCustomerID, string varCustomerID1, string varCustomerPN, string varDateFrom, string varDateTo, string varChecked,string varSupplierPN) <br>{ <br>try <br>{ <br>#region Code Here................ <br><br>DataTable dtResult = new DataTable(); <br><br>String strSql="select * from V_QueryGR where 1=1" <br>.DtEqualsOrBetween(varDateFrom,varDateTo,GRTime) <br>.strEquals(varShipto,Plant) <br>.strEqualsOrBetween(varGRNO,GRNO) <br>.strEquals(varMaterialNO,MaterialNO) <br>.strEqualsOrBetween(varPL,PackingNO) <br>.strEquals(varCustomerID,CustomID) <br>.strEquals(varCustomerID1,CustomID2) <br>.strEquals(varCustomerPN,CustomerPN) <br>.DtEqualsOrBetween(varDateFrom,varDateTo,GRTime) <br>.strEquals(varSupplierPN,SuplierPN) <br>try <br>{ <br>ControlHandleDB(); <br>dtResult = ControlSqlAccess.GetDataTable(sbSql.ToString()); <br>} <br>catch <br>{ <br>throw; <br>} <br>finally <br>{ <br>ControlSqlAccess.CloseConnection(); <br>} <br><br>return dtResult; <br><br>#endregion <br>} <br><br>catch (CommonObjectsException ex) <br>{ <br><br>} <br>catch (Exception ex) <br>{ <br><br>} <br>} <br><br> <br>对于其他的一下扩展方法，我写了一个类文件，有兴趣的可以点此下载。 <br>第一次正正经经的写博文，累死我了。由于自己也是个菜鸟，想把一个问题讲清楚让更多的“菜鸟”也能看懂，难免有些啰嗦，有不足的地方还请大家多多指教。<p></p></li><li>            </li><li>                                </li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre>