dotConnect Universal Documentation
Using dotConnect Universal with dotConnect for Oracle
support@devart.com

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

dotConnect for Oracle is a full-featured 100% managed ADO.NET data provider for the .NET Framework and the .NET Compact 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:

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

The Professional 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 Standard Edition of dotConnect Universal, the 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:

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

Please note, that the dotConnect Universal Mobile connects to the Oracle database only in the Direct mode, and you should omit the "Direct" connection string parameter in this case.

The following table enumerates most important connection string parameters.

Name Description
Connect Mode Allows to open a session with administrative privileges SYSDBA or SYSOPER.
Connection Lifetime When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.
Connection Timeout Time (in seconds) to wait while trying to establish a connection before terminating the attempt and generating an error. A value of 0 indicates no limit. The default value is 15 seconds.
Data Source -or- Server -or- Host The name of TNS alias of Oracle database to which to connect. For more information refer to Server property.
Direct If true, dotConnect for Oracle Data Provider can operate without an Oracle client installed. The default value is false.
Home The Oracle Home that will be used.
Max Pool Size The maximum number of connections allowed in the pool. Setting the Max Pool Size value of the ConnectionString can affect performance. The default value is 100.
Min Pool Size The minimum number of connections allowed in the pool. The default value is 0.
Password The password for the Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication)
Pooling If true, by default, the UniConnection object is drawn from the appropriate pool or is created and added to the appropriate pool.
Port Number of a port to communicate with listener on the server to use in Direct mode. The default value is 1521.
SID -or- Service Name Name of listener service running on the server to use in Direct mode. The default value is empty string.
Unicode Specifies whether the dotConnect for Oracle Data Provider uses UTF16 mode API calls. The default value is false.
User ID -or- User The Oracle login account. Leave blank if you want to use Integrated Security connections (OS authentication).

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.

C#csharpCopy Code
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");
Visual BasicCopy Code
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:

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.

UniDbType Oracle Type
Array VARRAY
BigInt NUMBER
Binary RAW
Bit NUMBER(p) [p<10]
Blob LOB
Boolean NUMBER
Byte NUMBER(p) [p<10]
Char CHAR
Clob CLOB
Currency NUMBER
Cursor REF CURSOR
Date DATE
DateTime DATE
Decimal NUMBER
Double NUMBER(p,s)[p>=10 or S>0]
Guid VARCHAR2
Int NUMBER(p) [p<10]
IntervalDS INTERVAL DAY TO SECOND
IntervalYM INTERVAL YEAR TO MONTH
NChar NCHAR
NClob NCLOB
NVarChar NVARCHAR
Object One of the Oracle object types defined by the user
Single An Oracle 10g BINARY_FLOAT
SmallInt NUMBER(p) [p<10]
TinyInt NUMBER(p) [p<10]
Time DATE
TimeStamp TIMESTAMP
VarChar VARCHAR
Xml XMLTYPE

Additional Information

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

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