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             try
102             {
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             finally
112             {
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             try
128             {
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             finally
137             {
138                 cmd.Parameters.Clear();
139             }
140         }
141 
142         /// <summary>
143         /// 設置SqlCommand
144         /// </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     }