1 public abstract class OracleHelper
2 {
3 public OracleHelper() { }
4
5 /// <summary>
6 /// 数据库连接字符串
7 /// </summary>
8 protected static string connectionString = ConfigurationManager.AppSettings["Oracle_BPMS"];
9
64 #region 执行简单SQL语句
65
66 /// <summary>
67 /// 执行SQL语句，返回影响的记录数
68 /// </summary>
69 /// <param name="SQLString">SQL语句</param>
70 /// <returns>影响的记录数</returns>
71 public static int ExecuteSql(string SQLString)
72 {
73 using (OracleConnection connection = new OracleConnection(connectionString))
74 {
75 using (OracleCommand cmd = new OracleCommand(SQLString, connection))
76 {
77 try
78 {
79 connection.Open();
80 int rows = cmd.ExecuteNonQuery();
81 return rows;
82 }
83 catch (System.Data.SqlClient.SqlException e)
84 {
85 connection.Close();
86 throw e;
87 }
88 }
89 }
90 }
91
92 /// <summary>
93 /// 2012-2-21新增重载，执行SQL语句，返回影响的记录数
94 /// </summary>
95 /// <param name="connection">SqlConnection对象</param>
96 /// <param name="trans">SqlTransaction事件</param>
97 /// <param name="SQLString">SQL语句</param>
98 /// <returns>影响的记录数</returns>
99 public static int ExecuteSql(OracleConnection connection, OracleTransaction trans, string SQLString)
100 {
101 using (OracleCommand cmd = new OracleCommand(SQLString, connection))
102 {
103 try
104 {
105 cmd.Connection = connection;
106 cmd.Transaction = trans;
107 int rows = cmd.ExecuteNonQuery();
108 return rows;
109 }
110 catch (System.Data.SqlClient.SqlException e)
111 {
112 trans.Rollback();
113 throw e;
114 }
115 }
116 }
117
118 public static int ExecuteSqlByTime(string SQLString, int Times)
119 {
120 using (OracleConnection connection = new OracleConnection(connectionString))
121 {
122 using (OracleCommand cmd = new OracleCommand(SQLString, connection))
123 {
124 try
125 {
126 connection.Open();
127 cmd.CommandTimeout = Times;
128 int rows = cmd.ExecuteNonQuery();
129 return rows;
130 }
131 catch (System.Data.SqlClient.SqlException e)
132 {
133 connection.Close();
134 throw e;
135 }
136 }
137 }
138 }
139
140
141
142 /// <summary>
143 /// 执行查询语句，返回DataSet
144 /// </summary>
145 /// <param name="SQLString">查询语句</param>
146 /// <returns>DataSet</returns>
147 public static DataSet Query(string SQLString)
148 {
149
150 using (OracleConnection connection = new OracleConnection(connectionString))
151 {
152 DataSet ds = new DataSet();
153 try
154 {
155 connection.Open();
156 OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
157 command.Fill(ds, "ds");
158 }
159 catch (System.Data.SqlClient.SqlException ex)
160 {
161 throw new Exception(ex.Message);
162 }
163 return ds;
164 }
165
166 }
167
168
169 /// <summary>
170 /// 2012-2-21新增重载，执行查询语句，返回DataSet
171 /// </summary>
172 /// <param name="connection">SqlConnection对象</param>
173 /// <param name="trans">SqlTransaction事务</param>
174 /// <param name="SQLString">SQL语句</param>
175 /// <returns>DataSet</returns>
176 public static DataSet Query(OracleConnection connection, OracleTransaction trans, string SQLString)
177 {
178 DataSet ds = new DataSet();
179 try
180 {
181 OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
182 command.SelectCommand.Transaction = trans;
183 command.Fill(ds, "ds");
184 }
185 catch (System.Data.SqlClient.SqlException ex)
186 {
187 throw new Exception(ex.Message);
188 }
189 return ds;
190
191 }
192
193
194 #endregion
195
196 #region 其他方法
197 /// <summary>
198 /// 执行命令,返回受影响的行数
199 /// </summary>
200 /// <param name="tran">事务类 </param>
201 /// <param name="cmdText">操作字符串</param>
202 /// <param name="parms">sql语句需要的参数</param>
203 /// <param name="cmdtype">执行类型,是存储过程还是普通sql</param>
204 /// <returns>返回受影响的行数</returns>
205 public static int ExecuteNonQuery(OracleTransaction tran, string cmdText, OracleParameter[] parms, CommandType cmdtype)
206 {
207 int retVal = 0;
208 OracleCommand cmd = new OracleCommand(cmdText);
209 cmd.Connection = tran.Connection;
210 cmd.Transaction = tran;
211 cmd.CommandType = cmdtype;
212 if (parms != null)
213 {
214 cmd.Parameters.AddRange(parms);
215 }
216 retVal = cmd.ExecuteNonQuery();
217 cmd.Parameters.Clear();
218 return retVal;
219 }
220
221 /// <summary>
222 /// 执行命令,返回受影响的行数
223 /// </summary>
224 /// <param name="cmdText">查询的文本</param>
225 /// <param name="parms">需要的参数</param>
226 /// <param name="cmdtype">如何解释命令字符串</param>
227 /// <returns>返回受影响的行数</returns>
228 public static int ExecuteNonQuery(string cmdText, OracleParameter[] parms, CommandType cmdtype)
229 {
230 int retVal;
231 using (OracleConnection conn = new OracleConnection(connectionString))
232 {
233 OracleCommand cmd = new OracleCommand(cmdText, conn);
234 cmd.CommandType = cmdtype;
235
236 if (parms != null)
237 {
238 //添加参数
239 cmd.Parameters.AddRange(parms);
240 }
241 conn.Open();
242 retVal = cmd.ExecuteNonQuery();
243 conn.Close();
244 }
245
246 return retVal;
247 }
248
249
250 /// <summary>
251 /// 执行命令, 返回受影响的行数
252 /// </summary>
253 /// <param name="cmdText">查询的文本</param>
254 /// <param name="cmdtype"></param>
255 /// <returns>返回受影响的行数</returns>
256 public static int ExecuteNonQuery(string cmdText, CommandType cmdtype)
257 {
258 int retVal;
259
260 using (OracleConnection conn = new OracleConnection(connectionString))
261 {
262 OracleCommand cmd = new OracleCommand(cmdText, conn);
263 cmd.CommandType = cmdtype;
264
265 conn.Open();
266
267 retVal = cmd.ExecuteNonQuery();
268 conn.Close();
269 }
270
271
272 return retVal;
273 }
274
275 /// <summary>
276 /// 执行命令, 返回受影响的行数
277 /// </summary>
278 /// <param name="cmdText">查询的文本</param>
279 /// <returns>返回受影响的行数</returns>
280 public static int ExecuteNonQuery(string cmdText)
281 {
282 int retVal;
283 using (OracleConnection conn = new OracleConnection(connectionString))
284 {
285 OracleCommand cmd = new OracleCommand(cmdText, conn);
286 cmd.CommandType = CommandType.StoredProcedure;
287
288 conn.Open();
289 retVal = cmd.ExecuteNonQuery();
290
291 conn.Close();
292 }
293 return retVal;
294 }
295
296
297 /// <summary>
298 /// 执行命令,返回第一行第一列
299 /// </summary>
300 /// <param name="cmdText">查询的文本</param>
301 /// <param name="parms">需要的参数</param>
302 /// <param name="cmdtype">如何解释命令字符串</param>
303 /// <returns>返回第一行第一列,不存在返回Null</returns>
304 public static object ExecuteScalar(string cmdText, OracleParameter[] parms, CommandType cmdtype)
305 {
306 object retVal;
307
308 using (OracleConnection conn = new OracleConnection(connectionString))
309 {
310 OracleCommand cmd = new OracleCommand(cmdText, conn);
311 cmd.CommandType = cmdtype;
312
313 if (parms != null)
314 {
315 //添加参数
316 cmd.Parameters.AddRange(parms);
317 }
318
319 conn.Open();
320 retVal = cmd.ExecuteScalar();
321 conn.Close();
322 }
323 return retVal == DBNull.Value ? null : retVal;
324 }
325
326
327
328 /// <summary>
329 /// 执行命令,返回第一行第一列
330 /// </summary>
331 /// <param name="cmdText">查询的文本</param>
332 /// <param name="parms">需要的参数</param>
333 /// <param name="cmdtype">如何解释命令字符串</param>
334 /// <returns>返回第一行第一列,不存在返回Null</returns>
335 public static object ExecuteScalar(OracleTransaction tran, string cmdText, OracleParameter[] parms, CommandType cmdtype)
336 {
337
338 object retVal;
339
340 OracleCommand cmd = new OracleCommand(cmdText);
341 cmd.Connection = tran.Connection;
342 cmd.Transaction = tran;
343 cmd.CommandType = cmdtype;
344 if (parms != null)
345 {
346 //添加参数
347 cmd.Parameters.AddRange(parms);
348 }
349
350 retVal = cmd.ExecuteScalar();
351
352 return retVal == DBNull.Value ? null : retVal;
353 }
354
355
356
357
358 /// <summary>
359 /// 执行命令,返回第一行第一列
360 /// </summary>
361 /// <param name="cmdText">查询的文本</param>
362 /// <param name="cmdtype">如何解释命令字符串</param>
363 /// <returns>返回第一行第一列,不存在返回Null</returns>
364 public static object ExecuteScalar(string cmdText, CommandType cmdtype)
365 {
366 object retVal;
367
368 using (OracleConnection conn = new OracleConnection(connectionString))
369 {
370 OracleCommand cmd = new OracleCommand(cmdText, conn);
371 cmd.CommandType = cmdtype;
372
373 conn.Open();
374 retVal = cmd.ExecuteScalar();
375
376 conn.Close();
377 }
378 return retVal == DBNull.Value ? null : retVal;
379 }
380
381 /// <summary>
382 /// 执行命令,返回第一行第一列
383 /// </summary>
384 /// <param name="cmdText">查询的文本</param>
385 /// <returns>返回第一行第一列,不存在返回Null</returns>
386 public static object ExecuteScalar(string cmdText)
387 {
388 object retVal;
389
390 using (OracleConnection conn = new OracleConnection(connectionString))
391 {
392 OracleCommand cmd = new OracleCommand(cmdText, conn);
393 cmd.CommandType = CommandType.StoredProcedure;
394
395 conn.Open();
396 retVal = cmd.ExecuteScalar();
397 conn.Close();
398 }
399
400 return retVal == DBNull.Value ? null : retVal;
401 }
402
403 /// <summary>
404 /// 执行命令,返回一个数据读取器,注意使用完毕后关闭读取器
405 /// </summary>
406 /// <param name="cmdText">查询的文本</param>
407 /// <param name="parms">需要的参数</param>
408 /// <param name="cmdtype">如何解释命令字符串</param>
409 /// <returns>返回一个数据读取器</returns>
410 public static OracleDataReader ExecuteReader(string cmdText, OracleParameter[] parms, CommandType cmdtype)
411 {
412 OracleDataReader reader;
413
414 OracleConnection conn = new OracleConnection(connectionString);
415
416 OracleCommand cmd = new OracleCommand(cmdText, conn);
417 cmd.CommandType = cmdtype;
418
419 if (parms != null)
420 {
421 //添加参数
422 cmd.Parameters.AddRange(parms);
423 }
424
425 conn.Open();
426 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
427 return reader;
428 }
429
430 /// <summary>
431 /// 执行命令,返回一个数据读取器,注意使用完毕后关闭读取器
432 /// </summary>
433 /// <param name="cmdText">查询的文本</param>
434 /// <param name="cmdtype">如何解释命令字符串</param>
435 /// <returns>返回一个数据读取器</returns>
436 public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdtype)
437 {
438 OracleDataReader reader;
439
440 OracleConnection conn = new OracleConnection(connectionString);
441 OracleCommand cmd = new OracleCommand(cmdText, conn);
442 cmd.CommandType = cmdtype;
443
444 conn.Open();
445 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
446 return reader;
447 }
448 /// <summary>
449 /// 执行命令,返回DataTable
450 /// </summary>
451 /// <param name="cmdText">查询的文本</param>
452 /// <param name="parms">需要的参数</param>
453 /// <param name="cmdtype">如何解释命令字符串</param>
454 /// <returns>返回DataTable</returns>
455 public static DataTable ExecuteDataTable(string cmdText, OracleParameter[] parms, CommandType cmdtype)
456 {
457 DataTable dt = new DataTable();
458
459 using (OracleConnection conn = new OracleConnection(connectionString))
460 {
461 OracleDataAdapter apt = new OracleDataAdapter(cmdText, conn);
462 apt.SelectCommand.CommandType = cmdtype;
463
464 if (parms != null)
465 {
466 apt.SelectCommand.Parameters.AddRange(parms);
467 }
468
469 apt.Fill(dt);
470 conn.Close();
471 }
472 return dt;
473 }
474
475 /// <summary>
476 /// 执行命令,返回DataSet
477 /// </summary>
478 /// <param name="cmdText">查询的文本</param>
479 /// <param name="parms">需要的参数</param>
480 /// <param name="cmdtype">如何解释命令字符串</param>
481 /// <returns>返回DataSet</returns>
482 public static DataSet ExecuteDataSet(string cmdText, OracleParameter[] parms, CommandType cmdtype)
483 {
484 DataSet ds = new DataSet();
485
486 using (OracleConnection conn = new OracleConnection(connectionString))
487 {
488 OracleDataAdapter apt = new OracleDataAdapter(cmdText, conn);
489 apt.SelectCommand.CommandType = cmdtype;
490
491 if (parms != null)
492 {
493 apt.SelectCommand.Parameters.AddRange(parms);
494 }
495
496 apt.Fill(ds);
497 conn.Close();
498 }
499 return ds;
500 }
501
502
503 /// <summary>
504 /// 执行命令,返回DataTable
505 /// </summary>
506 /// <param name="cmdText">查询的文本</param>
507 /// <param name="cmdtype">如何解释命令字符串</param>
508 /// <returns>返回DataTable</returns>
509 public static DataTable ExecuteDataTable(string cmdText, CommandType cmdtype)
510 {
511 DataTable dt = new DataTable();
512
513 using (OracleConnection conn = new OracleConnection(connectionString))
514 {
515 OracleDataAdapter apt = new OracleDataAdapter(cmdText, conn);
516 apt.SelectCommand.CommandType = cmdtype;
517 apt.Fill(dt);
518 conn.Close();
519 }
520 return dt;
521 }
522
523 /// <summary>
524 /// 执行命令,返回DataTable
525 /// </summary>
526 /// <param name="cmdText">查询的文本</param>
527 /// <returns>返回DataTable</returns>
528 public static DataTable ExecuteDataTable(string cmdText)
529 {
530 DataTable dt = new DataTable();
531
532 using (OracleConnection conn = new OracleConnection(connectionString))
533 {
534 OracleDataAdapter apt = new OracleDataAdapter(cmdText, conn);
535 apt.SelectCommand.CommandType = CommandType.StoredProcedure;
536 apt.Fill(dt);
537 conn.Close();
538 }
539 return dt;
540 }
541
542 /// <summary>
543 /// 执行命令,返回第一行,不存在返回Null
544 /// </summary>
545 /// <param name="cmdText">查询的文本</param>
546 /// <param name="parms">需要的参数</param>
547 /// <param name="cmdtype">如何解释命令字符串</param>
548 /// <returns>返回第一行,不存在返回Null</returns>
549 public static DataRow ExecuteFirstRow(string cmdText, OracleParameter[] parms, CommandType cmdtype)
550 {
551 DataRow row = null;
552 using (OracleConnection conn = new OracleConnection(connectionString))
553 {
554 DataTable dt = new DataTable();
555 OracleDataAdapter apt = new OracleDataAdapter(cmdText, conn);
556 apt.SelectCommand.CommandType = cmdtype;
557
558 if (parms != null)
559 {
560 apt.SelectCommand.Parameters.AddRange(parms);
561 }
562 apt.Fill(dt);
563 if (dt.Rows.Count > 0)
564 {
565 row = dt.Rows[0];
566 }
567 conn.Close();
568 }
569 return row;
570 }
571
572 /// <summary>
573 /// 执行命令,返回第一行,不存在返回Null
574 /// </summary>
575 /// <param name="cmdText">查询的文本</param>
576 /// <param name="cmdtype">如何解释命令字符串</param>
577 /// <returns>返回第一行,不存在返回Null</returns>
578 public static DataRow ExecuteFirstRow(string cmdText, CommandType cmdtype)
579 {
580 DataRow row = null;
581 using (OracleConnection conn = new OracleConnection(connectionString))
582 {
583 DataTable dt = new DataTable();
584 OracleDataAdapter apt = new OracleDataAdapter(cmdText, conn);
585 apt.SelectCommand.CommandType = cmdtype;
586 apt.Fill(dt);
587 if (dt.Rows.Count > 0)
588 {
589 row = dt.Rows[0];
590 }
591 conn.Close();
592 }
593 return row;
594 }
595
596 /// <summary>
597 /// 执行命令,返回第一行,不存在返回Null
598 /// </summary>
599 /// <param name="cmdText">查询的文本</param>
600 /// <returns>返回第一行,不存在返回Null</returns>
601 public static DataRow ExecuteFirstRow(string cmdText)
602 {
603 DataRow row = null;
604 using (OracleConnection conn = new OracleConnection(connectionString))
605 {
606 DataTable dt = new DataTable();
607 OracleDataAdapter apt = new OracleDataAdapter(cmdText, conn);
608 apt.SelectCommand.CommandType = CommandType.StoredProcedure;
609 apt.Fill(dt);
610 if (dt.Rows.Count > 0)
611 {
612 row = dt.Rows[0];
613 }
614 conn.Close();
615 }
616 return row;
617 }
618
619
620 #endregion
621 }
