DBHelper 数据库帮助类

 1   /// <summary> 2   /// 数据库帮助类 3   /// <author>Devin</author> 4   /// </summary> 5   public sealed class DBHelper 6   { 7     private DBHelper() { } 8  9     /// <summary> 10     /// 数据库连接字符串 11     /// </summary> 12     private static string connStr = System.Configuration.ConfigurationManager.AppSettings["connStr"].ToString(); 13  14  15     /// <summary> 16     /// 一个有效的数据库连接对象 17     /// </summary> 18     private static SqlConnection _MyConnection; 19     public static SqlConnection MyConnection 20     { 21       get 22       { 23         if(_MyConnection == null) 24         { 25           _MyConnection = new SqlConnection(connStr); 26         } 27         return _MyConnection; 28       } 29     } 30  31  32     /// <summary> 33     /// 返回受影响行数 34     /// </summary> 35     /// <param name="cmdText">SQL语句</param> 36     /// <param name="commandParameters">参数集</param> 37     /// <returns></returns> 38     public static int MyExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters) 39     { 40       int result = 0; 41       SqlCommand cmd = new SqlCommand(); 42       SqlConnection conn = MyConnection; 43       try 44       { 45         PrepareCommand(cmd, conn, null, cmdText, commandParameters); 46         result = cmd.ExecuteNonQuery(); 47       } 48       catch(SqlException ex) 49       { 50         throw new Exception(ex.Message); 51       } 52       finally 53       { 54         cmd.Parameters.Clear(); 55       } 56       return result; 57     } 58  59  60  61     /// <summary> 62     /// 返回DataSet 63     /// </summary> 64     /// <param name="cmdText">SQL语句</param> 65     /// <param name="commandParameters">参数集</param> 66     /// <returns></returns> 67     public static DataSet MyExecuteDataset(string cmdText, params SqlParameter[] commandParameters) 68     { 69       DataSet ds = new DataSet(); 70       SqlConnection conn = MyConnection; 71       SqlCommand cmd = new SqlCommand(); 72       PrepareCommand(cmd, conn, null, cmdText, commandParameters); 73       using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 74       { 75         try 76         { 77           da.Fill(ds); 78         } 79         catch (SqlException ex) 80         { 81           throw new Exception(ex.Message); 82         } 83         finally 84         { 85           cmd.Parameters.Clear(); 86         } 87       } 88       return ds; 89     } 90  91     /// <summary> 92     /// 返回DataReader (使用后请关闭DataReader) 93     /// </summary> 94     /// <param name="cmdText">SQL语句</param> 95     /// <param name="commandParameters">参数集</param> 96     /// <returns></returns> 97     public static SqlDataReader MyExecuteReader(string cmdText, params SqlParameter[] commandParameters) 98     { 99       SqlConnection conn = MyConnection;100       SqlCommand cmd = new SqlCommand();101       try102       {103         PrepareCommand(cmd,conn,null,cmdText,commandParameters);104         SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//关闭DataReader的同时，Connection也将同时关闭105         return myReader;106       }107       catch(SqlException ex)108       {109         throw new Exception(ex.Message);110       }111       finally112       {113         cmd.Parameters.Clear();114       }115     }116 117     /// <summary>118     /// 返回首行首列的值119     /// </summary>120     /// <param name="cmdText">SQL语句</param>121     /// <param name="commandParameters">参数集</param>122     /// <returns></returns>123     public static object MyExecuteScalar(string cmdText,params SqlParameter[] commandParameters)124     {125       SqlConnection conn = MyConnection;126       SqlCommand cmd = new SqlCommand();127       try128       {129         PrepareCommand(cmd,conn,null,cmdText,commandParameters);130         return cmd.ExecuteScalar();131       }132       catch(SqlException ex)133       {134         throw new Exception(ex.Message);135       }136       finally137       {138         cmd.Parameters.Clear();139       }140     }141 142     /// <summary>143     /// 设置SqlCommand144     /// </summary>145     /// <param name="cmd">执行对象</param>146     /// <param name="conn">连接对象</param>147     /// <param name="trans">事务</param>148     /// <param name="cmdText">SQL语句</param>149     /// <param name="cmdParms">参数集</param>150     private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)151     {152       if (conn.State != ConnectionState.Open)153         conn.Open();154       cmd.Connection = conn;155       cmd.CommandText = cmdText;156       if (trans != null)157         cmd.Transaction = trans;158       cmd.CommandType = CommandType.Text;159       if (cmdParms != null)160       {161         foreach (SqlParameter parameter in cmdParms)162         {163           if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))164           {165             parameter.Value = DBNull.Value;166           }167           cmd.Parameters.Add(parameter);168         }169       }170     }171   }

原标题：DBHelper 数据库帮助类

关键词：数据库