微软通用的DBHelper

view plain

1. usingSystem;
3. usingSystem.Configuration;
5. usingSystem.Data;
7. usingSystem.Data.Common;
9. usingSystem.Data.OleDb;
11. usingSystem.Data.SqlClient;
13. namespaceLelog.Data
15. {
17. ///<summary>
19. ///数据库助手类
21. ///</summary>
23. publicclassDbHelper
25. {
27. privatestaticstringdbProviderName=ConfigurationSettings.AppSettings["DbProviderName"];
29. privatestaticstringdbConnectionString=ConfigurationManager.AppSettings["TaxInvoiceConn"];
32. privateDbConnectionconnection;
34. publicDbHelper()
36. {
38. this.connection=CreateConnection(DbHelper.dbConnectionString);
40. }
43. publicDbHelper(stringconnectionString)
45. {
47. this.connection=CreateConnection(connectionString);
49. }
52. publicstaticDbConnectionCreateConnection()
54. {
56. DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
58. DbConnectiondbconn=dbfactory.CreateConnection();
60. dbconn.ConnectionString=DbHelper.dbConnectionString;
62. returndbconn;
64. }
66. publicstaticDbConnectionCreateConnection(stringconnectionString)
68. {
70. DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
72. DbConnectiondbconn=dbfactory.CreateConnection();
74. dbconn.ConnectionString=connectionString;
76. returndbconn;
78. }
81. publicDbCommandGetStoredProcCommond(stringstoredProcedure)
83. {
85. DbCommanddbCommand=connection.CreateCommand();
87. dbCommand.CommandText=storedProcedure;
89. dbCommand.CommandType=CommandType.StoredProcedure;
91. returndbCommand;
93. }
95. publicDbCommandGetSqlStringCommond(stringsqlQuery)
97. {
99. DbCommanddbCommand=connection.CreateCommand();
101. dbCommand.CommandText=sqlQuery;
103. dbCommand.CommandType=CommandType.Text;
105. returndbCommand;
107. }
109. #region增加参数
111. publicvoidAddParameterCollection(DbCommandcmd,DbParameterCollectiondbParameterCollection)
113. {
115. foreach(DbParameterdbParameterindbParameterCollection)
117. {
119. cmd.Parameters.Add(dbParameter);
121. }
123. }
125. publicvoidAddOutParameter(DbCommandcmd,stringparameterName,DbTypedbType,intsize)
127. {
129. DbParameterdbParameter=cmd.CreateParameter();
131. dbParameter.DbType=dbType;
133. dbParameter.ParameterName=parameterName;
135. dbParameter.Size=size;
137. dbParameter.Direction=ParameterDirection.Output;
139. cmd.Parameters.Add(dbParameter);
141. }
143. publicvoidAddInParameter(DbCommandcmd,stringparameterName,DbTypedbType,objectvalue)
145. {
147. DbParameterdbParameter=cmd.CreateParameter();
149. dbParameter.DbType=dbType;
151. dbParameter.ParameterName=parameterName;
153. dbParameter.Value=value;
155. dbParameter.Direction=ParameterDirection.Input;
157. cmd.Parameters.Add(dbParameter);
159. }
161. publicvoidAddReturnParameter(DbCommandcmd,stringparameterName,DbTypedbType)
163. {
165. DbParameterdbParameter=cmd.CreateParameter();
167. dbParameter.DbType=dbType;
169. dbParameter.ParameterName=parameterName;
171. dbParameter.Direction=ParameterDirection.ReturnValue;
173. cmd.Parameters.Add(dbParameter);
175. }
177. publicDbParameterGetParameter(DbCommandcmd,stringparameterName)
179. {
181. returncmd.Parameters[parameterName];
183. }
186. #endregion
188. #region执行
190. publicDataSetExecuteDataSet(DbCommandcmd)
192. {
194. DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
196. DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
198. dbDataAdapter.SelectCommand=cmd;
200. DataSetds=newDataSet();
202. dbDataAdapter.Fill(ds);
204. returnds;
206. }
209. publicDataTableExecuteDataTable(DbCommandcmd)
211. {
213. DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
215. DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
217. dbDataAdapter.SelectCommand=cmd;
219. DataTabledataTable=newDataTable();
221. dbDataAdapter.Fill(dataTable);
223. returndataTable;
225. }
228. publicDbDataReaderExecuteReader(DbCommandcmd)
230. {
232. cmd.Connection.Open();
234. DbDataReaderreader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
236. returnreader;
238. }
240. publicintExecuteNonQuery(DbCommandcmd)
242. {
244. cmd.Connection.Open();
246. intret=cmd.ExecuteNonQuery();
248. cmd.Connection.Close();
250. returnret;
252. }
255. publicobjectExecuteScalar(DbCommandcmd)
257. {
259. cmd.Connection.Open();
261. objectret=cmd.ExecuteScalar();
263. cmd.Connection.Close();
265. returnret;
267. }
269. #endregion
271. #region执行事务
273. publicDataSetExecuteDataSet(DbCommandcmd,Transt)
275. {
277. cmd.Connection=t.DbConnection;
279. cmd.Transaction=t.DbTrans;
281. DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
283. DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
285. dbDataAdapter.SelectCommand=cmd;
287. DataSetds=newDataSet();
289. dbDataAdapter.Fill(ds);
291. returnds;
293. }
296. publicDataTableExecuteDataTable(DbCommandcmd,Transt)
298. {
300. cmd.Connection=t.DbConnection;
302. cmd.Transaction=t.DbTrans;
304. DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName);
306. DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter();
308. dbDataAdapter.SelectCommand=cmd;
310. DataTabledataTable=newDataTable();
312. dbDataAdapter.Fill(dataTable);
314. returndataTable;
316. }
319. publicDbDataReaderExecuteReader(DbCommandcmd,Transt)
321. {
323. cmd.Connection.Close();
325. cmd.Connection=t.DbConnection;
327. cmd.Transaction=t.DbTrans;
329. DbDataReaderreader=cmd.ExecuteReader();
331. DataTabledt=newDataTable();
333. returnreader;
335. }
337. publicintExecuteNonQuery(DbCommandcmd,Transt)
339. {
341. cmd.Connection.Close();
343. cmd.Connection=t.DbConnection;
345. cmd.Transaction=t.DbTrans;
347. intret=cmd.ExecuteNonQuery();
349. returnret;
351. }
354. publicobjectExecuteScalar(DbCommandcmd,Transt)
356. {
358. cmd.Connection.Close();
360. cmd.Connection=t.DbConnection;
362. cmd.Transaction=t.DbTrans;
364. objectret=cmd.ExecuteScalar();
366. returnret;
368. }
370. #endregion
372. }
375. publicclassTrans:IDisposable
377. {
379. privateDbConnectionconn;
381. privateDbTransactiondbTrans;
383. publicDbConnectionDbConnection
385. {
387. get{returnthis.conn;}
389. }
391. publicDbTransactionDbTrans
393. {
395. get{returnthis.dbTrans;}
397. }
400. publicTrans()
402. {
404. conn=DbHelper.CreateConnection();
406. conn.Open();
408. dbTrans=conn.BeginTransaction();
410. }
412. publicTrans(stringconnectionString)
414. {
416. conn=DbHelper.CreateConnection(connectionString);
418. conn.Open();
420. dbTrans=conn.BeginTransaction();
422. }
424. publicvoidCommit()
426. {
428. dbTrans.Commit();
430. this.Colse();
432. }
435. publicvoidRollBack()
437. {
439. dbTrans.Rollback();
441. this.Colse();
443. }
446. publicvoidDispose()
448. {
450. this.Colse();
452. }
455. publicvoidColse()
457. {
459. if(conn.State==System.Data.ConnectionState.Open)
461. {
463. conn.Close();
465. }
467. }
470. }
472. }

2、直接执行sql语句

view plain

1. DbHelperdb=newDbHelper();
3. DbCommandcmd=db.GetSqlStringCommond("insertt1(id)values(‘?haha‘?)");
5. db.ExecuteNonQuery(cmd);

3、执行存储过程

view plain

1. DbHelperdb=newDbHelper();
3. DbCommandcmd=db.GetStoredProcCommond("t1_insert");
5. db.AddInParameter(cmd,"@id",DbType.String,"heihei");
7. db.ExecuteNonQuery(cmd);

4、返回DataSet

view plain

1. DbHelperdb=newDbHelper();
3. DbCommandcmd=db.GetSqlStringCommond("select*fromt1");
5. DataSetds=db.ExecuteDataSet(cmd);

5、返回DataTable

view plain

1. DbHelperdb=newDbHelper();
3. DbCommandcmd=db.GetSqlStringCommond("t1_findall");
5. DataTabledt=db.ExecuteDataTable(cmd);

6、输入参数/输出参数/返回值的使用(比较重要哦)

view plain

1. DbHelperdb=newDbHelper();
3. DbCommandcmd=db.GetStoredProcCommond("t2_insert");
5. db.AddInParameter(cmd,"@timeticks",DbType.Int64,DateTime.Now.Ticks);
7. db.AddOutParameter(cmd,"@outString",DbType.String,20);
9. db.AddReturnParameter(cmd,"@returnValue",DbType.Int32);
12. db.ExecuteNonQuery(cmd);
15. strings=db.GetParameter(cmd,"@outString").Valueasstring;
17. intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);

7、DataReader使用

view plain

1. DbHelperdb=newDbHelper();
3. DbCommandcmd=db.GetStoredProcCommond("t2_insert");
5. db.AddInParameter(cmd,"@timeticks",DbType.Int64,DateTime.Now.Ticks);
7. db.AddOutParameter(cmd,"@outString",DbType.String,20);
9. db.AddReturnParameter(cmd,"@returnValue",DbType.Int32);
12. using(DbDataReaderreader=db.ExecuteReader(cmd))
14. {
16. dt.Load(reader);
18. }
20. strings=db.GetParameter(cmd,"@outString").Valueasstring;
22. intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);

8、事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦)

以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:

view plain

1. pubicvoidDoBusiness()
3. {
5. using(Transt=newTrans())
7. {
9. try
11. {
13. D1(t);
15. thrownewException();
17. D2(t);
19. t.Commit();
21. }
23. catch
25. {
27. t.RollBack();
29. }
31. }
33. }
35. publicvoidD1(Transt)
37. {
39. DbHelperdb=newDbHelper();
41. DbCommandcmd=db.GetStoredProcCommond("table_insert");
43. db.AddInParameter(cmd,"@timeticks",DbType.Int64,DateTime.Now.Ticks);
45. db.AddOutParameter(cmd,"@outString",DbType.String,20);
47. db.AddReturnParameter(cmd,"@returnValue",DbType.Int32);
50. if(t==null)db.ExecuteNonQuery(cmd);
52. elsedb.ExecuteNonQuery(cmd,t);
55. strings=db.GetParameter(cmd,"@outString").Valueasstring;
57. intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);
59. }
61. publicvoidD2(Transt)
63. {
65. DbHelperdb=newDbHelper();
67. DbCommandcmd=db.GetSqlStringCommond("inserttable(id)values(@id)");
69. if(t==null)db.ExecuteNonQuery(cmd);
71. elsedb.ExecuteNonQuery(cmd,t);
73. }