c#连接oracle数据库 DBHelper
闲着没事自己写了一个OracleHelper类，希望大神给点建议优化
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Data; 6 using System.Data.OracleClient; 7 8 namespace Common 9 { 10 public class DBHelper 11 { 12 private string connectionstring { get; set; } = System.Configuration.ConfigurationManager.ConnectionStrings["cms_mh"].ConnectionString; 13 14 public DBHelper() 15 { 16 OpenConnection(); 17 } 18 19 /// <summary> 20 /// 根据配置节读取连接数据库 21 /// </summary> 22 /// <param name="key">需要连接的数据库</param> 23 public DBHelper(string key) 24 { 25 this.connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings[key].ConnectionString; 26 OpenConnection(); 27 } 28 29 /// <summary> 30 /// 定义Oracle连接 31 /// </summary> 32 private OracleConnection conn { get; set; } 33 34 private OracleCommand cmd; 35 /// <summary> 36 /// 打开连接 37 /// </summary> 38 public void OpenConnection() 39 { 40 if (conn == null) 41 conn = new OracleConnection(connectionstring); 42 if (conn.State == ConnectionState.Closed) 43 conn.Open(); 44 } 45 46 /// <summary> 47 /// 构建返回Command 48 /// </summary> 49 /// <param name="cmdText">CommandText</param> 50 /// <param name="cmd">需要返回的Command</param> 51 /// <param name="cmdType">执行类型</param> 52 /// <param name="param">参数列表</param> 53 public void BuilderCommand(string cmdText, out OracleCommand cmd, CommandType cmdType, params OracleParameter[] param) 54 { 55 cmd = new OracleCommand(); 56 cmd.Connection = conn; 57 cmd = conn.CreateCommand(); 58 cmd.CommandType = cmdType; 59 cmd.CommandText = cmdText; 60 foreach (var item in param) 61 { 62 cmd.Parameters.Add(item); 63 } 64 } 65 66 /// <summary> 67 /// 执行存储过程 68 /// </summary> 69 /// <param name="storedName">存储过程名称</param> 70 /// <param name="param">参数列表</param> 71 /// <returns>返回执行成功条数</returns> 72 public int ExecuteStored(string storedName, params OracleParameter[] param) 73 { 74 BuilderCommand(storedName, out cmd, CommandType.StoredProcedure, param); 75 return cmd.ExecuteNonQuery(); 76 } 77 78 /// <summary> 79 /// 获取DataSet 80 /// </summary> 81 /// <param name="cmdText">执行语句</param> 82 /// <returns>DataSet</returns> 83 public DataSet GetDateSet(string cmdText, params OracleParameter[] param) 84 { 85 using (OracleDataAdapter _da = new OracleDataAdapter(cmdText, conn)) 86 { 87 DataSet ds = new DataSet(); 88 _da.Fill(ds); 89 return ds; 90 } 91 } 92 93 /// <summary> 94 /// 获取Table 95 /// </summary> 96 /// <param name="cmdText">执行语句</param> 97 /// <returns>DataTable</returns> 98 public DataTable GetTable(string cmdText) 99 { 100 return GetDateSet(cmdText).Tables[0]; 101 } 102 103 /// <summary> 104 /// 执行sql 105 /// </summary> 106 /// <param name="cmdText">执行语句</param> 107 /// <param name="param">执行所需参数</param> 108 /// <returns>成功执行数</returns> 109 public int ExecuteSql(string cmdText, params OracleParameter[] param) 110 { 111 OracleCommand cmd = new OracleCommand(); 112 BuilderCommand(cmdText, out cmd, CommandType.Text, param); 113 return cmd.ExecuteNonQuery(); 114 } 115 116 /// <summary> 117 /// 构建DataReader 118 /// </summary> 119 /// <param name="cmdText">执行语句</param> 120 /// <param name="param">执行所需参数</param> 121 /// <returns>DataReader</returns> 122 public OracleDataReader BuilderDataReader(string cmdText, params OracleParameter[] param) 123 { 124 BuilderCommand(cmdText, out cmd, CommandType.Text, param); 125 return cmd.ExecuteReader(); 126 } 127 } 128 }
