oracle数据库连接方式

安装了Oracle Db Provider后Oracle.DataAccess.dll的路径是：D:\app\Administrator\product\11.2.0\client_1\ODP.NET\bin\2.x

下面演示了使用OleDb, msdaora, System.Data.OracleClient.OracleConnection, Oracle.DataAccess.Client.OracleConnection 以及用DbProviderFactories.GetFactory方式来获取Connection

另外如果是64位操作系统，需要安装64位的驱动

代码如下：

using System;
using System.Data;
using System.Data.Common;

namespace WinTest
{
    class Program
    {
        static void Main(string[] args)
        {
            ShowAllDbProviderFactories();

            string connString;

            /* ole db provider */
            connString = "Provider=OraOledb.Oracle;Data Source=xe;User ID=sa;Password=jack2008;";
            using (DbConnection conn = new System.Data.OleDb.OleDbConnection(connString))
            {
                TestConnection(conn);
            }

            connString = "Provider=OraOleDb.Oracle.1;Data Source=xe;User ID=sa;Password=jack2008;";
            using (DbConnection conn = new System.Data.OleDb.OleDbConnection(connString))
            {
                TestConnection(conn);
            }

            /* ms oracle provider, it is x86 version */
            //connString = "Provider=msdaora;Data Source=xe;User ID=sa;Password=jack2008;";
            //using (DbConnection conn = new System.Data.OleDb.OleDbConnection(connString))
            //{
            //    TestConnection(conn);
            //}

            connString = "Data Source=xe;User ID=sa;Password=jack2008;";
            using (DbConnection conn = new System.Data.OracleClient.OracleConnection(connString))
            {
                TestConnection(conn);
            }

            connString = "Data Source=xe;User ID=sa;Password=jack2008;";
            using (DbConnection conn = new Oracle.DataAccess.Client.OracleConnection(connString))
            {
                TestConnection(conn);
            }

            string providerInvariantName;
            providerInvariantName = "Oracle.DataAccess.Client";
            connString = "Data Source=xe;User ID=sa;Password=jack2008;";
            UseDbProviderFactoryGetConnection(providerInvariantName,connString);

            providerInvariantName = "System.Data.OracleClient";
            connString = "Data Source=xe;User ID=sa;Password=jack2008;";
            UseDbProviderFactoryGetConnection(providerInvariantName, connString);

            //connString = "Data Source=xe;User ID=sa;Password=jack2008;";
            //DbProviderFactory factory = DbProviderFactories.GetFactory("");
            //using (DbConnection conn = factory.CreateConnection())
            //{
            //    conn.ConnectionString = connString;
            //    TestConnection(conn);
            //}

            Console.WriteLine("go");
            Console.ReadLine();

        }

        

        private static void ShowAllDbProviderFactories()
        {
            /* all Db Provider Factory */
            DataTable dtFactory = DbProviderFactories.GetFactoryClasses();
            Console.WriteLine("------------------------------------");
            foreach (DataRow row in dtFactory.Rows)
            {
                //for (int i = 0; i < dtFactory.Columns.Count; i++)
                //{
                //    Console.Write("{0}\t", row[i]);
                //}
                Console.Write("{0}\t{1}", row[2], row[3]);
                Console.WriteLine();
            }
        }

        private static void UseDbProviderFactoryGetConnection(string providerInvariantName, string connString)
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory(providerInvariantName);
            using (DbConnection conn = factory.CreateConnection())
            {
                conn.ConnectionString = connString;
                TestConnection(conn);
            }
        }

        private static void TestConnection(DbConnection conn)
        {
            Console.WriteLine("------------------------------------");
            string sql = "SELECT * FROM dept";
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            conn.Open();
            using (IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    Console.WriteLine("{0}\t{1}\t{2}", dr[0], dr[1], dr[2]);
                }
            }
            conn.Close();
        }

        private static void AddParameter(DbCommand cmd, string paramName, object paramValue)
        {
            DbParameter p = cmd.CreateParameter();
            p.ParameterName = paramName;
            p.Value = paramValue ?? DBNull.Value;

            cmd.Parameters.Add(p);
        }
    }
}