How to connect to a remote Oracle database

Question

I need to connect to a remote Oracle DBMS in my .NET C # web service.

• Is the request to install an Oracle client? Why?
• When you need to use ODP.NET

thanks

Answer 1

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

To save your users from having to install Oracle Client separately, download Oracle Instant Client , open the following files ...

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), so you cannot build the Any Processor platform ( ² more).

The .NET code is identical to what you would use under the "thick" Oracle Client (and is very similar to any other ADO.NET provider there), except that you should probably consider using the "tnsnames.ora independent" line connections , for example:

 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 provider, which is now outdated (and will not save you from installing external Oracle DLLs).

² Either wait until the Fully Managed Oracle Provider or edit the project file (MSBuild XML) 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 the following:

 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 the remote database.

You can specify the connection string in an external ressource, such as a configuration file

Answer 3

We use the OLEDB drivers provided by Oracle to connect to a remote Oracle database in a .net desktop application. Should work for web services too.

 String conString = "Provider=OraOLEDB.Oracle.1;User ID=username;password=password;Data Source=your_tnsname;Persist Security Info=False"; String query = "Select 2 from dual"; OleDbConnection OleDbCon = new OleDbConnection(conString); OleDbCon.Open(); OleDbCommand cmd = new OleDbCommand(query, OleDbCon); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); decimal dResult = reader.GetDecimal(0); con.Close(); return Convert.ToInt32(dResult); 

You must add proper exception handling.

Answer 4

I like to use System.Data.OracleClient. I know that it is deprecated, but the fact that it is built in makes it dead easy to use.

I also like to use the SqlDataSource object from System.Web even in applications without ASP.NET. The following is sample code. Then getting the data is as simple as calling GetDataView () and passing it to your select statement. You will need to implement GetDefaultConnectionString () and GetDefaultProviderName () yourself. The provider name is "System.Data.OracleClient", and these should start with the connection string.

Please note: since this depends on System.Web for SqlDataSource, the application will require the entire .NET Framework 4 profile (and not just the smaller client profile). Depending on what you are doing, this may or may not be a problem. You can always implement your own equivalent of SqlDataSource, but I prefer not to reinvent the wheel if it does not give me a pleasant advantage.

  /// <summary> /// Creates a SqlDataSource object using the Default connectionstring in the web.config file and returns it. /// </summary> /// <returns>An SqlDataSource that has been initialized.</returns> public static SqlDataSource GetDBConnection() { SqlDataSource db = new SqlDataSource(); db.ConnectionString = GetDefaultConnectionString(); db.ProviderName = GetDefaultProviderName(); return db; } /// <summary> /// Creates a DataView object using the provided query and an SqlDataSource object. /// </summary> /// <param name="query">The select command to perform.</param> /// <returns>A DataView with data results from executing the query.</returns> public static DataView GetDataView(string query) { SqlDataSource ds = GetDBConnection(); ds.SelectCommand = query; DataView dv = (DataView)ds.Select(DataSourceSelectArguments.Empty); return dv; } 

Creating updates / inserts / deletes is similarly easy ...

 SqlDataSource ds=GetDBConnection(); ds.InsertCommand="insert into my_table values ('5','6')"; ds.Insert();