How to connect to a remote Oracle database

Question

I have to connect to a remote Oracle DBMS into my .NET C# web service

• Is request the client Oracle installation? Why?
• When you have to use ODP.NET

Thanks

Answer 1

I recommend using ODP.NET, as it's free and is the "official" ADO.NET compatible provider for connecting to Oracle.¹

To spare your users from having to separately install the Oracle Client, download the Oracle Instant Client, take the following files from there...

oci.dll
Oracle.DataAccess.dll (the managed ODP.NET assembly itself)
orannzsbb11.dll
oraociei11.dll
OraOps11w.dll

...and distribute them with your application.

Unfortunately, most of these DLLs are native (and 32-bit / 64-bit specific), so you won't be able to build for "Any CPU" platform (yet²).

The .NET code is identical to what you would use under "fat" Oracle Client (and is very similar to any other ADO.NET provider out there), except you should probably consider using "tnsnames.ora independent" connection string such as:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

---

¹ There are commercial alternatives and the old Microsoft's provider that is now deprecated (and won't save you from having to install Oracle native DLLs anyway).

² Either wait for Fully Managed Oracle Provider, or edit your project file (the MSBuild XML) to conditionally include 32-bit or 64-bit DLLs depending on the build platform, similar to this:

  <Choose>
    <When Condition="'$(Platform)' == 'x64'">
      <ItemGroup>
        <Reference Include="Oracle.DataAccess, processorArchitecture=x64">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\ThirdParty\ODP.NET\x64\Oracle.DataAccess.dll</HintPath>
        </Reference>
        <Content Include="..\ThirdParty\ODP.NET\x64\oci.dll">
          <Link>oci.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
        <Content Include="..\ThirdParty\ODP.NET\x64\orannzsbb11.dll">
          <Link>orannzsbb11.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
        <Content Include="..\ThirdParty\ODP.NET\x64\oraociei11.dll">
          <Link>oraociei11.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
        <Content Include="..\ThirdParty\ODP.NET\x64\OraOps11w.dll">
          <Link>OraOps11w.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
      </ItemGroup>
    </When>
    <When Condition="'$(Platform)' == 'x86'">
      <ItemGroup>
        <Reference Include="Oracle.DataAccess, processorArchitecture=x86">
          <SpecificVersion>False</SpecificVersion>
          <HintPath>..\ThirdParty\ODP.NET\x86\Oracle.DataAccess.dll</HintPath>
        </Reference>
        <Content Include="..\ThirdParty\ODP.NET\x86\oci.dll">
          <Link>oci.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
        <Content Include="..\ThirdParty\ODP.NET\x86\orannzsbb11.dll">
          <Link>orannzsbb11.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
        <Content Include="..\ThirdParty\ODP.NET\x86\oraociei11.dll">
          <Link>oraociei11.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
        <Content Include="..\ThirdParty\ODP.NET\x86\OraOps11w.dll">
          <Link>OraOps11w.dll</Link>
          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </Content>
      </ItemGroup>
    </When>
  </Choose>

Answer 2

I think you can use the Oracle.DataAccess namespace from ODP.NET

You can use like this:

var _testConx = new OracleConnection(_testConnectionString);
var rezList = new List<Type>();
string _GetSQL = @"SELECT STATEMENT";
var dbCommand = new OracleCommand(_GetSQL , _testConx);
dbCommand .CommandType = CommandType.Text;
var reader = dbCommand .ExecuteReader();
while (reader.Read())
{
   var rez = new Type();
   rez.Field1= TryGetInt(reader.GetOracleValue(0));
   rez.Field2= TryGetString(reader.GetOracleValue(1));

   rezList.Add(rez);
}
return rezList;

This will use the oracle client to connect to Remote Database.

You can specify the connection string in external ressource like config file