C#中Oracle数据库的连接类

C#中Oracle数据库的连接类
1.C#连接Oracle数据库，⾸先需要在引⽤中添加System.Data.OracleClient组件
2.在类中引⽤System.Data.OracleClient组件：using System.Data.OracleClient
3.打开Oracle数据库连接
在打开数据库连接时，根据实际情况，我们把连接的字符串信息写在xml配置⽂件中，读取xml配置⽂件获取连接信息字符串。

配置⽂件信息如下：
注意：配置⽂件需要放在项⽬实际⽬录下的bin\Release中
Java代码
1. config.xml
2.
3. <?xml version="1.0" encoding="utf-8" ?>
4. <prog>
5. <oracle>
6. <connString>server=192.168.1.189;uid=AAMS;pwd=111111;data source=orcl</con
nString>
7. <oleString>Password=111111;User ID=AAMS;Data Source=ORCL;Persist Security
Info=True</oleString>
8. </oracle>
9. </prog>
读取xml配置⽂件信息：
C#代码
1. /// <summary>
2. /// 获取XML⽂件中对应节点的值
3. /// </summary>
4. /// <param name="name"></param>
5. /// <returns></returns>
6. public static string getXmlValue(string nodeName)
7. {
8. string s = "";
9. string nodeValue = "";
10. //需要using System.Xml
11. XmlDocument xd = new XmlDocument();
12. xd.Load(Program.PATH + "\\config.xml");
13. XmlNodeReader xnr = new XmlNodeReader(xd);
14. while (xnr.Read())
15. {
16. switch (xnr.NodeType)
17. {
18. case XmlNodeType.Element:
19. s = ;
20. break;
21. case XmlNodeType.Text:
22. if (s.Equals(nodeName))
23. {
24. nodeValue = xnr.Value;
25. }
26. break;
27. }
28. }
29. return nodeValue;
30. }
打开数据库连接
1. public static string connString = "";
2. public static OracleConnection conn = null;
3.
4. //打开数据库连接
5. public static bool Open()
6. {
7. //从配置⽂件中获取连接字符串
8. //配置⽂件需要放在项⽬⽬录下的bin\Release中
9. connString = getXmlValue("connString");
10. conn = new OracleConnection(getXmlValue("connString"));
11. try
12. {
13. conn.Open();
14. Console.WriteLine("数据库连接成功");
15. return true;
16. }
17. catch (System.Exception ex)
18. {
19. Console.Write(ex.Message);
20. MessageBox.Show("未能连接到数据库");
21. return false;
22. }
4.关闭数据库连接
C#代码
1. public static void Close()
2. {
3. if (conn != null)
4. {
5. conn.Close();
6. conn.Dispose();
7. conn = null;
8. Console.WriteLine("数据库关闭成功");
9. }
10. }
5.添加数据（使⽤拼接sql语句的⽅法）
C#代码
1. public static int insert(int ruleId, int equipmentId, String equipmentName, String propert
yName, int ruleType, String ruleRequest, String ruleRequestOther, String ruleExplain)
2. {
3. String sql = "insert into Device_Attr t(CA_ID,BASEID,BASENAME,CA_NAME,CA
_RULETYPE,CA_RULETEXT,CA_RULETEXT2,t.CONTENT) values(" + ruleId + "," + e quipmentId + ",'" + equipmentName + "$','" + propertyName + "','" + ruleType + "','" + rule Request + "','" + ruleRequestOther + "','" + ruleExplain + "')";
4. Console.WriteLine(sql);
5. OracleCommand oc = new OracleCommand(sql, conn);
6. int result = oc.ExecuteNonQuery();
7. return result;
8. }
6.删除数据
C#代码
1. public static int delete(string sql)
2. {
3. OracleCommand cmd = new OracleCommand(sql, conn);
4. int result = cmd.ExecuteNonQuery();
5. return result;
6. }
7.修改数据（使⽤参数的⽅法，与添加数据⽤的是两种⽅法，这两种⽅法都可⾏）
1. public static int update(int ruleId, int equipmentId, String equipmentName, String proper
tyName, int ruleType, String ruleRequest, String ruleRequestOther, String ruleExplain)
2. {
3. String sql = "update Device_Attr t set t.BASEID = :equipmentId, t.BASENAME = :e
quipmentName,t.CA_NAME = :propertyName,t.CA_RULETYPE = :ruleType,t.CA_RULE TEXT = :ruleRequest,t.CA_RULETEXT2 = :ruleRequestOther,t.CONTENT = :ruleExplai n where t.CA_ID =:ruleId";
4. OracleCommand cmd = new OracleCommand(sql, conn);
5. OracleParameter param_1 = new OracleParameter(":equipmentId", equipmentId);
6. cmd.Parameters.Add(param_1);
7. OracleParameter param_2 = new OracleParameter(":equipmentName", equipmen
tName + "$");
8. cmd.Parameters.Add(param_2);
9. OracleParameter param_3 = new OracleParameter(":propertyName", propertyNa
me);
10. cmd.Parameters.Add(param_3);
11. OracleParameter param_4 = new OracleParameter(":ruleType", ruleType);
12. cmd.Parameters.Add(param_4);
13. OracleParameter param_5 = new OracleParameter(":ruleRequest", ruleRequest);
14. cmd.Parameters.Add(param_5);
15. OracleParameter param_6 = new OracleParameter(":ruleRequestOther", ruleReq
uestOther);
16. cmd.Parameters.Add(param_6);
17. OracleParameter param_7 = new OracleParameter(":ruleExplain", ruleExplain);
18. cmd.Parameters.Add(param_7);
19. OracleParameter param_8 = new OracleParameter(":ruleId", ruleId);
20. cmd.Parameters.Add(param_8);
21. int result = cmd.ExecuteNonQuery();
22. return result;
23. }
8.查询数据
C#代码
1. public static OracleDataReader QueryForReader(string sql)
2. {
3. try
4. {
5. OracleCommand cmd = conn.CreateCommand();
6. mandText = sql;
7. OracleDataReader dtr = cmd.ExecuteReader();
8. return dtr;
9. }
10. catch (System.Exception ex)
11. {
12. Console.WriteLine(ex.ToString());
13. return null;
14. }
15. }。