CPQuery: A new way to splice SQL statements

I have never liked the SQL concatenation method when accessing the database. The reasons are as follows:
1. Insecure: there is a risk of SQL injection.
2. Performance may be affected: Each SQL statement requires overhead such as [Statement Analysis] executed by the database engine.
3. Affect the maintainability of the Code: If the SQL statement is mixed with C #, you have to re-compile the program to modify the SQL statement, and the two types of code are mixed together, so the readability is not good.
Therefore, I usually select the [parameterized SQL] method to implement the database access process and separate the SQL statement from the project code (C.

However, some people may say that my business logic is very complicated, and the constraints in the Where clause cannot be determined in advance. Therefore, it is not enough to splice SQL statements.

Seeing these shortcomings, ORM users may think that using the ORM tool is the ultimate solution.
Yes. Indeed, ORM can solve these problems.
However, the solution is not only an ORM, but some users prefer to write SQL statements.
Therefore, this blog is not intended for ORM users, but for all friends who prefer to write SQL statements.

What is CPQuery?
When you see the blog title, Do you think: what is CPQuery?

Below is my answer:
1. CPQuery is an abbreviation: Concat Parameterized Query
2. CPQuery allows you to continue to use the familiar splicing method to write parameterized SQL statements.
3. CPQuery is a solution designed by me. It can solve the first two disadvantages of splicing SQL.
4. CPQuery is also the name of the core type in this solution.

I hope you can remember the name CPQuery.

Who is CPQuery suitable?
A: It is suitable for those who prefer to write SQL code, especially when writing dynamic queries.

Parameterized SQL statements
For scenarios that require dynamic queries, I think: splicing SQL may be necessary, but you should not splice the values into SQL statements, or, you should splice parameterized SQL statements to solve your problems.

Speaking of [splicing parameterized SQL], I want to explain this.
The implementation method of this method is: When splicing SQL statements, do not splice the parameter values into SQL statements. Use placeholder parameters in SQL statements. The specific parameter values are passed through ADO. NET command. parameters. add. This method should be used by popular ORM tools.

I think parameterized SQL statements can solve the problems mentioned at the beginning of this article, especially the first two. My Opinion on code maintenance is: If you mix SQL with C #, there is no way to parameterize SQL statements. To solve this problem, you need to separate the SQL statement from the project code, and then you can choose to use the configuration file or stored procedure as the container to save those SLQ statements.

Therefore, parameterized SQL statements are not omnipotent. the maintainability of code is irrelevant to the technical choice and related to the architecture design. Any excellent technology may write codes that are difficult to maintain. This is my point of view.

Modify existing splicing statements
Let's talk about dynamic query. Suppose I have such a query interface:

Obviously, when designing a program, it is impossible to know what filtering conditions the user will enter.
Therefore, people who like handwriting SQL usually write queries like this:
Copy codeThe Code is as follows:
Var query = "select ProductID, ProductName from Products where (1 = 1 )";
If (p. ProductID> 0)
Query = query + "and ProductID =" + p. ProductID. ToString ();
If (string. IsNullOrEmpty (p. ProductName) = false)
Query = query + "and ProductName like '" + p. ProductName + "'";
If (p. CategoryID> 0)
Query = query + "and CategoryID =" + p. CategoryID. ToString ();
If (string. IsNullOrEmpty (p. Unit) = false)
Query = query + "and Unit = '" + p. Unit + "'";
If (p. UnitPrice> 0)
Query = query + "and UnitPrice> =" + p. UnitPrice. ToString ();
If (p. Quantity> 0)
Query = query + "and Quantity> =" + p. Quantity. ToString ();

If this method is used, the first two shortcomings mentioned at the beginning of this article must exist.
I think a lot of people should know about parameterized queries. There may be two reasons for giving up:
1. This method of splicing SQL statements is very simple and easy to implement.
2. It is easy to wrap your own API. The parameter only needs one (omnipotent) string!
If you think it is difficult to solve these two problems, I will give you a solution today, "a very small change can solve the above two shortcomings, the transformed code is as follows:
Copy codeThe Code is as follows:
Var query = "select ProductID, ProductName from Products where (1 = 1)". AsCPQuery (true );
If (p. ProductID> 0)
Query = query + "and ProductID =" + p. ProductID. ToString ();
If (string. IsNullOrEmpty (p. ProductName) = false)
Query = query + "and ProductName like '" + p. ProductName + "'";
If (p. CategoryID> 0)
Query = query + "and CategoryID =" + p. CategoryID. ToString ();
If (string. IsNullOrEmpty (p. Unit) = false)
Query = query + "and Unit = '" + p. Unit + "'";
If (p. UnitPrice> 0)
Query = query + "and UnitPrice> =" + p. UnitPrice. ToString ();
If (p. Quantity> 0)
Query = query + "and Quantity> =" + p. Quantity. ToString ();

Have you seen the difference?
The difference is that the first line of code is followed by an extension method: AsCPQuery (true). The implementation code of this method will be discussed later.
The key code for this example is as follows:
Copy codeThe Code is as follows:
Private static readonly string ConnectionString =
ConfigurationManager. ConnectionStrings ["MyNorthwind_MSSQL"]. ConnectionString;
Private void btnQuery_Click (object sender, EventArgs e)
{
Product p = new Product ();
P. ProductID = SafeParseInt (txtProductID. Text );
P. ProductName = txtProductName. Text. Trim ();
P. CategoryID = SafeParseInt (txtCategoryID. Text );
P. Unit = txtUnit. Text. Trim ();
P. UnitPrice = SafeParseDecimal (txtUnitPrice. Text );
P. Quantity = SafeParseInt (txtQuantity. Text );
Var query = BuildDynamicQuery (p );
Try {
TxtOutput. Text = ExecuteQuery (query );
}
Catch (Exception ex ){
TxtOutput. Text = ex. Message;
}
}
Private CPQuery BuildDynamicQuery (Product p)
{
Var query = "select ProductID, ProductName from Products where (1 = 1)". AsCPQuery (true );
If (p. ProductID> 0)
Query = query + "and ProductID =" + p. ProductID. ToString ();
If (string. IsNullOrEmpty (p. ProductName) = false)
Query = query + "and ProductName like '" + p. ProductName + "'";
If (p. CategoryID> 0)
Query = query + "and CategoryID =" + p. CategoryID. ToString ();
If (string. IsNullOrEmpty (p. Unit) = false)
Query = query + "and Unit = '" + p. Unit + "'";
If (p. UnitPrice> 0)
Query = query + "and UnitPrice> =" + p. UnitPrice. ToString ();
If (p. Quantity> 0)
Query = query + "and Quantity> =" + p. Quantity. ToString ();
Return query;
}
Private string ExecuteQuery (CPQuery query)
{
StringBuilder sb = new StringBuilder ();
Using (SqlConnection connection = new SqlConnection (ConnectionString )){
SqlCommand command = connection. CreateCommand ();
// Bind the preceding result to the command object.
Query. BindToCommand (command );
// Output debugging information.
Sb. appendLine ("========================================== ============== ");
Sb. AppendLine (command. CommandText );
Foreach (SqlParameter p in command. Parameters)
Sb. AppendFormat ("{0 }={ 1} \ r \ n", p. ParameterName, p. Value );
Sb. appendLine ("========================================== =================\ r \ n ");
// Open the connection and execute the query
Connection. Open ();
SqlDataReader reader = command. ExecuteReader ();
While (reader. Read ())
Sb. AppendFormat ("{0}, {1} \ r \ n", reader [0], reader [1]);
}
Return sb. ToString ();
}
Private int SafeParseInt (string s)
{
Int result = 0;
Int. TryParse (s, out result );
Return result;
}
Private decimal SafeParseDecimal (string s)
{
Decimal result = 0 m;
Decimal. TryParse (s, out result );
Return result;
}

Let's take a look at the program running result:

According to the preceding debugging code:
Copy codeThe Code is as follows:
// Output debugging information.
Sb. appendLine ("========================================== ============== ");
Sb. AppendLine (command. CommandText );
Foreach (SqlParameter p in command. Parameters)
Sb. AppendFormat ("{0 }={ 1} \ r \ n", p. ParameterName, p. Value );
Sb. appendLine ("========================================== =================\ r \ n ");

As well as the facts reflected in the image, we can conclude that the transformed query is already a parameterized query!

Cause
Is it amazing: When an AsCPQuery () call is added, the original concatenated SQL statement is converted into a parameterized query?

The reasons are as follows:
1. The AsCPQuery () call generates a new object. Its type is not string, but CPQuery
2. When the + operator is executed each time, it is no longer the addition of two string objects.
3. When CPQuery is overloaded with the + operator, it recognizes the parameter values and SQL statement segments during the splicing process.
4. After the query is constructed, the result is no longer a string but a CPQuery object. It can generate parameterized SQL statements and contain all the parameter values.

AsCPQuery () is an extension method with the code:

Copy codeThe Code is as follows:
Public static CPQuery AsCPQuery (this string s)
{
Return new CPQuery (s, false );
}
Public static CPQuery AsCPQuery (this string s, bool autoDiscoverParameters)
{
Return new CPQuery (s, autoDiscoverParameters );
}

Therefore, a CPQuery object will be obtained after the call.
Observe the previous sample code and you will find that AsCPQuery () only needs to be called once.
To obtain a CPQuery object, you can also call the static method of the CPQuery type:
Copy codeThe Code is as follows:
Public static CPQuery New ()
{
Return new CPQuery (null, false );
}
Public static CPQuery New (bool autoDiscoverParameters)
{
Return new CPQuery (null, autoDiscoverParameters );
}

The two methods are equivalent. Sample Code:
Copy codeThe Code is as follows:
// The following two lines of code are equivalent and can be selected based on your preference.
Var query = "select ProductID, ProductName from Products where (1 = 1)". AsCPQuery ();
// Var query = CPQuery. New () + "select ProductID, ProductName from Products where (1 = 1 )";

Proceed with the splicing process:
Copy codeThe Code is as follows:
Public static CPQuery operator + (CPQuery query, string s)
{
Query. AddSqlText (s );
Return query;
}

CPQuery is overloaded with the + operator. Therefore, the result is no longer the result of adding two string objects, but the CPQuery object itself (JQuery's link design idea makes it easy to continue splicing ).
Think about it: "where id =" + "234" + "............ "
Do you think that 234 is a parameter value?
Similarly, "where name = '" + "Fish Li" + "'"
Obviously, "Fish Li" represents the parameter value of a string, because the left and right sides of the concatenation are surrounded.
Therefore, the CPQuery object recognizes the parameter values and SQL statement fragments during the splicing process.
The query merging is completed, but the SQL statement is saved in the CPQuery object at this time, and it cannot be returned by a single string, because it may contain multiple query parameters. Therefore, when executing a query, the related method needs to be able to receive the CPQuery object, for example:
Copy codeThe Code is as follows:
Static string ExecuteQuery (CPQuery query)
{
StringBuilder sb = new StringBuilder ();
Using (SqlConnection connection = new SqlConnection (ConnectionString )){
SqlCommand command = connection. CreateCommand ();
// Bind the preceding result to the command object.
Query. BindToCommand (command );

Once query. BindToCommand (command) is called, The CPQuery object assigns the parameterized SQL statement that is spliced internally and all the collected parameter values to the command object. Let's do the following things. I think everyone will, so I will not talk about it anymore.
CPQuery source code
Only some CPQuery code has been posted. All the relevant code is provided here:
Copy codeThe Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data. Common;
Namespace CPQueryDEMO
{
Public sealed class CPQuery
{
Private enum SPStep // processing progress of string Parameters
{
NotSet, // The string parameter is not started or has been spliced once.
EndWith, // end with a single quotation mark when Splicing
Skip // Skip splicing once
}
Private int _ count;
Private StringBuilder _ sb = new StringBuilder (1024 );
Private Dictionary <string, QueryParameter> _ parameters = new Dictionary <string, QueryParameter> (10 );
Private bool _ autoDiscoverParameters;
Private SPStep _ step = SPStep. NotSet;
Public CPQuery (string text, bool autoDiscoverParameters)
{
_ Sb. Append (text); _ autoDiscoverParameters = autoDiscoverParameters;
}
Public static CPQuery New ()
{
Return new CPQuery (null, false );
}
Public static CPQuery New (bool autoDiscoverParameters)
{
Return new CPQuery (null, autoDiscoverParameters );
}
Public override string ToString ()
{
Return _ sb. ToString ();
}
Public void BindToCommand (DbCommand command)
{
If (command = null)
Throw new ArgumentNullException ("command ");
Command. CommandText = _ sb. ToString ();
Command. Parameters. Clear ();
Foreach (KeyValuePair <string, QueryParameter> kvp in _ parameters ){
DbParameter p = command. CreateParameter ();
P. ParameterName = kvp. Key;
P. Value = kvp. Value. Value;
Command. Parameters. Add (p );
}
}
Private void AddSqlText (string s)
{
If (string. IsNullOrEmpty (s ))
Return;
If (_ autoDiscoverParameters ){
If (_ step = SPStep. NotSet ){
If (s [s. Length-1] = '\ '') {// end with a single quotation mark
_ Sb. Append (s. Substring (0, s. Length-1 ));
_ Step = SPStep. EndWith;} else {
Object val = TryGetValueFromString (s );
If (val = null)
_ Sb. Append (s );
Else
This. AddParameter (val. AsQueryParameter ());
}
}
Else if (_ step = SPStep. EndWith ){
// At this time, s should be a string parameter, not part of the SQL statement
// _ Step is uniformly modified in the AddParameter method to prevent non-string data from being spliced in the middle.
This. AddParameter (s. AsQueryParameter ());
}
Else {
If (s [0]! = '\'')
Throw new ArgumentException ("A string starting with a single quotation mark is waiting, but the parameter does not conform to the expected format. ");
// Find the closed input of single quotes.
_ Sb. Append (s. Substring (1 ));
_ Step = SPStep. NotSet;
}
}
Else {
// If the end of a single quotation mark is not checked, it must be part of an SQL statement.
_ Sb. Append (s );
}
}
Private void AddParameter (QueryParameter p)
{
If (_ autoDiscoverParameters & _ step = SPStep. Skip)
Throw new InvalidOperationException ("A string starting with a single quotation mark is waiting. Other parameters cannot be spliced. ");

String name = "@ p" + (_ count ++). ToString ();
_ Sb. Append (name );
_ Parameters. Add (name, p );

If (_ autoDiscoverParameters & _ step = SPStep. EndWith)
_ Step = SPStep. Skip;
}
Private object TryGetValueFromString (string s)
{
// 20, which can be byte, short, int, long, uint, ulong...
Int number1 = 0;
If (int. TryParse (s, out number1 ))
Return number1;
DateTime dt = DateTime. MinValue;
If (DateTime. TryParse (s, out dt ))
Return dt;
// 23.45, float, double, decimal
Decimal number5 = 0 m;
If (decimal. TryParse (s, out number5 ))
Return number5;
// Stop all other types.
Return null;
}

Public static CPQuery operator + (CPQuery query, string s)
{
Query. AddSqlText (s );
Return query;
}
Public static CPQuery operator + (CPQuery query, QueryParameter p)
{
Query. AddParameter (p );
Return query;
}
}
Public sealed class QueryParameter
{
Private object _ val;
Public QueryParameter (object val)
{
_ Val = val;
}
Public object Value
{
Get {return _ val ;}
}
Public static explicit operator QueryParameter (string)
{
Return new QueryParameter ();
}
Public static implicit operator QueryParameter (int)
{
Return new QueryParameter ();
}
Public static implicit operator QueryParameter (decimal)
{
Return new QueryParameter ();
}
Public static implicit operator QueryParameter (DateTime)
{
Return new QueryParameter ();
}
// Add other implicit type conversion operators to be supported.
}

Public static class CPQueryExtensions
{
Public static CPQuery AsCPQuery (this string s)
{
Return new CPQuery (s, false );
}
Public static CPQuery AsCPQuery (this string s, bool autoDiscoverParameters)
{
Return new CPQuery (s, autoDiscoverParameters );
}
Public static QueryParameter AsQueryParameter (this object B)
{
Return new QueryParameter (B );
}
}
}


Known CPQuery Problems and Solutions

Before reading this section, make sure you have read the source code, especially the AddSqlText and TryGetValueFromString methods. In the [cause of disclosure] section, I said: When CPQuery is overloaded with the + operator, it will identify the parameter values and SQL statement fragments during the splicing process. In fact, this so-called recognition process is mainly implemented in these two methods.

Especially in the TryGetValueFromString method, I wrote the following comment:
Copy codeThe Code is as follows:
// 20, which can be byte, short, int, long, uint, ulong...
// 23.45, float, double, decimal
// Stop all other types.

Obviously, when a number is converted into a string, it is difficult to know the original type of the number.
Therefore, in the implementation process of this method, I only use the most common data type that I think.
I cannot guarantee they will always run correctly.

In addition, although we can determine a string parameter value in the middle by judging two ', for the parameter values in the preceding example: if the string "Fish Li" is written as follows: "Fish" + "" + "Li "? It is very likely that the actual code is: s1 + "+ s2. In other words, the string parameter values are also spliced.

For these two questions, I can only say: I can't help either.

This is a known problem. Is there a solution?

The answer is: yes. The idea is also simple: since an error may occur during speculation, do not guess it. You must explicitly specify the parameter value.

How to [explicitly specify the parameter value?
In fact, it is not difficult. There are roughly the following methods:
1. Do not convert a non-string parameter value to a string. For example, set a number to a number.
2. String parameters must be identified separately.
For details, refer to the following sample code (equivalent to the previous Code ):
Copy codeThe Code is as follows:
Static CPQuery BuildDynamicQuery (Product p)
{
// The following two lines of code are equivalent and can be selected based on your preference.
Var query = "select ProductID, ProductName from Products where (1 = 1)". AsCPQuery ();
// Var query = CPQuery. New () + "select ProductID, ProductName from Products where (1 = 1 )";

// Note: The following Code cannot be written as query + = .....

If (p. ProductID> 0)
Query = query + "and ProductID =" + p. ProductID; // integer parameter.

If (string. IsNullOrEmpty (p. ProductName) = false)
// Add a string parameter to the query.
Query = query + "and ProductName like" + p. ProductName. AsQueryParameter ();

If (p. CategoryID> 0)
Query = query + "and CategoryID =" + p. CategoryID; // integer parameter.

If (string. IsNullOrEmpty (p. Unit) = false)
Query = query + "and Unit =" + (QueryParameter) p. Unit; // string parameter

If (p. UnitPrice> 0)
Query = query + "and UnitPrice> =" + p. UnitPrice; // decimal parameter.

If (p. Quantity> 0)
Query = query + "and Quantity> =" + p. Quantity; // integer parameter.

Return query;
}

In this Code, the number is not converted to a string. At runtime, it actually executes the implicit type conversion defined in the QueryParameter type. They are converted to the QueryParameter object. Therefore, there is no chance to make a mistake at all, and the execution efficiency is higher. The string parameter value must call the AsQueryParameter () extension method or explicitly convert it to a QueryParameter object. This does not need to be identified, so there is no chance of making a mistake.

I strongly recommend this method for splicing.

Note:
1. When concatenating string parameter values, you do not need to package two strings.
2. When AsCPQuery () or CPQuery. New () is called, no parameter is required or false is input.

Note:
1. When concatenating a string, C # itself allows the "abc" + 123 to write the string, but writing the string "abc" + 123. ToString () will be faster.
2. When CPQuery is used, all parameter values can be explicitly converted to QueryParameter, for example, "…" + (QueryParameter) p. Quantity

More CPQuery examples

CPQuery is designed to partially solve the disadvantages of splicing SQL. It is added to ClownFish as an enhancement function of ClownFish.

The ClownFish example also provides a more powerful example for CPQuery, which demonstrates the use of CPQuery in four databases:

To facilitate the use of CPQuery, The DbHelper class of ClownFish provides the corresponding overload methods for All database access methods:
Copy codeThe Code is as follows:
Public static int ExecuteNonQuery (CPQuery query)
Public static int ExecuteNonQuery (CPQuery query, DbContext dbContext)
Public static object ExecuteScalar (CPQuery query)
Public static object ExecuteScalar (CPQuery query, DbContext dbContext)
Public static T ExecuteScalar <T> (CPQuery query)
Public static T ExecuteScalar <T> (CPQuery query, DbContext dbContext)
Public static T GetDataItem <T> (CPQuery query)
Public static T GetDataItem <T> (CPQuery query, DbContext dbContext)
Public static List <T> FillList <T> (CPQuery query)
Public static List <T> FillList <T> (CPQuery query, DbContext dbContext)
Public static List <T> FillScalarList <T> (CPQuery query)
Public static List <T> FillScalarList <T> (CPQuery query, DbContext dbContext)
Public static DataTable FillDataTable (CPQuery query)
Public static DataTable FillDataTable (CPQuery query, DbContext dbContext)


Therefore, it is very easy to use:

Copy codeThe Code is as follows:
Var query = BuildDynamicQuery (p );
DataTable table = DbHelper. FillDataTable (query );


CPQuery design goals and recommendations

CPQuery is designed to convert traditional spliced SQL code into parameterized SQL, and to minimize the cost of use and learning.

The example at the beginning of this article proves that CPQuery has achieved this goal.
You only need to call the AsCPQuery () Extension Method on the first string to be concatenated, or add CPQuery. New () to all strings.

Note:

1. The AsCPQuery (true) or CPQuery. New (true) method is provided. It is only used to process existing code and can be considered as a compatibility solution.
2. I strongly recommend that you call AsCPQuery () or CPQuery. New () to process the stitching. The reason is explained above and will not be repeated here.

Some people see the sample code and think CPQuery is complicated to use. This statement is completely self-explanatory.
How short is the code for writing concatenated SQL statements?

As I have already said before: CPQuery is designed not to be a data access layer, but to solve the problem of splicing SQL.
It is inconvenient to use. It depends on the data access layer and the overall and packaging method of CPQuery.

To ensure that everyone can understand the sample code, I directly use ADO. NET, and the debugging code is included in the middle, so it looks a little longer. However, how many key codes are there?

CPQuery code is useless if you don't understand it. We only need to call its extension method (or static method) once.

For ease of use, I 'd like to say: If you want convenience, you can try ClownFish, which integrates CPQuery.

Reminder
At the beginning of this article, I have clearly expressed my point of view that CPQuery can only solve the first two shortcomings of SQL concatenation.

CPQuery should be used only when dynamic queries are required, because the code that is concatenated involves multiple statements is mixed together, which negatively affects the maintainability of the Code.

Click here to download the CPQuery source code and sample code