OracleHelper--> Oracle数据库连接类及测试

OracleHelper--> Oracle数据库连接类及测试

请先下载对应的 odp.net 的dll, 放在 bin 目录下面。　下载地址：点击打开链接

1. OracleHelper.cs 

using System;
using System.Data;
using Oracle.DataAccess.Client;
 
namespace Utils
{
    /// <summary>
    /// Desciption: Oracle数据库访问类 (注：针对 odp.net ). 
    /// Author    : yenange
    /// Date      : 2013-09-21
    /// </summary>
    public static class OracleHelper
    {
        #region [ 连接对象 ]
        /// <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=localhost/ORCL;Persist Security Info=True;User ID=leaf;Password=leaf;";
 
        /// <summary>
        /// 取得连接串
        /// </summary>
        public static string GetConnectionString
        {
            get
            {
                return connstr;
            }
        }
 
        /// <summary>
        /// 取得连接对象, 没有打开
        /// </summary>
        public static OracleConnection GetOracleConnection
        {
            get
            {
                return new OracleConnection(GetConnectionString);
            }
        }
 
        /// <summary>
        /// 取得连接对象， 并打开
        /// </summary>
        public static OracleConnection GetOracleConnectionAndOpen
        {
            get
            {
                OracleConnection conn = GetOracleConnection;
                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 [ ExecuteNonQuery ]
        /// <summary>
        /// 普通SQL语句执行增删改
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, commandParameters);
        }
        /// <summary>
        /// 存储过程执行增删改
        /// </summary>
        /// <param name="cmdText">存储过程</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQueryByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, commandParameters);
        }
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            int result = 0;
            OracleConnection conn = null;
 
            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();
                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                result = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                result = -1;
            }
            finally 
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }
 
            return result;
        }
        #endregion
 
        #region [ ExecuteReader ]
        /// <summary>
        /// SQL语句得到 OracleDataReader 对象
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns>OracleDataReader 对象</returns>
        public static OracleDataReader ExecuteReader(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteReader(cmdText, CommandType.Text, commandParameters);
        }
        /// <summary>
        /// 存储过程得到 OracleDataReader 对象
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns>OracleDataReader 对象</returns>
        public static OracleDataReader ExecuteReaderByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteReader(cmdText, CommandType.StoredProcedure, commandParameters);
        }
        /// <summary>
        /// 得到 OracleDataReader 对象
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns>OracleDataReader 对象</returns>
        public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            OracleDataReader result = null;
            OracleConnection conn = null;
 
            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();
                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                result = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                result = null;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }
 
            return result;
        }
        #endregion
 
        #region [ ExecuteScalar ]
        /// <summary>
        /// 执行SQL语句, 返回Object
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> Object </returns>
        public static Object ExecuteScalar(string cmdText, params OracleParameter[] commandParameters)
        { 
            return ExecuteScalar(cmdText, CommandType.Text, commandParameters);
        }
 
        /// <summary>
        /// 执行存储过程, 返回Object
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> Object </returns>
        public static Object ExecuteScalarByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteScalar(cmdText, CommandType.StoredProcedure, commandParameters);
        }
 
        /// <summary>
        /// 返回Object
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> Object </returns>
        public static Object ExecuteScalar(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            Object result = null;
            OracleConnection conn = null;
 
            try
            {
                conn = GetOracleConnectionAndOpen;
                OracleCommand command = new OracleCommand();
 
                PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
                result = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                result = null;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }
 
            return result;
        } 
        #endregion
 
        #region [ ExecuteDataSet ]
        /// <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="commandParameters">可变参数</param>
        /// <returns> DataSet </returns>
        public static DataSet ExecuteDataSetByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteDataSet(cmdText, CommandType.StoredProcedure, 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)
            {
                result = null;
            }
            finally
            {
                if (conn != null)
                    CloseOracleConnection(conn);
            }
 
            return result;
        }
        #endregion
 
        #region [ ExecuteDataTable ]
        /// <summary>
        /// 执行SQL语句, 返回DataTable
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> DataTable </returns>
        public static DataTable ExecuteDataTable(string cmdText, params OracleParameter[] commandParameters)
        { 
            return ExecuteDataTable(cmdText, CommandType.Text , commandParameters);
        }
 
        /// <summary>
        /// 执行存储过程, 返回DataTable
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> DataTable </returns>
        public static DataTable ExecuteDataTableByProc(string cmdText, params OracleParameter[] commandParameters)
        {
            return ExecuteDataTable(cmdText, CommandType.StoredProcedure, commandParameters);
        }
 
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="commandParameters">可变参数</param>
        /// <returns> DataTable </returns>
        public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params OracleParameter[] commandParameters)
        {
            DataTable dtResult = null;
            DataSet ds = ExecuteDataSet(cmdText, cmdType, commandParameters);
 
            if (ds != null && ds.Tables.Count > 0)
            {
                dtResult = ds.Tables[0];
            }
            return dtResult;
        }
        #endregion
 
        #region [ PrepareCommand ]
        /// <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 (trans != null) command.Transaction = trans;
 
                if (commandParameters != null)
                {
                    foreach (OracleParameter parm in commandParameters)
                        command.Parameters.Add(parm);
                }
            }
            catch
            {
 
            }
        }
        #endregion
    }//end of class
}//end of namespace

2. 测试类

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;
using Utils;
 
namespace OracleForNet
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (OracleConnection conn = Utils.OracleHelper.GetOracleConnectionAndOpen)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        Console.WriteLine("打开连接成功！");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("出现异常, 异常信息: " + ex.Message);
            }
            Console.Read();
        }
    }//end of class
}//end of namespace

https://blog.csdn.net/yenange/article/details/11880479