I never liked it. Use the method of splicing SQL when accessing the database for the following reasons:
1. Unsafe: There is a risk of being injected into SQL.
2. Performance may be affected: Each SQL statement requires the cost of the database engine to perform [statement analysis].
3. Impact code maintainability: SQL statements mixed with C #, want to modify the SQL will have to recompile the program, and two kinds of code mixed together, the readability is not good.
So I usually choose parameterized SQL to implement access to the database and separate the SQL statement from the project Code (C #).
However, some people may say: My business logic is very complex, where the filter conditions can not be determined in advance, so do not splice SQL is not.
To see these drawbacks, ORM users may think that using ORM Tools is the ultimate solution.
Yes, it does ORM to solve these problems.
However, the solution is not only an ORM, but some people like to write SQL.
So, instead of writing to ORM users, this blog is written to all friends who like to write SQL statements.
What is Cpquery?
See the title of the blog, you will not think: Cpquery is what?
Here is my answer:
1. Cpquery is an abbreviation: Concat parameterized Query
2. Cpquery allows you to continue to write parameterized SQL using familiar stitching methods
3. Cpquery is a solution that I designed to solve the first two drawbacks of stitching SQL.
4. Cpquery is also the name of the core type in this solution.
I hope you can remember the name of Cpquery.
who is cpquery suitable for use?
A: It is suitable for people who prefer to write SQL code, especially if you need to do dynamic queries.
Parameterized SQL statements
For scenarios where dynamic queries are required, I think stitching up SQL may be necessary, but you should not splice the values into the SQL statement, or you should splice the parameterized SQL to solve the problem you are experiencing.
Speaking of "splicing parameterized SQL", I want to explain this thing.
This method is implemented by stitching the SQL statements, do not splice the parameter values into the SQL statements, the use of placeholder parameters in SQL statements, the specific parameter values through the Ado.net COMMAND.PARAMETERS.ADD () passed in. Today's popular ORM tools should be used in this way.
I think parameterized SQL statements can solve the problems mentioned at the beginning of this article, especially the first two. For the maintenance of the code, my point is: If you just mix SQL with C #, then parameterized SQL statements are not. If you want to solve this problem, you need to separate the SQL statement from the project code, and then you can choose to use a configuration file or stored procedure as a container to hold those SLQ statements.
Therefore, parameterized SQL is not omnipotent, the maintainability of the code is not related to the choice of technology, and the design of the architecture. It's my point of view that any good technology can write code that is difficult to maintain.
To transform existing stitching statements
or dynamic query, suppose I have such a query interface:
Obviously, when designing a program, it is impossible to know what filter conditions the user will enter.
As a result, people who prefer handwritten SQL often write queries like this:
Copy Code code as follows:
var query = "Select ProductID, ProductName from the 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 you use this approach, the first two drawbacks mentioned at the beginning of this article must be present.
I think a lot of people should be aware of parameterized queries, the final waiver may have the following 2 reasons:
1. The way to stitch SQL statements is simple and easy to implement.
2. Easy to wrap your own API, parameters only need one (omnipotent) string!
If you think these 2 reasons are difficult to solve, then I will give you today "a small change can solve the above two shortcomings" solution, the modified code is as follows:
Copy Code code as follows:
var query = "Select ProductID, ProductName from the 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 ();
Do you see the difference?
The difference lies in the first line of code, followed by an extension method: Ascpquery (True), the implementation code for this method I'll say later.
The main key code for this example is as follows:
Copy Code code 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 the 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 ();
Binds the previous concatenation 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 connection, execute 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 = 0m;
Decimal. TryParse (s, out result);
return result;
}
Let's take a look at the results of the program running:
Based on the debugging code given earlier:
Copy Code code 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 picture, you can draw the conclusion: after the transformation of the query has been parameterized query!
The reason for revealing
Is it amazing: adding a ascpquery () call, the original concatenation of the SQL into a parameterized query?
The following are some of the reasons for this:
1. The call to Ascpquery () produces a new object whose type is not string but cpquery
2. Each time the + operator is executed, it is no longer the addition of two string objects.
3. Cpquery overloads the + operator, which identifies the parameter values in the concatenation process and the SQL statement fragment.
4. After the query construction is complete, the resulting result is no longer a string, but rather a Cpquery object that generates a parameterized SQL statement that also contains all the parameter values.
Ascpquery () is an extension method, code:
Copy Code code 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);
}
So after the call, you get a Cpquery object.
Looking at the previous example code, you will find that ascpquery () only needs to be invoked once.
To get a Cpquery object, you can also call a static method of the Cpquery type:
Copy Code code as follows:
public static Cpquery New ()
{
return new Cpquery (null, FALSE);
}
public static Cpquery New (bool autodiscoverparameters)
{
return new Cpquery (null, autodiscoverparameters);
}
These two methods are equivalent, sample code:
Copy Code code as follows:
The following two lines of code are equivalent and can be selected according to preferences.
var query = "Select ProductID, ProductName from the products where (1=1)". Ascpquery ();
var query = cpquery.new () + "Select ProductID, ProductName from the products where (1=1)";
Continue to see the stitching processing:
Copy Code code as follows:
public static cpquery operator + (cpquery query, string s)
{
Query. Addsqltext (s);
return query;
}
Cpquery overloaded the + operator, so the result is no longer the result of the addition of the two string objects, but the Cpquery object itself (the idea of the link design of jquery to facilitate continued stitching).
Think about this: "Where id =" + "234" + "..."
Do you think I can tell if 234 is a parameter value?
Similarly, "where name = '" + "Fish Li" + ""
Obviously, "Fish Li" is the parameter value of a string, because the stitching of the left and right sides are "surrounded."
Therefore, the Cpquery object will recognize the parameter values and the SQL statement fragment during stitching.
The query concatenation is complete, but the SQL statement at this point is saved in the Cpquery object, and it is not possible to return it through a string, because it may also contain multiple query parameters. Therefore, when executing a query, the related methods need to be able to receive the Cpquery object, for example:
Copy Code code as follows:
static string executequery (Cpquery query)
{
StringBuilder sb = new StringBuilder ();
using (SqlConnection connection = new SqlConnection (ConnectionString)) {
SqlCommand Command = connection. CreateCommand ();
Binds the previous concatenation result to the Command object.
Query. Bindtocommand (command);
Once you have called query. Bindtocommand (command); The Cpquery object assigns the parameterized SQL that it is stitching internally, and all the parameter values collected to the Command object. After the things, how to do it, I think we all will, will not say more.
Cpquery Source
Only a portion of the Cpquery code is posted, and here's all the relevant code:
Copy Code code 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//String parameter processing progress
{
NotSet,//No start or concatenation of string parameters has been completed.
Endwith,//stitching when encountered a single quote end
Skip//Skip Stitch 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] = = ' \ ') {//encountered a single quote end
_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 point s should be a string parameter, not part of the SQL statement
_step is uniformly modified in the Addparameter method to prevent splicing of non string data in the middle.
This. Addparameter (S.asqueryparameter ());
}
else {
if (s[0]!= ' \ ")
throw new ArgumentException ("is waiting for a string that starts with a single quote, but the parameter does not conform to the expected format." ");
Find the closed input for single quotes.
_SB. Append (s.substring (1));
_step = Spstep.notset;
}
}
else {
Do not check the end of a single quote, which is considered to be part of the SQL statement.
_SB. Append (s);
}
}
private void Addparameter (QueryParameter p)
{
if (_autodiscoverparameters && _step = = Spstep.skip)
throw new InvalidOperationException ("is waiting for a string that starts with a single quote, and no more arguments are allowed at this time." ");
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, 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, can be float, double, decimal
Decimal Number5 = 0m;
if (decimal. TryParse (S, out Number5))
return NUMBER5;
All other types discard the attempt.
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 a)
{
return new QueryParameter (a);
}
public static implicit operator queryparameter (int a)
{
return new QueryParameter (a);
}
public static implicit operator QueryParameter (decimal a)
{
return new QueryParameter (a);
}
public static implicit operator QueryParameter (DateTime a)
{
return new QueryParameter (a);
}
Other implicit type conversion operator overloads that need to be supported please add them yourself.
}
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 issues and solutions for Cpquery
Before you begin reading this section, make sure you have read the previous source code, especially the Addsqltext,trygetvaluefromstring two methods. In the "Uncover Reason" section, I said: Cpquery overloaded the + operator, will identify the concatenation process of parameter values and SQL statement fragments. In fact, this so-called recognition process is mainly implemented in these two methods.
Especially in the Trygetvaluefromstring method, I have reluctantly written the following note:
Copy Code code as follows:
20, can be byte, short, int, long, uint, ulong ...
23.45, can be float, double, decimal
All other types discard the attempt.
Obviously, when you turn a number into a string, it's hard to know what the original type of number is.
Therefore, in the implementation of this method, I use only the data types that I think are most common.
I can't guarantee that they will always work correctly.
Also, although we can determine the middle is a string parameter value by judging two ', however, for the parameter values in the previous example: "Fish Li" is the string if it is written like this: "Fish" + "" + "Li"? Because it is possible that the actual code is: S1 + "" + s2, in other words: string parameter values are also spliced.
For these two questions, I can only say: I have no way.
This is an already known problem, so is there a solution?
The answer is: yes. The idea is simple: since guessing can go wrong, then don't guess, you have to explicitly indicate the value of the parameter.
How do I "explicitly indicate the value of a parameter"?
In fact, it is not difficult, there are roughly the following methods:
1. Non-string parameter values do not turn into strings, for example: numbers let it be numbers.
2. String parameters need to be identified separately.
You can refer to the following sample code (equivalent to the previous code):
Copy Code code as follows:
Static Cpquery Builddynamicquery (Product p)
{
The following two lines of code are equivalent and can be selected according to preferences.
var query = "Select ProductID, ProductName from the products where (1=1)". Ascpquery ();
var query = cpquery.new () + "Select ProductID, ProductName from the products where (1=1)";
Note: The following stitching code can not be written in: Query + = ...
if (P.productid > 0)
query = query + "and ProductID =" + P.productid; The integer argument.
if (string. IsNullOrEmpty (p.productname) = = False)
Adds a string parameter to the query.
query = query + "and ProductName like" + p.productname.asqueryparameter ();
if (P.categoryid > 0)
query = Query + "and CategoryID =" + P.categoryid; The integer argument.
if (string. IsNullOrEmpty (p.unit) = = False)
query = query + "and unit =" + (queryparameter) p.unit; String parameters
if (P.unitprice > 0)
query = query + "and UnitPrice >=" + p.unitprice; The decimal parameter.
if (p.quantity > 0)
query = query + "and Quantity >=" + p.quantity; The integer argument.
return query;
}
In this code, the number does not turn into a string, which, at run time, is actually performing an implicit type conversion defined in the QueryParameter type, which translates into a QueryParameter object, so there is no chance of error, and execution is more efficient. String parameter values need to call the Asqueryparameter () extension method or explicitly convert to a QueryParameter object, and there is no need to identify it, so there is no chance of getting it wrong.
I highly recommend using this method to splice.
Attention:
1. String parameter values in stitching, do not need to be wrapped up by two.
2. In a call to Ascpquery () or cpquery.new (), no arguments are required, or false is passed in.
Description
1. When stitching strings, C # itself allows "ABC" + 123 to write, just say "abc" + 123.ToString () will be faster.
2. When using Cpquery, all parameter values can be explicitly converted to QueryParameter, for example: "..." + (QueryParameter) p.quantity
more Cpquery examples
Cpquery is designed to partially address the shortcomings of the concatenation of SQL, as clownfish enhancements have been added to the clownfish.
In the clownfish example, a more powerful example was also specifically prepared for cpquery, which demonstrated the use of cpquery in 4 different databases:
To facilitate the use of the Cpquery,clownfish DBHelper class for all database access methods, the corresponding overloaded method is provided:
Copy Code code 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)
So it's very easy to use:
Copy Code code as follows:
var query = Builddynamicquery (p);
DataTable table = dbhelper.filldatatable (query);
The design goal of Cpquery and suggestions for its use
Cpquery's design goal is to convert traditional stitching SQL code into parameterized SQL, and to minimize usage and learning costs.
The example at the beginning of this article I think has proved that Cpquery has achieved this goal.
The Ascpquery () extension method is called on only the first string to be spliced, or it can be resolved by adding cpquery.new () before all strings.
Attention:
1. Provides ascpquery (true) or cpquery.new (True) method, which is used only to process existing code and is considered a compatibility solution.
2. I strongly recommend that you call Ascpquery () or cpquery.new () to handle stitching, which is explained earlier and is not repeated here.
Some people have seen the example code think that Cpquery is very complex to use. This argument is entirely a no-brainer.
How much shorter will you write the code for splicing SQL?
As I said earlier: Cpquery's design goal is not a data access layer, it is designed to solve the mosaic SQL.
It is inconvenient to use, to see the specific data access layer to the overall and cpquery packaging methods.
Example code to ensure that everyone can understand, I directly used the ado.net, and the middle contains debugging code, so it looks a bit long, but, the key code how many, this still can't see?
The code of the Cpquery class, you do not understand and no relationship, we only need to call its extension method (or static method) on it.
As for usability, the last thing I want to say is: If you want to be convenient, you can try Clownfish, it integrates the cpquery.
Friendly Tips
At the outset of this article, I made it clear that Cpquery only solves the first two drawbacks of stitching SQL.
You should only use cpquery when you need to implement dynamic queries, because stitching can mix together code that involves multiple statements, which can adversely affect the maintainability of your code.
Click here to download Cpquery source and sample code
