DBHelper 數據庫幫助類，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 }
