Use formatter principles to automate parameterized queries and formatter Automation

Use formatter principles to automate parameterized queries and formatter Automation

For small partners who are often busy with server development, it is normal to deal with the DB layer. However, the addition of query conditions on each page often means that the back-end code parameters increase year-on-year, of course, you can directly PASS Parameters without using the parameterized condition query provided by sqlhelper. As a result, you may write less code, but there is a hidden problem: SQL Injection, I think you are familiar with SQL injection. The related resources and preventive measures are the same on the Internet. If you are interested, you can learn about them by yourself. The commonly used injection tool is sqlmapper. If you are interested, you can learn about it at the same time.

Therefore, to write code, you must maintain the code elegance, a small number of statements, and ensure the security and performance of the code without being easily injected. Are there any two compatible methods, for people who are generally accustomed to sticking to the original code thinking, there may be no such thing, but those who are used to thinking about problems from different perspectives can make things easier in Rome, for example, this is based on. NET comes with the stirngfromatter principle to achieve an automatic parameterized query ~~

Because the oral expression is not very good, I directly explained the texture, please refer

This is the original parameterized query.

1 public PayOrderEntity GetPayOrderInfoById (int id) 2 {3 PayOrderEntity parorderModel = new PayOrderEntity (); 4 List <SqlParameter> paramList = new List <SqlParameter> (); 5 string SQL = @ "select p. id as pid 6, c. id 7, c. userId 8, p. orderId 9, p. transactionId10, p. payType11, c. totalPrice as orderprice12, p. totalPrice as payprice13, c. [Status] as orderstatus14, p. [Status] as paystatus15, c. createTime 16, p. payTime17, c. remark as orderremark18, p. remark as payremark19 from t_ContentOrder (nolock) c20 left join t_Payment (nolock) p21. id = p. orderId where c. isDeleted = 0 and p. isDeleted = 0 and p. id = {0} "; 22 paramList. add (new SqlParameter ("@ id", id); 23 try24 {25 var dt = SqlHelper. executeDataset (write_connstring, CommandType. text, SQL, paramList. toArray (); 26 27 // call by: xuja 10: 34: 1928 // var dt = SqlQuery (SQL, id) using the rewritten DB driver query method ); 29 30 31 parorderModel = DbTableConvertor <PayOrderEntity>. convertToEntity (dt. tables [0]); 32} 33 catch (Exception ex) 34 {35 Core. log. traceLogger. error (ex); 36} 37 return parorderModel; 38}

View Code

 

Step 1 instantiate a sqlparams object

Step 2: Pass the parameters that meet the conditions into the defined parameter entity and assign values (conditional verification may be required)

Step 3: Pass the filled parameter object to the called sqlhelper query interface

In this way, the entire query process can be implemented in four steps.

Looking at the process is not complicated, but you have never thought of a problem. If there are more than six query conditions on a page, it will be difficult to define it. I believe everyone will have this feeling, I don't want to list external parameters. Next, we will use the formatter principle to look at the parameterized query code after reconstruction.

1 /// <summary> 2 // SQL parameter automatic concatenation method 3 // Creator: xujiangan 4 // 10:35:40 5 // </summary> 6 /// <param name = "sqlCommand"> SQL to be queried </param> 7 /// <param name = "param"> list of parameters to be spliced </param> 8 // <returns> </returns> 9 public Tuple <string, sqlParameter []> ProcessSqlCommand (string sqlCommand, params object [] param) 10 {11 var tempKVDic = param. select (item, I) => new KeyValuePair <string, object> ("@ an" + I, item )). toDictionary (k => k. key, v => v. value); 12 13 var tempSqlCommand = string. format (sqlCommand, tempKVDic. keys. toArray (); 14 15 var tempParams = tempKVDic. select (t => new SqlParameter (t. key, t. value )). toArray (); 16 17 return Tuple. create (tempSqlCommand, tempParams); 18}

View Code

 

The returned values of methods are defined by tuples because they are not specific. The capabilities of tuples are similar to those of the dynamic T type. We will not introduce them here. There are only two parameter conditions: 1. SQL statement 2. parameter conditions to be passed

Code logic: 1. the parameter list uses the key-Value Pair method to correspond to one by one, and the parameters can automatically grow to form the parameter list dictionary; 2. concatenate SQL statements and parameter dictionaries using the format method. 3. returns the concatenated query string to the caller.

The original interface does not have the overload of this parameter condition. Next, we will also learn to rewrite the interface for sqlhelper to execute the query, as shown below:

1 /// <summary> 2 // rewrite the ExcuteQuery method, so that you can add parameter 3 automatically. // The Creator: xujiangan 4 // 10:35:07 5 // </summary> 6 /// <param name = "sqlCommand"> run the SQL statement </param> 7 // <param name = "param"> query parameters to be added </param> 8 // <returns> </returns> 9 public DataSet SqlQuery (string sqlCommand, params object [] param) 10 {11 var dt = new DataSet (); 12 if (param = null | param. length = 0) 13 {14 dt = SqlHelper. executeDataset (write_connstring, sqlCommand, CommandType. text); 15} 16 17 var temp = ProcessSqlCommand (sqlCommand, param); 18 19 dt = SqlHelper. executeDataset (write_connstring, CommandType. text, temp. item1, temp. item2); 20 21 // object entity = DbTableConvertor <object>. convertToEntity (dt. tables [0]); 22 23 return dt; 24}

View Code

 

Everyone can understand the code. It is nothing more than a distinction between the two cases of parameter and no parameter. I will not explain it...

As the saying goes, to do good things, we must first sharpen the tool. With the above preparations, we can call the interface we just rewritten.

For more information about calling, see the following:

1 public PayOrderEntity GetPayOrderInfoById (int id) 2 {3 PayOrderEntity parorderModel = new PayOrderEntity (); 4 string SQL = @ "select p. id as pid 5, c. id 6, c. userId 7, p. orderId 8, p. transactionId 9, p. payType10, c. totalPrice as orderprice11, p. totalPrice as payprice12, c. [Status] as orderstatus13, p. [Status] as paystatus14, c. createTime 15, p. payTime16, c. remark as orderremark17, p. remark as payremark18 from t_ContentOrder (nolock) c19 left join t_Payment (nolock) p20 on c. id = p. orderId where c. isDeleted = 0 and p. isDeleted = 0 and p. id = {0} "; 21 try22 {23 // call by: xuja 2017-05-27 10: 34: 1924 var dt = SqlQuery (SQL, id); 25 parorderModel = DbTableConvertor <PayOrderEntity>. convertToEntity (dt. tables [0]); 26} 27 catch (Exception ex) 28 {29 Core. log. traceLogger. error (ex); 30} 31 return parorderModel; 32}

View Code

 

Did you find anything missing? Yes. At this time, we no longer need to define complicated sqlparams object pulling. Just pass the parameter directly. If there are too many parameters, we can define the array or parameter object transmission ~

PS: note that the parameter location in the SQL code statement is changed to a placeholder !!!

So far, the entire automatic parameterized query is basically over. If you have any questions, please leave a message to me and tell me that

The project has been applied to the admin.k.net payment order details page for query. After multiple tests, no problems have been found. It seems that the project can be used.

It is recommended that the SQL code be case-insensitive and be parsed only once.

Mountain up and down

By: Jsonxu