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.
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.
dotConnect Universal is compatible with dotConnect for Oracle version 5.00 and above.
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:
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). |
To get up and running quickly, you have to perform the following steps:
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.
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:
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}
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 |
For more information on dotConnect for Oracle Data Provider please visit the following locations:
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