ASP.NET系统开发（一）：层层递进之SQLHelper助手类的持续优化



ASP.NET开发中的三层开发思想指的是UI层（界面显示层），BLL层（业务逻辑层），DAL层（数据访问层）三层，三层之间通过函数的调用来达到降低耦合，易于系统维护的目的，SQLHelper助手类的主要作用在于接收并执行来自各个数据表累传来的sql语句或存储过程。一般的SQLHelper类中主要包括以下几个函数功能:

       

        1.执行不带参数的增删改方法

       2.执行带参数的增删改方法。

        3.执行不带参数的查询方法。

       4.执行带参数的查询方法。

 

作为一个程序员SQLHelper类编写的好坏不仅影响着系统的可维护性的强弱，而且它更体现的是一个编程人员的职业素质。一个程序员的成长过程中必然要经过代码的锤炼，代码见证了一个编程人员的成长历程，下面通过不同版本的SQLHelper类来向大家展示一个良好的SQLHelper助手类是怎样炼成的：

 

一、初涉江湖版(A级代码)

 

[csharp] view plain copy print ?

1. namespace dal  
2. {  
3.     public class SQLHelper  
4.     {  
5.         /// 执行带参数的增删改方法  
6.         public int ExecuteNonQuery(string sqltext,CommandType ct,SqlParameter [] paras)  
7.         {  
8.             string strconn ="server=YCH-PC;database=newssystem;uid=sa;pwd=1314517";                
9.             SqlConnection conn = new SqlConnection(strconn);                //定义一个数据库连接对象（下同)  
10.             conn.Open();                                                   //打开数据库连接（下同）  
11.               SqlCommand cmd = new SqlCommand(sqltext  , conn );            //实例化一个命令对象（下同）  
12.               cmd.CommandType = ct;                                        //指定命令类型（下同）  
13.               cmd.Parameters.AddRange(paras);                             //增加参数（下同）  
14.               int res = cmd.ExecuteNonQuery();                           //执行命令（下同）  
15.               conn .Close ();                                           //关闭数据库连接（下同）  
16.               return res;                                              //返回执行结果（下同）  
17.         }  
18.         /// 执行不带参数的增删改方法  
19.         public int ExecuteNonQuery(string sqltext, CommandType ct)  
20.         {  
21.             string strconn = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";  
22.             SqlConnection conn = new SqlConnection(strconn);  
23.             conn.Open();  
24.             SqlCommand cmd = new SqlCommand(sqltext, conn);  
25.             cmd.CommandType = ct;  
26.             int res = cmd.ExecuteNonQuery();  
27.             conn.Close();  
28.             return res;  
29.         }  
30.         /// 执行不带参数的查询方法  
31.         public DataTable  ExecuteQuery(string sqltext, CommandType ct)  
32.         {  
33.             string strconn = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";  
34.             SqlConnection conn = new SqlConnection(strconn);  
35.             conn.Open();  
36.             SqlDataReader sdr ;  
37.             DataTable dt=new DataTable ();  
38.             SqlCommand cmd = new SqlCommand(sqltext, conn);  
39.             cmd.CommandType = ct;  
40.             sdr = cmd.ExecuteReader();  
41.             dt.Load(sdr);  
42.             conn.Close();  
43.             return dt ;  
44.         }  
45.         /// 执行带参数的查询操作  
46.         public DataTable  ExecuteQuery(string sqltext, CommandType ct, SqlParameter[] paras)  
47.         {  
48.             string strconn = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";  
49.             SqlConnection conn = new SqlConnection(strconn);  
50.             conn.Open();  
51.             SqlDataReader sdr;  
52.             DataTable dt = new DataTable();  
53.             SqlCommand cmd = new SqlCommand(sqltext, conn);  
54.             cmd.CommandType = ct;  
55.             cmd.Parameters.AddRange(paras);  
56.             sdr  = cmd.ExecuteReader();  
57.             dt.Load(sdr);  
58.             conn.Close();  
59.             return dt ;  
60.         }  
61.     }  
62. }  

namespace dal
{
    public class SQLHelper
    {
        /// 执行带参数的增删改方法
        public int ExecuteNonQuery(string sqltext,CommandType ct,SqlParameter [] paras)
        {
            string strconn ="server=YCH-PC;database=newssystem;uid=sa;pwd=1314517";              
            SqlConnection conn = new SqlConnection(strconn);                //定义一个数据库连接对象（下同)
            conn.Open();                                                   //打开数据库连接（下同）
              SqlCommand cmd = new SqlCommand(sqltext  , conn );            //实例化一个命令对象（下同）
              cmd.CommandType = ct;                                        //指定命令类型（下同）
              cmd.Parameters.AddRange(paras);                             //增加参数（下同）
              int res = cmd.ExecuteNonQuery();                           //执行命令（下同）
              conn .Close ();                                           //关闭数据库连接（下同）
              return res;                                              //返回执行结果（下同）
        }
        /// 执行不带参数的增删改方法
        public int ExecuteNonQuery(string sqltext, CommandType ct)
        {
            string strconn = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";
            SqlConnection conn = new SqlConnection(strconn);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sqltext, conn);
            cmd.CommandType = ct;
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        /// 执行不带参数的查询方法
        public DataTable  ExecuteQuery(string sqltext, CommandType ct)
        {
            string strconn = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";
            SqlConnection conn = new SqlConnection(strconn);
            conn.Open();
            SqlDataReader sdr ;
            DataTable dt=new DataTable ();
            SqlCommand cmd = new SqlCommand(sqltext, conn);
            cmd.CommandType = ct;
            sdr = cmd.ExecuteReader();
            dt.Load(sdr);
            conn.Close();
            return dt ;
        }
        /// 执行带参数的查询操作
        public DataTable  ExecuteQuery(string sqltext, CommandType ct, SqlParameter[] paras)
        {
            string strconn = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";
            SqlConnection conn = new SqlConnection(strconn);
            conn.Open();
            SqlDataReader sdr;
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(sqltext, conn);
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);
            sdr  = cmd.ExecuteReader();
            dt.Load(sdr);
            conn.Close();
            return dt ;
        }
    }
}

 

二、血雨腥风版（AA级代码）

 

从上面的代码中我们可以很明显的看出，各个函数中数据库连接字符串、连接对象以及数据库连接的打开和关闭都是相同的，代码的重复往往是糟糕代码的标志，下面我们就通过代码改造来减少代码冗余，提高代码的复用率。代码的主要改动部分就是针对上述相同的代码片段进行的：

 

[csharp] view plain copy print ?

1. namespace dal  
2. {  
3.     public class SQLHelper  
4.     {  
5.         private SqlConnection conn = null;                                         
6.         private SqlCommand cmd = null;  
7.         /// 实例化SQLHelper类的时候便实例化一个数据库连接  
8.         public SQLHelper()  
9.         {  
10.             string connStr = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";  
11.             conn = new SqlConnection(connStr);  
12.         }  
13.         /// 打开数据库连接  
14.         private SqlConnection GetConn()  
15.         {  
16.             if (conn.State == ConnectionState.Closed)  
17.             {  
18.                 conn.Open();  
19.             }  
20.             return conn;  
21.         }  
22.         /// 关闭数据库连接  
23.         private void OutConn()  
24.         {  
25.             if (conn.State == ConnectionState.Open)  
26.             {  
27.                 conn.Close();  
28.             }  
29.         }  
30.         /// 执行不带参数的增删改操作  
31.         public int ExecuteNonQuery(string cmdText, CommandType ct)  
32.         {  
33.             cmd = new SqlCommand(cmdText, GetConn());  
34.             cmd.CommandType = ct;  
35.             int res = cmd.ExecuteNonQuery();  
36.             OutConn();  
37.             return res;  
38.         }  
39.         /// 执行带参数的增删改方法  
40.         public int ExecuteNonQuery(string sqltext, CommandType ct, SqlParameter[] paras)  
41.         {  
42.             SqlCommand cmd = new SqlCommand(sqltext, GetConn());  
43.             cmd.CommandType = ct;  
44.             cmd.Parameters.AddRange(paras);  
45.             int res = cmd.ExecuteNonQuery();  
46.             OutConn();  
47.             return res;  
48.         }  
49.         /// 执行不带参数的查询方法  
50.         public DataTable ExecuteQuery(string sqltext, CommandType ct)  
51.         {  
52.             DataTable dt = new DataTable();  
53.             SqlCommand cmd = new SqlCommand(sqltext, GetConn());  
54.             cmd.CommandType = ct;  
55.             SqlDataReader sdr = cmd.ExecuteReader();  
56.             dt.Load(sdr);  
57.             sdr.Close();  
58.             OutConn();  
59.             return dt;  
60.         }  
61.           
62.        /// 执行带参数的查询操作  
63.         public DataTable  ExecuteQuery(string sqltext, CommandType ct, SqlParameter[] paras)  
64.         {  
65.             DataTable dt = new DataTable();  
66.             SqlCommand cmd = new SqlCommand(sqltext, GetConn());  
67.             cmd.CommandType = ct;  
68.             cmd.Parameters.AddRange(paras);  
69.             SqlDataReader sdr  = cmd.ExecuteReader();  
70.             dt.Load(sdr);  
71.             sdr.Close();  
72.             OutConn();  
73.             return dt ;  
74.         }  
75.     }  
76. }  

namespace dal
{
    public class SQLHelper
    {
        private SqlConnection conn = null;                                       
        private SqlCommand cmd = null;
        /// 实例化SQLHelper类的时候便实例化一个数据库连接
        public SQLHelper()
        {
            string connStr = "server=YCH-PC;database=newssystem;uid=sa;pwd=123456";
            conn = new SqlConnection(connStr);
        }
        /// 打开数据库连接
        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        /// 关闭数据库连接
        private void OutConn()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        /// 执行不带参数的增删改操作
        public int ExecuteNonQuery(string cmdText, CommandType ct)
        {
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            int res = cmd.ExecuteNonQuery();
            OutConn();
            return res;
        }
        /// 执行带参数的增删改方法
        public int ExecuteNonQuery(string sqltext, CommandType ct, SqlParameter[] paras)
        {
            SqlCommand cmd = new SqlCommand(sqltext, GetConn());
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);
            int res = cmd.ExecuteNonQuery();
            OutConn();
            return res;
        }
        /// 执行不带参数的查询方法
        public DataTable ExecuteQuery(string sqltext, CommandType ct)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(sqltext, GetConn());
            cmd.CommandType = ct;
            SqlDataReader sdr = cmd.ExecuteReader();
            dt.Load(sdr);
            sdr.Close();
            OutConn();
            return dt;
        }
        
       /// 执行带参数的查询操作
        public DataTable  ExecuteQuery(string sqltext, CommandType ct, SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(sqltext, GetConn());
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);
            SqlDataReader sdr  = cmd.ExecuteReader();
            dt.Load(sdr);
            sdr.Close();
            OutConn();
            return dt ;
        }
    }
}

 

三、炉火纯青版（AAA级代码）

通过”瘦身行动“，现在的代码是不是比第一版代码清晰了很多呢？但这还不是最后的结果。有的人会提出这样的问题：把连接字符串写在SQLHelper类中，如果我现在要更换数据库，那岂不是要对SQLHelper助手类进行更改，这样似乎不符合我们”开放扩展，关闭修改“的要求。另外在执行查询过程时需要先关闭SqlDataReader再关闭数据库连接，这样在代码中也出现了重复，能不能再进一步对代码进行改造来解决这两个问题呢？下面我们就上述两个问题对代码进行进一的步改造: 解决更换数据库的问题我们采取的方法是将连接字符串写在配置文件中，具体步骤为：

1. 双击web层中Web.config文件，配置文件中的相关内容将会出现。
2. 找到<connectionStrings>和</connectionStrings>,在二者之间添加如下内容：<add name=<add name=连接字符串 connectionString ="server=YCH-PC;database=newssystem;uid=sa;pwd=具体的登陆密码"/>
3. 修改SQLHelper类中的相关代码如下：

 

[csharp] view plain copy print ?

1. namespace dal  
2. {  
3.     public class SQLHelper  
4.     {  
5.         private SqlConnection conn = null;  
6.         private SqlCommand cmd = null;  
7.         private SqlDataReader sdr = null;  
8.         public SQLHelper()  
9.         {  
10.             string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;  
11.             conn = new SqlConnection(strconn );  
12.         }  
13.    
14.         private SqlConnection GetConn()  
15.         {  
16.             if (conn.State == ConnectionState.Closed)  
17.             {  
18.                 conn.Open();  
19.             }  
20.             return conn;  
21.         }  
22.           
23.         //关闭数据库连接  
24.         private void OutConn()  
25.         {  
26.             if (conn.State == ConnectionState.Open)  
27.             {  
28.                 conn.Close();  
29.             }  
30.         }  
31.         ///  执行不带参数的增删改SQL语句或存储过程  
32.         public int ExecuteNonQuery(string cmdText, CommandType ct)  
33.         {  
34.             int res;  
35.             try  
36.             {  
37.                 cmd = new SqlCommand(cmdText, GetConn());  
38.                 cmd.CommandType = ct;  
39.                 res = cmd.ExecuteNonQuery();  
40.             }  
41.             catch (Exception ex)  
42.             {  
43.                 throw ex;  
44.             }  
45.             finally  
46.             {  
47.                 OutConn();  
48.             }  
49.             return res;  
50.         }  
51.         ///  执行带参数的增删改SQL语句或存储过程  
52.         public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)  
53.         {  
54.             int res;  
55.             try  
56.             {  
57.                 cmd = new SqlCommand(cmdText, GetConn());  
58.                 cmd.CommandType = ct;  
59.                 cmd.Parameters.AddRange(paras);  
60.                 res = cmd.ExecuteNonQuery();  
61.             }  
62.             catch (Exception ex)  
63.             {  
64.                 throw ex;  
65.             }  
66.             finally  
67.             {  
68.                 OutConn();  
69.             }  
70.             return res;  
71.         }  
72.    
73.         ///  执行不带参数的查询SQL语句或存储过程  
74.         public DataTable ExecuteQuery(string cmdText, CommandType ct)  
75.         {  
76.             DataTable dt = new DataTable();  
77.             cmd = new SqlCommand(cmdText, GetConn());  
78.             cmd.CommandType = ct;  
79.             using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))  
80.             {  
81.                 dt.Load(sdr);  
82.             }  
83.             return dt;  
84.         }  
85.         ///  执行带参数的查询SQL语句或存储过程  
86.         public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)  
87.         {  
88.             DataTable dt = new DataTable();  
89.             cmd = new SqlCommand(cmdText, GetConn());  
90.             cmd.CommandType = ct;  
91.             cmd.Parameters.AddRange(paras);  
92.             using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))  
93.             {  
94.                 dt.Load(sdr);  
95.             }  
96.             return dt;  
97.         }  
98.     }  
99. }  

namespace dal
{
    public class SQLHelper
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;
        public SQLHelper()
        {
            string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;
            conn = new SqlConnection(strconn );
        }
 
        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        
        //关闭数据库连接
        private void OutConn()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        ///  执行不带参数的增删改SQL语句或存储过程
        public int ExecuteNonQuery(string cmdText, CommandType ct)
        {
            int res;
            try
            {
                cmd = new SqlCommand(cmdText, GetConn());
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                OutConn();
            }
            return res;
        }
        ///  执行带参数的增删改SQL语句或存储过程
        public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            int res;
            try
            {
                cmd = new SqlCommand(cmdText, GetConn());
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                OutConn();
            }
            return res;
        }
 
        ///  执行不带参数的查询SQL语句或存储过程
        public DataTable ExecuteQuery(string cmdText, CommandType ct)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        ///  执行带参数的查询SQL语句或存储过程
        public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
    }
}

经过对代码的再次改造，不但代码的重复率大大降低，而且如需更换数据库，只要在配置文件中更改相应的连接字符串就可完。在编写查询（包括带参数的和不带参数的查询方法）方法时，用using方法，在关闭SqlDataReader对象的时候同时关闭与之相关联的数据库连接，避免了try catch语句的使用，代码更加简洁。经过三次改造，SQLHelper助手类的变身就此完成。