C#连接Oracle数据库

分享一个连接Oracle数据库比较好用的类，使用该类可以避免版本不兼容的问题，以及路径问题；再也不用担心打开Oracle报各种连接异常。

1. Nuget安装Oracle.ManagedDataAccess 

2. 主要代码（用户名、密码要填入自己数据库的）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Configuration;
using System.Data;
using System.Data.Common;

namespace Demo
{
    public struct ConnInfo
    {
        public string ConnectionString { get; set; }
        public DbProviderFactory ProviderFactory { get; set; }
    }

    public class SqlHelper : IDisposable
    {
        static SqlHelper()
        {
            string user ="用户名";
            string password = "密码";
            string host = "主机IP";
            string serviceName = "服务名";
            string port = "端口";
            //string providerName = "System.Data.OracleClient";
            //數據庫連接字符串
            string str = string.Format("Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST ={0})(PORT = {1}))) (CONNECT_DATA = (SERVICE_NAME ={2})));User Id ={3}; Password={4}",
                                                     host, port, serviceName, user, password);
            ConnInfo ci = new ConnInfo
            {
                ConnectionString = str
            };
            _ConnInfo = ci;
        }

        private string _DbAlias = "LabelDb";
        public static ConnInfo _ConnInfo;
        private OracleConnection _Conn;
        private OracleTransaction _Trans;


        public SqlHelper()
        {
            Initial();
        }

        public SqlHelper(string DbAlias)
            : base()
        {
            _DbAlias = DbAlias;
            Initial();
        }

        public void Dispose()
        {
            if (_Trans != null)
            {
                _Trans.Rollback();
            }
            if (_Conn != null)
            {
                if (_Conn.State == ConnectionState.Open)
                    _Conn.Close();
                _Conn.Dispose();
            }
        }

        private void Initial()
        {
            _Conn = new OracleConnection();
            _Conn.ConnectionString = _ConnInfo.ConnectionString;
            _Conn.Open();
            _Conn.Close();
        }

        public DataTable ExecuteQuery(string CommandText)
        {
            if (_Conn.State != ConnectionState.Open)
                _Conn.Open();
            OracleCommand command = _Conn.CreateCommand();
            command.CommandText = CommandText;
            command.Transaction = _Trans;
            OracleDataAdapter adapter = new OracleDataAdapter();
            adapter.SelectCommand = command;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }

        public int ExecuteNonQuery(string CommandText)
        {
            if (_Conn.State != ConnectionState.Open)
                _Conn.Open();
            OracleCommand command = _Conn.CreateCommand();
            command.CommandText = CommandText;
            command.Transaction = _Trans;
            return command.ExecuteNonQuery();
        }

        public int ExecuteNonQuery(string CommandText, string[] ParamNames, object[] ParamValues)
        {
            if (_Conn.State != ConnectionState.Open)
                _Conn.Open();
            OracleCommand command = _Conn.CreateCommand();
            command.CommandText = CommandText;
            for (int i = 0; i < ParamNames.Length; i++)
            {
                OracleParameter p = command.CreateParameter();
                p.ParameterName = ParamNames[i];
                p.Value = ParamValues[i];
                p.Direction = ParameterDirection.Input;
                command.Parameters.Add(p);
            }
            command.Transaction = _Trans;
            return command.ExecuteNonQuery();
        }


        public OracleDataReader ExecuteReader(string CommandText, CommandBehavior behavior)
        {
            if (_Conn.State != ConnectionState.Open)
                _Conn.Open();
            OracleCommand command = _Conn.CreateCommand();
            command.CommandText = CommandText;
            command.Transaction = _Trans;
            return command.ExecuteReader(behavior);
        }

        public OracleDataReader ExecuteReader(string CommandText)
        {
            return ExecuteReader(CommandText, CommandBehavior.Default);
        }

        public object ExecuteScalar(string CommandText)
        {
            if (_Conn.State != ConnectionState.Open)
                _Conn.Open();
            OracleCommand command = _Conn.CreateCommand();
            command.CommandText = CommandText;
            command.Transaction = _Trans;
            return command.ExecuteScalar();
        }

        public DbTransaction BeginTrans()
        {
            if (_Trans == null)
            {
                _Trans = _Conn.BeginTransaction();
            }

            return _Trans;
        }

        public void Commit()
        {
            if (_Trans != null)
            {
                _Trans.Commit();
                _Trans = null;
            }
        }

        public void Rollback()
        {
            if (_Trans != null)
            {
                _Trans.Rollback();
                _Trans = null;
            }
        }

        public OracleConnection Conenction()
        {
            return _Conn;
        }
    }
}

3. 调用：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Demo
{
    public partial class Form5 : Form
    {
        public Form5()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (SqlHelper helper = new SqlHelper())
            {
                string sqlStr = "select * from employess";
                DataTable dt = helper.ExecuteQuery(sqlStr);
                dataGridView1.DataSource = dt;
            }
        }
    }
}

4. 结果