我把數據庫操作類整理了一下,它包含了常用的數據庫操作,由三種方式:簡單的SQL拼接字符串的形式,SQL語句使用參數的形式和存儲過程的形式,每種形式均有五個方法,並且都有事務.,可以直接調用.代碼如下:
1//======================================================================
2//
3// Copyright (C) 2007-2008 三月軟件工作室
4// All rights reserved
5//
6// filename :SQLDataBase
7// description :
8//
9// created by 侯壘 at 04/14/2008 18:33:32
10//
11//
12//======================================================================
13
14using System;
15using System.Collections;
16using System.Collections.Specialized;
17using System.Data;
18using System.Data.SqlClient;
19using System.Configuration;
20using System.Data.Common;
21
22namespace SQLDataBase
23{
24 /**//// <summary>
25 /// 數據訪問基礎類(基於SQLServer)
26 /// </summary>
27 class SQLDataBase
28 {
29 protected static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
30 public SQLDataBase()
31 {
32
33 }
34
35 執行簡單SQL語句#region 執行簡單SQL語句
36
37 /**//// <summary>
38 /// 執行SQL語句，返回影響的記錄數
39 /// </summary>
40 /// <param name="SQLString">SQL語句</param>
41 /// <returns>影響的記錄數</returns>
42 public int ExecuteSql(string SQLString)
43 {
44 using (SqlConnection connection = new SqlConnection(connectionString))
45 {
46 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
47 {
48 try
49 {
50 connection.Open();
51 int rows = cmd.ExecuteNonQuery();
52 return rows;
53 }
54 catch (System.Data.SqlClient.SqlException E)
55 {
56 connection.Close();
57 throw new Exception(E.Message);
58 }
59 }
60 }
61 }
62
63 /**//// <summary>
64 /// 執行多條SQL語句，實現數據庫事務。
65 /// </summary>
66 /// <param name="SQLStringList">多條SQL語句</param>
67 public void ExecuteSqlTran(ArrayList SQLStringList)
68 {
69 using (SqlConnection conn = new SqlConnection(connectionString))
70 {
71 conn.Open();
72 SqlCommand cmd = new SqlCommand();
73 cmd.Connection = conn;
74 SqlTransaction tx = conn.BeginTransaction();
75 cmd.Transaction = tx;
76 try
77 {
78 for (int n = 0; n < SQLStringList.Count; n++)
79 {
80 string strsql = SQLStringList[n].ToString();
81 if (strsql.Trim().Length > 1)
82 {
83 cmd.CommandText = strsql;
84 cmd.ExecuteNonQuery();
85 }
86 }
87 tx.Commit();
88 }
89 catch (System.Data.SqlClient.SqlException E)
90 {
91 tx.Rollback();
92 throw new Exception(E.Message);
93 }
94 }
95 }
96 /**//// <summary>
97 /// 執行一條計算查詢結果語句，返回查詢結果（object）。
98 /// </summary>
99 /// <param name="SQLString">計算查詢結果語句</param>
100 /// <returns>查詢結果（object）</returns>
101 public object GetSingle(string SQLString)
102 {
103 using (SqlConnection connection = new SqlConnection(connectionString))
104 {
105 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
106 {
107 try
108 {
109 connection.Open();
110 object obj = cmd.ExecuteScalar();
111 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
112 {
113 return null;
114 }
115 else
116 {
117 return obj;
118 }
119 }
120 catch (System.Data.SqlClient.SqlException e)
121 {
122 connection.Close();
123 throw new Exception(e.Message);
124 }
125 }
126 }
127 }
128 /**//// <summary>
129 /// 執行查詢語句，返回SqlDataReader
130 /// </summary>
131 /// <param name="strSQL">查詢語句</param>
132 /// <returns>SqlDataReader</returns>
133 public DbDataReader ExecuteReader(string strSQL)
134 {
135 SqlConnection connection = new SqlConnection(connectionString);
136 SqlCommand cmd = new SqlCommand(strSQL, connection);
137 try
138 {
139 connection.Open();
140 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
141 return myReader;
142 }
143 catch (System.Data.SqlClient.SqlException e)
144 {
145 throw new Exception(e.Message);
146 }
147
148 }
149 /**//// <summary>
150 /// 執行查詢語句，返回DataSet
151 /// </summary>
152 /// <param name="SQLString">查詢語句</param>
153 /// <returns>DataSet</returns>
154 public DataSet GetDataSet(string SQLString)
155 {
156 using (SqlConnection connection = new SqlConnection(connectionString))
157 {
158 DataSet ds = new DataSet();
159 try
160 {
161 connection.Open();
162 SqlDataAdapter adapter = new SqlDataAdapter(SQLString, connection);
163 adapter.Fill(ds, "ds");
164 connection.Close();
165 return ds;
166 }
167 catch (System.Data.SqlClient.SqlException ex)
168 {
169 throw new Exception(ex.Message);
170 }
171 }
172 }
173
174
175 #endregion
176
177 執行帶參數的SQL語句#region 執行帶參數的SQL語句
178
179 /**//// <summary>
180 /// 執行SQL語句，返回影響的記錄數
181 /// </summary>
182 /// <param name="SQLString">SQL語句</param>
183 /// <returns>影響的記錄數</returns>
184 public int ExecuteSql(string SQLString, DbParameter[] cmdParms)
185 {
186 using (SqlConnection connection = new SqlConnection(connectionString))
187 {
188 using (SqlCommand cmd = new SqlCommand())
189 {
190 try
191 {
192 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
193 int rows = cmd.ExecuteNonQuery();
194 cmd.Parameters.Clear();
195 return rows;
196 }
197 catch (System.Data.SqlClient.SqlException E)
198 {
199 throw new Exception(E.Message);
200 }
201 }
202 }
203 }
204
205
206 /**//// <summary>
207 /// 執行多條SQL語句，實現數據庫事務。
208 /// </summary>
209 /// <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）</param>
210 public void ExecuteSqlTran(Hashtable SQLStringList)
211 {
212 using (SqlConnection conn = new SqlConnection(connectionString))
213 {
214 conn.Open();
215 using (SqlTransaction trans = conn.BeginTransaction())
216 {
217 SqlCommand cmd = new SqlCommand();
218 try
219 {
220 //循環
221 foreach (DictionaryEntry myDE in SQLStringList)
222 {
223 string cmdText = myDE.Key.ToString();
224 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
225 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
226 int val = cmd.ExecuteNonQuery();
227 cmd.Parameters.Clear();
228 }
229 trans.Commit();
230 }
231 catch
232 {
233 trans.Rollback();
234 throw;
235 }
236 }
237 }
238 }
239
240
241 /**//// <summary>
242 /// 執行一條計算查詢結果語句，返回查詢結果（object）,返回首行首列的值;
243 /// </summary>
244 /// <param name="SQLString">計算查詢結果語句</param>
245 /// <returns>查詢結果（object）</returns>
246 public object GetSingle(string SQLString, DbParameter[] cmdParms)
247 {
248 using (SqlConnection connection = new SqlConnection(connectionString))
249 {
250 using (SqlCommand cmd = new SqlCommand())
251 {
252 try
253 {
254 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
255 object obj = cmd.ExecuteScalar();
256 cmd.Parameters.Clear();
257 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
258 {
259 return null;
260 }
261 else
262 {
263 return obj;
264 }
265 }
266 catch (System.Data.SqlClient.SqlException e)
267 {
268 throw new Exception(e.Message);
269 }
270 }
271 }
272 }
273
274 /**//// <summary>
275 /// 執行查詢語句，返回SqlDataReader
276 /// </summary>
277 /// <param name="strSQL">查詢語句</param>
278 /// <returns>SqlDataReader</returns>
279 public DbDataReader ExecuteReader(string SQLString, DbParameter[] cmdParms)
280 {
281 SqlConnection connection = new SqlConnection(connectionString);
282 SqlCommand cmd = new SqlCommand();
283 try
284 {
285 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
286 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
287 cmd.Parameters.Clear();
288 return myReader;
289 }
290 catch (System.Data.SqlClient.SqlException e)
291 {
292 throw new Exception(e.Message);
293 }
294
295 }
296
297 /**//// <summary>
298 /// 執行查詢語句，返回DataSet
299 /// </summary>
300 /// <param name="SQLString">查詢語句</param>
301 /// <returns>DataSet</returns>
302 public DataSet GetDataSet(string SQLString, DbParameter[] cmdParms)
303 {
304 using (SqlConnection connection = new SqlConnection(connectionString))
305 {
306 SqlCommand cmd = new SqlCommand();
307 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
308 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
309 {
310 DataSet ds = new DataSet();
311 try
312 {
313 da.Fill(ds, "ds");
314 cmd.Parameters.Clear();
315 return ds;
316 }
317 catch (System.Data.SqlClient.SqlException ex)
318 {
319 throw new Exception(ex.Message);
320 }
321 }
322 }
323 }
324
325
326 private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, DbParameter[] cmdParms)
327 {
328 if (conn.State != ConnectionState.Open)
329 conn.Open();
330 cmd.Connection = conn;
331 cmd.CommandText = cmdText;
332 if (trans != null)
333 cmd.Transaction = trans;
334 cmd.CommandType = CommandType.Text;//cmdType;
335 if (cmdParms != null)
336 {
337 foreach (SqlParameter parm in cmdParms)
338 cmd.Parameters.Add(parm);
339 }
340 }
341
342 #endregion
343
344 存儲過程操作#region 存儲過程操作
345 /**//// <summary>
346 /// 執行存儲過程;
347 /// </summary>
348 /// <param name="storeProcName">存儲過程名</param>
349 /// <param name="parameters">所需要的參數</param>
350 /// <returns>返回受影響的行數</returns>
351 public int RunProcedureExecuteSql(string storeProcName, DbParameter[] parameters)
352 {
353 using (SqlConnection connection = new SqlConnection(connectionString))
354 {
355 SqlCommand cmd = BuildQueryCommand(connection, storeProcName, parameters);
356 int rows = cmd.ExecuteNonQuery();
357 cmd.Parameters.Clear();
358 connection.Close();
359 return rows;
360 }
361 }
362 /**//// <summary>
363 /// 執行存儲過程,返回首行首列的值
364 /// </summary>
365 /// <param name="storeProcName">存儲過程名</param>
366 /// <param name="parameters">存儲過程參數</param>
367 /// <returns>返回首行首列的值</returns>
368 public Object RunProcedureGetSingle(string storeProcName, DbParameter[] parameters)
369 {
370 using (SqlConnection connection = new SqlConnection(connectionString))
371 {
372 try
373 {
374 SqlCommand cmd = BuildQueryCommand(connection, storeProcName, parameters);
375 object obj = cmd.ExecuteScalar();
376 cmd.Parameters.Clear();
377 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
378 {
379 return null;
380 }
381 else
382 {
383 return obj;
384 }
385 }
386 catch (System.Data.SqlClient.SqlException e)
387 {
388 throw new Exception(e.Message);
389 }
390 }
391 }
392 /**//// <summary>
393 /// 執行存儲過程
394 /// </summary>
395 /// <param name="storedProcName">存儲過程名</param>
396 /// <param name="parameters">存儲過程參數</param>
397 /// <returns>SqlDataReader</returns>
398 public DbDataReader RunProcedureGetDataReader(string storedProcName, DbParameter[] parameters)
399 {
400 SqlConnection connection = new SqlConnection(connectionString);
401 SqlDataReader returnReader;
402 SqlCommand cmd = BuildQueryCommand(connection, storedProcName, parameters);
403 cmd.CommandType = CommandType.StoredProcedure;
404 returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
405 cmd.Parameters.Clear();
406 return returnReader;
407 }
408 /**//// <summary>
409 /// 執行存儲過程
410 /// </summary>
411 /// <param name="storedProcName">存儲過程名</param>
412 /// <param name="parameters">存儲過程參數</param>
413 /// <returns>DataSet</returns>
414 public DataSet RunProcedureGetDataSet(string storedProcName, DbParameter[] parameters)
415 {
416 using (SqlConnection connection = new SqlConnection(connectionString))
417 {
418 DataSet dataSet = new DataSet();
419 connection.Open();
420 SqlDataAdapter sqlDA = new SqlDataAdapter();
421 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
422 sqlDA.Fill(dataSet);
423 connection.Close();
424 sqlDA.SelectCommand.Parameters.Clear();
425 sqlDA.Dispose();
426 return dataSet;
427 }
428 }
429 /**//// <summary>
430 /// 執行多個存儲過程，實現數據庫事務。
431 /// </summary>
432 /// <param name="SQLStringList">存儲過程的哈希表（key是該語句的DbParameter[],value為存儲過程語句）</param>
433 public bool RunProcedureTran(Hashtable SQLStringList)
434 {
435 using (SqlConnection connection = new SqlConnection(connectionString))
436 {
437 connection.Open();
438 using (SqlTransaction trans = connection.BeginTransaction())
439 {
440 SqlCommand cmd = new SqlCommand();
441 try
442 {
443 //循環
444 foreach (DictionaryEntry myDE in SQLStringList)
445 {
446 cmd.Connection = connection;
447 string storeName = myDE.Value.ToString();
448 SqlParameter[] cmdParms = (SqlParameter[])myDE.Key;
449
450 cmd.Transaction = trans;
451 cmd.CommandText = storeName;
452 cmd.CommandType = CommandType.StoredProcedure;
453 if (cmdParms != null)
454 {
455 foreach (SqlParameter parameter in cmdParms)
456 cmd.Parameters.Add(parameter);
457 }
458 int val = cmd.ExecuteNonQuery();
459 cmd.Parameters.Clear();
460 }
461 trans.Commit();
462 return true;
463 }
464 catch
465 {
466 trans.Rollback();
467 return false;
468 throw;
469 }
470 }
471 }
472 }
473 /**//// <summary>
474 /// 構建 SqlCommand 對象(用來返回一個結果集，而不是一個整數值)
475 /// </summary>
476 /// <param name="connection">數據庫連接</param>
477 /// <param name="storedProcName">存儲過程名</param>
478 /// <param name="parameters">存儲過程參數</param>
479 /// <returns>SqlCommand</returns>
480 private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, DbParameter[] parameters)
481 {
482 if (connection.State != ConnectionState.Open)
483 connection.Open();
484 SqlCommand command = new SqlCommand(storedProcName, connection);
485 command.CommandType = CommandType.StoredProcedure;
486 if (parameters != null)
487 {
488 foreach (SqlParameter parameter in parameters)
489 {
490 command.Parameters.Add(parameter);
491 }
492 }
493 return command;
494 }
495 #endregion
496
497 }
498}
499
