This tutorial describes how to connect to Oracle server.

In this walkthrough:

• Requirements
• General information
• Creating OracleConnection
• Opening connection
• Closing connection
• Modifying connection
• Additional information

Requirements

In order to connect to Oracle server you need the server itself running, dotConnect for Oracle installed and IDE running. If you have Oracle Client Software installed and want to use it, you need to know TNS alias name, login and password. If you do not wish to use OCI, you have to know host name or IP address, SID (or ServiceName), port, login and password.

Note that if you do not use design-time (specifically, if you do not place the OracleConnection component from the toolbox on a form designer), you have to embed licensing information manually. This is described in the Licensing topic.

General information

To establish a connection to server you have to provide some connection parameters to dotConnect for Oracle. This information is used by the OracleConnection component to find the server and login with credentials of your account. The parameters are represented as connection string. You can compose the connection string manually or have dotConnect for Oracle construct it for you.

There are two ways to connect to server: with and without Oracle Client Interface. This is controlled by Direct property. It indicates whether the Oracle Client Interface will be used for connecting to server. By default Direct mode is disabled to preserve maximal functionality. Switch to Direct mode if you want to work in a system without Oracle Client Software installed.

Note: In order to use OCI mode, you must have the Oracle Client of the same bitness as your application. For 32bit application, you must have 32bit Oracle Client installed, and for 64bit application you must have 64bit Oracle Client installed.

Creating OracleConnection

Design time creation

Note that design-time connection creation, described here, is applicable only to Windows Forms applications for Full .NET Framework.

The following assumes that you have IDE running, and you are currently focused on a form designer.

1. Open Toolbox and find OracleConnection component in dotConnect for Oracle category.
2. Double-click the component. Note that new object appears on the designer underneath the form. If this is first time you create OracleConnection in this application, it is named oracleConnection1.
3. Click on the oracleConnection1 object and press F4 to focus on object's properties.
4. If you connect through OCI, in the Server property provide TNS alias of the server.
5. If you use Direct mode, perform the following assignments:
   • set Direct to true
   • in the Server property provide DNS name or IP address of the computer where Oracle server is running
   • in the Sid property provide the name of the Oracle database instance
   • In the Port property specify the port that the server listens to (usually this is 1521).
6. In the UserId property specify your login. For example, scott.
7. In the Password property specify your password. For example, tiger.
8. Note that as you assign values to these properties the ConnectionString property is automatically updated to reflect your settings. Now it contains something like User Id=scott;Password=tiger;Server=OraServer; (example given for OCI mode).

Run time creation

Same operations performed in runtime look as follows (note that you have to add references to Devart.Data.Oracle.dll and Devart.Data.dll assemblies for Full .NET Framework or install the Devart.Data.Oracle NuGet package for .NET Core):

using Devart.Data.Oracle;
...
OracleConnection oracleConnection1 = new OracleConnection();
oracleConnection1.Server = "OraServer";
oracleConnection1.UserId = "scott";
oracleConnection1.Password = "tiger";

Imports Devart.Data.Oracle
...
Dim oracleConnection1 As OracleConnection = New OracleConnection()
oracleConnection1.Server = "OraServer"
oracleConnection1.UserId = "scott"
oracleConnection1.Password = "tiger"

Or, for the Direct mode:

using Devart.Data.Oracle;
...
OracleConnection oracleConnection2 = new OracleConnection();
oracleConnection2.Direct = true;
oracleConnection2.Server = "192.168.0.100";
oracleConnection2.Sid = "ORACLE18c";
oracleConnection2.Port = 1521;
oracleConnection2.UserId = "scott";
oracleConnection2.Password = "tiger";

Imports Devart.Data.Oracle
...
Dim oracleConnection2 As OracleConnection = New OracleConnection()
oracleConnection2.Direct = True
oracleConnection2.Server = "192.168.0.100"
oracleConnection2.Sid = "ORACLE18c"
oracleConnection2.Port = 1521
oracleConnection2.UserId = "scott"
oracleConnection2.Password = "tiger"
oracleConnection2.Password = "tiger"

You can do this all in single assignment. It actually does not matter whether connection string is assigned directly or composed with particular properties. After you assign a value to ConnectionString property all other properties are populated with parsed values. So you can choose what is more convenient for you.

oracleConnection1.ConnectionString = "User Id=scott;Password=tiger;Server=OraServer;";

oracleConnection2.ConnectionString = "Direct=True;User Id=scott;Password=tiger;Server=192.168.0.100;SID=ORACLE18c;Port=1521;";

oracleConnection1.ConnectionString = "User Id=scott;Password=tiger;Server=OraServer;"

oracleConnection2.ConnectionString = "Direct=True;User Id=scott;Password=tiger;Server=192.168.0.100;SID=ORACLE18c;Port=1521;"

Additional Features of the Server Parameter

If you are connecting in the Direct mode, the Server connection property (or the Server connection string parameter) allows providing not only the DNS name or IP address of the server, but also a number of other settings. You can assign a full TNS name descriptor to it or use a shortened form, which allows you to specify the secure protocol to use, port, service name or SID. The shortened form allows you to make connection string shorter, while maintaining readability and clarity.

For example, here is how you assign a TNS name descriptor to the Server parameter in a connection string:

Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcps)(HOST=OracleSSL)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=orcl))(SECURITY=(SSL_SERVER_CERT_DN="C=UA,O=Devart,OU=DevartSSL,CN=TestSSL")));

Alternative, shortened form can be one of the following:

<protocol>://<domain_name_or_IP_address>:<port>:<SID> or <protocol>://<domain_name_or_IP_address>:<port>/<Service_name>

Here protocol can be either tcps for SSL connections or ssh for SSH connections. For other cases, the <protocol>:// part is omitted.

Every part except the <domain_name_or_IP_address> is optional, and every part except protocol can be specified via it's own connection string parameter.

For example:

Server=192.168.10.137:1521/ORACLE19

Using connection string builder

If you decide to setup a connection by assigning values to several properties, consider using the the OracleConnectionStringBuilder class. It has all of the possible connection settings exposed as properties, thus allowing you to customize the connection at full extent. The following example demonstrates how to compose a more complex connection string:

OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Direct = true;
oraCSB.Server = "192.168.0.1";
oraCSB.Port = 1251;
oraCSB.Sid = "OracleSid";
oraCSB.UserId = "scott";
oraCSB.Password = "tiger";
oraCSB.MaxPoolSize = 150;
oraCSB.ConnectionTimeout = 30;
OracleConnection myConnection = new OracleConnection(oraCSB.ConnectionString);

Dim oraCSB As OracleConnectionStringBuilder = New OracleConnectionStringBuilder
oraCSB.Direct = true
oraCSB.Server = "192.168.0.1"
oraCSB.Port = 1251
oraCSB.Sid = "OracleSid"
oraCSB.UserId = "scott"
oraCSB.Password = "tiger"
oraCSB.MaxPoolSize = 150
oraCSB.ConnectionTimeout = 30
Dim myConnection As OracleConnection = New OracleConnection(oraCSB.ConnectionString)

Note that in this example we used OracleConnection constructor that accepts connection string as argument.

For the information on arguments are allowed in the connection string, refer to the description of the OracleConnection.ConnectionString property.

Opening connection

Opening a connection is as simple as that:

oracleConnection1.Open();

OracleConnection1.Open()

Of course, OracleConnection1 must have a valid connection string assigned earlier. When you call Open, dotConnect for Oracle tries to find the host and connect to server. If any problem occurs it raises an exception with brief explanation on what is wrong. If no problem is encountered dotConnect for Oracle tries to establish the connection during ConnectionTimeout interval. Finally, when connection is established, the Open method returns and State property is changed to Open.

In design time you can connect to server in few steps:

1. Right-click on oracleConnection1 object in designer.
2. Select Connect from the popup menu.
3. In the dialog window provide necessary logon information.
4. Click the Connect button to establish connection.

Or you can simply change the State property to Open in the Properties window to establish a connection using the current connection string.

Closing connection

To close a connection call its Close method, or set its State property to Closed.

The following example summarizes aforementioned information and shows how to create, setup, open, use and then close the connection.

OracleConnection myConn = new OracleConnection();
myConn.ConnectionString = "User Id=scott;Password=tiger;Server=OraServer;";
myConn.Open();
MessageBox.Show(myConn.ServerVersion);
myConn.Close();

Dim myConn As OracleConnection = New OracleConnection()
myConn.ConnectionString = "User Id=scott;Password=tiger;Server=OraServer;"
myConn.Open()
MessageBox.Show(myConn.ServerVersion)
myConn.Close()

The sample code connects to a server, shows its version and then closes the connection. This actually is rare usage, because in real applications connections are used by other objects like OracleCommand, OracleDataTable and others. For more information on this, please see the corresponding tutorials or the reference information.

Modifying connection

You can modify connection by changing properties of OracleConnection object. Keep in mind that while some of the properties can be altered freely, most of them close connection when new value is assigned. For example, if you change Server property, it gets closed immediately, and you have to reopen it manually.

Additional information

dotConnect for Oracle has wide set of features you can take advantage of. The following list enumerates some of them so you can explore the advanced techniques to achieve better performance, balance network load or enable additional capabilities.

• Asynchronous connection opening
• Connection pooling (refer to MSDN documentation for information about connection pooling)

See Also

Getting Started  | Connecting to Oracle with Proxy Authentication  | Connecting to Oracle using OracleCredential  | SSL Support in Direct Mode  | OracleConnection Class  | Connecting to Oracle Autonomous Database