In the course of our development, in particular, the development of management systems, often encounter multiple conditions query (or called indeterminate conditions) of the case, is provided to the user input query conditions have several different query fields, and, in the actual use of the user will not be able to determine what conditions to use as a search condition.
The figure below is a screenshot of a query page in our actual project,
User in the actual operation, it is possible to only according to the [debit number] query, so, as long as in the [Debit number] field input number, the other fields are left blank, then the query statement on the card [debit number] This condition may also be directly based on the date range query, as long as the input starting dates can be. Of course, in actual development we can not pre-sentence user behavior, so, under normal circumstances, we are using the SQL splicing method to deal with this problem:
Copy Code code as follows:
Stringbulider sbsql=new Stringbulider ();
Sbsql.append ("select * from V_view1 where 1=1");
/* "Note that in order to ensure that the syntax of the concatenation SQL statement is correct, add" 1=1 "because all subsequent query conditions may be empty, and this statement will end with" where 1=1 ". There used to be in the garden to see the article said that plus "1=1" on the query efficiency has a certain impact, this has not been in-depth study, the retention of this attitude in view of our only for general development, the amount of data is not very large, so for this issue for the time being no discussion
if (!string. IsNullOrEmpty (Vargrno))
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, the in-SQL where condition is not entered, is not considered.
For the judgment of the date range, you can write this:
Copy Code code as follows:
Stringbulider sbsql=new Stringbulider ();
Sbsql.append ("select * from V_view1 where 1=1");
if (!string. IsNullOrEmpty (Vargrno))
Sbsql.appendformat ("and Bolnr = ' {0} '", Vargrno);
if (!string. IsNullOrEmpty (Vardtfrom))
{
Sbsql.appendformat ("and Crdate >= ' {0} '", Convert.todatetime (Vardtfrom));
if (!string. IsNullOrEmpty (Vardtto))
{
Sbsql.appendformat ("and Crdate <= ' {0} '", Convert.todatetime (Vardtto));
}
}
Here is the complete code in our actual development (omitting some irrelevant logic):
Copy Code code as follows:
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)
{
Try
{
#region Code here ..........
DataTable dtresult = new DataTable ();
StringBuilder sbsql = new StringBuilder ();
Sbsql.append ("SELECT *")
. Append ("from V_querygr")
. Append ("WHERE (grtime>= '" + Vardatefrom + "' and grtime<= '" + Vardateto + ")");
if (!string. IsNullOrEmpty (Varshipto))
{
Sbsql.append ("and plant= '" +varshipto+ "");
}
if (!string. IsNullOrEmpty (Vargrno))
{
if (!string. IsNullOrEmpty (Vargrnoto))
Sbsql.append ("and (grno>= ') + Vargrno +" ' and grno<= ' "+vargrnoto+") ");
Else
Sbsql.append ("and grno= '" + Vargrno + "");
}
if (!string. IsNullOrEmpty (Varmaterialno))
{
Sbsql.append ("and materialno= '" +varmaterialno+ "");
}
if (!string. IsNullOrEmpty (VARPL))
{
if (!string. IsNullOrEmpty (Varplto))
Sbsql.append ("and (packingno>= ') + VARPL +" ' and packingno<= ' "+varplto+") ");
Else
Sbsql.append ("and packingno= '" + VARPL + "");
}
if (!string. IsNullOrEmpty (Varcustomerid))
{
Sbsql.append ("and customid= '" + Varcustomerid + "");
}
if (string. IsNullOrEmpty (Varcustomerid))
{
Clscommon Objcommon = new Clscommon (userData);
Sbsql.append ("and Customid in (" + objcommon.getvendorpermissionstring () + ")");
}
if (!string. IsNullOrEmpty (varCustomerID1))
{
Sbsql.append ("and customid2= '" + varCustomerID1 + "");
}
if (!string. IsNullOrEmpty (VARCUSTOMERPN))
{
Sbsql.append ("and customerpn= '" + VARCUSTOMERPN + "");
}
if (!string. IsNullOrEmpty (Vardatefrom))
{
if (!string. IsNullOrEmpty (Vardateto))
Sbsql.append ("and (grtime>= ') + Vardatefrom +" ' and grtime<= ' "+ Vardateto +") ");
Else
Sbsql.append ("and packingno= '" + Vardatefrom + "");
}
if (varchecked = = "Checked")
{
Sbsql.append ("and checkprice=1");
}
if (varchecked = = "UnChecked")
{
Sbsql.append ("and checkprice=0");
}
if (!string. IsNullOrEmpty (VARSUPPLIERPN))
{
Sbsql.append ("and suplierpn= '" + VARSUPPLIERPN + "");
}
Try
{
Controlhandledb ();
Dtresult = Controlsqlaccess.getdatatable (sbsql.tostring ());
}
Catch
{
Throw
}
Finally
{
Controlsqlaccess.closeconnection ();
}
return dtresult;
#endregion
}
catch (Commonobjectsexception ex)
{
}
catch (Exception ex)
{
}
}
As a result, if you have more than one parameter, a simple get method will write more than 50 lines of code, although not the number of lines of code to evaluate development efficiency, but this method has undoubtedly increased the amount of code,
It also reduces the readability and maintainability of the code.
Previously, in order to give this situation to find a more "elegant", a more concise method, there are some information on the Internet, found that other people's methods are similar, almost all of which are in accordance with the conditions of stitching.
There is a classmate in the garden (now forget who it is O (∩_∩) o ha! ) proposes a solution to write the logic of judgment directly into SQL statements or stored procedures:
Copy Code code as follows:
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 mixed in the SQL statements, personal feeling is not a good way, and greatly increased the difficulty of maintenance. Of course, interested students can
To study for yourself.
Since all the above methods have drawbacks, is there a better emancipation plan? The answer is yes, the last time I used EF, it suddenly occurred to me that the extension methods in. NET can optimize this problem.
First, let's take a look at what the extension method is, and here's an explanation of MSDN:
Extension methods enable you to add a method to an existing type without creating a new derived type, recompiling, or otherwise modifying the original type. An extension method is a special static method, but can be invoked just like an instance method on an extended type.
The using System.Linq referenced in our common Linq is actually an extension method library, and more detailed information can be used in reference to the MSDN and C # extension methods (cranes). Here, I'll just give you a simple example:
For example, it is normal to judge whether a string is null by writing:
Copy Code code as follows:
String. IsNullOrEmpty (str);
If you add a method that we extend ourselves:
<summary>
Check whether the string is empty (IsNullOrEmpty)
</summary>
<param name= "str" ></param>
<returns></returns>
public static bool IsNullOrEmpty (this string str)
{
return string. IsNullOrEmpty (str);
}
Then it's OK to judge whether the string is empty at a later time:
Copy Code code as follows:
Is it simple, elegant, a lot of it?
OK, back to the point, and see how to optimize the concatenation of SQL statements with the features of extension methods. Since the extension method allows us to invoke the static method in the form of an instance method, can we extend a method to the string instance of the SQL statement to manipulate it?
Like this sql:
Copy Code code as follows:
Stringbulider sbsql=new Stringbulider ();
Sbsql.append ("select * from V_view1 where 1=1");
if (!string. IsNullOrEmpty (Vargrno))
Sbsql.appendformat ("and Bolnr = ' {0} '", Vargrno);
In effect, a variable is judged, and then the string instance is manipulated.
So, let's add one such extension:
Copy Code code as follows:
public static string Strequals (This string strSQL, String strvalue, String colname)
{
if (!string. IsNullOrEmpty (strvalue))
return string. Format (strSQL + "and {0}= ' {1} '", ColName, strvalue);
Else
return strSQL;
}
See, there is no null judgment for parameters inside the method, so the code above can be written like this:
Copy Code code as follows:
String strsql= "SELECT * from V_view1 where 1=1"
Strsql=strsql.strequals (VARGRNO,BOLNR)
Is it a lot less code?
If there are more parameters, we can write like LINQ as gracefully:
Copy Code code as follows:
String strsql= "SELECT * from V_view1 where 1=1"
. Strequals (VARGRNO,BOLNR)
. Strequals (Varplno,vbeln)
. Strequals (Varpono,ebeln)
For like statements, do the following extensions
Copy Code code as follows:
public static string Strlike (This string strSQL, String strvalue, String colname)
{
if (!string. IsNullOrEmpty (strvalue))
return string. Format (strSQL + "and {0} like '%{1}% '", ColName, strvalue);
Else
return strSQL;
}
and scope of expansion:
Copy Code code as follows:
public static string Strequalsorbetween (This string strSQL, String Strstart, String strend, String colname)
{
if (string. IsNullOrEmpty (Strstart) && string. IsNullOrEmpty (Strend))
return strSQL;
else if (!string. IsNullOrEmpty (Strstart) &&!string. IsNullOrEmpty (Strend))
{
Return Strsql.strbigger (Strstart, colname). Strsmaller (Strend, colname);
}
else if (string. IsNullOrEmpty (Strstart) &&!string. IsNullOrEmpty (Strend))
Return Strsql.strequals (Strend, colname);
Else
Return Strsql.strequals (Strstart, colname);
}
As a result, a large section of code above can be written like this:
Copy Code code as follows:
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)
{
Try
{
#region Code here ..........
DataTable dtresult = new DataTable ();
String strsql= "SELECT * from V_querygr where 1=1"
. Dtequalsorbetween (Vardatefrom,vardateto,grtime)
. Strequals (Varshipto,plant)
. Strequalsorbetween (Vargrno,grno)
. Strequals (Varmaterialno,materialno)
. Strequalsorbetween (Varpl,packingno)
. Strequals (Varcustomerid,customid)
. Strequals (VARCUSTOMERID1,CUSTOMID2)
. Strequals (VARCUSTOMERPN,CUSTOMERPN)
. Dtequalsorbetween (Vardatefrom,vardateto,grtime)
. Strequals (VARSUPPLIERPN,SUPLIERPN)
Try
{
Controlhandledb ();
Dtresult = Controlsqlaccess.getdatatable (sbsql.tostring ());
}
Catch
{
Throw
}
Finally
{
Controlsqlaccess.closeconnection ();
}
return dtresult;
#endregion
}
catch (Commonobjectsexception ex)
{
}
catch (Exception ex)
{
}
}
For the other extension methods, I wrote a class file that interested you can click here to download.
The first time Turkey to write blog, exhausted me. Because he is also a rookie, want to put a question clearly let more "rookie" also can see understand, inevitably some long-winded, there is a shortage of places to ask everyone a lot of advice.
