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 ///10 ///Oracle数据库操作帮助类 11 /// 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 ///19 /// 返回受影响的行数 20 /// 21 /// sql语句 22 ///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 /// 38 /// 返回一个临时表数据,一般执行显示表中多条数据操作 select 39 /// 40 /// 要执行的查询sql语句 41 ///返回一个DataTable 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 ///63 /// 执行查询操作,返回一个结果集 64 /// 65 /// 要执行的sql语句 66 ///返回一个读取器 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 ///82 /// 执行查询操作,返回一个单值,一般执行根据名称找编号或执行聚合函数 83 /// 84 /// 要执行的sql查询语句 85 ///返回一个object类型 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 finally100 {101 conn.Close();102 }103 }104 105 ///106 ///开始事务 107 /// 108 public static void BeginTran()109 {110 conn.Open();111 tran = conn.BeginTransaction();112 }113 114 ///115 /// 提交事务116 /// 117 public static void CommitTran()118 {119 tran.Commit();120 tran.Dispose();121 conn.Close();122 tran = null;123 }124 125 ///126 /// 回滚127 /// 128 public static void RollbackTran()129 {130 tran.Rollback();131 tran.Dispose();132 conn.Close();133 tran = null;134 }135 }136 }