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 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:
- 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.
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:
- 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
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:
- 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.
- Create a UniConnection object.
- Set the ConnectionString property of the UniConnection object to a string containing the part Provider=Oracle.
- Create a UniCommand object and link it to the UniConnection object.
- 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#csharp |  Copy 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 Basic | Copy 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:
- 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.
| 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:
- 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