If you followed my Oracle Express installation guide, you are probably wondering how to connect to the Oracle database from inside our favorite IDE, Visual Studio. Not being one to exclude a group of developers as big as the .NET crowd, Oracle created a set of tools specifically aimed at making it easier to use Oracle with .NET applications and wrapped them inside a single installation called the Oracle Data Access Components. In this guide I’ll walk you through the task of getting these set up on your machine and connecting to an Oracle database from within Visual Studio for the first time.

Installing the Oracle Data Access Components

To get started, head over to Oracle’s web site and download the latest Oracle Data Access Components (ODAC). They should be contained within a zip file that you will need to extract to a temporary folder on your machine. Once you’ve extracted the installation files, double-click on setup.exe.

This will launch the Oracle Universal Installer. Click Next. Since Oracle Express (XE) is in version 10g and the latest ODAC is in version 11g I had to install the client version of the software. If you try to install the server version, you will get the following error:

Oracle Data Access Components can only be installed over an existing Oracle Server 11g

There may be a way to install the server version alongside Oracle 10g XE, but I wasn’t able to find it. You can download a 10g version of ODAC, but it will not have support for Visual Studio 2008 which kind of defeats the purpose of what we’re doing here.

Click Next to move on to page 3 of the installer, where you’ll provide installation location info. The installation Oracle Base destination you supply must be different than the folder for your Oracle 10g XE installation. It’s important to note that the ODAC 11g installation will use the location of specified here as the new ORACLE_HOME that all applications will use.

Click Next to move on and check the prerequisites for installing the products. If all prerequisites are met, click Next to verify which components you want to install. I recommend installing all of the ODAC components unless you only need a specified subset of the tools. After selecting the components to install and clicking Next, the following page will instruct you to run a few SQL scripts after the installation completes. This is completely optional and only necessary if you want to use Oracle to provide .NET membership and roles support. Click Next to review the ODAC installation summary, then Install to begin the installation process.

Once the installer is done, click Exit to complete the installation. Oracle Data Access Components should be installed on your machine and you’re now ready to connect to Oracle from Visual Studio. If you had any trouble with the installation process, please refer to the official installation guide. 

Connecting to Oracle from Visual Studio Server Explorer

The first, and most important, task after installing ODAC is to set up the sqlnet.ora and tnsnames.ora configuration files. These files must be placed in the %ORACLE_HOME%\Network\Admin directory. If you navigate to that directory, there should be a subdirectory called Sample which contains a sample of each file. The easiest thing to do is copy these files into the required directory and use them as a template. You do not need to configure sqlnet.ora as it already contains the basic configuration settings required. However, tnsnames.ora will need to be edited with a new connection. Here is the basic format for a connection in tnsnames.ora:

<data source alias> = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname or IP>)(PORT = <port>)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = <database service name>) 
    ) 
  )

Simply replace the fields in brackets with information about your Oracle installation. If you followed along with this guide, you might set up your connection like this:

oraclexe = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = xe) 
    ) 
  )

Here I’ve defined a connection named oraclexe which connects on localhost at port 1521 (the default port) to a service named xe (the default service name for Oracle 10g XE). You can add additional connections below oraclexe if you like. Simply copy and past the existing connection and change the necessary fields. So if you also want to be able to connect to a remote Oracle database, you would provide the host address and port number, as well as any other information required to connect to that database.

Once you’ve set up the configuration files, launch Visual Studio and open Server Explorer. Click on the Connect to Database button to set up the new Oracle database connection.

Usually Visual Studio is configured to connect to a Microsoft SQL Server database by default. To change the data source type, click Change…

In the Change Data Source wizard, select Oracle Database and choose Oracle Data Provider for .NET, which is part of the ODAC installation that we just completed, and click OK.

In the Data source name drop down list, you should see whatever Oracle connection you have set up on your machine. In our case we’ve already set up a connection in tnsnames.ora called oraclexe, so select that entry from the list.

Enter the login information in the User name and Password fields, and click the Save password checkbox to avoid having to enter login information in the future. You should, at a minimum, have a password for the SYS account if you followed my previous installation guide. If you want to learn how to create additional users, refer to the Oracle Database XE Getting Started Guide.

Finally, select an appropriate role based on the privileges that have been granted to the user. By default there will be two options, Default and SYSDBA.

Click Test Connection to verify that you have entered the credentials and settings correctly. If you have, you should see a successful connection message.

The first time I tried to connect, I received the message “ORA-12541: TNS:no listener”. This message usually means you need to either check that your listener service is running or that tnsnames.ora has the correct configuration settings. For more info, check out this tip.

An optional set of connection settings are accessible from the Apply Filters tab and Advanced… button. In the Apply Filters tab you’ll find a list of schemas available to the user. You can choose additional schemas to be displayed in the new connection once it is established from the Available schemas listbox, or remove schemas that you don’t want to display from the Displayed schemas listbox.

By clicking on the Advanced… button you can access the Advanced Properties window. Here you can set initialization, pooling, real application clusters, security, and data source properties. Also, your connection string is displayed at the bottom of the window. Since we’re just getting started with Oracle 10g XE, you probably won’t need to worry about any of these settings.

Once you’ve got a successful connection set up, click OK to establish the connection in Server Explorer and you’re set!

Wrap Up

So there you have it – a relatively painless guide to getting connected with an Oracle database from within Visual Studio. My next Oracle post will explore some of the tools and capabilities database developers now have access to inside Visual Studio. For those of you .NET developers just getting started with Oracle: don’t worry, give it time, it gets better!