一般连接数据库的有好多种写法,现在只写两种,第二种感觉较好。
第一:
1 /// <summary>
2 /// 获取ACCESS数据库连接参数
3 /// 数据库的相对路径
4 /// </summary>
5 /// <returns></returns>
6 public static OleDbConnection getConn()
7 {
8 try
9 {
10 string connstr = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=" + Application.StartupPath + @"\Resources\BOM_log.mdb";
11 OleDbConnection tempconn = new OleDbConnection(connstr);
12 return (tempconn);
13 }
14 catch (Exception ex)
15 {
16 MessageBox.Show("Error:\n\n"+ex.Message);
17 return null;
18 }
19 }
2 /// 获取ACCESS数据库连接参数
3 /// 数据库的相对路径
4 /// </summary>
5 /// <returns></returns>
6 public static OleDbConnection getConn()
7 {
8 try
9 {
10 string connstr = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=" + Application.StartupPath + @"\Resources\BOM_log.mdb";
11 OleDbConnection tempconn = new OleDbConnection(connstr);
12 return (tempconn);
13 }
14 catch (Exception ex)
15 {
16 MessageBox.Show("Error:\n\n"+ex.Message);
17 return null;
18 }
19 }
1/// <summary>
2 /// 通过SQL语句得到数据集
3 /// 参数是SQL语句
4 /// </summary>
5 /// <param name="GetSql"></param>
6 /// <returns></returns>
7 public DataView GetData(string GetSql)
8 {
9 DataView dataview;
10 System.Data.DataSet dataset = new DataSet();
11 try
12 {
13 OleDbConnection conn = getConn();
14 OleDbDataAdapter adp = new OleDbDataAdapter();
15 adp.SelectCommand = new OleDbCommand(GetSql, conn);
16 adp.Fill(dataset, "mytable");
17 conn.Close();
18 }
19 catch (Exception ee)
20 {
21 MessageBox.Show("错误提示 : \n\n" + ee.Message);
22 }
23 dataview = new DataView(dataset.Tables["mytable"]);
24 return dataview;
25 }
2 /// 通过SQL语句得到数据集
3 /// 参数是SQL语句
4 /// </summary>
5 /// <param name="GetSql"></param>
6 /// <returns></returns>
7 public DataView GetData(string GetSql)
8 {
9 DataView dataview;
10 System.Data.DataSet dataset = new DataSet();
11 try
12 {
13 OleDbConnection conn = getConn();
14 OleDbDataAdapter adp = new OleDbDataAdapter();
15 adp.SelectCommand = new OleDbCommand(GetSql, conn);
16 adp.Fill(dataset, "mytable");
17 conn.Close();
18 }
19 catch (Exception ee)
20 {
21 MessageBox.Show("错误提示 : \n\n" + ee.Message);
22 }
23 dataview = new DataView(dataset.Tables["mytable"]);
24 return dataview;
25 }
1 /// <summary>
2 /// 通过SQL语句修改数据库
3 /// </summary>
4 /// <param name="UpdateSql"></param>
5 /// <returns></returns>
6 public bool UpdateData(string UpdateSql)
7 {
8 //在此判断配置文件参数是否为true,使用方便
9 if (System.Configuration.ConfigurationManager.AppSettings["OperateOrNot"].ToString() == "true")
10 {
11 OleDbConnection conn = getConn();
12 OleDbCommand mycmd = new OleDbCommand(UpdateSql, conn);
13 try
14 {
15 conn.Open();
16 mycmd.ExecuteNonQuery();
17 return true;
18 }
19 catch (Exception ex)
20 {
21 throw (new Exception(ex.Message));
22 return false;
23 }
24 finally
25 {
26 mycmd.Dispose();
27 conn.Close();
28 }
29 }
30 else
31 {
32 return false ;
33 }
34 }
2 /// 通过SQL语句修改数据库
3 /// </summary>
4 /// <param name="UpdateSql"></param>
5 /// <returns></returns>
6 public bool UpdateData(string UpdateSql)
7 {
8 //在此判断配置文件参数是否为true,使用方便
9 if (System.Configuration.ConfigurationManager.AppSettings["OperateOrNot"].ToString() == "true")
10 {
11 OleDbConnection conn = getConn();
12 OleDbCommand mycmd = new OleDbCommand(UpdateSql, conn);
13 try
14 {
15 conn.Open();
16 mycmd.ExecuteNonQuery();
17 return true;
18 }
19 catch (Exception ex)
20 {
21 throw (new Exception(ex.Message));
22 return false;
23 }
24 finally
25 {
26 mycmd.Dispose();
27 conn.Close();
28 }
29 }
30 else
31 {
32 return false ;
33 }
34 }
第二种:
1using System;
2using System.Collections;
3using System.Collections.Generic;
4using System.Configuration;
5using System.Data;
6using System.Data.Common;
7using System.Data.OleDb;
8using System.Text;
9using System.Text.RegularExpressions;
10using log4net;
11
12namespace AFC.BOM.Common.DB
13{
14 /// <summary>
15 /// OLE DB数据库访问接口。
16 /// </summary>
17 public class DBA : IDisposable
18 {
19 private bool throwException = true ;
20 private OleDbConnection conn ;
21
22 private string dbName ;
23
24 private ILog log;
25 private OleDbTransaction txn;
26
27 /// <summary>
28 /// 创建DBA对象。
29 /// </summary>
30 /// <param name="dbName">MDB文件的路径名称</param>
31 public DBA(string dbName) : this (dbName, true)
32 {
33 }
34
35 public DBA(string dbName, bool throwException)
36 {
37 this.throwException = throwException;
38 log = LogManager.GetLogger("DB::" + dbName);
39 this.dbName = dbName;
40 GetConnection();
41 }
42
43 private int GetConnection ()
44 {
45 if (string.IsNullOrEmpty(dbName))
46 {
47 log.Error("dbName is NULL ot EMPTY.");
48 return -1;
49 }
50 string connString = @"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=" + dbName;
51
52 log.Info("Connect to [" + connString + "].");
53 try
54 {
55 conn = new OleDbConnection(connString);
56 conn.Open();
57 }
58 catch (Exception e)
59 {
60 HandleOleError(e);
61 log.Error("Open Database Error." + e);
62 if (! throwException)
63 return -1;
64 if (e is DbException)
65 throw e;
66 else
67 throw new DBAException("Open Database Error.", e);
68 }
69
70 log.Info("Database opened sucessfully.");
71 return 0;
72
73 }
74
75 /// <summary>
76 /// 启动一个事务。
77 /// </summary>
78 /// <returns></returns>
79 public int StartTransaction ()
80 {
81 if (conn == null && GetConnection() != 0)
82 {
83 log.Error("Can't start transaction.");
84 return -1;
85 }
86
87 if (txn != null)
88 {
89 log.Warn("Previous transaction not completed, will rollback it.");
90 txn.Rollback();
91 }
92 try
93 {
94 txn = conn.BeginTransaction();
95 }
96 catch (Exception e)
97 {
98 HandleOleError(e);
99 log.Error("Start Transaction Error.", e);
100 if (!throwException)
101 return -1;
102 if (e is DbException)
103 throw e;
104 else
105 throw new DBAException("Start Transaction Error.", e);
106 }
107 return 0;
108 }
109
110 /// <summary>
111 /// 提交事务。
112 /// </summary>
113 /// <returns></returns>
114 public int Commit ()
115 {
116 if (txn == null)
117 {
118 log.Error("Transaction not start yet.");
119 return -1;
120 }
121
122 try
123 {
124 txn.Commit();
125 }
126 catch (Exception e)
127 {
128 HandleOleError(e);
129 log.Error("Commit transaction Error." , e);
130 if (!throwException)
131 return -1;
132 if (e is DbException)
133 throw e;
134 else
135 throw new DBAException("Commit transaction Error.", e);
136 }
137 finally
138 {
139 txn = null;
140 }
141
142 return 0;
143 }
144
145
146 /// <summary>
147 /// 事务回滚。
148 /// </summary>
149 /// <returns></returns>
150 public int Rollback()
151 {
152 if (txn == null)
153 {
154 log.Error("Transaction not start yet.");
155 return -1;
156 }
157
158 try
159 {
160 txn.Rollback();
161 }
162 catch (Exception e)
163 {
164 HandleOleError(e);
165 log.Error("Rollback transaction Error.", e);
166 if (!throwException)
167 return -1;
168 if (e is DbException)
169 throw e;
170 else
171 throw new DBAException("Rollback transaction Error.", e);
172 }
173 finally
174 {
175 txn = null;
176 }
177
178 return 0;
179 }
180
181 /// <summary>
182 /// 销毁连接对象。
183 /// </summary>
184 public void Dispose ()
185 {
186 if (conn == null)
187 {
188 log.Warn("Connection is NULL.");
189 return;
190 }
191 try
192 {
193 conn.Close();
194 conn.Dispose();
195 }
196 catch (Exception e)
197 {
198 HandleOleError(e);
199 log.Error("Dispose Connection Error.", e);
200
201 }
202 log.Info("Connection Disposed.");
203 }
204
205 /// <summary>
206 /// 执行非查询语句。
207 /// </summary>
208 /// <param name="sqlString">SQL格式串</param>
209 /// <param name="args">SQL参数</param>
210 /// <returns></returns>
211 public int ExecuteNonQuery(string sqlString, params object[] args)
212 {
213 sqlString = BuildSql(sqlString, args);
214
215 if (sqlString == null)
216 return -1;
217 return ExecuteNonQuery(sqlString);
218 }
219
220 /// <summary>
221 /// 执行非查询语句。
222 /// </summary>
223 /// <param name="sqlString">SQL语句</param>
224 /// <returns></returns>
225 public int ExecuteNonQuery (string sqlString)
226 {
227 if (conn == null && GetConnection() != 0)
228 {
229 log.Error("Can't ExecuteNonQuery.");
230 return -1;
231 }
232
233 if (log.IsDebugEnabled)
234 log.Debug("Will ExecuteNonQuery (" + sqlString + ").");
235 try
236 {
237 OleDbCommand cmd = conn.CreateCommand();
238
239 using (cmd)
240 {
241 if (txn != null)
242 {
243 cmd.Transaction = txn;
244 }
245 cmd.CommandText = sqlString;
246 cmd.Connection = conn;
247 int count = cmd.ExecuteNonQuery();
248
249 log.Debug("ExecuteNonQuery effect row count: " + count);
250 return count;
251 }
252 }
253 catch (Exception e)
254 {
255 HandleOleError(e);
256 log.Error("ExecuteNonQuery[" + sqlString + "] Error.", e);
257 if (!throwException)
258 return -1;
259 if (e is DbException)
260 throw e;
261 else
262 throw new DBAException("ExecuteNonQuery Error.", e);
263 }
264 }
265
266 /// <summary>
267 /// 执行查询语句,返回DataTable。
268 /// </summary>
269 /// <param name="sqlString">SQL格式串</param>
270 /// <param name="args">SQL参数</param>
271 /// <returns></returns>
272 public DataTable ExecuteQuery (string sqlString, params object [] args)
273 {
274 sqlString = BuildSql(sqlString, args);
275
276 if (sqlString == null)
277 return null;
278 return ExecuteQuery(sqlString);
279 }
280
281 /// <summary>
282 /// 执行查询语句,返回DataTable。
283 /// </summary>
284 /// <param name="sqlString">SQL语句</param>
285 /// <returns></returns>
286 public DataTable ExecuteQuery (string sqlString)
287 {
288 if (conn == null && GetConnection() != 0)
289 {
290 log.Error("Can't ExecuteQuery.");
291 return null;
292 }
293
294 if (log.IsDebugEnabled)
295 log.Debug("Will ExecuteQuery (" + sqlString + ").");
296
297 try
298 {
299 OleDbDataAdapter adapter = new OleDbDataAdapter(sqlString, conn);
300
301 using (adapter)
302 {
303 DataTable table = new DataTable("Table");
304
305 adapter.Fill(table);
306 log.Debug("ExecuteQuery return table with rows: " + table.Rows.Count);
307 return table;
308 }
309 }
310 catch (Exception e)
311 {
312 HandleOleError(e);
313 log.Error("ExecuteQuery[" + sqlString + "] Error.", e);
314 if (!throwException)
315 return null;
316 if (e is DbException)
317 throw e;
318 else
319 throw new DBAException("ExecuteQuery Error.", e);
320 }
321 }
322
323 /// <summary>
324 /// 执行查询语句,返回DataSet。
325 /// </summary>
326 /// <param name="sqlString">SQL格式串</param>
327 /// <param name="args">SQL参数</param>
328 /// <returns></returns>
329 public DataSet ExecuteDataSetQuery(string sqlString, params object[] args)
330 {
331 sqlString = BuildSql(sqlString, args);
332
333 if (sqlString == null)
334 return null;
335 return ExecuteDataSetQuery(sqlString);
336 }
337
338 /// <summary>
339 /// 执行查询语句,返回DataSet。
340 /// </summary>
341 /// <param name="sqlString">SQL格式串</param>
342 /// <returns></returns>
343 public DataSet ExecuteDataSetQuery (string sqlString)
344 {
345 DataTable table = ExecuteQuery(sqlString);
346
347 if (table == null)
348 return null;
349 else
350 {
351 DataSet ds = new DataSet();
352
353 ds.Tables.Add(table);
354 return ds;
355 }
356 }
357
358 /// <summary>
359 /// 执行标量查询语句。
360 /// </summary>
361 /// <param name="sqlString">SQL格式串</param>
362 /// <param name="args">SQL参数</param>
363 /// <returns></returns>
364 public object ExecuteScalar(string sqlString, params object[] args)
365 {
366 sqlString = BuildSql(sqlString, args);
367
368 if (sqlString == null)
369 return null;
370 return ExecuteScalar(sqlString);
371 }
372
373 /// <summary>
374 /// 执行标量查询语句。
375 /// </summary>
376 /// <param name="sqlString">SQL语句</param>
377 /// <returns></returns>
378 public object ExecuteScalar (string sqlString)
379 {
380 if (conn == null && GetConnection() != 0)
381 {
382 log.Error("Can't ExecuteScalar.");
383 return null;
384 }
385
386 if (log.IsDebugEnabled)
387 log.Debug("Will ExecuteScalar (" + sqlString + ").");
388 try
389 {
390 OleDbCommand cmd = conn.CreateCommand();
391
392 using (cmd)
393 {
394 if (txn != null)
395 {
396 cmd.Transaction = txn;
397 }
398 cmd.CommandText = sqlString;
399 cmd.Connection = conn;
400 object ret = cmd.ExecuteScalar();
401
402 log.Debug("ExecuteScalar return value: [" + ret + "].");
403 return ret;
404 }
405 }
406 catch (Exception e)
407 {
408 HandleOleError(e);
409 log.Error("ExecuteScalar[" + sqlString + "] Error.", e);
410 if (!throwException)
411 return null;
412 if (e is DbException)
413 throw e;
414 else
415 throw new DBAException("ExecuteScalar Error.", e);
416 }
417 }
418
419 /// <summary>
420 /// 创建SQL语句
421 /// </summary>
422 /// <param name="sqlString"></param>
423 /// <param name="args"></param>
424 /// <returns></returns>
425 public string BuildSql (string sqlString, params object [] args)
426 {
427 if (string.IsNullOrEmpty(sqlString))
428 {
429 log.Error("SqlString is NULL or EMPTY." , new ArgumentNullException("sqlString"));
430 return null;
431 }
432 try
433 {
434 return string.Format(sqlString, args);
435 }
436 catch (Exception e)
437 {
438 log.Error("Format sql Error[" + sqlString + "]" , e);
439 if (!throwException)
440 return null;
441 if (e is DbException)
442 throw e;
443 else
444 throw new DBAException("Format sql Erro.", e);
445
446 }
447 }
448
449 /// <summary>
450 /// 执行参数化语句,仅用于非查询语句(兼容UD部分的SQL语句)。
451 /// </summary>
452 /// <param name="sqlString">SQL格式串</param>
453 /// <param name="args">SQL参数</param>
454 /// <returns></returns>
455 public int ExecuteParamSql (string sqlString, SqlParamList args)
456 {
457 if (conn == null && GetConnection() != 0)
458 {
459 log.Error("Can't ExecuteParamSql.");
460 return -1;
461 }
462 log.Debug("Will ExecuteParamSql [" + sqlString + "].");
463 if (CheckParamSql (sqlString, args) != 0)
464 return 0;
465
466 try
467 {
468 OleDbCommand cmd = conn.CreateCommand();
469
470 using (cmd)
471 {
472 if (txn != null)
473 {
474 cmd.Transaction = txn;
475 }
476 cmd.CommandText = sqlString;
477 cmd.Connection = conn;
478
479 for (int i = 0; args != null && args.list != null && i < args.list.Count; i++)
480 {
481 SqlParam sqlParam = args.list[i];
482
483 string key = sqlParam.name;
484 object value = sqlParam.value;
485
486 if (log.IsDebugEnabled)
487 log.Debug("Param[" + i + "] Name[" + key + "], Type[" +
488 (value == null ? "null" : value.GetType().ToString()) +
489 "], Value[" + value + "].");
490
491 OleDbParameter param = cmd.CreateParameter();
492 param.ParameterName = key;
493 param.OleDbType = GetDbType(value);
494 param.Value = value;
495 cmd.Parameters.Add(param);
496 }
497
498 int count = cmd.ExecuteNonQuery();
499 log.Debug("ExecuteParamSql effect row count: " + count);
500 return count;
501 }
502 }
503 catch (Exception e)
504 {
505 HandleOleError(e);
506 log.Error("ExecuteParamSql[" + sqlString + "] Error.", e);
507 if (!throwException)
508 return -1;
509 if (e is DbException)
510 throw e;
511 else
512 throw new DBAException("ExecuteParamSql Error.", e);
513 }
514 }
515
516 private int CheckParamSql (string sqlString, SqlParamList list)
517 {
518 if (! log.IsDebugEnabled)
519 return 0;
520
521 Regex r = new Regex(@"@(?<x>[0-9a-zA-Z]*)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
522 MatchCollection mc = r.Matches(sqlString);
523 int err = 0;
524 for (int i = 0; i < mc.Count; i++)
525 {
526 string paramName = mc[i].Result("$1");
527
528 if (i >= list.list.Count)
529 {
530 log.Error("SQL Param Mismatch, SqlString params [" + i + "] = " + paramName + ", SqlParamList.Count = " + list.list.Count + ".");
531 err ++;
532 continue;
533 }
534
535 if (paramName != list.list [i].name)
536 {
537 log.Error("SQL Param Mismatch, SqlString params [" + i + "] = " + paramName + ", SqlParamList [" + i + "] = " + list.list[i].name + ".");
538 err++;
539 continue;
540 }
541 }
542 return err;
543 }
544
545 private OleDbType GetDbType(object value)
546 {
547 if (value == null)
548 return OleDbType.Empty;
549
550 Type type = value.GetType();
551
552 if (type.IsEnum)
553 return OleDbType.Integer;
554
555 if (type == typeof (bool))
556 return OleDbType.TinyInt;
557
558 if (type == typeof (string))
559 return OleDbType.VarChar;
560
561 if (type == typeof (uint))
562 return OleDbType.UnsignedInt;
563
564 if (type == typeof(int))
565 return OleDbType.Integer;
566
567 if (type == typeof (byte []))
568 return OleDbType.VarBinary;
569
570 if (type == typeof (short))
571 return OleDbType.SmallInt;
572
573 if (type == typeof(ushort))
574 return OleDbType.UnsignedSmallInt;
575
576 if (type == typeof (byte))
577 return OleDbType.UnsignedTinyInt;
578
579 return OleDbType.IUnknown;
580 }
581
582 private void HandleOleError (Exception e)
583 {
584 if (! (e is OleDbException))
585 {
586 return;
587 }
588
589 OleDbException ex = (OleDbException) e;
590
591 string errorMessages = "OleDbException::" + ex.ErrorCode + "\n";
592
593 for (int i = 0; i < ex.Errors.Count; i++)
594 {
595 errorMessages += "\tIndex #" + i + "\n" +
596 "\tMessage: " + ex.Errors[i].Message + "\n" +
597 "\tNativeError: " + ex.Errors[i].NativeError + "\n" +
598 "\tSource: " + ex.Errors[i].Source + "\n" +
599 "\tSQLState: " + ex.Errors[i].SQLState + "\n";
600 }
601
602 log.Error(errorMessages);
603 }
604 }
605}
606
2using System.Collections;
3using System.Collections.Generic;
4using System.Configuration;
5using System.Data;
6using System.Data.Common;
7using System.Data.OleDb;
8using System.Text;
9using System.Text.RegularExpressions;
10using log4net;
11
12namespace AFC.BOM.Common.DB
13{
14 /// <summary>
15 /// OLE DB数据库访问接口。
16 /// </summary>
17 public class DBA : IDisposable
18 {
19 private bool throwException = true ;
20 private OleDbConnection conn ;
21
22 private string dbName ;
23
24 private ILog log;
25 private OleDbTransaction txn;
26
27 /// <summary>
28 /// 创建DBA对象。
29 /// </summary>
30 /// <param name="dbName">MDB文件的路径名称</param>
31 public DBA(string dbName) : this (dbName, true)
32 {
33 }
34
35 public DBA(string dbName, bool throwException)
36 {
37 this.throwException = throwException;
38 log = LogManager.GetLogger("DB::" + dbName);
39 this.dbName = dbName;
40 GetConnection();
41 }
42
43 private int GetConnection ()
44 {
45 if (string.IsNullOrEmpty(dbName))
46 {
47 log.Error("dbName is NULL ot EMPTY.");
48 return -1;
49 }
50 string connString = @"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=" + dbName;
51
52 log.Info("Connect to [" + connString + "].");
53 try
54 {
55 conn = new OleDbConnection(connString);
56 conn.Open();
57 }
58 catch (Exception e)
59 {
60 HandleOleError(e);
61 log.Error("Open Database Error." + e);
62 if (! throwException)
63 return -1;
64 if (e is DbException)
65 throw e;
66 else
67 throw new DBAException("Open Database Error.", e);
68 }
69
70 log.Info("Database opened sucessfully.");
71 return 0;
72
73 }
74
75 /// <summary>
76 /// 启动一个事务。
77 /// </summary>
78 /// <returns></returns>
79 public int StartTransaction ()
80 {
81 if (conn == null && GetConnection() != 0)
82 {
83 log.Error("Can't start transaction.");
84 return -1;
85 }
86
87 if (txn != null)
88 {
89 log.Warn("Previous transaction not completed, will rollback it.");
90 txn.Rollback();
91 }
92 try
93 {
94 txn = conn.BeginTransaction();
95 }
96 catch (Exception e)
97 {
98 HandleOleError(e);
99 log.Error("Start Transaction Error.", e);
100 if (!throwException)
101 return -1;
102 if (e is DbException)
103 throw e;
104 else
105 throw new DBAException("Start Transaction Error.", e);
106 }
107 return 0;
108 }
109
110 /// <summary>
111 /// 提交事务。
112 /// </summary>
113 /// <returns></returns>
114 public int Commit ()
115 {
116 if (txn == null)
117 {
118 log.Error("Transaction not start yet.");
119 return -1;
120 }
121
122 try
123 {
124 txn.Commit();
125 }
126 catch (Exception e)
127 {
128 HandleOleError(e);
129 log.Error("Commit transaction Error." , e);
130 if (!throwException)
131 return -1;
132 if (e is DbException)
133 throw e;
134 else
135 throw new DBAException("Commit transaction Error.", e);
136 }
137 finally
138 {
139 txn = null;
140 }
141
142 return 0;
143 }
144
145
146 /// <summary>
147 /// 事务回滚。
148 /// </summary>
149 /// <returns></returns>
150 public int Rollback()
151 {
152 if (txn == null)
153 {
154 log.Error("Transaction not start yet.");
155 return -1;
156 }
157
158 try
159 {
160 txn.Rollback();
161 }
162 catch (Exception e)
163 {
164 HandleOleError(e);
165 log.Error("Rollback transaction Error.", e);
166 if (!throwException)
167 return -1;
168 if (e is DbException)
169 throw e;
170 else
171 throw new DBAException("Rollback transaction Error.", e);
172 }
173 finally
174 {
175 txn = null;
176 }
177
178 return 0;
179 }
180
181 /// <summary>
182 /// 销毁连接对象。
183 /// </summary>
184 public void Dispose ()
185 {
186 if (conn == null)
187 {
188 log.Warn("Connection is NULL.");
189 return;
190 }
191 try
192 {
193 conn.Close();
194 conn.Dispose();
195 }
196 catch (Exception e)
197 {
198 HandleOleError(e);
199 log.Error("Dispose Connection Error.", e);
200
201 }
202 log.Info("Connection Disposed.");
203 }
204
205 /// <summary>
206 /// 执行非查询语句。
207 /// </summary>
208 /// <param name="sqlString">SQL格式串</param>
209 /// <param name="args">SQL参数</param>
210 /// <returns></returns>
211 public int ExecuteNonQuery(string sqlString, params object[] args)
212 {
213 sqlString = BuildSql(sqlString, args);
214
215 if (sqlString == null)
216 return -1;
217 return ExecuteNonQuery(sqlString);
218 }
219
220 /// <summary>
221 /// 执行非查询语句。
222 /// </summary>
223 /// <param name="sqlString">SQL语句</param>
224 /// <returns></returns>
225 public int ExecuteNonQuery (string sqlString)
226 {
227 if (conn == null && GetConnection() != 0)
228 {
229 log.Error("Can't ExecuteNonQuery.");
230 return -1;
231 }
232
233 if (log.IsDebugEnabled)
234 log.Debug("Will ExecuteNonQuery (" + sqlString + ").");
235 try
236 {
237 OleDbCommand cmd = conn.CreateCommand();
238
239 using (cmd)
240 {
241 if (txn != null)
242 {
243 cmd.Transaction = txn;
244 }
245 cmd.CommandText = sqlString;
246 cmd.Connection = conn;
247 int count = cmd.ExecuteNonQuery();
248
249 log.Debug("ExecuteNonQuery effect row count: " + count);
250 return count;
251 }
252 }
253 catch (Exception e)
254 {
255 HandleOleError(e);
256 log.Error("ExecuteNonQuery[" + sqlString + "] Error.", e);
257 if (!throwException)
258 return -1;
259 if (e is DbException)
260 throw e;
261 else
262 throw new DBAException("ExecuteNonQuery Error.", e);
263 }
264 }
265
266 /// <summary>
267 /// 执行查询语句,返回DataTable。
268 /// </summary>
269 /// <param name="sqlString">SQL格式串</param>
270 /// <param name="args">SQL参数</param>
271 /// <returns></returns>
272 public DataTable ExecuteQuery (string sqlString, params object [] args)
273 {
274 sqlString = BuildSql(sqlString, args);
275
276 if (sqlString == null)
277 return null;
278 return ExecuteQuery(sqlString);
279 }
280
281 /// <summary>
282 /// 执行查询语句,返回DataTable。
283 /// </summary>
284 /// <param name="sqlString">SQL语句</param>
285 /// <returns></returns>
286 public DataTable ExecuteQuery (string sqlString)
287 {
288 if (conn == null && GetConnection() != 0)
289 {
290 log.Error("Can't ExecuteQuery.");
291 return null;
292 }
293
294 if (log.IsDebugEnabled)
295 log.Debug("Will ExecuteQuery (" + sqlString + ").");
296
297 try
298 {
299 OleDbDataAdapter adapter = new OleDbDataAdapter(sqlString, conn);
300
301 using (adapter)
302 {
303 DataTable table = new DataTable("Table");
304
305 adapter.Fill(table);
306 log.Debug("ExecuteQuery return table with rows: " + table.Rows.Count);
307 return table;
308 }
309 }
310 catch (Exception e)
311 {
312 HandleOleError(e);
313 log.Error("ExecuteQuery[" + sqlString + "] Error.", e);
314 if (!throwException)
315 return null;
316 if (e is DbException)
317 throw e;
318 else
319 throw new DBAException("ExecuteQuery Error.", e);
320 }
321 }
322
323 /// <summary>
324 /// 执行查询语句,返回DataSet。
325 /// </summary>
326 /// <param name="sqlString">SQL格式串</param>
327 /// <param name="args">SQL参数</param>
328 /// <returns></returns>
329 public DataSet ExecuteDataSetQuery(string sqlString, params object[] args)
330 {
331 sqlString = BuildSql(sqlString, args);
332
333 if (sqlString == null)
334 return null;
335 return ExecuteDataSetQuery(sqlString);
336 }
337
338 /// <summary>
339 /// 执行查询语句,返回DataSet。
340 /// </summary>
341 /// <param name="sqlString">SQL格式串</param>
342 /// <returns></returns>
343 public DataSet ExecuteDataSetQuery (string sqlString)
344 {
345 DataTable table = ExecuteQuery(sqlString);
346
347 if (table == null)
348 return null;
349 else
350 {
351 DataSet ds = new DataSet();
352
353 ds.Tables.Add(table);
354 return ds;
355 }
356 }
357
358 /// <summary>
359 /// 执行标量查询语句。
360 /// </summary>
361 /// <param name="sqlString">SQL格式串</param>
362 /// <param name="args">SQL参数</param>
363 /// <returns></returns>
364 public object ExecuteScalar(string sqlString, params object[] args)
365 {
366 sqlString = BuildSql(sqlString, args);
367
368 if (sqlString == null)
369 return null;
370 return ExecuteScalar(sqlString);
371 }
372
373 /// <summary>
374 /// 执行标量查询语句。
375 /// </summary>
376 /// <param name="sqlString">SQL语句</param>
377 /// <returns></returns>
378 public object ExecuteScalar (string sqlString)
379 {
380 if (conn == null && GetConnection() != 0)
381 {
382 log.Error("Can't ExecuteScalar.");
383 return null;
384 }
385
386 if (log.IsDebugEnabled)
387 log.Debug("Will ExecuteScalar (" + sqlString + ").");
388 try
389 {
390 OleDbCommand cmd = conn.CreateCommand();
391
392 using (cmd)
393 {
394 if (txn != null)
395 {
396 cmd.Transaction = txn;
397 }
398 cmd.CommandText = sqlString;
399 cmd.Connection = conn;
400 object ret = cmd.ExecuteScalar();
401
402 log.Debug("ExecuteScalar return value: [" + ret + "].");
403 return ret;
404 }
405 }
406 catch (Exception e)
407 {
408 HandleOleError(e);
409 log.Error("ExecuteScalar[" + sqlString + "] Error.", e);
410 if (!throwException)
411 return null;
412 if (e is DbException)
413 throw e;
414 else
415 throw new DBAException("ExecuteScalar Error.", e);
416 }
417 }
418
419 /// <summary>
420 /// 创建SQL语句
421 /// </summary>
422 /// <param name="sqlString"></param>
423 /// <param name="args"></param>
424 /// <returns></returns>
425 public string BuildSql (string sqlString, params object [] args)
426 {
427 if (string.IsNullOrEmpty(sqlString))
428 {
429 log.Error("SqlString is NULL or EMPTY." , new ArgumentNullException("sqlString"));
430 return null;
431 }
432 try
433 {
434 return string.Format(sqlString, args);
435 }
436 catch (Exception e)
437 {
438 log.Error("Format sql Error[" + sqlString + "]" , e);
439 if (!throwException)
440 return null;
441 if (e is DbException)
442 throw e;
443 else
444 throw new DBAException("Format sql Erro.", e);
445
446 }
447 }
448
449 /// <summary>
450 /// 执行参数化语句,仅用于非查询语句(兼容UD部分的SQL语句)。
451 /// </summary>
452 /// <param name="sqlString">SQL格式串</param>
453 /// <param name="args">SQL参数</param>
454 /// <returns></returns>
455 public int ExecuteParamSql (string sqlString, SqlParamList args)
456 {
457 if (conn == null && GetConnection() != 0)
458 {
459 log.Error("Can't ExecuteParamSql.");
460 return -1;
461 }
462 log.Debug("Will ExecuteParamSql [" + sqlString + "].");
463 if (CheckParamSql (sqlString, args) != 0)
464 return 0;
465
466 try
467 {
468 OleDbCommand cmd = conn.CreateCommand();
469
470 using (cmd)
471 {
472 if (txn != null)
473 {
474 cmd.Transaction = txn;
475 }
476 cmd.CommandText = sqlString;
477 cmd.Connection = conn;
478
479 for (int i = 0; args != null && args.list != null && i < args.list.Count; i++)
480 {
481 SqlParam sqlParam = args.list[i];
482
483 string key = sqlParam.name;
484 object value = sqlParam.value;
485
486 if (log.IsDebugEnabled)
487 log.Debug("Param[" + i + "] Name[" + key + "], Type[" +
488 (value == null ? "null" : value.GetType().ToString()) +
489 "], Value[" + value + "].");
490
491 OleDbParameter param = cmd.CreateParameter();
492 param.ParameterName = key;
493 param.OleDbType = GetDbType(value);
494 param.Value = value;
495 cmd.Parameters.Add(param);
496 }
497
498 int count = cmd.ExecuteNonQuery();
499 log.Debug("ExecuteParamSql effect row count: " + count);
500 return count;
501 }
502 }
503 catch (Exception e)
504 {
505 HandleOleError(e);
506 log.Error("ExecuteParamSql[" + sqlString + "] Error.", e);
507 if (!throwException)
508 return -1;
509 if (e is DbException)
510 throw e;
511 else
512 throw new DBAException("ExecuteParamSql Error.", e);
513 }
514 }
515
516 private int CheckParamSql (string sqlString, SqlParamList list)
517 {
518 if (! log.IsDebugEnabled)
519 return 0;
520
521 Regex r = new Regex(@"@(?<x>[0-9a-zA-Z]*)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
522 MatchCollection mc = r.Matches(sqlString);
523 int err = 0;
524 for (int i = 0; i < mc.Count; i++)
525 {
526 string paramName = mc[i].Result("$1");
527
528 if (i >= list.list.Count)
529 {
530 log.Error("SQL Param Mismatch, SqlString params [" + i + "] = " + paramName + ", SqlParamList.Count = " + list.list.Count + ".");
531 err ++;
532 continue;
533 }
534
535 if (paramName != list.list [i].name)
536 {
537 log.Error("SQL Param Mismatch, SqlString params [" + i + "] = " + paramName + ", SqlParamList [" + i + "] = " + list.list[i].name + ".");
538 err++;
539 continue;
540 }
541 }
542 return err;
543 }
544
545 private OleDbType GetDbType(object value)
546 {
547 if (value == null)
548 return OleDbType.Empty;
549
550 Type type = value.GetType();
551
552 if (type.IsEnum)
553 return OleDbType.Integer;
554
555 if (type == typeof (bool))
556 return OleDbType.TinyInt;
557
558 if (type == typeof (string))
559 return OleDbType.VarChar;
560
561 if (type == typeof (uint))
562 return OleDbType.UnsignedInt;
563
564 if (type == typeof(int))
565 return OleDbType.Integer;
566
567 if (type == typeof (byte []))
568 return OleDbType.VarBinary;
569
570 if (type == typeof (short))
571 return OleDbType.SmallInt;
572
573 if (type == typeof(ushort))
574 return OleDbType.UnsignedSmallInt;
575
576 if (type == typeof (byte))
577 return OleDbType.UnsignedTinyInt;
578
579 return OleDbType.IUnknown;
580 }
581
582 private void HandleOleError (Exception e)
583 {
584 if (! (e is OleDbException))
585 {
586 return;
587 }
588
589 OleDbException ex = (OleDbException) e;
590
591 string errorMessages = "OleDbException::" + ex.ErrorCode + "\n";
592
593 for (int i = 0; i < ex.Errors.Count; i++)
594 {
595 errorMessages += "\tIndex #" + i + "\n" +
596 "\tMessage: " + ex.Errors[i].Message + "\n" +
597 "\tNativeError: " + ex.Errors[i].NativeError + "\n" +
598 "\tSource: " + ex.Errors[i].Source + "\n" +
599 "\tSQLState: " + ex.Errors[i].SQLState + "\n";
600 }
601
602 log.Error(errorMessages);
603 }
604 }
605}
606
1using System;
2using System.Collections.Generic;
3using System.Data.Common;
4using System.Text;
5
6namespace AFC.BOM.Common.DB
7{
8 public class DBAException : DbException
9 {
10 public DBAException(string message, Exception innerException) : base(message, innerException)
11 {
12 }
13 }
14}
15
2using System.Collections.Generic;
3using System.Data.Common;
4using System.Text;
5
6namespace AFC.BOM.Common.DB
7{
8 public class DBAException : DbException
9 {
10 public DBAException(string message, Exception innerException) : base(message, innerException)
11 {
12 }
13 }
14}
15
在第二种中还有一些相关数据库操作,同时还有记录操作相关信息,是通过引用log4net来实现的,