数据库连接

1. 代码
  1  public class SqlHelper
  2     {
  3         private static string connStr = GetConnStr();
  4 
  5         /// <summary>
  6         /// 连接Web.config中配置的数据库连接串
  7         /// </summary>
  8         /// <returns>返回连接的字符串</returns>
  9         private static string GetConnStr()
 10         {
 11             return ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
 12         }
 13 
 14         /// <summary>
 15         /// 执行查询操作(sql语句有参数)
 16         /// </summary>
 17         /// <param name="sqlCommand">查询语句</param>
 18         /// <param name="param">查询参数</param>
 19         /// <returns>返回查询得到的DataTable</returns>
 20         public static DataTable GetDataTableBySqlWithParam(string sqlCommand, SqlParameter[] param)
 21         {
 22             DataTable dtSelected = new DataTable();
 23             using (SqlConnection conn = new SqlConnection(connStr))
 24             {
 25                 using (SqlCommand comm = new SqlCommand(sqlCommand, conn))
 26                 {
 27                     comm.CommandTimeout = 180;
 28                     try
 29                     {
 30                         comm.Parameters.AddRange(param);
 31                         conn.Open();
 32                         using (SqlDataAdapter da = new SqlDataAdapter(comm))
 33                         {
 34                             da.SelectCommand.CommandTimeout = 180;
 35                             da.Fill(dtSelected);
 36                         }
 37                         //return dtSelected;
 38                     }
 39                     catch (Exception ex)
 40                     {
 41                         //可以将ex异常信息写到日志文件中,便于排查错误
 42                     }
 43                     finally
 44                     {
 45                         conn.Close();
 46                     }
 47                 }
 48             }
 49             return dtSelected;
 50         }
 51 
 52         /// <summary>
 53         /// 执行查询操作(sql语句无参数)
 54         /// </summary>
 55         /// <param name="sqlCommand">查询语句</param>
 56         /// <param name="param">查询参数</param>
 57         /// <returns>返回查询得到的DataTable</returns>
 58         public static DataTable GetDataTableBySqlNoParam(string sqlCommand)
 59         {
 60             DataTable dtSelected = new DataTable();
 61             using (SqlConnection conn = new SqlConnection(connStr))
 62             {
 63                 using (SqlCommand comm = new SqlCommand(sqlCommand, conn))
 64                 {
 65                     comm.CommandTimeout = 180;
 66                     try
 67                     {
 68                         conn.Open();
 69                         using (SqlDataAdapter da = new SqlDataAdapter(comm))
 70                         {
 71                             da.SelectCommand.CommandTimeout = 180;
 72                             da.Fill(dtSelected);
 73                         }
 74                         //return dtSelected;
 75                     }
 76                     catch (Exception ex)
 77                     {
 78                         //可以将ex异常信息写到日志文件中,便于排查错误
 79                     }
 80                     finally
 81                     {
 82                         conn.Close();
 83                     }
 84                 }
 85             }
 86             return dtSelected;
 87         }
 88 
 89         /// <summary>
 90         /// 执行插入,删除,更新操作(sql语句有参数)
 91         /// </summary>
 92         /// <param name="sqlCommand"></param>
 93         /// <param name="param"></param>
 94         /// <returns></returns>
 95         public static int ExecBySqlWithParam(string sqlCommand, SqlParameter[] param)
 96         {
 97             int effectLine = 0;
 98             using (SqlConnection conn = new SqlConnection(connStr))
 99             {
100                 using (SqlCommand comm = new SqlCommand(sqlCommand, conn)) 
101                 {
102                     comm.CommandTimeout = 180;
103                     try
104                     {
105                         comm.Parameters.AddRange(param);
106                         conn.Open();
107                         effectLine = comm.ExecuteNonQuery();
108                     }
109                     catch (Exception ex)
110                     {
111                         effectLine = -1;
112                     }
113                     finally
114                     {
115                         conn.Close();
116                     }
117                 }
118             }
119             return effectLine;
120         }
121 
122         /// <summary>
123         /// 执行插入,删除,更新操作(sql语句无参数)
124         /// </summary>
125         /// <param name="sqlCommand"></param>
126         /// <param name="param"></param>
127         /// <returns></returns>
128         public static int ExecBySqlNoParam(string sqlCommand)
129         {
130             int effectLine = 0;
131             using (SqlConnection conn = new SqlConnection(connStr))
132             {
133                 using (SqlCommand comm = new SqlCommand(sqlCommand, conn))
134                 {
135                     comm.CommandTimeout = 180;
136                     try
137                     {
138                         conn.Open();
139                         effectLine = comm.ExecuteNonQuery();
140                         return effectLine;
141                     }
142                     catch (Exception ex)
143                     {
144                         effectLine = -1;
145                         return effectLine;
146                     }
147                     finally
148                     {
149                         effectLine = 3;
150                         conn.Close();
151                     }
152                 }
153             }
154             //return effectLine;
155         }
156     }
View Code

2. 代码分析
  (1)通常数据库连接操作属于Web开发中的常用操作,故封装成SqlHelper类
  (2)为什么要将操作分为有sql参数和无sql参数?
       通常sql查询语句要么带参数,要么不带参数,如下面所示:
       select * from Student;
       select * from Student where Sno = >Sno;
       故将Sql操作分为两种类型 --〉产生的问题:代码冗余
       --〉解决办法:添加第三个参数,用于判定是否是带参数的查询语句
       --〉产生新的问题:sql操作时,增加判定,从而增加操作时间
       综上:权衡考虑采用上面的代码,不采用参数判定法。
3. 代码使用
  (1)Web.config数据库配置
      (a)本地配置

<connectionStrings>
  <add name ="Test" connectionString="Data Source=(local)\MYDATABASE;Initial Catalog=Test;User ID=sa;password=Ctrip185515"/>
</connectionStrings>

     (b)远程服务器配置

<connectionStrings>  
  <add name ="Test" connectionString="Data Source=192.169.3.4; Initial Catalog=Test; User ID=sa; password=Ctrip185515"/> </connectionStrings>

  (2)SqlHelper类的使用
      (a)无sql参数
           string sqlCmd = "select * from Student";
           DataTable dt = SqlHelper.GetDataTableBySqlNoParam(sqlCmd);
      (b)有sql参数
           string sqlCmd = "select * from Student where Sno=>Sno";
           string Sno = "030340814";
           //亦可以添加多个参数,中间用逗号隔开
           SqlParameter[] para = new SqlParameter[]
           {
               new SqlParameter("@Sno",Sno)
           };
           DataTable dt = SqlHelper.GetDataTableBySqlWithParam(sqlCmd, para);
4. 写代码遇到的问题

  (1)try,catch,finally的问题
       (a)在finally中不能return某个值,只能在try块,catch块或者程序的最后return某个值
       (b)如果在同时在try块和catch块中返回某个值,在程序的最后不返回该值,但是在finally块中修改 
            了该返回值。其实返回的仍然是try块或者catch块中的值,因为当运行到try块或者catch块中的 
            return值时,先将它们存储到某个临时变量,到程序最后再取回并return


数据库连接,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。