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 }
