Using Attribute features to simplify the splicing of SQL statements with multiple query Conditions

Recently, the company is working on the Wuhan public transport information management system. The most painful thing for such management projects is the addition, modification, and query of forms. Add or modify Article As mentioned in, the reflection mechanism can be used to do nothing. Code . See Automatic Data Binding and modification in the ORM framework <1>. (Unfortunately, the current project is not used, but it is still painful to write a value assignment statement)

The above only solves the problem of adding, modifying, and displaying the list. However, when querying multiple keywords, you must start to assign values and splice strings. In order to be lazy and save the annoying and cumbersome things, I began to think of a clever way.

For multi-Keyword queries, the general processing method is to retrieve these query conditions and splice them into strings. Then, a data-layer function is passed and a data set is returned. There are a few annoying points in this process:

1. When there are too many query conditions, there will be more than N parameter names. Poor code
2. You need to connect the string and determine whether the specific requirement is like OR =
3. It is troublesome to change the code when the query conditions change or the query requirements change.

The first one is better to solve. Just declare these parameters as a class and directly transmit them as parameters. This is what we do in our current project.

The second one is troublesome. How can we let these parameters know what they are doing? You can also say how to makeProgramAutomatically know what to do with these attributes? Attribute .. Net. Just suck him to the parameter and pass the operator to him!

The code is simple. Just write:

First, declare an attribute with an attribute and use this attribute to store operators.

[Attributeusage (attributetargets. Field)] // This attribute can only be used on Field
Public   Class Opattribute: attribute
{
Private   String _ OP =   String . Empty;
Public Opattribute ( String OP)
{
_ OP = OP;
}
  Public   String Name
{
Get { Return _ OP ;}
Set {_ OP = Value ;}
}
}

Add these attributes to the query condition class.

 Public     Class  Attributeclass
{
[Opattribute (  " =  "  )]
  Public     Int  Myproperty;
[Opattribute (  "  =  "  )]
  Public     String  AA;
[Opattribute (  " Like  "  )]
  Public     String  BB;
} 

Then, call a combination of SQL statements.

Attributeclass AC =   New Attributeclass ();
AC. myproperty =   11 ;
AC. AA =   " ABCD " ;
AC. bb =   " Alas, you " ;
String SQL = " 1 = 1 " ;
Type type =   Typeof (Attributeclass );
Foreach (System. reflection. fieldinfo In Type. getfields ())
{
Foreach (Attribute In Fieldinfo. getcustomattributes ( True ))
{
Opattribute ATT = At As Opattribute;
If (ATT ! =   Null )
{
SQL + =   " And "   + Fieldinfo. Name + "   " + Att. Name + "   " + Fieldinfo. getvalue (AC );
}
}
}

Success. Simple and practical. This method is too convenient when you have dozens of query conditions. Of course, this is just a specific idea. For example, implement the or group by operation. You can also write this concatenation string method as an accumulation, so that this attributeclass will splice the string by itself!