13 Oracle Database Lite ADO.NET Provider

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

13.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()

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

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

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

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

13.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 13.1.5.1, "Using BLOB Objects in Parameterized SQL Statements"

• Section 13.1.5.2, "Query Tables With BLOB Columns"

• Section 13.1.5.3, "Read and Write Data to BLOB Objects"

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

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

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

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

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

13.1.6 Data Synchronization With the OracleSync Class

You can perform a synchronization programatically with one of the following methods:

• Section 13.1.6.1, "Using the OracleSync Class to Synchronize"

• Section 13.1.6.2, "Using the OracleEngine to Synchronize"

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

sync.SetTableSyncFlag (String pub_name,                           String tbl_name, boolean remove)

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

Void MyProgress(SyncStage stage, int Percentage)

OracleEngine.Synchronize(false)

OracleEngine.Synchronize(true)

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

OracleEngine.Synchronize(args)

String args = "S11U1/manager@myserver.mydomain.com /save /ssl /force"

13.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 13.1.6.2, "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).

The following is the signature of the CreateDatabase method:

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

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

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

13.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);
        } 

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