) Use the extension method to optimize multi-condition queries

Address: http://www.cnblogs.com/Gamain/archive/2012/11/21/2780413.html

 

During our development process, especially the development of management systems, we often encounter multi-condition queries (or indefinite condition queries, it means that the query conditions provided to users have multiple query columns. In actual use, it is not certain which conditions the User will use as the search conditions.
Is a query page in our actual project,

In actual operations, the User may only query by [deduction number]. Therefore, you only need to enter the number in the [deduction number] field, and leave the other fields blank, then, the query statement is stuck only with the [deduction number] condition. This condition may also be queried directly based on the date range, as long as the start date is entered. Of course, in actual development, we cannot predict User behavior. Therefore, we normally use SQL concatenation to solve this problem:

   1:  StringBulider sbSql=new StringBulider();

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

3:/* "Note: To ensure that the syntax of the concatenated SQL statement is correct, add" 1 = 1 ", because all the query conditions below may be empty, this statement must end with "where 1 = 1. In the past, I also saw an article in the garden saying that adding "1 = 1" has a certain impact on query efficiency. I have not studied it in depth, and I hold a reserved attitude. As we only focus on general development here, the data volume is not large, so we will not discuss this issue for the moment */

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

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

In this way, the User's input behavior is judged before the SQL statement is generated: For a query condition, if the User has input, it is added to the SQL Where condition, and there is no input, this parameter is not considered.
For the determination of the date range, you can write as follows:

   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:          }

The complete code in our actual development is as follows (some irrelevant logic is omitted ):

   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:          }

In this way, if there are more parameters, a simple Get method requires more than 50 lines of code. Although the development efficiency cannot be evaluated by the number of lines of code, but this method undoubtedly adds the amount of code,
It also reduces the readability and maintainability of the Code.
In the past, in order to find a more "elegant" and more concise method for this situation, I also found some information on the Internet and found that the methods of others were similar, almost all of them are spliced based on conditions.
There was a classmate in the Garden (now I forgot who I was, O ha !) A solution is proposed to directly write the judgment logic to SQL statements or stored procedures:

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

Although this method reduces the amount of code to a certain extent, but the business logic is mixed in SQL statements, I personally feel that this method is not very good, and greatly increased the difficulty of maintenance. Of course, you can
Study by yourself.

Since the above methods have drawbacks, is there a better solution for liberation? The answer is yes. When I used EF last time, I suddenly thought that the extension method in. Net could optimize this problem.
First, let's take a look at the extension method. Here is an explanation of MSDN:

The extension method enables you to "add" methods to an existing type without creating a new derived type, re-compiling, or modifying the original type in other ways. An extension method is a special static method, but it can be called like an instance method of an extension type.

The using System. Linq referenced in our commonly used Linq is actually an extended Runtime Library. For more details, refer to the MSDN and c # extension methods (Crane chongtian ). Here, I will just give a simple example:
For example, if a string is null, it is written as follows:

   1:  string.IsNullOrEmpty(str);

If we add an extension method:

   1:   /// <summary>

2: // check whether the string is empty (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:          }

Then you can determine whether the string is null in the future:

   1:  str.IsNullOrEmpty();

Is it concise and elegant?

Well, let's go back to the topic and see how to optimize SQL statement concatenation with the extended method features. Since the extension method allows us to call static methods using the instance method, can we extend a method to the string instance of the SQL statement to operate on it?

For example, this SQL statement:

   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);

In fact, it is to judge a variable and then operate the string instance.

Then, we will add an extension like this:

   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:          }

If no value is displayed, the preceding code can be written as follows:

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

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

Is there a lot of code missing?
If there are more parameters, we can write as elegant as the following:

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

   2:                    .strEquals(varGRNO,BOLNR)

   3:                    .strEquals(varPLNO,VBELN)

   4:                    .strEquals(varPONO,EBELN)

For the like statement, perform the following extension

   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:      

And range extension:

   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:          }

In this way, the above Code can be written as follows:

   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:   

For other extension methods, I wrote a class file. If you are interested, click here to download it.

I am exhausted when I write a blog post for the first time. As I am also a cainiao, I want to clarify a question so that more "cainiao" can understand it. It is inevitable that I will be a bit wordy. If there are any shortcomings, please give me more advice.