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

Previous
Previous
Next
Next
 

6 Oracle Database Lite 10g ADO.NET Provider

This document discusses 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.

The topics that are discussed in this document are the following:

6.1 Classes

This section describes the classes in the Oracle Database Lite 10g ADO.NET provider.

6.1.1 OracleConnection

This is the primary interface to establish a connection to Oracle Database Lite. This class implements the System.data.IDBConnection interface. You can pass an ODBC data source name when constructing an instance of the OracleConnection class. For example:

IDBConnection conn = new OracleConnection ("POLITE");
 
conn.Open();

In a general case, it is possible to pass a full connection string as described in Oracle Database Lite ODBC documentation for the SQLDriverConnect API. For example:

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

You can also construct an empty connection object and set ConnectionString property later.

With an embedded database, it's recommended to open the connection at the beginning and leave it open for the lifetime of the program. Note that closing the connection will also close all the IDataReader cursors using it.

6.1.2 Transaction Management

By default, Oracle Database Lite connection is in autocommit mode. To begin a transaction, use the BeginTransaction() method in the OracleConnection object. Commit or Rollback on the returned IDbTransaction will do the requested action and return the database to autocommit mode. You can use SQL syntax to set up, remove and undo savepoints within a transaction.

For Microsoft Pocket PC-based devices, Oracle Database Lite only supports one process accessing a given database. When a process tries to connect to a database in use, the OracleConnectionOpen method will throw an OracleException. You can temporarily close a connection to allow another process to connect.

6.1.3 OracleCommand

The OracleCommand class implements the System.Data.IDBCommand interface. The recommended way to create commands is through the CreateCommand() method of the OracleConnection class. OracleCommand does have constructors recommended by the ADO.NET manual, for example OracleCommand(OracleConnection conn, string cmd).

However this use will make it difficult to port the code to, for example, the ODBC provider on Windows 32. Creating a connection and then using interface methods to derive other objects will make changing the provider trivial at compile time (or even using reflection API at runtime).

6.1.4 OracleParameter and Prepared Statements

Parsing a new SQL statement takes a significant time. It's important to use prepared statements for any performance-critical operations. Although, IDbCommand has an explicit Prepare method, a statement will also be prepared on the first use. Just reuse the object repeatedly without calling Dispose or changing the CommandText property.

6.1.4.1 Parameters

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

Example:

Let us assume that you have created the following table:

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

You can use the following parameters in the context of the table that you have created.

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();

The relevant class names are OracleParameter and OracleParameterCollection.

6.1.5 OracleBlob and Large Object Support

Oracle Database Lite 10g includes in its classes the OracleBlob class to support large objects. Currently, the Oracle Database Lite ADO.NET implementation supports only the BLOB data type. Create a new OracleBlob object to instantiate (or insert) a new BLOB object in the database, as follows:

OracleBlob blob = new OracleBlob(conn);

This object can be used in the same way as the objects for the other datatypes. You can use it 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();

The Oracle Blob object also can be retrieved using the data reader to query a table with a BLOB column:

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

The OracleBlob class supports reading and writing to the underlying BLOB, as well as retrieving and modifying the BLOB's size. Use the Length property of OracleBlob to get or to set its size and 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:

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

You can use the Connection property of OracleBlob to retrieve the current OracleConnection. You can also 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.

6.1.6 OracleSync and Data Synchronization

To programmatically synchronize databases, you can use the OracleSync class. Instantiate an instance of the OracleSync class, set relevant properties, and call the Synchronize method to trigger data synchronization.

For example,

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

If you want to get Synchronization Progress information, you must set the SyncEventHandler attribute of the OracleSync class.

For example,

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

The MyProgress method must have the following signature.

Void MyProgress(SyncStage stage, int Percentage)

The Oracle Database Lite ADO.NET provider offers basic support for synchronization with the Oracle server database through the launching of the mSync tool. To bring up the mSync tool's user interface and to enable the user to modify settings before doing a synchronization, call:

OracleEngine.synchronize(false)

If the settings are already correct and you want to do an automatic synchronization, call:

OracleEngine.synchronize(true)

To do a regular synchronization with a specific server, you can use the following:

OracleEngine.synchronize("S11U1", "manager", "myserver.mydomain.com")

Finally, you can call the following:

OracleEngine.synchronize(args)


Note:

Construct "args" using the options that are listed in Table 6-1.

Table 6-1 lists the command line options and "args" (or arguments) that are recognized.

Table 6-1 Command Line Options

Option Description
username/password@server[:port][@proxy:port] Automatically synchronize to the specified server.
/a Automatically sync 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.
/cast5 Synchronize with CAST5 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.
/high_priority Enable high priority data synchronization.

DataException will be thrown if synchronization fails. Note that you need to close all database connections before doing a synchronization.

6.2 Running the Demo

This release comes with a sample code demo that illustrates working code using the Oracle Database Lite ADO.NET provider. To run the demo, follow these steps:

  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, for example olite.us.pocket_pc.arm.CAB from the following directory:

    &fmv615;\Mobile\Sdk\wince\Pocket_PC\cabfiles

  3. Open 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.

The demo is a minimalist timecard application for a cable technician who might install, remove, or repair service and keep track of the hours worked. 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's row to remove it. You can also navigate to a different date to review past work (change date on the device to create some work items first).

Examine MainForm.cs in ClockIn subdirectory. Pay special attention to the following items:

  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.

Now make some changes to become familiar with ADO.NET development:

  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 will need to define a primary key on ClockIn table (use a sequence).

To use the Oracle Database Lite ADO.NET provider from your own project, add a reference to Oracle.DataAccess.Lite_wce.dll.

6.3 Limitations

In this release of the Oracle Database Lite ADO.NET provider, GetSchemaTable only returns partial data. For example, it claims that all the columns are primary key, doesn't report unique constraints, and returns null for BaseTableName, BaseSchemaName and BaseColumnName. It is recommended that you use ALL_TABLES and ALL_TAB_COLUMNS instead of this call to get Oracle Database Lite meta information.

6.3.1 Thread Safety

To build a thread-safe program, make sure that different threads use different IDbCommand and IDataReader objects. The OracleConnection and IDbTransaction methods can be called concurrently, except for opening and closing the connection.