Skip Headers

Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 11 Oracle Database Lite ADO.NET Provider

The following sections discuss the Oracle Database Lite ADO.NET provider for Microsoft .NET and Microsoft .NET Compact Framework. The Oracle Database Lite ADO.NET provider resides in the `Oracle.DataAccess.Lite` namespace.

- Section 11.1, "Discussion of the Classes That Support the ADO.NET Provider"

- Section 11.2, "Limitations for the ADO.NET Provider"

## 11.1 Discussion of the Classes That Support the ADO.NET Provider

The Oracle Database Lite ADO.NET driver is implemented in the following DLLs:

- Windows—`Oracle.DataAccess.Lite.dll`

- Windows CE—`Oracle.DataAccess.Lite_wce.dll`

If you are building an application that uses the ADO.Net driver, you must package the driver with the application files. The assembly DLL must be located in the same directory as your application executable (`*.exe` file). Alternatively, you can add the driver to the Windows global assembly cache. See the Microsoft documentation on how to add this DLL to the Global Assembly Cache.

The following sections describe classes for the Oracle Database Lite ADO.NET provider:

- Section 11.1.1, "Establish Connections With the OracleConnection Class"

- Section 11.1.2, "Transaction Management"

- Section 11.1.3, "Create Commands With the OracleCommand Class"

- Section 11.1.4, "Maximize Performance Using Prepared Statements With the OracleParameter Class"

- Section 11.1.5, "Large Object Support With the OracleBlob Class"

### 11.1.1 Establish Connections With the OracleConnection Class

The `OracleConnection` interface establishes connections to Oracle Database Lite. This class implements the `System.data.IDBConnection` interface. When constructing an instance of the `OracleConnection` class, implement one of the following to open a connection to the back-end database:

- Pass in a full connection string as described in the Microsoft ODBC documentation for the `SQLDriverConnect` API, which is shown below:

      OracleConnection conn = new OracleConnection
            ("Data_Directory=\\orace;Database=polite;DSN=*;uid=system;pwd=manager");
      conn.Open();

- Construct an empty connection object and set the `ConnectionString` property later.

With an embedded database, we recommended that you open the connection at the initiation and leave it open for the life of the program. When you close the connection, all of the `IDataReader` cursors that use the connection are also closed.

### 11.1.2 Transaction Management

By default, Oracle Database Lite connection uses the autocommit mode. If you do not want the autocommit to be on, then you can start a transaction with the `BeginTransaction` method in the `OracleConnection` object. The `BeginTransaction` method returns a reference to the `IDbTransaction` object. Then, when finished, execute either the `Commit` or `Rollback` methods on the returned `IDbTransaction`, which either commits or rolls back the transaction. Once the transaction is completed, the database is returned to autocommit mode.

Whenever you rollback a transaction, it rolls back all the operations that you have performed before. Sometimes you need to undo the transaction to a certain point. For this scenario, you can use the Save Points functionality. Save Points allows you to rollback a transaction to a certain point. Oracle Lite supports Save Points. Within a transaction, you can set up, remove or undo any number of Save Points using SQL statements. Using save points gives you better granular control over the transaction. Within the transaction, use SQL syntax to set up, remove and undo savepoints.

For WinCE devices, Oracle Database Lite supports only one process to access a given database. When a process tries to connect to a database that is already in use, the `OracleConnectionOpen` method throws an `OracleException`. To avoid this exception being thrown, close a connection to allow another process to connect.

The following is an example in turning off autocommit for a C# application:

    OracleConnection conn = new OracleConnection ("DSN=consroot;uid=system");
    conn.Open();
    IDbTransaction trans = conn.BeginTransaction(); // Turn off AUTOCOMMIT
    OracleCommand cmd = (OracleCommand)conn.CreateCommand();
    cmd.CommandText = "create table TEST1 (c0 number)";
    cmd.ExecuteNonQuery();
    trans.Commit(); // AutoCommit is 'ON'
    cmd.Dispose();
    conn.Close();

The following is an example in turning off autocommit for a VB.NET application:

    conn = New Oracle.DataAccess.Lite.OracleConnection(("DSN=consroot;uid=system")
    conn.Open()
    IDbTransaction trans = conn.BeginTransaction()
    OracleCommand cmd = New OracleCommand (conn)
    cmd.CommandText = "create table TEST1 (c0 number)"
    cmd.ExecuteNonQuery()
    trans.Commit()
    cmd.Dispose()
    conn.Close()

### 11.1.3 Create Commands With the OracleCommand Class

The `OracleCommand` class implements the `System.Data.IDBCommand` interface. Create any commands through the `CreateCommand` method of the `OracleConnection` class. The `OracleCommand` has constructors recommended by the ADO.NET manual, such as `OracleCommand(OracleConnection conn, string cmd)`.

However, if you use the `OracleCommand` constructors, it is difficult to port the code to other platforms, such as the ODBC provider on Windows 32. Instead, create the connection and then use interface methods to derive other objects. With this model, you can either change the provider at compile time or use the reflection API at runtime.

### 11.1.4 Maximize Performance Using Prepared Statements With the OracleParameter Class

Parsing a new SQL statement can take significant time; thus, use prepared statements for any performance-critical operations. Although, `IDbCommand` has an explicit `Prepare` method, this method always prepares a statement on the first use. You can reuse the object repeatedly without needing to call `Dispose` or change the `CommandText` property.

#### 11.1.4.1 SQL String Parameter Syntax

Oracle Database Lite uses ODBC-style parameters in the SQL string, such as the `?` character. Parameter names and data types are ignored by the driver and are only for the programmer's use.

For example, assume the following table:

    create table t1(c1 int, c2 varchar(80), c3 data)

You can use the following parameters in the context of this table:

    IDbCommand cmd = conn.CreateCommand();
    cmd.CommandText  = "insert into t1 values(?,?,?);"
    cmd.Parameters.Add("param1", 5);
    cmd.Parameters.Add("param2", "Hello");
    cmd.Parameters.Add("param3", DateTime.Now);
    cmd.ExecuteNonQuery();

Note:

The relevant class names are `OracleParameter` and `OracleParameterCollection`.

### 11.1.5 Large Object Support With the OracleBlob Class

The `OracleBlob` class supports large objects. Create a new `OracleBlob` object to instantiate or insert a new BLOB object in the database, as follows:

    OracleBlob blob = new OracleBlob(conn);

Since the BLOB is created on a connection, you can use the `Connection` property of `OracleBlob` to retrieve the current `OracleConnection`.

Functions that you can perform with a BLOB are as follows:

- Section 11.1.5.1, "Using BLOB Objects in Parameterized SQL Statements"

- Section 11.1.5.2, "Query Tables With BLOB Columns"

- Section 11.1.5.3, "Read and Write Data to BLOB Objects"

#### 11.1.5.1 Using BLOB Objects in Parameterized SQL Statements

You can use the BLOB object in parameterized SQL statements, as follows:

    OracleCommand cmd = (OracleCommand)conn.CreateCommand();
    cmd.CommandText = "create table LOBTEST(X int, Y BLOB)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into LOBTEST values(1, ?)";
    cmd.Parameters.Add(new OracleParameter("Blob", blob));
    cmd.ExecuteNonQuery();

#### 11.1.5.2 Query Tables With BLOB Columns

You can retrieve the `OracleBlob` object using the data reader to query a table with a BLOB column, as follows:

    cmd.CommandText = "select * from LOBTEST";
    IDataReader rd = cmd.ExecuteReader();
    rd.read();
    OracleBlob b = (Blob)rd["Y"];

Or you can write the last line of code, as follows:

    OracleBlob b = (OracleBlob)rd.getvalue(1);

#### 11.1.5.3 Read and Write Data to BLOB Objects

The `OracleBlob` class supports reading and writing to the underlying BLOB, and retrieving and modifying the BLOB size. Use the `Length` property of `OracleBlob` to get or to set the size. Use the following functions to read and write to the BLOB, as follows:

    public long GetBytes(long blobPos, byte [] buf, int bufOffset, int len);
    public byte [] GetBytes(long blobPos, int len);
    public void SetBytes(long blobPos, byte [] buf, int bufOffset, int len);
    public void SetBytes(long blobPos, byte [] buf);

For example, the following appends data to a BLOB and retrieves the bytes from position five forward:

    byte [] data = { 0, 1, 2, 3, 4, 5, 6, 7, 8 };
     blob.SetBytes(0, data); //append data to the blob
    byte [] d = blob.GetBytes(5, (int)blob.Length - 5); //get bytes from position 5 up to the end
    blob.Length = 0; //truncate the blob completely

Use the `GetBytes` method of the data reader to read the BLOB sequentially, but without accessing it as a `OracleBlob` object. You should not, however, use the `GetBytes` method of the reader and retrieve it as a `OracleBlob` object at the same time.

## 11.2 Limitations for the ADO.NET Provider

The following are limitations to the Oracel Database Lite ADO.NET provider:

- Section 11.2.1, "Partial Data Returned with GetSchemaTable"

- Section 11.2.2, "Creating Multiple DataReader Objects Can Invalidate Each Other"

- Section 11.2.3, "Calling DataReader.GetString Twice Results in a DbNull Object"

- Section 11.2.4, "Thread Safety"

### 11.2.1 Partial Data Returned with GetSchemaTable

The Oracle Database Lite ADO.NET provider method—`GetSchemaTable`—only returns partial data. For example, it claims that all of the columns are primary key, does not report unique constraints, and returns null for `BaseTableName`, `BaseSchemaName` and `BaseColumnName`. Instead, to retrieve Oracle Database Lite meta information, use `ALL_TABLES` and `ALL_TAB_COLUMNS` instead of this call to get Oracle Database Lite meta information.

### 11.2.2 Creating Multiple DataReader Objects Can Invalidate Each Other

The Oracle Database Lite ADO.NET provider does not support multiple concurrent `DataReader` objects created from a single `OracleCommand` object. If you need more than one active `DataReader` objects at the same time, create them using separate `OracleCommand` objects.

The following example shows how if you create multiple `DataReader` objects from a single `OracleCommand` object, then the creation of `reader2` invalidates the `reader1` object.

    OracleCommand cmd = (OracleCommand)conn.CreateCommand();
    cmd.CommandText = "SELECT table_name FROM all_tables";
    cmd.Prepare();
    IDataReader reader1 = cmd.ExecuteReader();
    IDataReader reader2 = cmd.ExecuteReader();

### 11.2.3 Calling DataReader.GetString Twice Results in a DbNull Object

Calling the `GetString` method of `DataReader` twice on the same column and for the same row results in a `DbNull` object. The following example demonstrates this in that the second invocation of `GetString` results in a `DbNull` object.

    IDataReader dr = cmd.ExecuteReader();
            String st = null;
            while(dr.Read())
            {
                    st = dr.GetString (1);
                    st = dr.GetString (1);
            }

### 11.2.4 Thread Safety

To build a thread-safe program, make sure that different threads use separate `IDbCommand` and `IDataReader` objects. The `OracleConnection` and `IDbTransaction` methods can be called concurrently, except for when used to open and close the connection.