Skip Headers
Oracle® Database Lite Developer's Guide
10g (10.2.0)
Part No. B15920-01
  Go To Table Of Contents
Go To Index


14 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.

A DataException is thrown if synchronization fails. Also, you must close all database connections before doing a synchronization.

14.1 Discussion of the Classes That Support the ADO.NET Provider

To use the Oracle Database Lite ADO.NET provider from your own project, add a reference to Oracle.DataAccess.Lite_wce.dll. This section describes the following classes for the Oracle Database Lite ADO.NET provider.

14.1.1 Establish Connections With the OracleConnection Class

The OracleConnection interface establishes connections to Oracle Database Lite. This class implements the 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
  • 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.

14.1.2 Transaction Management

By default, Oracle Database Lite connection uses the autocommit mode. Alternatively, you can start a transaction with the BeginTransaction method in the OracleConnection 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.

Within the transaction, use SQL syntax to set up, remove and undo savepoints.

For Microsoft Pocket PC-based 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.

14.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.

14.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. 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);


The relevant class names are OracleParameter and OracleParameterCollection.

14.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: 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.CommandText = "insert into LOBTEST values(1, ?)";
cmd.Parameters.Add(new OracleParameter("Blob", blob));
cmd.ExecuteNonQuery(); 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();;
OracleBlob b = (Blob)rd["Y"];

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

OracleBlob b = (OracleBlob)rd.getvalue(1); 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.

14.1.6 Data Synchronization With the OracleSync Class

You can perform a synchronization programatically with one of the following methods: Using the OracleSync Class to Synchronize

To programmatically synchronize databases, perform the following:

  1. Instantiate an instance of the OracleSync class.

  2. Set relevant properties, such as username, password and URL.

  3. Call the Synchronize method to trigger data synchronization.

This is demonstrated in the following example:

OracleSync sync = new OracleSync();
sync.UserName = "JOHN";
sync.Password = "JOHN";
sync.ServerURL = "mobile_server";

The attributes that you can set are described in Table 14-1.

Table 14-1 OracleSync Attributes

Attibute Description
UserName Assign a string in quotes with the name of the user for synchronization.
Password Assign a string in quotes with the password for the user.
ServerURL Assign a string in quotes with the Mobile Server host name.
ProxyHost Assign a string in quotes with the host name of the proxy server.
ProxyPort Assign a string in quotes with the port of the proxy server.
Secure Set to true if using SSL; false if not.
PushOnly If true, upload changes from the client to the server only, as download is not allowed. This is useful when the data transfer is a one way transmission from the client to server.
HighPriority Set to true if requesting a high priority synchronization.
SetTableSyncFlag Three arguments are required for SetTablesyncFlag, as follows:
sync.SetTableSyncFlag (String pub_name,                           String tbl_name, boolean remove)

Passing pub_name, null tbl_name, remove = 0 turns off syncFlag for everytable in that publication. Passing pub_name, tbl_name, remove = 1 turns on syncFlag for that specific table. Thus, you can set synchronization off for all tables, then turn on each individual table that you want to synchronize.

If you want to retrieve the synchronization progress information, set the SyncEventHandler attribute of the OracleSync class before your execute the sync.synchronize method, as follows.

sync.SetEventHandler (new OracleSync.SyncEventHandler                               (MyProgress), true); 

You pass in your implementation of the MyProgress method, which has the following signature:

Void MyProgress(SyncStage stage, int Percentage) Using the OracleEngine to Synchronize

You can synchronize with the same engine that performs the synchronization for the msync tool. You can actually launch the GUI to have the user enter information and click Synchronize or you can enter the information programmatically and synchronize without launching the GUI. Launch the msync Tool for User Input

You can launch the msync tool, so that the user can modify settings and initialize the synchronization, by executing the following:


Providing the false as the input parameter tells the engine that you are not providing the input parameters, but to bring up the msync GUI for the user to input the information. Set the Environment and Synchronize With the OracleEngine

You can set the information and call for a synchronization through the OracleEngine class without bringing up the GUI.

If you accept the default synchronization settings, provide true as the input parameter to automatically synchronize, as follows:


You can execute the synchronize method with three input parameters that define a specific server: the server name, usename and password.

OracleEngine.Synchronize("S11U1", "manager", "")

Alternatively, you can configure a string that contains the options listed in Table 14-2 with a single String input parameter and synchronize, as follows:


In the above example, the String args input parameter is a combination of the options in Table 14-2.

String args = "S11U1/ /save /ssl /force"

Include as many of the options that you wish to enable in the String.

Table 14-2 Command Line Options

Option Description
username/password@server[:port][@proxy:port] Automatically synchronize to the specified server.
/a Automatically synchronize to saved preferred server.
/save Save user info and exit.
/proxy:(proxy_server)[:port] Connect by specific proxy server and port.
/ssl Synchronize with SSL encryption.
/force Force refresh.
/noapp:(application_name) Do not synchronize specific Web-to-Go application data. Synchronize with other applications.
/nopub:(publication_name) Do not synchronize specific publication data. Synchronize with other publications.
/notable:(table_name)/notable:(odb_name).(table_name) Do not synchronize specific table data. Synchronize with other tables.
/onlyapp:(application_name) Synchronize only specific Web-to-Go application data. Do not synchronize with other applications.
/onlypub:(publication_name) Synchronize only specific publication data. Do not synchronize with other publications.
/onlytable:(table_name)/onlytable:(odbc_name). (table_name) Synchronize only specific table data. Do not synchronize with other tables.
/hp Enable high priority data synchronization.

14.1.7 Creating a Database for Testing

In a non-production environment, you may want to create a database to test your ADO.NET application against. In the production environment, the database is created when you perform the OracleEngine.Synchronize method (see Section, "Using the OracleEngine to Synchronize" for more information). However, to just create the database without synchronization, you can use the CreateDatabase method of the OracleEngine class. To remove the database after testing is complete, use the RemoveDatabase method. These methods are only supported when you install the Mobile Development Kit (MDK).


Use the CAB file provided with the MDK.

The following is the signature of the CreateDatabase method:

OracleEngine.CreateDatabase (string dsn, string db, string pwd) 

14.2 Running the Demo for the ADO.NET Provider

This release comes with sample code that illustrates the Oracle Database Lite ADO.NET provider. The demo is a timecard application for a cable technician who might install, remove, or repair service and keep track of the hours worked. To use the Oracle Database Lite ADO.NET provider from your own project, add a reference to Oracle.DataAccess.Lite_wce.dll.

Perform the following to run the demo:

  1. If you have not already done so, install the .NET Compact Framework on your device using netcfsetup.msi.

  2. Install Oracle Database Lite on your device—such as the—from the following directory:


  3. Open the ClockIn_wce.csdproj from the ADO.NET\ADOCE\Clockin_wce directory with Visual Studio.NET 2003. Make sure that the Oracle.DataAccess.Lite reference in the project points to the DLL in the ADO.NET\ADOCE directory.

  4. Select Deploy Application from the Project menu to install the ClockIn sample application on your Pocket PC device.

  5. Use the file manager to launch msql in the \OraCE directory on your device. Go to the Tools tab and click Create to create the POLITE database and its corresponding ODBC data source. Exit msql.

  6. Use the file manager to start the ClockIn demo in the \Program files directory.

Choose the job type and time from the drop down lists at the bottom of the screen and Click Add to enter a new work item and update summary on the title bar. Click on an existing work item row to remove it. You can also navigate to a different date to review past work.

Examine the MainForm.cs in the ClockIn subdirectory. Notice the following items, which demonstrate the functionality discussed in this chapter:

  1. Creating an Oracle Database Lite connection.

  2. Using prepared statements and cleaning up at program exit.

  3. Using LiteDataAdapter to retrieve data into disconnected ResultSet and delete an existing row.

  4. Using DataGrid to display data on screen.

You can make some changes to become familiar with ADO.NET development, such as:

  1. Add checking for overlapping work items and give an appropriate error.

  2. Add an ability to edit an existing work item and give arbitrary start/end times and description by clicking on a row.

  3. Add sync support to ClockIn. You need to define a primary key on the ClockIn table using a sequence.

14.3 Limitations for the ADO.NET Provider

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

14.3.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.

14.3.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";
IDataReader reader1 = cmd.ExecuteReader(); 
IDataReader reader2 = cmd.ExecuteReader();

14.3.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;
                st = dr.GetString (1);
                st = dr.GetString (1);

14.3.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.