DbHelper.cs
1
2 using System;
3 using System.Text;
4 using System.Reflection;
5 using System.Collections;
6 using System.Collections.Generic;
7 using System.Collections.Specialized;
8 using System.Data;
9 using System.Data.Common;
10 namespace His.Dal
11 {
12 public class DbHelper:IDisposable
13 {
14 #region 构造
15 private DbProviderFactory provider = null;
16 private DbConnection conn = null;
17
18 public DbHelper()
19 {
20 try
21 {
22 this.provider = DbProviderFactories.GetFactory(DbConfig.ConnStr.ProviderName);//工厂模式 获取数据库驱动
23 this.conn = provider.CreateConnection();
24 this.conn.ConnectionString = DbConfig.ConnStr.ConnectionString;
25 }
26 catch(Exception ex)
27 { new Exception(string.Format("创建数据库连接错误,{0}",ex.Message)); }
28 }
29
30 public void Dispose()
31 {
32 provider = null;
33 conn = null;
34 GC.Collect();
35 }
36
37 #endregion
38
39 #region ConvertParmeter
40
41 private DbParameter CreateParmeter(string name, object value,ParameterDirection direction)
42 {
43 DbParameter parm = provider.CreateParameter();
44 parm.ParameterName = string.Format("{0}{1}", DbConfig.strProfix, name);
45 parm.Direction = direction;
46 parm.Value = value != null ? value : string.Empty;
47 return parm;
48 }
49
50 private DbType GetDbType(object o)
51 {
52 if (o != null)
53 {
54 //判断实体字段类型名
55 switch (o.GetType().ReflectedType.FullName)
56 {
57 case "String"://类型为String,参数类型为String
58 return DbType.String;
59
60 case "Int32"://类型为Int32, 参数类型为Int32
61 return DbType.Int32;
62
63 case "Single": return DbType.Single;
64
65 case "Decimal": return DbType.Decimal;
66
67 case "Double": return DbType.Double;
68
69 case "DateTime": return DbType.DateTime;
70
71 case "Byte[]": return DbType.Binary;
72
73 case "StringBuilder":return DbType.String;
74 default:
75 if (o.GetType().ReflectedType.IsEnum)
76 {
77 return DbType.Int32;
78 }
79 return DbType.String;
80 }
81
82 }
83 return DbType.String;
84 }
85
86 /// <summary>
87 /// 获取输出参数的值（针对存储过程）
88 /// </summary>
89 /// <param name="cmd">执行存储过程的命令</param>
90 /// <returns>返回参数的值集合，Key:参数名，Value:参数值</returns>
91 private Dictionary<string, object> DiscoverOutParamterValues(DbCommand cmd)
92 {
93 //this.m_db.DiscoverParameters(cmd);//根据cmd从数据库中获取存储过程的参数列表
94 Dictionary<string, object> outValues = new Dictionary<string, object>();
95 foreach (DbParameter param in cmd.Parameters)
96 {
97
98 if (param.Direction == ParameterDirection.Output || param.Direction == ParameterDirection.InputOutput)
99 {
100 string key = param.ParameterName.TrimStart("@:".ToCharArray()).ToUpper();
101
102 if (param.Value != DBNull.Value)
103 outValues.Add(key, param.Value);//将参数的名和值作为键/值对放在集合中
104
105 else
106 outValues.Add(key, null);//将参数的名和值作为键/值对放在集合中
107 }
108 }
109 if (outValues.Count > 0)
110 {
111 return outValues;
112 }
113 return null;
114 }
115
116 /// <summary>
117 /// 用命令类型和命令文本创建一个DbCommand实例对象，并且可以给这个对象添加参数。
118 /// </summary>
119 /// <param name="commandType">命令类型</param>
120 /// <param name="commandText">命令文本</param>
121 /// <param name="commandParameters">参数</param>
122 /// <returns>返回DbCommand实例对象</returns>
123 private DbCommand CreateCurrentCommand(System.Data.CommandType commandType, string commandText, params object[] parameters)
124 {
125 if (string.IsNullOrEmpty(commandText))
126 throw new Exception("SQL语句错误");
127
128 DbCommand cmd = this.provider.CreateCommand();
129 cmd.Connection = conn;
130 cmd.CommandText = commandText;
131 try
132 {
133 if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open();
134 }
135 catch(Exception ex)
136 {
137 throw new Exception("数据库打开错误，请检查数据库连接配置！",ex);
138 }
139
140 //给DbCommand实例对象添加参数
141 if (parameters != null)
142 {
143 foreach (DbParameter param in parameters)
144 {
145 cmd.Parameters.Add(param);
146 }
147 }
148 return cmd;
149 }
150
151 private Dictionary<string, object> GetOutParmeters(IDataParameter[] parameterValues)
152 {
153 Dictionary<string, object> o = new Dictionary<string, object>();
154 foreach (DbParameter parmeter in parameterValues)
155 {
156 if (parmeter.Direction == ParameterDirection.Output || parmeter.Direction == ParameterDirection.ReturnValue)
157 o.Add(parmeter.ParameterName, parmeter.Value);
158 }
159 return o;
160 }
161
162 public IDataParameter[] SetParmeters(Dictionary<string, object> inputVales, Dictionary<string, object> outputVales, Dictionary<string, object> rtnVales)
163 {
164 ArrayList list = new ArrayList();
165
166 if (inputVales != null)
167 {
168 foreach (KeyValuePair<string, object> key in inputVales)
169 list.Add(CreateParmeter(key.Key, key.Value, ParameterDirection.Input));
170 }
171 if (outputVales != null)
172 {
173 foreach (KeyValuePair<string, object> key in inputVales)
174 list.Add(CreateParmeter(key.Key, key.Value, ParameterDirection.Input));
175 }
176 if (rtnVales != null)
177 {
178 foreach (KeyValuePair<string, object> key in inputVales)
179 list.Add(CreateParmeter(key.Key, key.Value, ParameterDirection.Input));
180 }
181 return (IDataParameter[])list.ToArray(typeof(IDataParameter));
182 }
183
184 /// <summary>
185 /// SQL关键字过滤
186 /// </summary>
187 /// <param name="strSql">原始字符串</param>
188 /// <returns>过滤后的字符串</returns>
189 public string FixSql(string strSql)
190 {
191 strSql = strSql.ToLower();
192 switch (DbConfig.DBaseType)
193 {
194 case DbConfig.DatabaseType.SqlServer:
195 {
196 strSql += " ";
197 strSql = strSql.Replace(" user ", " [user] ");
198
199 break;
200 }
201
202 default:
203 break;
204 }
205 return strSql;
206 }
207
208 #endregion
209
210 #region ExecuteNonQuery
211
212 /// <summary>
213 /// 根据执行方式、执行文本，返回执行成功的记录数量
214 /// </summary>
215 /// <param name="commandType">执行方式</param>
216 /// <param name="commandText">执行文本</param>
217 /// <returns>返回执行成功的记录数量</returns>
218 public int ExecuteNonQuery(CommandType commandType, string commandText)
219 {
220 return this.ExecuteNonQuery(commandType, commandText, null);
221 }
222
223 /// <summary>
224 /// 根据执行方式、执行文本，返回执行成功的记录数量（在事务中执行）
225 /// </summary>
226 /// <param name="commandType">执行方式</param>
227 /// <param name="commandTexts">执行文本数组</param>
228 /// <returns>返回执行成功的记录数量</returns>
229 public int ExecuteNonQuery(CommandType commandType, string[] commandTexts)
230 {
231 int count = 0;
232 if (conn.State == ConnectionState.Closed)
233 this.conn.Open();
234
235 DbTransaction tran = this.conn.BeginTransaction();//必须从打开的连接中创建事务
236 try
237 {
238 foreach (string text in commandTexts)
239 {
240 DbCommand cmd = this.CreateCurrentCommand(commandType, text, null);//
241 count += cmd.ExecuteNonQuery();//在Tran中，包含了当前的可用的Connection
242 }
243 tran.Commit();
244 }
245 catch (Exception ex)
246 {
247 tran.Rollback();
248 throw ex;
249 }
250 finally
251 {
252 if (this.conn != null) this.conn.Close();
253 }
254 return count;
255 }
256
257 /// <summary>
258 /// 根据执行方式、多条带参数的执行文本，返回执行成功的记录数量（在事务中执行）
259 /// </summary>
260 /// <param name="commandType">执行方式</param>
261 /// <param name="commandTexts">执行文本数组</param>
262 /// <param name="commandParameters">参数交错数组与执行文本数组对应</param>
263 /// <returns>返回执行成功的记录数量</returns>
264 public int ExecuteNonQuery(CommandType commandType, string[] commandTexts, params IDataParameter[] commandParameters)
265 {
266 int count = 0;
267 if (conn.State == ConnectionState.Closed)
268 this.conn.Open();
269
270 DbTransaction tran = this.conn.BeginTransaction();//必须从打开的连接中创建事务
271 string msg = "";
272 try
273 {
274 int index = 0;
275 foreach (string text in commandTexts)
276 {
277 msg = text;
278 DbCommand cmd = this.CreateCurrentCommand(commandType, text, commandParameters[index]);
279 count += cmd.ExecuteNonQuery();//在Tran中，包含了当前的可用的Connection
280 index++;
281 }
282 tran.Commit();
283 }
284 catch (Exception ex)
285 {
286 tran.Rollback();
287 throw new Exception(ex.Message + "\n" + msg, ex);
288 }
289 finally
290 {
291 if (this.conn != null)
292 this.conn.Close();
293 }
294 return count;
295
296 }
297
298 /// <summary>
299 /// 根据执行方式、一条带参数的执行文本，返回执行成功数
300 /// </summary>
301 /// <param name="commandType">执行方式</param>
302 /// <param name="commandText">文本：语句或存储过程</param>
303 /// <param name="commandParameters">参数</param>
304 /// <returns>返回执行成功数</returns>
305 public int ExecuteNonQuery(System.Data.CommandType commandType, string commandText, params IDataParameter[] commandParameters)
306 {
307 int res = 0;
308 try
309 {
310 if (conn.State == ConnectionState.Closed)
311 this.conn.Open();
312 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
313 res = cmd.ExecuteNonQuery();
314 cmd = null;
315 }
316 catch (Exception ex)
317 {
318 throw new Exception(ex.Message + "\n" + commandText, ex);
319 }
320 finally
321 {
322 if (conn != null) conn.Close();
323 }
324 return res;
325 }
326
327 /// <summary>
328 /// 执行带参数的存储过程，返回执行成功数
329 /// </summary>
330 /// <param name="spName">存储过程名字</param>
331 /// <param name="parameterValues">参数值</param>
332 /// <returns>返回执行成功数</returns>
333 public int ExecuteNonQuery(string spName, params IDataParameter[] parameterValues)
334 {
335 int res = 0;
336 try
337 {
338 if (conn.State == ConnectionState.Closed)
339 this.conn.Open();
340 using (DbCommand cmd = CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues))
341 {
342 res = cmd.ExecuteNonQuery();
343 conn.Close();
344 }
345 }
346 catch (Exception ex)
347 {
348 throw ex;
349 }
350 finally { if (conn != null) conn.Close(); }
351 return res;
352 }
353
354 /// <summary>
355 /// 执行有输出参数的存储过程，没有参数不会出错
356
357 /// </summary>
358 /// <param name="spName">存储过程名字</param>
359 /// <param name="parameterValues">输入参数值</param>
360 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
361 /// <returns>返回执行成功的数量</returns>
362 public int ExecuteNonQuery(string spName, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
363 {
364 outValues = null;
365 int count = 0;
366 try
367 {
368 using (DbCommand cmd = CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues))
369 {
370 count = cmd.ExecuteNonQuery();
371 outValues = this.DiscoverOutParamterValues(cmd);//从执行后的DbCommand中获取输出参数的值
372 }
373 }
374 catch (Exception ex)
375 {
376 throw ex;
377 }
378 finally { if (conn != null) conn.Close(); }
379 return count;
380 }
381
382 #endregion
383
384 #region ExecuteDataset
385 /// <summary>
386 /// 执行SQL语句，返回包含结果的数据集
387 /// </summary>
388 /// <param name="commandText">sql语句</param>
389 /// <param name="tableName">返回数据集名称（对应的表、视图名）</param>
390 /// <returns>返回包含结果的一个数据集</returns>
391 public DataSet ExecuteSql(string commandText, string tableName, params IDataParameter[] parmeters)
392 {
393 return this.ExecuteDataset(CommandType.Text, commandText, tableName, parmeters);
394 }
395
396 /// <summary>
397 /// 执行可带参数值的存储过程，返回结果集
398 /// </summary>
399 /// <param name="spName">存储过程名字</param>
400 /// <param name="tableNames">数据集名称(对应表、视图的名字)</param>
401 /// <param name="parameterValues">参数值</param>
402 /// <returns>返回包含结果集的数据集</returns>
403 public DataSet ExecuteStoredProcedure(string spName, string tableName, params IDataParameter[] parameterValues)
404 {
405 return this.ExecuteDataset(CommandType.StoredProcedure, spName, tableName, parameterValues);
406 }
407
408 /// <summary>
409 /// 执行带参数的 语句或存储过程，返回结果集
410
411 /// </summary>
412 /// <param name="commandType">执行方式:语句或存储过程</param>
413 /// <param name="commandText">带参数的语句或存储过程</param>
414 /// <param name="tableName">对应表、视图的名字</param>
415 /// <param name="commandParameters">参数数组</param>
416 /// <returns>返回包含结果集的数据集</returns>
417 public DataSet ExecuteDataset(CommandType commandType, string commandText, string tableName, params IDataParameter[] commandParameters)
418 {
419 DataSet ds = null;
420 try
421 {
422 ds = new DataSet();
423 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
424 using (DbDataAdapter adaper = provider.CreateDataAdapter())
425 {
426 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
427 adaper.SelectCommand = cmd;
428 adaper.Fill(ds, tableName);
429 }
430 }
431 catch (Exception ex)
432 {
433 throw ex;
434 }
435 finally { if (this.conn != null)conn.Close(); }
436 return ds;
437 }
438
439 /// <summary>
440 /// 执行有输出参数的存储过程，没有参数不会出错
441 /// </summary>
442 /// <param name="spName">存储过程名字</param>
443 /// <param name="tableNames">对应表、视图的名字</param>
444 /// <param name="parameterValues">输入参数值</param>
445 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
446 /// <returns>返回执行的结果集</returns>
447 public DataSet ExecuteDataset(string spName, string tableNames, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
448 {
449 outValues = null;
450 DataSet ds = null;
451 if (this.conn.State == ConnectionState.Closed) this.conn.Open();
452 DbTransaction tran = this.conn.BeginTransaction();//必须从打开的连接中创建事务
453 try
454 {
455 DbCommand cmd = this.CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues);
456 ds = new DataSet();
457 using (DbDataAdapter adaper = provider.CreateDataAdapter())
458 {
459 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
460 adaper.SelectCommand = cmd;
461 adaper.Fill(ds, tableNames);
462 }
463 tran.Commit();
464 outValues = GetOutParmeters(parameterValues);
465 }
466 catch (Exception ex)
467 {
468 tran.Rollback();
469 throw ex;
470 }
471 finally { if (this.conn != null)conn.Close(); }
472 return ds;
473 }
474
475 /// <summary>
476 /// 执行多条SQL语句或者存储过程，返回包含结果的数据集 (带 Parmeter 参数)
477 /// </summary>
478 /// <param name="list">SQL命令类型 SQL语句/存储过程 </param>
479 /// <param name="list">SQL语句和表名的集合</param>
480 public DataSet ExecuteDataset(CommandType commandType, List<DataCmd> list)
481 {
482 DataSet ds = null;
483 StringBuilder sbSql = new StringBuilder();
484 try
485 {
486 if (conn.State == ConnectionState.Closed)
487 this.conn.Open();
488 using (DbDataAdapter adaper = provider.CreateDataAdapter())
489 {
490 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
491 ds = new DataSet();
492 foreach (DataCmd cmd in list)
493 {
494 if (commandType == CommandType.StoredProcedure) cmd.SqlText.Replace(";","");
495 DbCommand command = this.CreateCurrentCommand(commandType, cmd.SqlText, cmd.Parmeters);
496 adaper.SelectCommand = command;
497 adaper.Fill(ds, cmd.TableName);
498 }
499 }
500 }
501 catch (Exception ex)
502 {
503 throw ex;
504 }
505 finally { if (this.conn != null)conn.Close(); }
506 return ds;
507 }
508
509 /// <summary>
510 /// 执行多条SQL语句，返回包含结果的数据集 (不带 Parmeter 参数)
511 /// </summary>
512 /// <param name="list">SQL语句和表名的集合</param>
513 public DataSet ExecuteDataset(List<DataCmd> list)
514 {
515 DataSet ds = null;
516 StringBuilder sbSql = new StringBuilder();
517 for (int i = 0; i < list.Count; i++)
518 sbSql.AppendFormat("{0};", list[i].SqlText);
519
520 if (conn.State == ConnectionState.Closed)
521 this.conn.Open();
522 try
523 {
524 using (DbDataAdapter adaper = provider.CreateDataAdapter())
525 {
526 DbCommand cmd = this.CreateCurrentCommand(CommandType.Text, sbSql.ToString(), null);
527 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
528 ds = new DataSet();
529 adaper.Fill(ds, "table");
530 for (int i = 0; i < ds.Tables.Count; i++)
531 ds.Tables[i].TableName = list[i].TableName;
532 }
533 }
534 catch (Exception ex)
535 {
536 throw ex;
537 }
538 finally { if (this.conn != null)conn.Close(); }
539 return ds;
540 }
541 #endregion
542
543 #region ExecuteReader
544 /// <summary>
545 /// 根据执行方式、执行文本，返回包含结果集的DataReader
546 /// </summary>
547 /// <param name="commandType">执行方式</param>
548 /// <param name="commandText">执行文本</param>
549 /// <returns>返回包含结果集的DataReader</returns>
550 public IDataReader ExecuteReader(CommandType commandType, string commandText)
551 {
552 return this.ExecuteReader(commandType, commandText, null);
553 }
554
555 /// <summary>
556 /// 执行带参数的 语句或存储过程，返回只读数据流
557
558 /// </summary>
559 /// <param name="commandType">执行方式:语句或存储过程</param>
560 /// <param name="commandText">带参数的语句或存储过程</param>
561 /// <param name="commandParameters">参数数组</param>
562 /// <returns>返回只读数据流</returns>
563 public IDataReader ExecuteReader(CommandType commandType, string commandText, params IDataParameter[] commandParameters)
564 {
565 DbDataReader reader = null;
566 try
567 {
568 if (conn.State == ConnectionState.Closed) this.conn.Open();
569 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
570 reader= cmd.ExecuteReader();
571 }
572 catch (Exception ex)
573 {
574 throw ex;
575 }
576 finally
577 { if (this.conn != null) conn.Close();}
578 return reader;
579 }
580
581 /// <summary>
582 /// 执行可带参数值的存储过程，返回只读数据流
583 /// </summary>
584 /// <param name="spName">存储过程名字</param>
585 /// <param name="parameterValues">参数值</param>
586 /// <returns>返回只读数据流</returns>
587 public IDataReader ExecuteReader(string spName, params IDataParameter[] parameterValues)
588 {
589 DbDataReader reader = null;
590 try
591 {
592 if (conn.State == ConnectionState.Closed) this.conn.Open();
593 DbCommand cmd = this.CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues);
594 reader= cmd.ExecuteReader();
595 }
596 catch (Exception ex)
597 {
598 throw ex;
599 }
600 finally
601 {
602 if (this.conn != null) conn.Close();
603 }
604 return reader;
605 }
606
607 /// <summary>
608 /// 执行有输出参数的存储过程，没有参数不会出错
609
610 /// </summary>
611 /// <param name="spName">存储过程名字</param>
612 /// <param name="parameterValues">输入参数值</param>
613 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
614 /// <returns>返回执行的结果集数据流</returns>
615 public IDataReader ExecuteReader(string spName, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
616 {
617 outValues = null;
618 DbDataReader reader = null;
619 try
620 {
621 if (conn.State == ConnectionState.Closed) this.conn.Open();
622 DbCommand cmd = this.CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues);
623 outValues = GetOutParmeters(parameterValues);
624 reader= cmd.ExecuteReader();
625 }
626 catch (Exception ex)
627 {
628 throw ex;
629 }
630 finally { if (this.conn != null) conn.Close();}
631 return reader;
632 }
633
634 #endregion
635
636 #region ExecuteScalar
637 /// <summary>
638 /// 根据执行方式、执行文本，返回结果集的第一行第一列
639 /// </summary>
640 /// <param name="commandType">执行方式</param>
641 /// <param name="commandText">执行文本</param>
642 /// <returns>返回结果集的第一行第一列</returns>
643 public object ExecuteScalar(CommandType commandType, string commandText)
644 {
645 return this.ExecuteScalar(commandType, commandText, null);
646 }
647
648 /// <summary>
649 /// 执行可带参数值的存储过程，返回数据结果集的第一行第一列
650 /// </summary>
651 /// <param name="spName">存储过程名字</param>
652 /// <param name="parameterValues">参数值</param>
653 /// <returns>返回数据结果集的第一行第一列</returns>
654 public object ExecuteScalar(string spName, params IDataParameter[] parameterValues)
655 {
656 return this.ExecuteScalar(CommandType.StoredProcedure, spName, parameterValues);
657 }
658
659 /// <summary>
660 /// 执行带参数的 语句或存储过程， 返回数据结果集的第一行第一列
661 /// </summary>
662 /// <param name="commandType">执行方式:语句或存储过程</param>
663 /// <param name="commandText">带参数的语句或存储过程</param>
664 /// <param name="commandParameters">参数数组</param>
665 /// <returns>返回数据结果集的第一行第一列</returns>
666 public object ExecuteScalar(CommandType commandType, string commandText, params IDataParameter[] commandParameters)
667 {
668 object o = null;
669 try
670 {
671 if (conn.State == ConnectionState.Closed) this.conn.Open();
672 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
673 o = cmd.ExecuteScalar();
674 }
675 catch (Exception ex)
676 {
677 throw ex;
678 }
679 finally { if (this.conn != null) conn.Close();}
680 return o;
681 }
682
683 /// <summary>
684 /// 执行有输出参数的存储过程，没有参数不会出错
685 /// </summary>
686 /// <param name="spName">存储过程名字</param>
687 /// <param name="parameterValues">输入参数值</param>
688 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
689 /// <returns>返回执行的结果集的第一行第一列</returns>
690 public object ExecuteScalar(string spName, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
691 {
692 outValues = null;
693 object o = null;
694 try
695 {
696 if (conn.State == ConnectionState.Closed) this.conn.Open();
697 o = ExecuteScalar(CommandType.StoredProcedure, spName, parameterValues);
698 outValues = this.GetOutParmeters(parameterValues);
699 }
700 catch (Exception ex)
701 {
702 throw ex;
703 }
704 finally { if (this.conn != null) conn.Close();}
705 return o;
706 }
707
708 #endregion
709
710 #region UpdateDataSet
711
712 /// <summary>
713 /// 用数据集更新数据库(事务中执行)
714 /// </summary>
715 /// <param name="dataSet">要更新的数据集</param>
716 /// <param name="tableName">要更新的数据表</param>
717 /// <param name="commands">用于更新的命令（Insert、Update、Delete命令都不能为空）</param>
718 /// <returns>返回成功的数量</returns>
719 public int UpdateDataSet(DataSet ds, string tableName, DbCommand command)
720 {
721 int count = 0;
722 if (this.conn.State == ConnectionState.Closed) conn.Open();
723 DbTransaction tran = this.conn.BeginTransaction();
724 try
725 {
726 DbDataAdapter adaper = provider.CreateDataAdapter();
727 command.Transaction = tran;
728 adaper.UpdateCommand = command;
729 adaper.Update(ds, tableName);
730 tran.Commit();
731 }
732 catch (Exception ex)
733 {
734 tran.Rollback();
735 throw ex;
736 }
737 finally
738 {
739 if (conn != null) conn.Close();
740 }
741 return count;
742 }
743
744 #endregion
745
746 }
747
748 /// <summary>
749 /// SQL命令
750 /// </summary>
751 public struct DataCmd
752 {
753 /// <summary>
754 /// SQL语句或者存储过程名
755 /// </summary>
756 public string SqlText;
757
758 /// <summary>
759 /// 要存储到目的数据集的表名
760 /// </summary>
761 public string TableName;
762
763 /// <summary>
764 /// SQL参数
765 /// </summary>
766 public DbParameter[] Parmeters;
767
768 /// <summary>
769 /// SQL命令
770 /// </summary>
771 /// <param name="cmd">SQL语句</param>
772 /// <param name="tablename">要存储到目的数据集的表名</param>
773 /// <param name="parmeters">SQL参数</param>
774 public DataCmd(string cmd,string tablename,DbParameter[] parmeters) {
775 this.SqlText = cmd;
776 this.TableName = tablename;
777 this.Parmeters = parmeters;
778 }
779 }
780 }
2 using System;
3 using System.Text;
4 using System.Reflection;
5 using System.Collections;
6 using System.Collections.Generic;
7 using System.Collections.Specialized;
8 using System.Data;
9 using System.Data.Common;
10 namespace His.Dal
11 {
12 public class DbHelper:IDisposable
13 {
14 #region 构造
15 private DbProviderFactory provider = null;
16 private DbConnection conn = null;
17
18 public DbHelper()
19 {
20 try
21 {
22 this.provider = DbProviderFactories.GetFactory(DbConfig.ConnStr.ProviderName);//工厂模式 获取数据库驱动
23 this.conn = provider.CreateConnection();
24 this.conn.ConnectionString = DbConfig.ConnStr.ConnectionString;
25 }
26 catch(Exception ex)
27 { new Exception(string.Format("创建数据库连接错误,{0}",ex.Message)); }
28 }
29
30 public void Dispose()
31 {
32 provider = null;
33 conn = null;
34 GC.Collect();
35 }
36
37 #endregion
38
39 #region ConvertParmeter
40
41 private DbParameter CreateParmeter(string name, object value,ParameterDirection direction)
42 {
43 DbParameter parm = provider.CreateParameter();
44 parm.ParameterName = string.Format("{0}{1}", DbConfig.strProfix, name);
45 parm.Direction = direction;
46 parm.Value = value != null ? value : string.Empty;
47 return parm;
48 }
49
50 private DbType GetDbType(object o)
51 {
52 if (o != null)
53 {
54 //判断实体字段类型名
55 switch (o.GetType().ReflectedType.FullName)
56 {
57 case "String"://类型为String,参数类型为String
58 return DbType.String;
59
60 case "Int32"://类型为Int32, 参数类型为Int32
61 return DbType.Int32;
62
63 case "Single": return DbType.Single;
64
65 case "Decimal": return DbType.Decimal;
66
67 case "Double": return DbType.Double;
68
69 case "DateTime": return DbType.DateTime;
70
71 case "Byte[]": return DbType.Binary;
72
73 case "StringBuilder":return DbType.String;
74 default:
75 if (o.GetType().ReflectedType.IsEnum)
76 {
77 return DbType.Int32;
78 }
79 return DbType.String;
80 }
81
82 }
83 return DbType.String;
84 }
85
86 /// <summary>
87 /// 获取输出参数的值（针对存储过程）
88 /// </summary>
89 /// <param name="cmd">执行存储过程的命令</param>
90 /// <returns>返回参数的值集合，Key:参数名，Value:参数值</returns>
91 private Dictionary<string, object> DiscoverOutParamterValues(DbCommand cmd)
92 {
93 //this.m_db.DiscoverParameters(cmd);//根据cmd从数据库中获取存储过程的参数列表
94 Dictionary<string, object> outValues = new Dictionary<string, object>();
95 foreach (DbParameter param in cmd.Parameters)
96 {
97
98 if (param.Direction == ParameterDirection.Output || param.Direction == ParameterDirection.InputOutput)
99 {
100 string key = param.ParameterName.TrimStart("@:".ToCharArray()).ToUpper();
101
102 if (param.Value != DBNull.Value)
103 outValues.Add(key, param.Value);//将参数的名和值作为键/值对放在集合中
104
105 else
106 outValues.Add(key, null);//将参数的名和值作为键/值对放在集合中
107 }
108 }
109 if (outValues.Count > 0)
110 {
111 return outValues;
112 }
113 return null;
114 }
115
116 /// <summary>
117 /// 用命令类型和命令文本创建一个DbCommand实例对象，并且可以给这个对象添加参数。
118 /// </summary>
119 /// <param name="commandType">命令类型</param>
120 /// <param name="commandText">命令文本</param>
121 /// <param name="commandParameters">参数</param>
122 /// <returns>返回DbCommand实例对象</returns>
123 private DbCommand CreateCurrentCommand(System.Data.CommandType commandType, string commandText, params object[] parameters)
124 {
125 if (string.IsNullOrEmpty(commandText))
126 throw new Exception("SQL语句错误");
127
128 DbCommand cmd = this.provider.CreateCommand();
129 cmd.Connection = conn;
130 cmd.CommandText = commandText;
131 try
132 {
133 if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open();
134 }
135 catch(Exception ex)
136 {
137 throw new Exception("数据库打开错误，请检查数据库连接配置！",ex);
138 }
139
140 //给DbCommand实例对象添加参数
141 if (parameters != null)
142 {
143 foreach (DbParameter param in parameters)
144 {
145 cmd.Parameters.Add(param);
146 }
147 }
148 return cmd;
149 }
150
151 private Dictionary<string, object> GetOutParmeters(IDataParameter[] parameterValues)
152 {
153 Dictionary<string, object> o = new Dictionary<string, object>();
154 foreach (DbParameter parmeter in parameterValues)
155 {
156 if (parmeter.Direction == ParameterDirection.Output || parmeter.Direction == ParameterDirection.ReturnValue)
157 o.Add(parmeter.ParameterName, parmeter.Value);
158 }
159 return o;
160 }
161
162 public IDataParameter[] SetParmeters(Dictionary<string, object> inputVales, Dictionary<string, object> outputVales, Dictionary<string, object> rtnVales)
163 {
164 ArrayList list = new ArrayList();
165
166 if (inputVales != null)
167 {
168 foreach (KeyValuePair<string, object> key in inputVales)
169 list.Add(CreateParmeter(key.Key, key.Value, ParameterDirection.Input));
170 }
171 if (outputVales != null)
172 {
173 foreach (KeyValuePair<string, object> key in inputVales)
174 list.Add(CreateParmeter(key.Key, key.Value, ParameterDirection.Input));
175 }
176 if (rtnVales != null)
177 {
178 foreach (KeyValuePair<string, object> key in inputVales)
179 list.Add(CreateParmeter(key.Key, key.Value, ParameterDirection.Input));
180 }
181 return (IDataParameter[])list.ToArray(typeof(IDataParameter));
182 }
183
184 /// <summary>
185 /// SQL关键字过滤
186 /// </summary>
187 /// <param name="strSql">原始字符串</param>
188 /// <returns>过滤后的字符串</returns>
189 public string FixSql(string strSql)
190 {
191 strSql = strSql.ToLower();
192 switch (DbConfig.DBaseType)
193 {
194 case DbConfig.DatabaseType.SqlServer:
195 {
196 strSql += " ";
197 strSql = strSql.Replace(" user ", " [user] ");
198
199 break;
200 }
201
202 default:
203 break;
204 }
205 return strSql;
206 }
207
208 #endregion
209
210 #region ExecuteNonQuery
211
212 /// <summary>
213 /// 根据执行方式、执行文本，返回执行成功的记录数量
214 /// </summary>
215 /// <param name="commandType">执行方式</param>
216 /// <param name="commandText">执行文本</param>
217 /// <returns>返回执行成功的记录数量</returns>
218 public int ExecuteNonQuery(CommandType commandType, string commandText)
219 {
220 return this.ExecuteNonQuery(commandType, commandText, null);
221 }
222
223 /// <summary>
224 /// 根据执行方式、执行文本，返回执行成功的记录数量（在事务中执行）
225 /// </summary>
226 /// <param name="commandType">执行方式</param>
227 /// <param name="commandTexts">执行文本数组</param>
228 /// <returns>返回执行成功的记录数量</returns>
229 public int ExecuteNonQuery(CommandType commandType, string[] commandTexts)
230 {
231 int count = 0;
232 if (conn.State == ConnectionState.Closed)
233 this.conn.Open();
234
235 DbTransaction tran = this.conn.BeginTransaction();//必须从打开的连接中创建事务
236 try
237 {
238 foreach (string text in commandTexts)
239 {
240 DbCommand cmd = this.CreateCurrentCommand(commandType, text, null);//
241 count += cmd.ExecuteNonQuery();//在Tran中，包含了当前的可用的Connection
242 }
243 tran.Commit();
244 }
245 catch (Exception ex)
246 {
247 tran.Rollback();
248 throw ex;
249 }
250 finally
251 {
252 if (this.conn != null) this.conn.Close();
253 }
254 return count;
255 }
256
257 /// <summary>
258 /// 根据执行方式、多条带参数的执行文本，返回执行成功的记录数量（在事务中执行）
259 /// </summary>
260 /// <param name="commandType">执行方式</param>
261 /// <param name="commandTexts">执行文本数组</param>
262 /// <param name="commandParameters">参数交错数组与执行文本数组对应</param>
263 /// <returns>返回执行成功的记录数量</returns>
264 public int ExecuteNonQuery(CommandType commandType, string[] commandTexts, params IDataParameter[] commandParameters)
265 {
266 int count = 0;
267 if (conn.State == ConnectionState.Closed)
268 this.conn.Open();
269
270 DbTransaction tran = this.conn.BeginTransaction();//必须从打开的连接中创建事务
271 string msg = "";
272 try
273 {
274 int index = 0;
275 foreach (string text in commandTexts)
276 {
277 msg = text;
278 DbCommand cmd = this.CreateCurrentCommand(commandType, text, commandParameters[index]);
279 count += cmd.ExecuteNonQuery();//在Tran中，包含了当前的可用的Connection
280 index++;
281 }
282 tran.Commit();
283 }
284 catch (Exception ex)
285 {
286 tran.Rollback();
287 throw new Exception(ex.Message + "\n" + msg, ex);
288 }
289 finally
290 {
291 if (this.conn != null)
292 this.conn.Close();
293 }
294 return count;
295
296 }
297
298 /// <summary>
299 /// 根据执行方式、一条带参数的执行文本，返回执行成功数
300 /// </summary>
301 /// <param name="commandType">执行方式</param>
302 /// <param name="commandText">文本：语句或存储过程</param>
303 /// <param name="commandParameters">参数</param>
304 /// <returns>返回执行成功数</returns>
305 public int ExecuteNonQuery(System.Data.CommandType commandType, string commandText, params IDataParameter[] commandParameters)
306 {
307 int res = 0;
308 try
309 {
310 if (conn.State == ConnectionState.Closed)
311 this.conn.Open();
312 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
313 res = cmd.ExecuteNonQuery();
314 cmd = null;
315 }
316 catch (Exception ex)
317 {
318 throw new Exception(ex.Message + "\n" + commandText, ex);
319 }
320 finally
321 {
322 if (conn != null) conn.Close();
323 }
324 return res;
325 }
326
327 /// <summary>
328 /// 执行带参数的存储过程，返回执行成功数
329 /// </summary>
330 /// <param name="spName">存储过程名字</param>
331 /// <param name="parameterValues">参数值</param>
332 /// <returns>返回执行成功数</returns>
333 public int ExecuteNonQuery(string spName, params IDataParameter[] parameterValues)
334 {
335 int res = 0;
336 try
337 {
338 if (conn.State == ConnectionState.Closed)
339 this.conn.Open();
340 using (DbCommand cmd = CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues))
341 {
342 res = cmd.ExecuteNonQuery();
343 conn.Close();
344 }
345 }
346 catch (Exception ex)
347 {
348 throw ex;
349 }
350 finally { if (conn != null) conn.Close(); }
351 return res;
352 }
353
354 /// <summary>
355 /// 执行有输出参数的存储过程，没有参数不会出错
356
357 /// </summary>
358 /// <param name="spName">存储过程名字</param>
359 /// <param name="parameterValues">输入参数值</param>
360 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
361 /// <returns>返回执行成功的数量</returns>
362 public int ExecuteNonQuery(string spName, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
363 {
364 outValues = null;
365 int count = 0;
366 try
367 {
368 using (DbCommand cmd = CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues))
369 {
370 count = cmd.ExecuteNonQuery();
371 outValues = this.DiscoverOutParamterValues(cmd);//从执行后的DbCommand中获取输出参数的值
372 }
373 }
374 catch (Exception ex)
375 {
376 throw ex;
377 }
378 finally { if (conn != null) conn.Close(); }
379 return count;
380 }
381
382 #endregion
383
384 #region ExecuteDataset
385 /// <summary>
386 /// 执行SQL语句，返回包含结果的数据集
387 /// </summary>
388 /// <param name="commandText">sql语句</param>
389 /// <param name="tableName">返回数据集名称（对应的表、视图名）</param>
390 /// <returns>返回包含结果的一个数据集</returns>
391 public DataSet ExecuteSql(string commandText, string tableName, params IDataParameter[] parmeters)
392 {
393 return this.ExecuteDataset(CommandType.Text, commandText, tableName, parmeters);
394 }
395
396 /// <summary>
397 /// 执行可带参数值的存储过程，返回结果集
398 /// </summary>
399 /// <param name="spName">存储过程名字</param>
400 /// <param name="tableNames">数据集名称(对应表、视图的名字)</param>
401 /// <param name="parameterValues">参数值</param>
402 /// <returns>返回包含结果集的数据集</returns>
403 public DataSet ExecuteStoredProcedure(string spName, string tableName, params IDataParameter[] parameterValues)
404 {
405 return this.ExecuteDataset(CommandType.StoredProcedure, spName, tableName, parameterValues);
406 }
407
408 /// <summary>
409 /// 执行带参数的 语句或存储过程，返回结果集
410
411 /// </summary>
412 /// <param name="commandType">执行方式:语句或存储过程</param>
413 /// <param name="commandText">带参数的语句或存储过程</param>
414 /// <param name="tableName">对应表、视图的名字</param>
415 /// <param name="commandParameters">参数数组</param>
416 /// <returns>返回包含结果集的数据集</returns>
417 public DataSet ExecuteDataset(CommandType commandType, string commandText, string tableName, params IDataParameter[] commandParameters)
418 {
419 DataSet ds = null;
420 try
421 {
422 ds = new DataSet();
423 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
424 using (DbDataAdapter adaper = provider.CreateDataAdapter())
425 {
426 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
427 adaper.SelectCommand = cmd;
428 adaper.Fill(ds, tableName);
429 }
430 }
431 catch (Exception ex)
432 {
433 throw ex;
434 }
435 finally { if (this.conn != null)conn.Close(); }
436 return ds;
437 }
438
439 /// <summary>
440 /// 执行有输出参数的存储过程，没有参数不会出错
441 /// </summary>
442 /// <param name="spName">存储过程名字</param>
443 /// <param name="tableNames">对应表、视图的名字</param>
444 /// <param name="parameterValues">输入参数值</param>
445 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
446 /// <returns>返回执行的结果集</returns>
447 public DataSet ExecuteDataset(string spName, string tableNames, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
448 {
449 outValues = null;
450 DataSet ds = null;
451 if (this.conn.State == ConnectionState.Closed) this.conn.Open();
452 DbTransaction tran = this.conn.BeginTransaction();//必须从打开的连接中创建事务
453 try
454 {
455 DbCommand cmd = this.CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues);
456 ds = new DataSet();
457 using (DbDataAdapter adaper = provider.CreateDataAdapter())
458 {
459 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
460 adaper.SelectCommand = cmd;
461 adaper.Fill(ds, tableNames);
462 }
463 tran.Commit();
464 outValues = GetOutParmeters(parameterValues);
465 }
466 catch (Exception ex)
467 {
468 tran.Rollback();
469 throw ex;
470 }
471 finally { if (this.conn != null)conn.Close(); }
472 return ds;
473 }
474
475 /// <summary>
476 /// 执行多条SQL语句或者存储过程，返回包含结果的数据集 (带 Parmeter 参数)
477 /// </summary>
478 /// <param name="list">SQL命令类型 SQL语句/存储过程 </param>
479 /// <param name="list">SQL语句和表名的集合</param>
480 public DataSet ExecuteDataset(CommandType commandType, List<DataCmd> list)
481 {
482 DataSet ds = null;
483 StringBuilder sbSql = new StringBuilder();
484 try
485 {
486 if (conn.State == ConnectionState.Closed)
487 this.conn.Open();
488 using (DbDataAdapter adaper = provider.CreateDataAdapter())
489 {
490 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
491 ds = new DataSet();
492 foreach (DataCmd cmd in list)
493 {
494 if (commandType == CommandType.StoredProcedure) cmd.SqlText.Replace(";","");
495 DbCommand command = this.CreateCurrentCommand(commandType, cmd.SqlText, cmd.Parmeters);
496 adaper.SelectCommand = command;
497 adaper.Fill(ds, cmd.TableName);
498 }
499 }
500 }
501 catch (Exception ex)
502 {
503 throw ex;
504 }
505 finally { if (this.conn != null)conn.Close(); }
506 return ds;
507 }
508
509 /// <summary>
510 /// 执行多条SQL语句，返回包含结果的数据集 (不带 Parmeter 参数)
511 /// </summary>
512 /// <param name="list">SQL语句和表名的集合</param>
513 public DataSet ExecuteDataset(List<DataCmd> list)
514 {
515 DataSet ds = null;
516 StringBuilder sbSql = new StringBuilder();
517 for (int i = 0; i < list.Count; i++)
518 sbSql.AppendFormat("{0};", list[i].SqlText);
519
520 if (conn.State == ConnectionState.Closed)
521 this.conn.Open();
522 try
523 {
524 using (DbDataAdapter adaper = provider.CreateDataAdapter())
525 {
526 DbCommand cmd = this.CreateCurrentCommand(CommandType.Text, sbSql.ToString(), null);
527 adaper.MissingSchemaAction = MissingSchemaAction.AddWithKey;
528 ds = new DataSet();
529 adaper.Fill(ds, "table");
530 for (int i = 0; i < ds.Tables.Count; i++)
531 ds.Tables[i].TableName = list[i].TableName;
532 }
533 }
534 catch (Exception ex)
535 {
536 throw ex;
537 }
538 finally { if (this.conn != null)conn.Close(); }
539 return ds;
540 }
541 #endregion
542
543 #region ExecuteReader
544 /// <summary>
545 /// 根据执行方式、执行文本，返回包含结果集的DataReader
546 /// </summary>
547 /// <param name="commandType">执行方式</param>
548 /// <param name="commandText">执行文本</param>
549 /// <returns>返回包含结果集的DataReader</returns>
550 public IDataReader ExecuteReader(CommandType commandType, string commandText)
551 {
552 return this.ExecuteReader(commandType, commandText, null);
553 }
554
555 /// <summary>
556 /// 执行带参数的 语句或存储过程，返回只读数据流
557
558 /// </summary>
559 /// <param name="commandType">执行方式:语句或存储过程</param>
560 /// <param name="commandText">带参数的语句或存储过程</param>
561 /// <param name="commandParameters">参数数组</param>
562 /// <returns>返回只读数据流</returns>
563 public IDataReader ExecuteReader(CommandType commandType, string commandText, params IDataParameter[] commandParameters)
564 {
565 DbDataReader reader = null;
566 try
567 {
568 if (conn.State == ConnectionState.Closed) this.conn.Open();
569 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
570 reader= cmd.ExecuteReader();
571 }
572 catch (Exception ex)
573 {
574 throw ex;
575 }
576 finally
577 { if (this.conn != null) conn.Close();}
578 return reader;
579 }
580
581 /// <summary>
582 /// 执行可带参数值的存储过程，返回只读数据流
583 /// </summary>
584 /// <param name="spName">存储过程名字</param>
585 /// <param name="parameterValues">参数值</param>
586 /// <returns>返回只读数据流</returns>
587 public IDataReader ExecuteReader(string spName, params IDataParameter[] parameterValues)
588 {
589 DbDataReader reader = null;
590 try
591 {
592 if (conn.State == ConnectionState.Closed) this.conn.Open();
593 DbCommand cmd = this.CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues);
594 reader= cmd.ExecuteReader();
595 }
596 catch (Exception ex)
597 {
598 throw ex;
599 }
600 finally
601 {
602 if (this.conn != null) conn.Close();
603 }
604 return reader;
605 }
606
607 /// <summary>
608 /// 执行有输出参数的存储过程，没有参数不会出错
609
610 /// </summary>
611 /// <param name="spName">存储过程名字</param>
612 /// <param name="parameterValues">输入参数值</param>
613 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
614 /// <returns>返回执行的结果集数据流</returns>
615 public IDataReader ExecuteReader(string spName, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
616 {
617 outValues = null;
618 DbDataReader reader = null;
619 try
620 {
621 if (conn.State == ConnectionState.Closed) this.conn.Open();
622 DbCommand cmd = this.CreateCurrentCommand(CommandType.StoredProcedure, spName, parameterValues);
623 outValues = GetOutParmeters(parameterValues);
624 reader= cmd.ExecuteReader();
625 }
626 catch (Exception ex)
627 {
628 throw ex;
629 }
630 finally { if (this.conn != null) conn.Close();}
631 return reader;
632 }
633
634 #endregion
635
636 #region ExecuteScalar
637 /// <summary>
638 /// 根据执行方式、执行文本，返回结果集的第一行第一列
639 /// </summary>
640 /// <param name="commandType">执行方式</param>
641 /// <param name="commandText">执行文本</param>
642 /// <returns>返回结果集的第一行第一列</returns>
643 public object ExecuteScalar(CommandType commandType, string commandText)
644 {
645 return this.ExecuteScalar(commandType, commandText, null);
646 }
647
648 /// <summary>
649 /// 执行可带参数值的存储过程，返回数据结果集的第一行第一列
650 /// </summary>
651 /// <param name="spName">存储过程名字</param>
652 /// <param name="parameterValues">参数值</param>
653 /// <returns>返回数据结果集的第一行第一列</returns>
654 public object ExecuteScalar(string spName, params IDataParameter[] parameterValues)
655 {
656 return this.ExecuteScalar(CommandType.StoredProcedure, spName, parameterValues);
657 }
658
659 /// <summary>
660 /// 执行带参数的 语句或存储过程， 返回数据结果集的第一行第一列
661 /// </summary>
662 /// <param name="commandType">执行方式:语句或存储过程</param>
663 /// <param name="commandText">带参数的语句或存储过程</param>
664 /// <param name="commandParameters">参数数组</param>
665 /// <returns>返回数据结果集的第一行第一列</returns>
666 public object ExecuteScalar(CommandType commandType, string commandText, params IDataParameter[] commandParameters)
667 {
668 object o = null;
669 try
670 {
671 if (conn.State == ConnectionState.Closed) this.conn.Open();
672 DbCommand cmd = this.CreateCurrentCommand(commandType, commandText, commandParameters);
673 o = cmd.ExecuteScalar();
674 }
675 catch (Exception ex)
676 {
677 throw ex;
678 }
679 finally { if (this.conn != null) conn.Close();}
680 return o;
681 }
682
683 /// <summary>
684 /// 执行有输出参数的存储过程，没有参数不会出错
685 /// </summary>
686 /// <param name="spName">存储过程名字</param>
687 /// <param name="parameterValues">输入参数值</param>
688 /// <param name="outValues">字典数组，字典的键值队中的键为参数名、值为参数值</param>
689 /// <returns>返回执行的结果集的第一行第一列</returns>
690 public object ExecuteScalar(string spName, IDataParameter[] parameterValues, out Dictionary<string, object> outValues)
691 {
692 outValues = null;
693 object o = null;
694 try
695 {
696 if (conn.State == ConnectionState.Closed) this.conn.Open();
697 o = ExecuteScalar(CommandType.StoredProcedure, spName, parameterValues);
698 outValues = this.GetOutParmeters(parameterValues);
699 }
700 catch (Exception ex)
701 {
702 throw ex;
703 }
704 finally { if (this.conn != null) conn.Close();}
705 return o;
706 }
707
708 #endregion
709
710 #region UpdateDataSet
711
712 /// <summary>
713 /// 用数据集更新数据库(事务中执行)
714 /// </summary>
715 /// <param name="dataSet">要更新的数据集</param>
716 /// <param name="tableName">要更新的数据表</param>
717 /// <param name="commands">用于更新的命令（Insert、Update、Delete命令都不能为空）</param>
718 /// <returns>返回成功的数量</returns>
719 public int UpdateDataSet(DataSet ds, string tableName, DbCommand command)
720 {
721 int count = 0;
722 if (this.conn.State == ConnectionState.Closed) conn.Open();
723 DbTransaction tran = this.conn.BeginTransaction();
724 try
725 {
726 DbDataAdapter adaper = provider.CreateDataAdapter();
727 command.Transaction = tran;
728 adaper.UpdateCommand = command;
729 adaper.Update(ds, tableName);
730 tran.Commit();
731 }
732 catch (Exception ex)
733 {
734 tran.Rollback();
735 throw ex;
736 }
737 finally
738 {
739 if (conn != null) conn.Close();
740 }
741 return count;
742 }
743
744 #endregion
745
746 }
747
748 /// <summary>
749 /// SQL命令
750 /// </summary>
751 public struct DataCmd
752 {
753 /// <summary>
754 /// SQL语句或者存储过程名
755 /// </summary>
756 public string SqlText;
757
758 /// <summary>
759 /// 要存储到目的数据集的表名
760 /// </summary>
761 public string TableName;
762
763 /// <summary>
764 /// SQL参数
765 /// </summary>
766 public DbParameter[] Parmeters;
767
768 /// <summary>
769 /// SQL命令
770 /// </summary>
771 /// <param name="cmd">SQL语句</param>
772 /// <param name="tablename">要存储到目的数据集的表名</param>
773 /// <param name="parmeters">SQL参数</param>
774 public DataCmd(string cmd,string tablename,DbParameter[] parmeters) {
775 this.SqlText = cmd;
776 this.TableName = tablename;
777 this.Parmeters = parmeters;
778 }
779 }
780 }
DbConfig.cs 1
2 using System;
3 using System.Configuration;
4 using System.Collections.Generic;
5 using System.Text;
6
7 namespace His.Dal
8 {
9 class DbConfig
10 {
11 const string _DEFAULT_DATABASE = "Default";
12
13 public DbConfig() { }
14
15 /// <summary>
16 /// 数据库类型选项
17 /// </summary>
18 public enum DatabaseType { None = 0, SqlServer = 1, Oracle = 2, MySql = 3, OleDb = 4, ODBC = 5, DB2 = 6 }
19
20 /// <summary>
21 /// 数据库连接
22 /// </summary>
23 public static ConnectionStringSettings ConnStr
24 {
25 get
26 {
27 string _conn = string.Empty;
28 try
29 {
30 string _defalt = ConfigurationManager.AppSettings[_DEFAULT_DATABASE].ToString();
31 return ConfigurationManager.ConnectionStrings[_defalt];
32 }
33 catch { throw new Exception("读取数据库配置文件错误！"); }
34 }
35 }
36
37 /// <summary>
38 /// 数据库类型
39 /// </summary>
40 public static DatabaseType DBaseType
41 {
42 get
43 {
44 string strProvider = ConnStr.ProviderName.ToUpper();
45 if (strProvider.Contains("SQL"))
46 return DatabaseType.SqlServer;
47
48 if (strProvider.Contains("OLEDB"))
49 return DatabaseType.OleDb;
50
51 if (strProvider.Contains("ORACLE"))
52 return DatabaseType.Oracle;
53
54 if (strProvider.Contains("MYSQL"))
55 return DatabaseType.MySql;
56
57 throw new Exception(string.Format("无法识别数据库驱动：{0}", ConnStr.ProviderName));
58 }
59
60 }
61
62 /// <summary>
63 /// 参数连接符
64 /// </summary>
65 public static string strProfix
66 {
67 get
68 {
69 switch (DBaseType)
70 {
71 case DatabaseType.SqlServer:
72 return "@";
73 case DatabaseType.Oracle:
74 return ":";
75 case DatabaseType.MySql:
76 return "@";
77 case DatabaseType.OleDb:
78 return "#";
79 case DatabaseType.DB2:
80 return "#";
81 default:
82 return "@";
83 }
84 }
85 }
86
87 }
88 }
2 using System;
3 using System.Configuration;
4 using System.Collections.Generic;
5 using System.Text;
6
7 namespace His.Dal
8 {
9 class DbConfig
10 {
11 const string _DEFAULT_DATABASE = "Default";
12
13 public DbConfig() { }
14
15 /// <summary>
16 /// 数据库类型选项
17 /// </summary>
18 public enum DatabaseType { None = 0, SqlServer = 1, Oracle = 2, MySql = 3, OleDb = 4, ODBC = 5, DB2 = 6 }
19
20 /// <summary>
21 /// 数据库连接
22 /// </summary>
23 public static ConnectionStringSettings ConnStr
24 {
25 get
26 {
27 string _conn = string.Empty;
28 try
29 {
30 string _defalt = ConfigurationManager.AppSettings[_DEFAULT_DATABASE].ToString();
31 return ConfigurationManager.ConnectionStrings[_defalt];
32 }
33 catch { throw new Exception("读取数据库配置文件错误！"); }
34 }
35 }
36
37 /// <summary>
38 /// 数据库类型
39 /// </summary>
40 public static DatabaseType DBaseType
41 {
42 get
43 {
44 string strProvider = ConnStr.ProviderName.ToUpper();
45 if (strProvider.Contains("SQL"))
46 return DatabaseType.SqlServer;
47
48 if (strProvider.Contains("OLEDB"))
49 return DatabaseType.OleDb;
50
51 if (strProvider.Contains("ORACLE"))
52 return DatabaseType.Oracle;
53
54 if (strProvider.Contains("MYSQL"))
55 return DatabaseType.MySql;
56
57 throw new Exception(string.Format("无法识别数据库驱动：{0}", ConnStr.ProviderName));
58 }
59
60 }
61
62 /// <summary>
63 /// 参数连接符
64 /// </summary>
65 public static string strProfix
66 {
67 get
68 {
69 switch (DBaseType)
70 {
71 case DatabaseType.SqlServer:
72 return "@";
73 case DatabaseType.Oracle:
74 return ":";
75 case DatabaseType.MySql:
76 return "@";
77 case DatabaseType.OleDb:
78 return "#";
79 case DatabaseType.DB2:
80 return "#";
81 default:
82 return "@";
83 }
84 }
85 }
86
87 }
88 }
小弟水平尚浅，欢迎各位前辈拍砖，拍死有奖
