不安装oracle客户端，连接到服务器的oracle (注：针对 odp.net)

浏览数：43 / 时间：2015年06月12日

　　前几天在研究怎样不安装oracle客户端去访问oracle，并把里面的数据同步到本地的Sql Server数据库中。

　　准备工作：首先你得有如下.dll，我这个是针对oracle10g的，如果是更高的版本，请使用10g以上版本。

oci.dll，ocijdbc10.dll，ociw32.dll，orannzsbb10.dll，oraocci10.dll，oraociei10.dll，System.Data.OracleClient.dll

最后那个dll我一开始是用Oracle.dataacess.dll，然后就一直报初始化失败，原因估计是本地的环境问题，等有空我在深究。上面那些dll我放在csdn上了，地址在结尾处了。

　　下面是demo中的一些代码：

using log4net;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.OracleClient;
using System.Data;
#region Orcle连接对象
        /// <summary>
        /// 连接对象 字段
        /// </summary>
        private static OracleConnection conn = null;
        /// <summary>
        /// 连接串 字段
        /// </summary>

        //private static string connstr = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));User Id=leaf;Password=leaf;";
        private static string connstr = @"Data Source服务器ip/orcl;Persist Security Info=True;User ID=账户;Password=密码;";

        /// <summary>
        /// 取得连接对象， 并打开
        /// </summary>
        public static OracleConnection GetOracleConnectionAndOpen
        {
            get
            {
                OracleConnection conn = new OracleConnection(connstr);
                conn.Open();
                return conn;
            }
        }

        /// <summary>
        /// 彻底关闭并释放 OracleConnection 对象，再置为null.
        /// </summary>
        /// <param name="conn">OracleConnection</param>
        public static void CloseOracleConnection(OracleConnection conn)
        {
            if (conn == null)
                return;
            conn.Close();
            conn.Dispose();
            conn = null;
        }
        #endregion
#region OracleExecuteDataSet
        /// <summary>
        /// 执行SQL语句, 返回DataSet
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> DataSet </returns>
        public static DataSet ExecuteDataSet(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteDataSet(cmdText, CommandType.Text, commandParameters);
        }

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> DataSet </returns>
        public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            DataSet result = null;
            OracleConnection conn = null;
            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();

                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = command;
                result = new DataSet();
                adapter.Fill(result);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }

            return result;
        }

        #endregion
#region PrepareOracleCommand
        /// <summary>
        /// Command对象执行前预处理
        /// </summary>
        /// <param name="command"></param>
        /// <param name="connection"></param>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        private static void PrepareCommand(OracleCommand command, OracleConnection connection, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {
            try
            {
                if (connection.State != ConnectionState.Open) connection.Open();

                command.Connection = connection;
                command.CommandText = cmdText;
                command.CommandType = cmdType;

                if (commandParameters != null)
                {
                    foreach (OracleParameter parm in commandParameters)
                        command.Parameters.Add(parm);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion