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

原文地址：http://www.cnblogs.com/Gamain/archive/2012/11/21/2780413.html

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

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

   1:  StringBulider sbSql=new StringBulider();

   2:      sbSql.Append("select * from V_view1 where 1=1 ");

   3:      /*"注意，这里为了确保拼接Sql语句的语法正确，要加上“1=1”，因为可能后面所有的查询条件都为空，这个语句 要以 "where 1=1" 结尾。 以前也有在园子里看到文章说加上“1=1”对查询效率有一定影响，这个没有深入研究过，对此持保留态度鉴于我们这里只针对一般开发，数据量不是很大，所以对于这个问题暂且不做讨论*/

   4:      if(!string.IsNullorEmpty(varGRNO))

   5:          sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO);

这样，就在生成Sql语句之前对User的输入行为做了判断：对于某个查询条件，如果User有输入，则加入Sql的Where条件中，有个没有输入，则不予考虑。
对于日期范围的判断，可以这样写：

   1:  StringBulider sbSql=new StringBulider();

   2:      sbSql.Append("select * from V_view1 where 1=1 ");

   3:      if(!string.IsNullorEmpty(varGRNO))

   4:          sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO);

   5:   

   6:      if(!string.IsNullorEmpty(vardtFrom))

   7:          {

   8:              sbSql.AppendFormat(" and CRDate >= '{0}' ",Convert.ToDateTime(vardtFrom));

   9:              if(!string.IsNullorEmpty(vardtTo))

  10:                  {

  11:                      sbSql.AppendFormat(" and CRDate &lt= '{0}' ",Convert.ToDateTime(vardtTo));

  12:                  }

  13:          }

下面是我们实际开发中的完整代码(省略了一些无关的逻辑):

   1:  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)

   2:          {

   3:              try

   4:              {

   5:                  #region Code Here................

   6:   

   7:                  DataTable dtResult = new DataTable();

   8:   

   9:                  StringBuilder sbSql = new StringBuilder();

  10:                  sbSql.Append(" SELECT * ")           

  11:                      .Append(" FROM V_QueryGR")

  12:                      .Append(" WHERE (GRTime>= '" + varDateFrom + " 'and GRTime<='" + varDateTo + "')");

  13:                  if (!string.IsNullOrEmpty(varShipto))               

  14:                  {

  15:                      sbSql.Append(" and  Plant='"+varShipto+"'");

  16:                  }

  17:   

  18:                  if (!string.IsNullOrEmpty(varGRNO))

  19:                  {

  20:                      if (!string.IsNullOrEmpty(varGRNOto))

  21:                          sbSql.Append(" and  (GRNO>='" + varGRNO +"' and GRNO<='"+varGRNOto+ "')");

  22:                      else

  23:                          sbSql.Append(" and  GRNO='" + varGRNO + "'");

  24:                  }

  25:                  if (!string.IsNullOrEmpty(varMaterialNO))

  26:                  {

  27:                      sbSql.Append(" and MaterialNO='"+varMaterialNO+"'");

  28:                  }

  29:   

  30:                  if (!string.IsNullOrEmpty(varPL))

  31:                  {

  32:                      if (!string.IsNullOrEmpty(varPLto))

  33:                          sbSql.Append("  and (PackingNO>='" + varPL + "' and PackingNO<='"+varPLto+"')");

  34:                      else 

  35:                          sbSql.Append("  and PackingNO='" + varPL + "'");

  36:                  }

  37:                  if (!string.IsNullOrEmpty(varCustomerID))

  38:                  {

  39:                      sbSql.Append(" and CustomID='" + varCustomerID + "'");

  40:                  }

  41:                  if (string.IsNullOrEmpty(varCustomerID))

  42:                  {

  43:                      ClsCommon ObjCommon = new ClsCommon(userData);

  44:                      sbSql.Append(" and CustomID in (" + ObjCommon.GetVendorPermissionString() + ")");

  45:                  }

  46:                  if (!string.IsNullOrEmpty(varCustomerID1))

  47:                  {

  48:                      sbSql.Append(" and CustomID2='" + varCustomerID1 + "'");

  49:                  }

  50:                  if (!string.IsNullOrEmpty(varCustomerPN))

  51:                  {

  52:                      sbSql.Append(" and CustomerPN='" + varCustomerPN + "'");

  53:                  }

  54:                  if (!string.IsNullOrEmpty(varDateFrom))

  55:                  {

  56:                      if (!string.IsNullOrEmpty(varDateTo))

  57:                          sbSql.Append(" and (GRTime>= '" + varDateFrom + "' and GRTime<='" + varDateTo + "')");

  58:                      else

  59:                          sbSql.Append("  and PackingNO='" + varDateFrom + "'");

  60:                  }

  61:                  if (varChecked == "Checked")

  62:                  {

  63:                      sbSql.Append(" and CheckPrice=1 ");

  64:                  }

  65:                  if (varChecked == "UnChecked")

  66:                  {

  67:                      sbSql.Append(" and CheckPrice=0");

  68:                  }

  69:                  if (!string.IsNullOrEmpty(varSupplierPN))

  70:                  {

  71:                      sbSql.Append(" and SuplierPN='" + varSupplierPN + "'");

  72:                  }

  73:   

  74:                  try

  75:                  {

  76:                      ControlHandleDB();

  77:                      dtResult = ControlSqlAccess.GetDataTable(sbSql.ToString());

  78:                  }

  79:                  catch

  80:                  {

  81:                      throw;

  82:                  }

  83:                  finally

  84:                  {

  85:                      ControlSqlAccess.CloseConnection();

  86:                  }

  87:   

  88:                  return dtResult;

  89:   

  90:                  #endregion

  91:              }

  92:              catch (CommonObjectsException ex)

  93:              {

  94:                  

  95:              } 

  96:              catch (Exception ex)

  97:              {

  98:               

  99:              }

 100:          }

这样一来，如果参数多一点的话，一个简单的Get方法就要写50行以上的代码，虽然不能以代码的行数来评定开发效率，但这种方法无疑增加了代码量，
也降低的代码的可读性和可维护性。
以前，为了给这种情况找到一种更“优雅”，更简洁的方法，也有在网上找了一些资料，发现其他人的方法也是大同小异，差不多都是这样按条件拼接。
园子里有一位同学(现在忘记是哪位了O(∩_∩)O哈！)提出了一种解决方案就是把判断的逻辑直接写到Sql语句或者存储过程中：

   1:  select * from V_view1 where ((ISNULL(@varGRNO,'')<>'' and BOLNR=@varGRNO ) or (1=1))

这个方法虽然一定程度上减少了代码量，但是把业务逻辑混杂在Sql语句中，个人感觉不是太好的方法，而且大大增加了维护的难度。当然，有兴趣的同学可以
自己去研究。

既然，以上方法都有弊端，那么有没有更好一点的解放方案呢？答案是肯定的，上次用EF的时候突然想到.Net中的扩展方法能够对这个问题进行优化。
首先，来看一下什么事扩展方法，一下是来做MSDN的解释：

扩展方法使您能够向现有类型“添加”方法，而无需创建新的派生类型、重新编译或以其他方式修改原始类型。 扩展方法是一种特殊的静态方法，但可以像扩展类型上的实例方法一样进行调用。

我们常用的Linq中引用的 using System.Linq 其实就是一个扩展方法库，更详细的内容可以参照MSDN和c# 扩展方法奇思妙用(鹤冲天)。在这里，我只举一个简单的例子：
比如，正常情况下判断一个字符串是否为空是这样写：

   1:  string.IsNullOrEmpty(str);

如果加上一个我们自己扩展的方法：

   1:   /// <summary>

   2:          /// 检查字符串是否是空(IsNullOrEmpty)

   3:          /// </summary>

   4:          /// <param name="str"></param>

   5:          /// <returns></returns>

   6:          public static bool IsNullOrEmpty(this string str)

   7:          {

   8:              return string.IsNullOrEmpty(str);

   9:          }

那么以后判断字符串是否为空就可以这样：

   1:  str.IsNullOrEmpty();

是不是简洁、优雅了许多呢？

好，回到正题，看看如何用扩展方法的特性来优化Sql语句的拼接问题。既然扩展方法允许我们以实例方法的方式来调用静态方法，那么我们是否可以给Sql语句的字符串实例扩展一个方法来对其操作呢？

比如这个Sql:

   1:  StringBulider sbSql=new StringBulider();

   2:          sbSql.Append("select * from V_view1 where 1=1 ");

   3:          if(!string.IsNullorEmpty(varGRNO))

   4:              sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO);

实际上就是对一个变量进行判断，然后操作字符串实例。

那么，我们就加行一个这样的扩展：

   1:   public static string strEquals(this string strSql, string strValue, string ColName)

   2:          {

   3:              if (!string.IsNullOrEmpty(strValue))

   4:                  return string.Format(strSql + "  and {0}='{1}'  ", ColName, strValue);

   5:              else

   6:                  return strSql;

   7:          }

看到没有，在方法内部进行参数的非空判断，那么，上面的代码就可以这样写：

   1:  String strSql="select * from V_view1 where 1=1"

   2:          strSql=strSql.strEquals(varGRNO,BOLNR)

是不是少了很多代码？
如果有更多的参数，我们可以写的想Linq一样优雅：

   1:  String strSql="select * from V_view1 where 1=1"

   2:                    .strEquals(varGRNO,BOLNR)

   3:                    .strEquals(varPLNO,VBELN)

   4:                    .strEquals(varPONO,EBELN)

对于like语句，进行下面的扩展

   1:   public static string strLike(this string strSql, string strValue, string ColName)

   2:          {

   3:              if (!string.IsNullOrEmpty(strValue))

   4:                  return string.Format(strSql + " and {0} like '%{1}%' ", ColName, strValue);

   5:              else

   6:                  return strSql;

   7:          }

   8:      

和范围的扩展：

   1:   public static string strEqualsOrBetween(this string strSql, string strStart, string strEnd, string ColName)

   2:          {

   3:              if (string.IsNullOrEmpty(strStart) && string.IsNullOrEmpty(strEnd))

   4:                  return strSql;

   5:              else if (!string.IsNullOrEmpty(strStart) && !string.IsNullOrEmpty(strEnd))

   6:              {

   7:                  return strSql.strBigger(strStart, ColName).strSmaller(strEnd, ColName);

   8:              }

   9:              else if (string.IsNullOrEmpty(strStart) && !string.IsNullOrEmpty(strEnd))

  10:                  return strSql.strEquals(strEnd, ColName);

  11:              else

  12:                  return strSql.strEquals(strStart, ColName);

  13:          }

这样一来，上面一大段的代码就可以写成这样：

   1:   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)

   2:   {

   3:       try

   4:              {

   5:                  #region Code Here................

   6:   

   7:                  DataTable dtResult = new DataTable();

   8:   

   9:                  String strSql="select * from V_QueryGR where 1=1"

  10:                    .DtEqualsOrBetween(varDateFrom,varDateTo,GRTime)

  11:                    .strEquals(varShipto,Plant)

  12:                    .strEqualsOrBetween(varGRNO,GRNO)

  13:                    .strEquals(varMaterialNO,MaterialNO)

  14:                    .strEqualsOrBetween(varPL,PackingNO)

  15:                    .strEquals(varCustomerID,CustomID)

  16:                    .strEquals(varCustomerID1,CustomID2)

  17:                    .strEquals(varCustomerPN,CustomerPN)

  18:                    .DtEqualsOrBetween(varDateFrom,varDateTo,GRTime)

  19:                    .strEquals(varSupplierPN,SuplierPN)

  20:           try

  21:                  {

  22:                      ControlHandleDB();

  23:                      dtResult = ControlSqlAccess.GetDataTable(sbSql.ToString());

  24:                  }

  25:                  catch

  26:                  {

  27:                      throw;

  28:                  }

  29:                  finally

  30:                  {

  31:                      ControlSqlAccess.CloseConnection();

  32:                  }

  33:   

  34:                  return dtResult;

  35:   

  36:                  #endregion

  37:              }

  38:   

  39:           catch (CommonObjectsException ex)

  40:              {

  41:                  

  42:              } 

  43:              catch (Exception ex)

  44:              {

  45:               

  46:              }

  47:   }    

  48:   

对于其他的一下扩展方法，我写了一个类文件，有兴趣的可以点此下载。

第一次正正经经的写博文，累死我了。由于自己也是个菜鸟，想把一个问题讲清楚让更多的“菜鸟”也能看懂，难免有些啰嗦，有不足的地方还请大家多多指教。