C#使用Oracle.ManagedDataAccess.dll元件連線Oracle資料庫

2022-05-26 18:03:32

一、概述

Oracle Data Provider for .NET, Managed Driver:

Oracle官方的託管資料庫存取元件，單DLL，Oracle.ManagedDataAccess.dll，不安裝Oracle使用者端，不區分32/64位元，直接參照即可。

與非託管程式為Oracle.DataAccess.dll相比，BulkCopy和CustomType之類的都是不支援的：

1、下載地址

官方下載地址：

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html (當前下版本為64-bit ODAC 12.2c Release 1 (12.2.0.1.0))

nuget：

https://www.nuget.org/packages/Oracle.ManagedDataAccess/ (當前下版本為19.7.0)

https://www.nuget.org/packages/Oracle.ManagedDataAccess.EntityFramework/ (當前下版本為19.7.0)

下載好後從這個目錄下 odp.netmanagedcommon拿出最關鍵的兩個dll

Oracle.ManagedDataAccess.dll 和 Oracle.ManagedDataAccess.EntityFramework.dll 。

也可以在專案Nuget中直接安裝這兩個包。

注意:如果需要Entity Framework 設計時特性和其他如TableAdapter嚮導Visual Studio設計器，可以從https://otn.oracle.com/dotnet下載32位元的“Oracle Developer Tools for Visual Studio” 。

此NuGet下載不支援設計時工具;它只提供執行時支援。這個版本的ODP.NET支援Oracle資料庫版本11.2或更高版本。

二、組態檔定義資料來源

1、無組態檔情況，直接連線DB

無SQL*Net組態檔tnsnames.ora，直接在C#程式中寫連線字串

string conString = "User Id=hr; password=hr;Data Source=localhost:1521/orcl; Pooling=false;";

2、使用組態檔方式：

1、使用 tnsnames.ora檔案，此檔案需放置在應用程式同目錄:

tnsnames.ora內容：

ORCL =
              (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = orcl)
                )
              )

2、使用app.config或web.config組態檔定義別名（無tnsnames.ora檔案）

<oracle.manageddataaccess.client>
  <version number="*">
    <dataSources>
       <dataSource alias="orcl" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = orcl)))"/>
     </dataSources>
   </version>
 </oracle.manageddataaccess.client>

3、使用 TNS_ADMIN目錄：

<oracle.manageddataaccess.client>
  <version number="*">
    <settings>
           <setting name="TNS_ADMIN" value="C:apporacleproduct12.1.0client_1NetworkAdmin"/>  
     </settings>
   </version>
 </oracle.manageddataaccess.client>

3、完整的app/web.config 檔案：

<configuration>
    <configSections>
    <section name="oracle.manageddataaccess.client"
      type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.19.1, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
    </configSections>
    <system.data>
        <DbProviderFactories>
            <remove invariant="Oracle.ManagedDataAccess.Client"/>
            <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"
                type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.19.1, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
        </DbProviderFactories>
    </system.data>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <publisherPolicy apply="no"/>
                <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral"/>
                <bindingRedirect oldVersion="4.121.0.0 - 4.65535.65535.65535" newVersion="4.122.19.1"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
    <oracle.manageddataaccess.client>
        <version number="*">
            <dataSources>
                <dataSource alias="SampleDataSource"
                    descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) "/>
            </dataSources>
        </version>
    </oracle.manageddataaccess.client>
</configuration>

三、使用

string conString = "User Id=hr; password=hr; Data Source=orcl;Pooling=false; ";

OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
con.Open();

OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select first_name from employees where department_id = 60";

OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    string conString = "User Id=hr; password=hr; Data Source=orcl;Pooling=false; ";

    OracleConnection con = new OracleConnection();
    con.ConnectionString = conString;
    con.Open();

    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "select first_name from employees where department_id = 60";

    OracleDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine("Employee Name: " + reader.GetString(0));
    }
    Console.WriteLine("Employee Name: " + reader.GetString(0));
}

到此這篇關於C#使用Oracle.ManagedDataAccess.dll元件連線Oracle資料庫的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。