dotConnect Universal Documentation
In This Topic
    Using dotConnect Universal with dotConnect for Oracle
    In This Topic

    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.

    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:

    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