Reconstruction of the data access layer (and reconstruction of Perl applications)

When I went to school, I heard the saying "most of the code written by a student in the first few years of programming after graduation is spam", and I did not care about it. I have been working for more than a year now, and I feel more and more omissions in my code. Therefore, I have been doing a lot of reconstruction recently. Take the data access layer that I wrote four months ago as an example. This layer is located at the bottom of the entire system, and queries and updates are performed based on the input SQL statements. Take the query as an example. I thought it was very simple at the time. The writing method is as follows:

1 // code segment 1
2 Public datatable executequery (string plain text)
3 {
4 If (plain text = NULL)
5 return NULL;
6
7 sqldataadapter adapter = new sqldataadapter (plain text, connection );
8 datatable table = new datatable ();
9 try
10 {
11 Adapter. Fill (table );
12}
13 catch (sqlexception E)
14 {
15
16 httpcontext. Current. response. Redirect (string. Format ("~ /Errorpage? Errorstring = {0} ", httpcontext. Current. server. urlencode (E. Message )));
17}
18 finally
19 {
20 connection. Close ();
21}
22
23 return table;
24}

This method is called as follows:

1 // code segment 2
2 string cmd = "select * From materialclass where materialclasscode = {0 }";
3cmd = string. Format (CMD, scope );
4 datatable table = new bitaecsqlexe (). executequery (CMD );

As you can easily see, this code is very fragile and cannot cope with special characters (single quotes, percent signs, etc.) in the query string ). The program has poor fault tolerance and is vulnerable to SQL injection attacks. In addition, for exceptions that may be thrown by SQL operations, it is also very bad to jump to the error page after capturing it here, because the underlying code will be called by pages, it is also called by other functions that are not caused by client requests (such as Windows Services deployed on servers and dedicated worker threads that process complex tasks ), httpcontext. current is meaningless. In fact, in such underlying code, exceptions can only be processed as necessary. Whether to jump to a friendly error prompt interface or write error logs, it should be decided by higher-level code.
The refactoring of this part is actually very simple, that is, all parameters are encapsulated with sqlparameter during the query. The method preparecommand is added to the refactoring to obtain the required sqlcommand object. The reconstruction of executequery is as follows:

1 // code segment 3
2 Private sqlcommand preparecommand (string parameter text, sqlparameter [] parameter parms)
3 {
4 If (plain text = NULL)
5 throw new argumentnullexception ();
6
7 sqlcommand cmd = new sqlcommand (plain text, connection );
8 If (partition parms! = NULL) & (partition parms. length> 0 ))
9 foreach (sqlparameter Param in milliseconds parms)
10 {
11 cmd. Parameters. Add (PARAM );
12}
13
14 return cmd;
15}
16
17 Public datatable executequery (string plain text, sqlparameter [] sqlparams)
18 {
19 if (plain text = NULL)
20 return NULL;
21
22 sqldataadapter adapter = new sqldataadapter (this. preparecommand (plain text, sqlparams ));
23 datatable table = new datatable ();
24 try
25 {
26 Adapter. Fill (table );
27}
28 catch (sqlexception E)
29 {
30 throw E;
31}
32 finally
33 {
34 if (connection. State! = Connectionstate. open)
35 connection. Close ();
36}
37
38 return table;
39}

The above refactoring is the definition of the executequery method, which is only the beginning of refactoring, because the existing code is full of method calls similar to code snippet 2, and needs to be changed to the following form:

1 // code segment 4
2 string cmd = "select * From materialclass where materialclasscode = @ materialclasscode and companyName = @ companyName ";
3 sqlparameter [] sqlparams =
4 {
5 new sqlparameter ("@ materialclasscode", materialclasscode ),
6 new sqlparameter ("@ companyName", companyName)
7}
8 datatable table = new bitaecsqlexe (). executequery (CMD );

Comparing code snippet 2 and code snippet 4 posted above, the difference between them is that I hope that I can bridge the gap through regular expressions (it is too tired to manually change one by one, more than one hundred ). Note that the number of parameters that may appear in the query statement is not fixed, and the number of methods that may be included in each file is also variable. I wanted to write a C # console applet to complete this batch operation, but I always felt that I was using the cool tool. Later I wrote a simple Perl script to use Perl's powerful text processing capabilities, this function is easily implemented. The script code is as follows:

1use filehandle;
2use file: Find;
3use strict;
4
5 # global variables
6my $ directory = "E:/gea52_svn/gea52/Web ";
7 # my $ directory = "E:/temp/Perl ";
8
9 find (\ & editfile, $ directory );
10
11 # use this method to modify every code file
12 # xingyk 20070702
13sub editfile ()
14 {
15 if (-F and/. CS? /)
16 {
17 my $ file =$ _;
18 open file, $ file;
19 my @ lines = <File>;
20 close file;
21
22 my @ maarr;
23 For my $ line (@ lines)
24 {
25 @ maarr = $ line = ~ /\ S + (\ W +) \ s * = {\ D}/g;
26
27 # replace the original query string first
28 $ line = ~ S/\ s + (\ W +) \ s * = {\ D}/$1 =\@ $1/g;
29 # Add the default constructor of the sqlparameter Array
30 my $ sqlparam = "\ n \ tsqlparameter [] sqlparams = {";
31 if (@ maarr> 0)
32 {
33 for (@ maarr)
34 {
35 $ sqlparam = $ sqlparam. "\ t \ tnew sqlparameter (\" @ ". $ _." \ ",". $ _. "), \ n ";
36}
37 $ line = $ line. $ sqlparam. "\ t}; \ n ";
38}
39
40}
41
42 open file, "> $ file ";
43 print file @ lines;
44 close file;
45}
46}

 

Reconstruction of the data access layer (and reconstruction of Perl applications)