c#sql_oracle_mysql 封装dbhelper_c#连接oracle数据库 DBHelper

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5 usingSystem.Data;6 usingSystem.Data.OracleClient;7

8 namespaceCommon9 {10 public classDBHelper11 {12 private string connectionstring { get; set; } = System.Configuration.ConfigurationManager.ConnectionStrings["cms_mh"].ConnectionString;13

14 publicDBHelper()15 {16 OpenConnection();17 }18

19 ///

20 ///根据配置节读取连接数据库21 ///

22 /// 需要连接的数据库

23 public DBHelper(stringkey)24 {25 this.connectionstring =System.Configuration.ConfigurationManager.ConnectionStrings[key].ConnectionString;26 OpenConnection();27 }28

29 ///

30 ///定义Oracle连接31 ///

32 private OracleConnection conn { get; set; }33

34 privateOracleCommand cmd;35 ///

36 ///打开连接37 ///

38 public voidOpenConnection()39 {40 if (conn == null)41 conn = newOracleConnection(connectionstring);42 if (conn.State ==ConnectionState.Closed)43 conn.Open();44 }45

46 ///

47 ///构建返回Command48 ///

49 /// CommandText

50 /// 需要返回的Command

51 /// 执行类型

52 /// 参数列表

53 public void BuilderCommand(string cmdText, out OracleCommand cmd, CommandType cmdType, paramsOracleParameter[] param)54 {55 cmd = newOracleCommand();56 cmd.Connection =conn;57 cmd =conn.CreateCommand();58 cmd.CommandType =cmdType;59 cmd.CommandText =cmdText;60 foreach (var item inparam)61 {62 cmd.Parameters.Add(item);63 }64 }65

66 ///

67 ///执行存储过程68 ///

69 /// 存储过程名称

70 /// 参数列表

71 /// 返回执行成功条数

72 public int ExecuteStored(string storedName, paramsOracleParameter[] param)73 {74 BuilderCommand(storedName, outcmd, CommandType.StoredProcedure, param);75 returncmd.ExecuteNonQuery();76 }77

78 ///

79 ///获取DataSet80 ///

81 /// 执行语句

82 /// DataSet

83 public DataSet GetDateSet(string cmdText, paramsOracleParameter[] param)84 {85 using (OracleDataAdapter _da = newOracleDataAdapter(cmdText, conn))86 {87 DataSet ds = newDataSet();88 _da.Fill(ds);89 returnds;90 }91 }92

93 ///

94 ///获取Table95 ///

96 /// 执行语句

97 /// DataTable

98 public DataTable GetTable(stringcmdText)99 {100 return GetDateSet(cmdText).Tables[0];101 }102

103 ///

104 ///执行sql105 ///

106 /// 执行语句

107 /// 执行所需参数

108 /// 成功执行数

109 public int ExecuteSql(string cmdText, paramsOracleParameter[] param)110 {111 OracleCommand cmd = newOracleCommand();112 BuilderCommand(cmdText, outcmd, CommandType.Text, param);113 returncmd.ExecuteNonQuery();114 }115

116 ///

117 ///构建DataReader118 ///

119 /// 执行语句

120 /// 执行所需参数

121 /// DataReader

122 public OracleDataReader BuilderDataReader(string cmdText, paramsOracleParameter[] param)123 {124 BuilderCommand(cmdText, outcmd, CommandType.Text, param);125 returncmd.ExecuteReader();126 }127 }128 }