1
//
代码段1
2 public DataTable ExecuteQuery( string cmdText)
3 {
4 if (cmdText == null)
5 return null;
6
7 SqlDataAdapter adapter = new SqlDataAdapter(cmdText, 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}
2 public DataTable ExecuteQuery( string cmdText)
3 {
4 if (cmdText == null)
5 return null;
6
7 SqlDataAdapter adapter = new SqlDataAdapter(cmdText, 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}
而此方法都是这样被我调用的:
1
//
代码段2
2 string cmd = " select * from MaterialClass where MaterialClassCode = {0} " ;
3 cmd = string .Format(cmd, scope);
4 DataTable table = new BitAECSqlExe().ExecuteQuery(cmd);
2 string cmd = " select * from MaterialClass where MaterialClassCode = {0} " ;
3 cmd = string .Format(cmd, scope);
4 DataTable table = new BitAECSqlExe().ExecuteQuery(cmd);
有心的朋友很容易就能看出,如此的代码是非常脆弱的,根本无法应付查询字符串中出现特殊字符的情况(单引号,百分号等)。程序容错性差不说,还容易遭到sql注入的攻击。另外,对于sql操作可能抛出的异常,这里捕获之后就跳转到出错页的方式也是很糟糕的;因为这段底层代码,除了会被页面调用以外,还会被其他一些不是由客户端请求引发的功能所调用(如部署到服务器上的windows服务,处理复杂任务的专用工作线程等),在这些调用环境里HttpContext.Current是没有意义的。实际上,在这种底层的代码里,只能对异常作必要的处理,至于究竟是跳转到一个友好的错误提示界面,还是写入错误日志,应当交给更高层的代码去决定。
对这部分的重构其实很简单,那就是在进行查询的时候,对于所有参数都以SqlParameter进行封装。重构中新增了方法PrepareCommand,用来得到我们需要的SqlCommand对象。对ExecuteQuery的重构如下:
1
//
代码段3
2 private SqlCommand PrepareCommand( string cmdText, SqlParameter[] cmdParms)
3 {
4 if (cmdText == null)
5 throw new ArgumentNullException();
6
7 SqlCommand cmd = new SqlCommand(cmdText, connection);
8 if((cmdParms!=null)&&(cmdParms.Length>0))
9 foreach (SqlParameter param in cmdParms)
10 {
11 cmd.Parameters.Add(param);
12 }
13
14 return cmd;
15}
16
17 public DataTable ExecuteQuery( string cmdText, SqlParameter[] sqlParams)
18 {
19 if (cmdText == null)
20 return null;
21
22 SqlDataAdapter adapter = new SqlDataAdapter(this.PrepareCommand(cmdText,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}
2 private SqlCommand PrepareCommand( string cmdText, SqlParameter[] cmdParms)
3 {
4 if (cmdText == null)
5 throw new ArgumentNullException();
6
7 SqlCommand cmd = new SqlCommand(cmdText, connection);
8 if((cmdParms!=null)&&(cmdParms.Length>0))
9 foreach (SqlParameter param in cmdParms)
10 {
11 cmd.Parameters.Add(param);
12 }
13
14 return cmd;
15}
16
17 public DataTable ExecuteQuery( string cmdText, SqlParameter[] sqlParams)
18 {
19 if (cmdText == null)
20 return null;
21
22 SqlDataAdapter adapter = new SqlDataAdapter(this.PrepareCommand(cmdText,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}
上面重构的是ExecuteQuery方法的定义部分,这其实只是重构的开始,因为现有代码中充满了类似代码段2那样的方法调用,都需要改成如下的形式:
1
//
代码段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);
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);
比照一下上面贴出的代码段2和代码段4,它们之间的差异就是我很希望自己能通过正则表达式跨越的鸿沟(一个一个手工去改的话实在太累了,一百多处啊,呵呵)。需要注意的是,查询语句中可能出现的参数个数是不定的,每个文件中可能包含的方法调用的数量也是不定的。本想写一个C#的console小程序来完成这个批量操作,但又总觉得杀鸡用牛刀,后来写了一个简单的perl脚本,利用perl强大的文本处理能力,很容易就实现了这个功能。脚本代码如下:
1
use
FileHandle;
2 use File :: Find;
3 use strict;
4
5 # 全局变量
6 my $directory = " E:/GEA52_SVN/GEA52/Web " ;
7 # my $directory = "E:/Temp/Perl";
8
9 find( \& editFile , $directory );
10
11 # 使用这个方法来修改每一个代码文件
12 #xingyk 20070702
13 sub 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 # 先替换原有查询字符串
28 $line =~ s /\ s + ( \ w + ) \ s *= { \ d} / $ 1 =\ @$ 1 / g;
29 # 接下来添加SqlParameter数组的默认构造器
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\t\t};\n " ;
38 }
39
40 }
41
42 open FILE , " >$file " ;
43 print FILE @lines ;
44 close FILE;
45 }
46 }
2 use File :: Find;
3 use strict;
4
5 # 全局变量
6 my $directory = " E:/GEA52_SVN/GEA52/Web " ;
7 # my $directory = "E:/Temp/Perl";
8
9 find( \& editFile , $directory );
10
11 # 使用这个方法来修改每一个代码文件
12 #xingyk 20070702
13 sub 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 # 先替换原有查询字符串
28 $line =~ s /\ s + ( \ w + ) \ s *= { \ d} / $ 1 =\ @$ 1 / g;
29 # 接下来添加SqlParameter数组的默认构造器
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\t\t};\n " ;
38 }
39
40 }
41
42 open FILE , " >$file " ;
43 print FILE @lines ;
44 close FILE;
45 }
46 }