数据库连接

  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     }