If you need to choose which data provider for Oracle to use with dotConnect Universal, we recommend using dotConnect for Oracle because of its obvious advantages. This article provides a brief overview of dotConnect for Oracle, describes some useful features and helps you get started quickly.

• Overview
• Compatibility
• Deployment
• Typical Connection String
• Quick Start Steps
• Special Features
• UniSQL Notes
• UniDbType Map
• Additional Information

Overview

dotConnect for Oracle is a full-featured 100% managed ADO.NET data provider for the .NET Framework. dotConnect for Oracle is acknowledged as the leading ADO.NET data provider for Oracle, and it has many advantages over any rival product. The main features of dotConnect for Oracle are:

• Direct mode to access without Oracle client
• High performance
• Easy deployment
• Comprehensive support for the latest versions of Oracle server
• Advanced design-time editors and wizards

The full list of dotConnect for Oracle features can be found on the product page.

Developer Edition of dotConnect Universal includes dotConnect for Oracle as a bundled provider. The Trial Edition includes trial dotConnect for Oracle Data Provider as well. For Express Edition of dotConnect Universal, dotConnect for Oracle Data Provider should be purchased and installed separately.

Compatibility

dotConnect Universal is compatible with dotConnect for Oracle version 5.00 and above.

Deployment

To deploy applications written with dotConnect Universal, copy the run-time assemblies Devart.Data.Universal.dll and Devart.Data.Universal.Oracle.dll to target machine. If you are using the standalone (not bundled) dotConnect for Oracle Data Provider, you will also have to deploy its Devart.Data.Oracle.dll assembly.

These assemblies can be registered at the Global Assembly Cache (GAC) for the appropriate .NET Framework or placed in the folder of your application.

For web projects, you may also need to deploy the Devart.Data.Universal.Web.dll, Devart.Data.Oracle.Web.dll and App_Licenses.dll assemblies. The *.Web.dll assemblies are required for using the ASP.NET Provider Model, and the App_Licenses.dll assembly is a part of licensing mechanism.

Web projects are usually run as partially trusted code. The following permissions are required by dotConnect Universal and dotConnect for Oracle:

• in Direct mode - medium trust plus ReflectionPermission and SocketPermission;
• in OCI mode - medium trust plus ReflectionPermission, RegistryPermission, EnvironmentPermission for PATH environment variable, FileIOPermission for Oracle Client

Connection String

Suppose you want to connect in OCI mode to an Oracle server with TNS name oraserver, with the user name scott and the password tiger. In this case your connection string would look like this:

Provider=Oracle;data source=oraserver;user=scott;password=tiger

Suppose you want to connect in Direct mode to an Oracle server running on the host 192.168.0.1, with system identifier orcl, and the port to connect is 1251. In the Direct mode the meaning of the Data Source parameter is different. It determines IP address or host name of the server. The whole connection string looks as follows:

Provider=Oracle;direct=true;data source=192.168.0.1;port=1521;sid=orcl;user=scott;password=tiger

The following table enumerates most important connection string parameters.

Quick Start Steps

To get up and running quickly, you have to perform the following steps:

1. Embed licensing information into your application. If you use bundled dotConnect for Oracle version, you need only dotConnect Universal licensing. If you use a standalone dotConnect for Oracle installation, embed the licensing information about both dotConnect Universal and dotConnect for Oracle data providers. For more details on licensing refer to the Licensing article. For bundled providers it may be enough to drop a UniConnection component onto a form designer to embed the licensing information.
2. Create a UniConnection object.
3. Set the ConnectionString property of the UniConnection object to a string containing the part Provider=Oracle.
4. Create a UniCommand object and link it to the UniConnection object.
5. Open the connection.

That's all you need to start executing queries on the server. Below is a code fragment that illustrates steps 2-5. It also shows how to perform INSERT and SELECT operations.

UniConnection connection = new UniConnection("Provider=Oracle;direct=true;data source=192.168.0.1;port=1521;sid=orcl;user=scott;password=tiger");
try {
  UniCommand cmd = connection.CreateCommand();
  cmd.CommandText = "INSERT INTO dept (DeptNo, Dname, Loc) VALUES (:DeptNo, :DName, :Loc)";
  UniParameter parameter = cmd.Parameters.Add("DeptNo", UniDbType.Int);
  parameter.Value = 150;
  parameter = cmd.Parameters.Add("DName", UniDbType.VarChar);
  parameter.Value = "DEPTNAME";
  cmd.Parameters.Add(parameter);
  parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar);
  parameter.Value = "LOCATION";
  cmd.Parameters.Add(parameter);
  connection.Open();
  cmd.ExecuteNonQuery();
}
finally {
  connection.Close();
}

...

DataSet dataSet = new DataSet();
UniDataAdapter da = new UniDataAdapter("SELECT * FROM dept",
  "Provider=Oracle;direct=true;data source=192.168.0.1;port=1521;sid=orcl;user=scott;password=tiger");
da.Fill(dataSet, "Dept");

Dim connection As Devart.Data.Universal.UniConnection = New _
  Devart.Data.Universal.UniConnection("Provider=Oracle;direct=true;data source=192.168.0.1;port=1521;sid=orcl;user=scott;password=tiger")
Try
  Dim cmd As UniCommand = connection.CreateCommand()
  cmd.CommandText = "INSERT INTO dept (DeptNo, DName, Loc) VALUES (:DeptNo, :DName, :Loc)"
  Dim parameter As Devart.Data.Universal.UniParameter = cmd.Parameters.Add("DEPTNO", UniDbType.Int)
  parameter.Value = 150
  parameter = cmd.Parameters.Add("DName", UniDbType.VarChar)
  parameter.Value = "DEPTNAME"
  parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar)
  parameter.Value = "LOCATION"
  connection.Open()
  cmd.ExecuteNonQuery()
Finally
  connection.Close()
End Try

...

Dim dataSet As DataSet = New DataSet()
Dim da As UniDataAdapter = New UniDataAdapter("Provider=Oracle;direct=true;data source=192.168.0.1;port=1521;sid=orcl;user=scott;password=tiger")
da.Fill(dataSet, "Dept")

Special Features

dotConnect Universal supports handling Oracle BLOB, CLOB, and NCLOB field types. For detailed information on how to work with BLOB objects, refer to the Working with BLOB Data topic.

dotConnect Universal supports the Oracle NUMBER type. Use the UniDecimal structure reference to find out how to manage big numbers on client side.

dotConnect Universal supports Oracle cursos. For detailed information on how to handle cursors, refer to the Working with Cursors topic.

It is also possible to take advantage of special network capabilities of dotConnect for Oracle by setting up the connection string parameters. This way you can enable:

• Direct mode
• Integrated Security
• SYSDBA, SYSOPER privileges
• Alternate Homes

UniSQL Notes

dotConnect for Oracle supports all features of UniSQL. Use Oracle predefined macro to build conditional UniSQL statements. For more information refer to the UniSQL topic.

In some old versions of Oracle server SQL syntax for OUTER JOINs differs from common standard. UniSQL allows to do handle the difference easily with {oj ...} construct:
{oj Outer_Join_Expression}

UniDbType Map

The following table shows how the UniDbType enumeration maps to the Oracle database type.

Additional Information

For more information on dotConnect for Oracle Data Provider please visit the following locations:

• Product home page at www.devart.com/dotconnect/oracle
• Product downloads page at www.devart.com/dotconnect/oracle/download.html to obtain the latest documentation
• Discussion forum at www.devart.com/forums/viewforum.php?f=1 to ask for help or report a bug

See Also

Using dotConnect Universal with SQL Client  | Using dotConnect Universal with dotConnect for MySQL  | Using dotConnect Universal with dotConnect for PostgreSQL  | Using dotConnect Universal with dotConnect for SQLite  | Working with BLOB Data