1 using System;
2 using System.Configuration;
3 using System.Data;
4 using System.Collections;
5 using Oracle.DataAccess.Client;
6
7 namespace Cont.DAL.Leave
8 {
9 /// <summary>
10 ///Oracle数据库操作帮助类
11 /// </summary>
12 public class OracleHelper
13 {
14 //从配置文件中读取配置好的连接字符串
15 private static OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
16 private static OracleTransaction tran = null;
17
18 /// <summary>
19 /// 返回受影响的行数
20 /// </summary>
21 /// <param name="sql">sql语句</param>
22 /// <returns></returns>
23 public static int NonQuery(string sql)
24 {
25 try
26 {
27 OracleCommand cmd = new OracleCommand(sql, conn);
28 int i = cmd.ExecuteNonQuery();
29 return i;//将受影响的行数返回
30 }
31 catch (Exception se)
32 {
33 throw se;
34 }
35
36 }
37 /// <summary>
38 /// 返回一个临时表数据，一般执行显示表中多条数据操作 select
39 /// </summary>
40 /// <param name="sql">要执行的查询sql语句</param>
41 /// <returns>返回一个DataTable</returns>
42 public static DataTable GetTable(string sql)
43 {
44 try
45 {
46 conn.Open();
47 OracleDataAdapter sda = new OracleDataAdapter(sql, conn);
48 DataTable dt = new DataTable();
49 sda.Fill(dt);
50 return dt;
51 }
52 catch (Exception se)
53 {
54 throw se;
55 }
56 finally
57 {
58 conn.Close();
59 }
60
61 }
62 /// <summary>
63 /// 执行查询操作，返回一个结果集
64 /// </summary>
65 /// <param name="sql">要执行的sql语句</param>
66 /// <returns>返回一个读取器</returns>
67 public static OracleDataReader Reader(string sql)
68 {
69 try
70 {
71 conn.Open();
72 OracleCommand cmd = new OracleCommand(sql, conn);
73 OracleDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//读取器和数据源此处不能被关闭
74 return sdr;
75 }
76 catch (Exception se)
77 {
78 throw se;
79 }
80 }
81 /// <summary>
82 /// 执行查询操作，返回一个单值，一般执行根据名称找编号或执行聚合函数
83 /// </summary>
84 /// <param name="sql">要执行的sql查询语句</param>
85 /// <returns>返回一个object类型</returns>
86 public static object Scalar(string sql)
87 {
88 try
89 {
90 conn.Open();
91 OracleCommand cmd = new OracleCommand(sql, conn);
92 object obj = cmd.ExecuteScalar();
93 return obj;
94 }
95 catch (Exception se)
96 {
97 throw se;
98 }
99 finally
100 {
101 conn.Close();
102 }
103 }
104
105 /// <summary>
106 ///开始事务
107 /// </summary>
108 public static void BeginTran()
109 {
110 conn.Open();
111 tran = conn.BeginTransaction();
112 }
113
114 /// <summary>
115 /// 提交事务
116 /// </summary>
117 public static void CommitTran()
118 {
119 tran.Commit();
120 tran.Dispose();
121 conn.Close();
122 tran = null;
123 }
124
125 /// <summary>
126 /// 回滚
127 /// </summary>
128 public static void RollbackTran()
129 {
130 tran.Rollback();
131 tran.Dispose();
132 conn.Close();
133 tran = null;
134 }
135 }
136 }
