Programming experience: dynamic SQL splicing skills

Common misunderstandings include:
1. dynamic SQL concatenation is not allowed with only ado.net.
2. There are several dynamic parameters, and the repeated amount of code becomes the number of combinations of these parameters. The more dynamic parameters, the larger the repeated amount.

The following error code is evidence of the second problem:Copy codeThe Code is as follows: if (id> 0 & string. IsNullOrEmpty (name ))
{
Command. CommandText = "select * from t1 where id =? ";
Command. Parameters. Add (id );
}
If (id <= 0 &&! String. IsNullOrEmpty (name ))
{
Command. CommandText = "select * from t1 where name =? ";
Command. Parameters. Add (name );
}
If (id <= 0 &&! String. IsNullOrEmpty (name ))
{
Command. CommandText = "select * from t1 where id =? And name =? ";
Command. Parameters. Add (id );
Command. Parameters. Add (name );
}

These two problems are well solved. Let's take a look at the correct code example:Copy codeThe Code is as follows: string SQL = "select * from t1 where 1 = 1 ";
If (id! = Null)
{
SQL + = "and id =? ";
AddParameterValue (cmd, id );
}
If (! String. IsNullOrEmpty (name ))
{
SQL + = "and name =? ";
AddParameterValue (cmd, name );
}
Command. CommandText = SQL;

The trick here is to use a "where 1 = 1" to cleverly solve the problem of "and" at the beginning of each line in subsequent SQL concatenation. However, this "where 1 = 1" does not affect the performance of database indexes.

This is a strange idea to arrange and combine parameters and then write SQL statements of various combinations. The problem is that many beginners have this habit of thinking. I am not from a computer science class. I don't know which textbook I want to teach. Unfortunately, this mindset is wrong.

"Where 1 = 1" is useful even though it is not in textbooks.

In addition, in the program, the user interface is generally used to input a number. The value of this number is automatically converted to a string in the code, and then the string is converted to int/long, finally, it is sent to the SQL function. Note that many people use a special value as the "default value not input by users", as shown in the error code at the beginning of this article:

Copy codeThe Code is as follows: if (id> 0 & string. IsNullOrEmpty (name ))

The problem is that 0 is an abnormal business value and cannot be seen in the code. It is not ruled out that programmers can specify a value at will as the "default value for no user input". If this default value is unfortunate, it actually makes other meanings, which may cause problems.

In the database theory, no specified data is represented by null, whether it is string or int/long.

This is a good idea. It can also be used in SQL concatenation. Therefore, we use the following code:Copy codeThe Code is as follows: if (id! = Null)

In the above Code,

AddParameterValue (cmd, name); is a simple encapsulation function used to encapsulate the following small pieces of code. The purpose is to make the final code simpler and more intuitive:Copy codeThe Code is as follows: DbParameter p = cmd. CreateParameter ();
P. ParameterName = "@ XXX ";
P. Value = TTT;
Cmd. Parameters. Add (p );

Of course, this addParameterValue () Encapsulation function is dispensable. Writing a few more DbParameter p = cmd. CreateParameter () is not a big problem.