How to Connect to Oracle DB from Visual Studio?

In this post, we will explain How you can connect to Oracle DB from Visual Studio step by step.

---

Connect to Oracle DB from Visual Studio step by step

As a Software developer, you should be aware of how you can connect to many kinds of databases like SQL Server, Oracle … etc.

Unfortunately, some of these databases like Oracle require installing and configuring some dependencies to can establish a connection to Oracle Database from Visual Studio properly.

Connect to Oracle DB from Visual Studio Prerequisites

To connect to Oracle DB from Visual Studio, you have to do the following:

1. Download and install Oracle Data Access Client and Oracle Data Provider for .Net.
2. Configure tnsnames.ora file for Visual Studio.
3. Establish a connection to Oracle Database from Visual Studio.

---

1) Download and install Oracle Client and Oracle Data Provider for .Net

As a prerequisite to connect to Oracle DB from Visual Studio, you have first to make sure that the Oracle Instant Client and Oracle Data Provider for .Net (x64) have been installed in your local machine environment as the following:

1. Download and install the Oracle Instant Client for Microsoft Windows (x64) from Instant Oracle Client Downloads for Microsoft Windows (x64)
2. Download Oracle Data Provider for .NET (x64).
3. Install Oracle Data Provider as mentioned at How to install Oracle Data Provider for Visual Studio.

Alternatively, you can download and install 64-bit Oracle Data Access that already includes Oracle Data Provider for .NET, Oracle Developer Tools for Visual Studio

2) Configure tnsnames.ora file for Visual Studio

After installing the ODAC, you have to configure the tnsnames.ora file to can connect to Oracle Database from Visual Studio properly by doing the following:

1. Locate the Admin folder in the Oracle Installation path Ex: F:\app\melqassas\product\11.2.0\client_1\Network\Admin\

If you don’t know the right installation path for Oracle Client Data Access Installation, you can find it as mentioned at Get the installed Oracle Client Path

2. In the ADMIN folder, Open the “tnsnames.ora” file to edit it using NotePad.

Note: If tnsnames.ora file was not found in the ADMIN folder, Go to Sample Folder > Copy tnsnames.ora file and paste it outside the Sample folder at the Admin folder.

3. Copy and paste the below connection string to your “tnsnames.ora” file.

Alias=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ServerIP) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =ServiceName)
)
)

4. Set the Alias name to the server hostname or as you prefer (Eg:PROD).

Note: This value will be set to the Server name at “Add Connection” dialog.

5. Set the HOST parameter with your Oracle DB Server IP.
6. Set the SERVICE_NAME as it’s configured in the Oracle Server.
   • Right-Click on the Connection Name > Click on Properties.
   • Copy the Service Name and st it as a value for SERVICE_NAME in tnsnames.ora file.

7. Save the tnsnames.ora file.
8. The final connection string in the tnsnames.ora file should look like the below

PROD=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.200.100) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =ERP)
)
)

If you set the Host value to the server hostname instead of the Server IP, so in this case, you should add a local DNS entry to the hosts file to let the Server IP point to Server hostname. For more details, please check How to configure Hosts file.

3) Configure Oracle Data Source Connection in Visual Studio

1. Open Visual Studio > Tools Menu > Select Connect to Database.

2. Here’s the following “Choose Data Source” dialog should be shown.
3. Select Oracle DB and for data provider select .Net Framework Data Provider for Oracle > Click Continue.

4. Here’s the following “Add Connection” dialog should be shown.

5. Set the Server name in “Add Connection” dialog, as it’s configured at the connection string in the tnsnames.ora file.

6. Provide the Username and Password > Test Connection > OK.
7. Great, the Oracle connection should be established successfully now, and you will be able to Connect to Oracle DB from Visual Studio properly.

---

Conclusion

In conclusion, we have learned how to connect to Oracle DB from Visual Studio by installing Oracle Data Access Client and configuring the TNSNAMES.ORA file.

Applied To

• Visual Studio 2017.
• Visual Studio 2015.
• Visual Studio 2013.
• Visual Studio 2010.

You might also like to read

• Read BLOB data in Oracle DB In ASP.NET.
• OCIEnvCreate failed with return code -1.
• Changing NLS_LANGUAGE Oracle Parameter via C# in ASP.NET
• OLE DB Oracle provider is not registered on the local machine.
• ORA-12154: TNS:could not resolve the connect identifier specified

Have a Question?

If you have any related questions, please don’t hesitate to ask deBUG.to Community.