C#中Oracle数据库的连接类

本文转自：http://changbl.iteye.com/blog/1750454

1.C#连接Oracle数据库，首先需要在引用中添加System.Data.OracleClient组件

2.在类中引用System.Data.OracleClient组件：using System.Data.OracleClient

3.打开Oracle数据库连接

   在打开数据库连接时，根据实际情况，我们把连接的字符串信息写在xml配置文件中，读取xml配置文件获取连接信息字符串。

配置文件信息如下：

注意：配置文件需要放在项目实际目录下的binRelease中

config.xml

<?xml version="1.0" encoding="utf-8" ?>
<prog>
    <oracle>
        <connString>server=192.168.1.189;uid=AAMS;pwd=111111;data source=orcl</connString>
        <oleString>Password=111111;User ID=AAMS;Data Source=ORCL;Persist Security Info=True</oleString>
    </oracle>
</prog>

 读取xml配置文件信息：

/// <summary>
        /// 获取XML文件中对应节点的值
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string getXmlValue(string nodeName)
        {
            string s = "";
            string nodeValue = "";
            //需要using System.Xml
            XmlDocument xd = new XmlDocument();
            xd.Load(Program.PATH + "\config.xml");
            XmlNodeReader xnr = new XmlNodeReader(xd);
            while (xnr.Read())
            {
                switch (xnr.NodeType)
                {
                    case XmlNodeType.Element:
                        s = xnr.Name;
                        break;
                    case XmlNodeType.Text:
                        if (s.Equals(nodeName))
                        {
                            nodeValue = xnr.Value;
                        }
                        break;
                }
            }
            return nodeValue;
        }

 打开数据库连接

public static string connString = "";
        public static OracleConnection conn = null;

        //打开数据库连接
        public static bool Open()
        {
            //从配置文件中获取连接字符串
            //配置文件需要放在项目目录下的binRelease中
            connString = getXmlValue("connString");
            conn = new OracleConnection(getXmlValue("connString"));
            try
            {
                conn.Open();
                Console.WriteLine("数据库连接成功");
                return true;
            }
            catch (System.Exception ex)
            {
                Console.Write(ex.Message);
                MessageBox.Show("未能连接到数据库");
                return false;
            }

 4.关闭数据库连接

public static void Close()
        {
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
                Console.WriteLine("数据库关闭成功");
            }
        }

 5.添加数据（使用拼接sql语句的方法）

public static int insert(int ruleId, int equipmentId, String equipmentName, String propertyName, int ruleType, String ruleRequest, String ruleRequestOther, String ruleExplain)
        {
            String sql = "insert into Device_Attr t(CA_ID,BASEID,BASENAME,CA_NAME,CA_RULETYPE,CA_RULETEXT,CA_RULETEXT2,t.CONTENT) values(" + ruleId + "," + equipmentId + ",'" + equipmentName + "$','" + propertyName + "','" + ruleType + "','" + ruleRequest + "','" + ruleRequestOther + "','" + ruleExplain + "')";
            Console.WriteLine(sql);
            OracleCommand oc = new OracleCommand(sql, conn);
            int result = oc.ExecuteNonQuery();
            return result;
        }

 6.删除数据

public static int delete(string sql)
        {
            OracleCommand cmd = new OracleCommand(sql, conn);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

 7.修改数据（使用参数的方法，与添加数据用的是两种方法，这两种方法都可行）

public static int update(int ruleId, int equipmentId, String equipmentName, String propertyName, int ruleType, String ruleRequest, String ruleRequestOther, String ruleExplain)
        {
            String sql = "update Device_Attr t set t.BASEID = :equipmentId, t.BASENAME = :equipmentName,t.CA_NAME = :propertyName,t.CA_RULETYPE = :ruleType,t.CA_RULETEXT = :ruleRequest,t.CA_RULETEXT2 = :ruleRequestOther,t.CONTENT = :ruleExplain where t.CA_ID =:ruleId";
                OracleCommand cmd = new OracleCommand(sql, conn);
            OracleParameter param_1 = new OracleParameter(":equipmentId", equipmentId);
            cmd.Parameters.Add(param_1);
            OracleParameter param_2 = new OracleParameter(":equipmentName", equipmentName + "$");
            cmd.Parameters.Add(param_2);
            OracleParameter param_3 = new OracleParameter(":propertyName", propertyName);
            cmd.Parameters.Add(param_3);
            OracleParameter param_4 = new OracleParameter(":ruleType", ruleType);
            cmd.Parameters.Add(param_4);
            OracleParameter param_5 = new OracleParameter(":ruleRequest", ruleRequest);
            cmd.Parameters.Add(param_5);
            OracleParameter param_6 = new OracleParameter(":ruleRequestOther", ruleRequestOther);
            cmd.Parameters.Add(param_6);
            OracleParameter param_7 = new OracleParameter(":ruleExplain", ruleExplain);
            cmd.Parameters.Add(param_7);
            OracleParameter param_8 = new OracleParameter(":ruleId", ruleId);
            cmd.Parameters.Add(param_8);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

 8.查询数据

public static OracleDataReader QueryForReader(string sql)
        {
            try
            {
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                OracleDataReader dtr = cmd.ExecuteReader();
                return dtr;
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return null;
            }
        }