微软通用的DBHelper

​​view plain​​
  
 
 
1. using System;  
2.   
3. using System.Configuration;  
4.   
5. using System.Data;  
6.   
7. using System.Data.Common;  
8.   
9. using System.Data.OleDb;  
10.   
11. using System.Data.SqlClient;  
12.   
13. namespace Lelog.Data  
14.   
15. {  
16.   
17. /// <summary>  
18.   
19. /// 数据库助手类  
20.   
21. /// </summary>  
22.   
23. public class DbHelper  
24.   
25. {  
26.   
27. private static string dbProviderName = ConfigurationSettings.AppSettings["DbProviderName"];  
28.   
29. private static string dbConnectionString = ConfigurationManager.AppSettings["TaxInvoiceConn"];  
30.   
31.   
32. private DbConnection connection;  
33.   
34. public DbHelper()  
35.   
36. {  
37.   
38. this.connection = CreateConnection(DbHelper.dbConnectionString);  
39.   
40. }  
41.   
42.   
43. public DbHelper(string connectionString)  
44.   
45. {  
46.   
47. this.connection = CreateConnection(connectionString);  
48.   
49. }  
50.   
51.   
52. public static DbConnection CreateConnection()  
53.   
54. {  
55.   
56. DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);  
57.   
58. DbConnection dbconn = dbfactory.CreateConnection();  
59.   
60. dbconn.ConnectionString = DbHelper.dbConnectionString;  
61.   
62. return dbconn;  
63.   
64. }  
65.   
66. public static DbConnection CreateConnection(string connectionString)  
67.   
68. {  
69.   
70. DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);  
71.   
72. DbConnection dbconn = dbfactory.CreateConnection();  
73.   
74. dbconn.ConnectionString = connectionString;  
75.   
76. return dbconn;  
77.   
78. }  
79.   
80.   
81. public DbCommand GetStoredProcCommond(string storedProcedure)  
82.   
83. {  
84.   
85. DbCommand dbCommand = connection.CreateCommand();  
86.   
87. dbCommand.CommandText = storedProcedure;  
88.   
89. dbCommand.CommandType = CommandType.StoredProcedure;  
90.   
91. return dbCommand;  
92.   
93. }  
94.   
95. public DbCommand GetSqlStringCommond(string sqlQuery)  
96.   
97. {  
98.   
99. DbCommand dbCommand = connection.CreateCommand();  
100.   
101. dbCommand.CommandText = sqlQuery;  
102.   
103. dbCommand.CommandType = CommandType.Text;  
104.   
105. return dbCommand;  
106.   
107. }  
108. 
109. #region 增加参数  
110.   
111. public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)  
112.   
113. {  
114.   
115. foreach (DbParameter dbParameter in dbParameterCollection)  
116.   
117. {  
118.   
119. cmd.Parameters.Add(dbParameter);  
120.   
121. }  
122.   
123. }  
124.   
125. public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)  
126.   
127. {  
128.   
129. DbParameter dbParameter = cmd.CreateParameter();  
130.   
131. dbParameter.DbType = dbType;  
132.   
133. dbParameter.ParameterName = parameterName;  
134.   
135. dbParameter.Size = size;  
136.   
137. dbParameter.Direction = ParameterDirection.Output;  
138.   
139. cmd.Parameters.Add(dbParameter);  
140.   
141. }  
142.   
143. public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)  
144.   
145. {  
146.   
147. DbParameter dbParameter = cmd.CreateParameter();  
148.   
149. dbParameter.DbType = dbType;  
150.   
151. dbParameter.ParameterName = parameterName;  
152.   
153. dbParameter.Value = value;  
154.   
155. dbParameter.Direction = ParameterDirection.Input;  
156.   
157. cmd.Parameters.Add(dbParameter);  
158.   
159. }  
160.   
161. public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)  
162.   
163. {  
164.   
165. DbParameter dbParameter = cmd.CreateParameter();  
166.   
167. dbParameter.DbType = dbType;  
168.   
169. dbParameter.ParameterName = parameterName;  
170.   
171. dbParameter.Direction = ParameterDirection.ReturnValue;  
172.   
173. cmd.Parameters.Add(dbParameter);  
174.   
175. }  
176.   
177. public DbParameter GetParameter(DbCommand cmd, string parameterName)  
178.   
179. {  
180.   
181. return cmd.Parameters[parameterName];  
182.   
183. }  
184. 
185. 
186. #endregion  
187. 
188. #region 执行  
189.   
190. public DataSet ExecuteDataSet(DbCommand cmd)  
191.   
192. {  
193.   
194. DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);  
195.   
196. DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();  
197.   
198. dbDataAdapter.SelectCommand = cmd;  
199.   
200. DataSet ds = new DataSet();  
201.   
202. dbDataAdapter.Fill(ds);  
203.   
204. return ds;  
205.   
206. }  
207.   
208.   
209. public DataTable ExecuteDataTable(DbCommand cmd)  
210.   
211. {  
212.   
213. DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);  
214.   
215. DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();  
216.   
217. dbDataAdapter.SelectCommand = cmd;  
218.   
219. DataTable dataTable = new DataTable();  
220.   
221. dbDataAdapter.Fill(dataTable);  
222.   
223. return dataTable;  
224.   
225. }  
226.   
227.   
228. public DbDataReader ExecuteReader(DbCommand cmd)  
229.   
230. {  
231.   
232. cmd.Connection.Open();  
233.   
234. DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
235.   
236. return reader;  
237.   
238. }  
239.   
240. public int ExecuteNonQuery(DbCommand cmd)  
241.   
242. {  
243.   
244. cmd.Connection.Open();  
245.   
246. int ret = cmd.ExecuteNonQuery();  
247.   
248. cmd.Connection.Close();  
249.   
250. return ret;  
251.   
252. }  
253.   
254.   
255. public object ExecuteScalar(DbCommand cmd)  
256.   
257. {  
258.   
259. cmd.Connection.Open();  
260.   
261. object ret = cmd.ExecuteScalar();  
262.   
263. cmd.Connection.Close();  
264.   
265. return ret;  
266.   
267. }  
268. 
269. #endregion  
270. 
271. #region 执行事务  
272.   
273. public DataSet ExecuteDataSet(DbCommand cmd, Trans t)  
274.   
275. {  
276.   
277. cmd.Connection = t.DbConnection;  
278.   
279. cmd.Transaction = t.DbTrans;  
280.   
281. DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);  
282.   
283. DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();  
284.   
285. dbDataAdapter.SelectCommand = cmd;  
286.   
287. DataSet ds = new DataSet();  
288.   
289. dbDataAdapter.Fill(ds);  
290.   
291. return ds;  
292.   
293. }  
294.   
295.   
296. public DataTable ExecuteDataTable(DbCommand cmd, Trans t)  
297.   
298. {  
299.   
300. cmd.Connection = t.DbConnection;  
301.   
302. cmd.Transaction = t.DbTrans;  
303.   
304. DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);  
305.   
306. DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();  
307.   
308. dbDataAdapter.SelectCommand = cmd;  
309.   
310. DataTable dataTable = new DataTable();  
311.   
312. dbDataAdapter.Fill(dataTable);  
313.   
314. return dataTable;  
315.   
316. }  
317.   
318.   
319. public DbDataReader ExecuteReader(DbCommand cmd, Trans t)  
320.   
321. {  
322.   
323. cmd.Connection.Close();  
324.   
325. cmd.Connection = t.DbConnection;  
326.   
327. cmd.Transaction = t.DbTrans;  
328.   
329. DbDataReader reader = cmd.ExecuteReader();  
330.   
331. DataTable dt = new DataTable();  
332.   
333. return reader;  
334.   
335. }  
336.   
337. public int ExecuteNonQuery(DbCommand cmd, Trans t)  
338.   
339. {  
340.   
341. cmd.Connection.Close();  
342.   
343. cmd.Connection = t.DbConnection;  
344.   
345. cmd.Transaction = t.DbTrans;  
346.   
347. int ret = cmd.ExecuteNonQuery();  
348.   
349. return ret;  
350.   
351. }  
352.   
353.   
354. public object ExecuteScalar(DbCommand cmd, Trans t)  
355.   
356. {  
357.   
358. cmd.Connection.Close();  
359.   
360. cmd.Connection = t.DbConnection;  
361.   
362. cmd.Transaction = t.DbTrans;  
363.   
364. object ret = cmd.ExecuteScalar();  
365.   
366. return ret;  
367.   
368. }  
369. 
370. #endregion  
371.   
372. }  
373.   
374.   
375. public class Trans : IDisposable  
376.   
377. {  
378.   
379. private DbConnection conn;  
380.   
381. private DbTransaction dbTrans;  
382.   
383. public DbConnection DbConnection  
384.   
385. {  
386.   
387. get { return this.conn; }  
388.   
389. }  
390.   
391. public DbTransaction DbTrans  
392.   
393. {  
394.   
395. get { return this.dbTrans; }  
396.   
397. }  
398.   
399.   
400. public Trans()  
401.   
402. {  
403.   
404. conn = DbHelper.CreateConnection();  
405.   
406. conn.Open();  
407.   
408. dbTrans = conn.BeginTransaction();  
409.   
410. }  
411.   
412. public Trans(string connectionString)  
413.   
414. {  
415.   
416. conn = DbHelper.CreateConnection(connectionString);  
417.   
418. conn.Open();  
419.   
420. dbTrans = conn.BeginTransaction();  
421.   
422. }  
423.   
424. public void Commit()  
425.   
426. {  
427.   
428. dbTrans.Commit();  
429.   
430. this.Colse();  
431.   
432. }  
433.   
434.   
435. public void RollBack()  
436.   
437. {  
438.   
439. dbTrans.Rollback();  
440.   
441. this.Colse();  
442.   
443. }  
444.   
445.   
446. public void Dispose()  
447.   
448. {  
449.   
450. this.Colse();  
451.   
452. }  
453.   
454.   
455. public void Colse()  
456.   
457. {  
458.   
459. if (conn.State == System.Data.ConnectionState.Open)  
460.   
461. {  
462.   
463. conn.Close();  
464.   
465. }  
466.   
467. }  
468.   
469.   
470. }  
471.   
472. }  
 
2、直接执行sql语句
 
 
   ​​view plain​​
  
 
 
1. DbHelper db = new DbHelper();  
2.   
3. DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values(‘?haha‘?)");  
4.   
5. db.ExecuteNonQuery(cmd);  
 
3、执行存储过程
 
 
   ​​view plain​​
  
 
 
1. DbHelper db = new DbHelper();  
2.   
3. DbCommand cmd = db.GetStoredProcCommond("t1_insert");  
4.   
5. db.AddInParameter(cmd, "@id", DbType.String, "heihei");  
6.   
7. db.ExecuteNonQuery(cmd);  
 
4、返回DataSet
 
 
   ​​view plain​​
  
 
 
1. DbHelper db = new DbHelper();  
2.   
3. DbCommand cmd = db.GetSqlStringCommond("select * from t1");  
4.   
5. DataSet ds = db.ExecuteDataSet(cmd);  
 
5、返回DataTable
 
 
   ​​view plain​​
  
 
 
1. DbHelper db = new DbHelper();  
2.   
3. DbCommand cmd = db.GetSqlStringCommond("t1_findall");  
4.   
5. DataTable dt = db.ExecuteDataTable(cmd);  
 
 
6、输入参数/输出参数/返回值的使用(比较重要哦)
 
 
   ​​view plain​​
  
 
 
1. DbHelper db = new DbHelper();  
2.   
3. DbCommand cmd = db.GetStoredProcCommond("t2_insert");  
4.   
5. db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);  
6.   
7. db.AddOutParameter(cmd, "@outString", DbType.String, 20);  
8.   
9. db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);  
10.   
11.   
12. db.ExecuteNonQuery(cmd);  
13.   
14.   
15. string s = db.GetParameter(cmd, "@outString").Value as string;   
16.   
17. int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);   
 
 
7、DataReader使用
 
 
 
   ​​view plain​​
  
 
 
1. DbHelper db = new DbHelper();  
2.   
3. DbCommand cmd = db.GetStoredProcCommond("t2_insert");  
4.   
5. db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);  
6.   
7. db.AddOutParameter(cmd, "@outString", DbType.String, 20);  
8.   
9. db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);  
10.   
11.   
12. using (DbDataReader reader = db.ExecuteReader(cmd))  
13.   
14. {  
15.   
16. dt.Load(reader);  
17.   
18. }   
19.   
20. string s = db.GetParameter(cmd, "@outString").Value as string;   
21.   
22. int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);   
 
 
8、事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦)
以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:
 
 
   ​​view plain​​
  
 
 
1. pubic void DoBusiness()  
2.   
3. {  
4.   
5. using (Trans t = new Trans())  
6.   
7. {  
8.   
9. try  
10.   
11. {  
12.   
13. D1(t);  
14.   
15. throw new Exception();  
16.   
17. D2(t);  
18.   
19. t.Commit();  
20.   
21. }  
22.   
23. catch  
24.   
25. {  
26.   
27. t.RollBack();  
28.   
29. }  
30.   
31. }  
32.   
33. }  
34.   
35. public void D1(Trans t)  
36.   
37. {  
38.   
39. DbHelper db = new DbHelper();  
40.   
41. DbCommand cmd = db.GetStoredProcCommond("table_insert");  
42.   
43. db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);  
44.   
45. db.AddOutParameter(cmd, "@outString", DbType.String, 20);  
46.   
47. db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);  
48.   
49.   
50. if (t == null) db.ExecuteNonQuery(cmd);  
51.   
52. else db.ExecuteNonQuery(cmd,t);  
53.   
54.   
55. string s = db.GetParameter(cmd, "@outString").Value as string;  
56.   
57. int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);  
58.   
59. }  
60.   
61. public void D2(Trans t)  
62.   
63. {  
64.   
65. DbHelper db = new DbHelper();  
66.   
67. DbCommand cmd = db.GetSqlStringCommond("insert table (id)values(@id)");   
68.   
69. if (t == null) db.ExecuteNonQuery(cmd);  
70.   
71. else db.ExecuteNonQuery(cmd, t);  
72.   
73. }