C# 一个操作Oracle的简易工具类（通过System.Data.OracleClient）

一、关于本文

运行本文中的代码，需要

1）需要手动添加引用System.Data.OracleClient

在代码最前面加入这句话：using System.Data.OracleClient;

2）需要安装 Instant Client

安装 Oracle Instant Client（OCI） 的步骤可以参考这篇文章

注：下载32位的OCI库，则在VS中需要选择x86作为目标平台；下载64位的OCI库，则需要选择x64作为目标平台

二、工具类OracleHelper代码

class OracleHelper
{
    //需要　oci.dll oraociei12.dll oraons.dll

    /// <summary>
    /// 协议：默认TCP
    /// </summary>
    public static string PROTOCOL = "TCP";
    /// <summary>
    /// 主机地址：默认171.0.0.132
    /// </summary>
    public static string HOST = "171.0.0.132";
    /// <summary>
    /// 端口号：默认1521
    /// </summary>
    public static string PORT = "1521";
    /// <summary>
    /// TNS服务名：默认TESTDB
    /// </summary>
    public static string SERVICE_NAME = "TESTDB";
    /// <summary>
    /// 用户名：默认NQ_MON
    /// </summary>
    public static string USER_ID = "NQ_MON";
    /// <summary>
    /// 密码：默认NQ_MON
    /// </summary>
    public static string PASSWORD = "NQ_MON";

    /// <summary>
    /// 初始化工具类中各项属性
    /// </summary>
    /// <param name="PTC">协议：默认TCP</param>
    /// <param name="HST">主机地址：默认171.0.0.132</param>
    /// <param name="PRT">端口号：默认1521</param>
    /// <param name="SRV">TNS服务名：默认TESTDB</param>
    /// <param name="UID">用户名：默认NQ_MON</param>
    /// <param name="PWD">密码：默认NQ_MON</param>
    public static void InitializeConfig(
        string PTC = "TCP",
        string HST = "171.0.0.132",
        string PRT = "1521",
        string SRV = "TESTDB",
        string UID = "NQ_MON",
        string PWD = "NQ_MON")
    {
        PROTOCOL = PTC;
        HOST = HST;
        PORT = PRT;
        SERVICE_NAME = SRV;
        USER_ID = UID;
        PASSWORD = PWD;
    }

    /// <summary>
    /// 生成连接字符串
    /// </summary>
    /// <param name="PROTOCOL">协议</param>
    /// <param name="HOST">主机地址</param>
    /// <param name="PORT">端口号</param>
    /// <param name="SERVICE_NAME">TNS服务名</param>
    /// <param name="USER_ID">用户名</param>
    /// <param name="PASSWORD">登录口令</param>
    /// <returns></returns>
    public static string GetConnectionString()
    {
        return string.Format(
            "DATA SOURCE=                   " +
            "    (DESCRIPTION=              " +
            "        (ADDRESS_LIST=         " +
            "            (ADDRESS=          " +
            "                (PROTOCOL={0}) " +
            "                (HOST={1})     " +
            "                (PORT={2})     " +
            "            )                  " +
            "        )                      " +
            "        (CONNECT_DATA=         " +
            "            (SERVICE_NAME={3}) " +
            "        )                      " +
            "    );                         " +
            "PERSIST SECURITY INFO=TRUE;    " +
            "USER ID={4};                   " +
            "PASSWORD={5}                   ",
            PROTOCOL, HOST, PORT, SERVICE_NAME, USER_ID, PASSWORD);
    }

    /// <summary>
    /// 向数据库中执行命令
    /// </summary>
    /// <param name="sqlExecute">无返回值SQL语句</param>
    /// <returns>SQL语句影响的行数</returns>
    public static int ExecuteCommand(string sqlExecute)
    {
        int lineschanged = 0;

        //生成连接字符串
        string conn = GetConnectionString();

        //读取数据
        using (OracleConnection oc = new OracleConnection(conn))
        {
            try
            {
                //打开连接
                oc.Open();
                OracleCommand cmd = oc.CreateCommand();
                cmd.CommandText = sqlExecute;
                lineschanged = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //输出异常后抛出
                Console.WriteLine("Function ExecuteCommand: " + ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                //关闭连接
                oc.Close();
            }
        }

        return lineschanged;
    }

    /// <summary>
    /// 从数据库中查询数据
    /// </summary>
    /// <param name="sqlSearch">查询用SQL语句</param>
    /// <returns></returns>
    public static System.Data.DataTable SearchCommand(string sqlSearch)
    {
        //返回的DataTable
        System.Data.DataTable dt = new System.Data.DataTable();

        //生成连接字符串
        string conn = GetConnectionString();

        //读取数据
        using (OracleConnection oc = new OracleConnection(conn))
        {
            try
            {
                //打开连接
                oc.Open();
                OracleCommand cmd = oc.CreateCommand();
                cmd.CommandText = sqlSearch;
                OracleDataReader odr = cmd.ExecuteReader();

                //读取列信息
                for (int i = 0; i < odr.FieldCount; i++)
                {
                    dt.Columns.Add(odr.GetName(i));
                }

                //读取各行存入到DataTable中
                while (odr.Read())
                {
                    string[] s = new string[odr.FieldCount];
                    for (int i = 0; i < odr.FieldCount; i++)
                    {
                        s[i] = odr.GetValue(i).ToString();
                    }
                    dt.Rows.Add(s);
                }

            }
            catch (Exception ex)
            {
                //输出异常后抛出
                Console.WriteLine("Function SearchCommand: " + ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                //关闭连接
                oc.Close();
            }
        }

        return dt;
    }
}

三、Main函数调用示例

namespace OracleTest
{
    class Program
    {
        /// <summary>
        /// 执行查询SQL语句示例
        /// </summary>
        public static void SearchTest()
        {
            //查询内容
            Console.WriteLine("执行SEARCH语句");
            System.Data.DataTable dt = OracleHelper.SearchCommand(
                "SELECT CURR_DATE, CURR_TIME FROM MONCRITEVENT WHERE ROWNUM <= 10");

            //输出查询结果
            Console.WriteLine("输出查询结果");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Console.Write(dt.Rows[i][j].ToString());
                    Console.Write('\t');
                }
                Console.WriteLine();
            }
        }

        /// <summary>
        /// 执行非查询SQL语句示例
        /// </summary>
        public static void CommandTest()
        {
            //执行SQL语句
            Console.WriteLine("执行UPDATE语句");
            int lineschanged = OracleHelper.ExecuteCommand(
                "UPDATE MONCRITEVENT SET CURR_DATE = 9999 WHERE CURR_DATE = 9999");
            Console.WriteLine("受影响的行数：" + lineschanged);
        }

        /// <summary>
        /// 循环发送Search语句并返回
        /// </summary>
        /// <param name="second">每次收发数据间隔时间</param>
        public static void SearchLoopTest(object second)
        {
            while (true)
            {
                SearchTest();
                System.Threading.Thread.Sleep(int.Parse(second.ToString()));
            }
        }

        static void Main(string[] args)
        {
            SearchTest(); //执行查询SQL语句示例
            Console.WriteLine("--------");
            CommandTest(); //执行非查询SQL语句示例
            Console.WriteLine("--------");

            //压力测试，执行时可以打开任务管理器辅助检测程序运行状况
            //每0.2秒发送一次，分10个线程发送
            /*
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //1
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //2
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //3
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //4
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //5
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //6
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //7
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //8
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //9
            System.Threading.Thread.Sleep(200);
            (new System.Threading.Thread(SearchLoopTest)).Start(2000); //10
             * */

            Console.ReadLine();
        }
    }
}

四、运行截图

END

转载于:https://my.oschina.net/Tsybius2014/blog/300170