Shulou(Shulou.com)05/31 Report--

CPQuery how to splice SQL, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

What is CPQuery? When you see the title of the blog, do you think: what is CPQuery? The following is my answer: 1. CPQuery is an acronym: Concat Parameterized Query 2. CPQuery allows you to continue to use the familiar splicing way to write parameterized SQL 3. CPQuery is a solution I designed, which can solve the first two shortcomings 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 suitable for CPQuery? A: it is suitable for people who like handwritten SQL code, especially when you need to write dynamic queries.

Parameterized SQL statements for scenarios that require dynamic queries, I think: splicing SQL may be necessary, but you should not concatenate values into SQL statements, or you should concatenate parameterized SQL to solve your problems. Speaking of [splicing parametric SQL], I want to explain this thing. This method is implemented by not concatenating the parameter values into the SQL statement when concatenating the SQL statement, using placeholder parameters in the SQL statement, and passing the specific parameter values through ADO.NET 's command.Parameters.Add (). This approach should be adopted by all the popular ORM tools today. I think parameterized SQL statements can solve the problems mentioned at the beginning of this article, especially the first two. As for the maintenance of the code, my point is: if you just mix SQL with C #, there is no way to parameterize SQL statements. If you want to solve this problem, you need to separate the SQL statements from the project code, and then you can choose to use the configuration file or stored procedure as the container for storing those SLQ statements. Therefore, the parameterized SQL is not omnipotent, and the maintainability of the code has nothing to do with the choice of technology, but related to the design of the architecture. It is my opinion that any good technology can write code that is difficult to maintain. Transform the existing stitching statement or dynamic query, suppose I have such a query interface:

Obviously, when designing a program, it is impossible to know what filter criteria the user will enter. Therefore, people who like handwritten SQL usually write queries like this: copy the code as follows: var query = "select ProductID, ProductName from Products where (1x1)"; 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 shortcomings mentioned at the beginning of this article must exist. I think many people should know about parameterized queries, and there may be two reasons for giving up: 1. This way of concatenating SQL statements is simple and easy to implement. two。 Easy to wrap your own API, parameters only need a (omnipotent) string! If you think these two reasons are difficult to solve, I will give you a solution today that can solve the above two shortcomings with minimal changes. The modified code is as follows: copy the code as follows: var query = "select ProductID, ProductName from Products where (1x1)" .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 a call to an extension method: AsCPQuery (true), the implementation code of which I'll talk about later. The main key code for this example is as follows: copy 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 Products where (1x 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 previous splicing result to the command object. Query.BindToCommand (command); / / outputs 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 = 0m; decimal.TryParse (s, out result); return result;}

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

According to the debug code given earlier: copy the 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 by the pictures, it can be concluded that the modified query is already a parameterized query! Isn't it amazing to reveal the reason: add a call to AsCPQuery () and turn the original spliced SQL into a parameterized query? The reasons for this are as follows: 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 and identifies parameter values and SQL statement fragments during splicing. 4. After the query is constructed, the result is no longer a string, but a CPQuery object, which generates a parameterized SQL statement that also contains all the parameter values. AsCPQuery () is an extension method. The code: copy the 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 (autoDiscoverParameters);}

So after the call, you get a CPQuery object. Looking at the previous sample code, you will find that AsCPQuery () only needs to be called once. To get a CPQuery object, you can also call a static method of type CPQuery: copy the code 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 the code as follows: / / the following two lines of code are equivalent and can be selected according to your preference. Var query = "select ProductID, ProductName from Products where (1x 1)" .AsCPQuery (); / / var query = CPQuery.New () + "select ProductID, ProductName from Products where (1x 1)"

Continue to look at the splicing process: copy the code as follows: public static CPQuery operator + (CPQuery query, string s) {query.AddSqlText (s); return query;}

CPQuery overloads the + operator, so the result is no longer the result of the addition of two string objects, but the CPQuery object itself (the link design idea of JQuery, which makes it easy to continue stitching). Think about it: "where id =" + "234" + "." Do you think I can tell 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 by'. Therefore, the CPQuery object recognizes the parameter values and SQL statement fragments in the splicing process. The query splicing is complete, but at this point the SQL statement is saved in the CPQuery object and cannot be returned as a string, because it may also contain multiple query parameters. Therefore, when executing the query, the relevant method needs to be able to receive the CPQuery object, for example: the copy 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 previous splicing result to the command object. Query.BindToCommand (command)

Once query.BindToCommand (command) is called, the CPQuery object assigns the parameterized SQL it spliced internally, as well as all the parameter values collected, to the command object. For the rest, you can do what you should do. I think everyone will, so I won't say any more. Only part of the CPQuery code is posted in front of the CPQuery source code. Here is all the relevant code: the copy 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 / / string parameter processing progress {NotSet, / / has not started or completed a string parameter stitching. EndWith, / / encountered a single quote ending Skip / / skipped once stitching} private int _ count; private StringBuilder _ sb = new StringBuilder (1024); private Dictionary _ parameters = new Dictionary (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 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] = ='\'') {/ / encounter a single quote closing _ 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) {/ / s at this time should be a string parameter, not a part of the SQL statement / / _ step is uniformly modified in the AddParameter method to prevent non-string data from being stitched. This.AddParameter (s.AsQueryParameter ());} else {if (s [0]! ='\') throw new ArgumentException ("waiting for a string starting with single quotation marks, but the arguments are not in the expected format.") ; / / find the closed input of single quotes. _ sb.Append (s.Substring (1)); _ step = SPStep.NotSet;}} else {/ / does not check the end of single quotation marks and 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 ("waiting for a string starting with single quotation marks, no other parameters are allowed to be concatenated 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.45It can be float, double, decimal decimal number5 = 0m; if (decimal.TryParse (s, out number5)) return number5; / / all other types give up trying. 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 your own. } 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 (autoDiscoverParameters);} public static QueryParameter AsQueryParameter (this object b) {return new QueryParameter (b);}

Known problems and Solutions for CPQuery before you start reading this section, make sure you have read the previous source code, especially the AddSqlText,TryGetValueFromString methods. In the Secret reason section, I said that CPQuery overloads the + operator and identifies parameter values and fragments of SQL statements during concatenation. In fact, this so-called recognition process is mainly realized in these two methods. Especially in the TryGetValueFromString method, I have no choice but to write the following comment: copy the code as follows: / / 20, it can be byte, short, int, long, uint, ulong. / / 23.45. it can be float, double, decimal / / other types all give up trying.

Obviously, when a number is turned into a string, it is difficult to know what the original type of the number is. Therefore, in the implementation of this method, I used only the data types that I thought were the most common. I can't guarantee that they will always work correctly. Also, although we can determine a string parameter value in the middle by judging two', however, for the parameter value in the previous example, what if the string "Fish Li" is written like this: "Fish" + "Li"? Because 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: there is nothing I can do. This is a known problem, so is there a solution? The answer is: yes. The idea is also simple: since guessing can go wrong, don't guess, you have to explicitly point out the parameter values. How to [explicitly indicate the parameter value]? In fact, it is not difficult, there are roughly the following ways: 1. The value of a non-string parameter should not be converted to a string, for example, let a number be a number. two。 String parameters need to be identified separately. For specific methods, please refer to the following sample code (equivalent to the previous code): copy the code as follows: static CPQuery BuildDynamicQuery (Product p) {/ / the following two lines of code are equivalent and can be selected according to 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 splicing code cannot be written as: AsCPQuery (). If (p.ProductID > 0) query = query + "and ProductID =" + p.ProductID; / / integer parameter. 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; / / 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 numbers are not converted to strings, but at run time, they are actually performing implicit type conversions defined in QueryParameter types, which are converted to QueryParameter objects, so there is no chance of getting it wrong at all, and the execution is more efficient. The string parameter value needs to be called the AsQueryParameter () extension method or explicitly converted to a QueryParameter object, and it doesn't need to be recognized at this time, so there's no chance to make a mistake. I highly recommend this method of splicing. Note: 1. When string parameter values are concatenated, they do not need to be wrapped by two's. 2. No parameter is required or false is passed in the call to AsCPQuery () or CPQuery.New (). Description: 1. C # itself allows "abc" + 123when concatenating strings, but it is faster to say "abc" + 123.ToString (). two。 When using CPQuery, all parameter values can be explicitly converted to QueryParameter, for example: "…" + (QueryParameter) p.Quantity more CPQuery sample CPQuery is designed to partially address the shortcomings of splicing SQL, which has been added to ClownFish as an enhancement to ClownFish. In the ClownFish example, a more powerful example is also prepared specifically for CPQuery, which demonstrates the use of CPQuery in four databases:

To facilitate the use of CPQuery ClownFish's DbHelper class provides corresponding overloaded methods for all database access methods: copy the 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 (CPQuery query) public static T ExecuteScalar (CPQuery query, DbContext dbContext) public static T GetDataItem (CPQuery query) public static T GetDataItem (CPQuery query, DbContext dbContext) public static List FillList (CPQuery query) public static List FillList (CPQuery query DbContext dbContext) public static List FillScalarList (CPQuery query) public static List FillScalarList (CPQuery query, DbContext dbContext) public static DataTable FillDataTable (CPQuery query) public static DataTable FillDataTable (CPQuery query, DbContext dbContext)

Therefore, it is also very easy to use: copy the code as follows: var query = BuildDynamicQuery (p); DataTable table = DbHelper.FillDataTable (query)

The design goal of CPQuery and use suggestion CPQuery is to transform the traditional spliced SQL code into parameterized SQL, and to minimize the cost of use and learning. I think the example at the beginning of this article has proved that CPQuery has achieved this goal. You just need to call the AsCPQuery () extension method on the first concatenated string, or precede all strings with CPQuery.New (). Note: 1. Providing AsCPQuery (true) or CPQuery.New (true) methods, which are used only to deal with existing code, can be considered a compatibility solution. two。 I strongly recommend calling AsCPQuery () or CPQuery.New () to handle splicing. The reason has been explained earlier and will not be repeated here. Some people will think that CPQuery is very complicated to use when they see the sample code. This is a completely mindless statement. How much shorter will your code for splicing SQL be? As I said earlier, the design goal of CPQuery is not a data access layer, it is only designed to solve the problem of splicing SQL. Whether it is convenient to use depends on the specific data access layer and the overall and packaging of CPQuery. In order to make sure that everyone can understand the sample code, I used ADO.NET directly and included debug code in the middle, so it looks a little long, but can't you see how much key code there is? It doesn't matter if you don't understand the code of the CPQuery class, we only need to call its extension method (or static method) once. The last thing I want to say about ease of use is that if you want convenience, you can try ClownFish, which integrates CPQuery.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.