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.
The Oracle Database Lite ADO.NET driver is implemented in the following assembly:
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 13.1.1, "Establish Connections With the OracleConnection Class"
Section 13.1.3, "Create Commands With the OracleCommand Class"
Section 13.1.4, "Maximize Performance Using Prepared Statements With the OracleParameter Class"
Section 13.1.5, "Large Object Support With the OracleBlob Class"
Section 13.1.6, "Data Synchronization With the OracleSync 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.
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()
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.
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.
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 areOracleParameter
and OracleParameterCollection
.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:
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();
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);
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.
You can perform a synchronization programatically with one of the following methods:
Note:
ADataException
is thrown if synchronization fails. Also, you must close all database connections before doing a synchronization.To programmatically synchronize databases, perform the following:
Instantiate an instance of the OracleSync
class.
Set relevant properties, such as username, password and URL. The username and password are limited to 28 characters each.
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"; sync.Synchronize();
The attributes that you can set are described in Table 13-1.
Table 13-1 OracleSync Attributes
Attibute | Description |
---|---|
|
Assign a string in quotes with the name of the user for synchronization. |
|
Assign a string in quotes with the password for the user. |
|
Assign a string in quotes with the Mobile Server host name. |
|
Assign a string in quotes with the host name of the proxy server. |
|
Assign a string in quotes with the port of the proxy server. |
|
Set to true if using SSL; false if not. |
|
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. |
|
Set to true if requesting a high priority synchronization. |
|
Three arguments are required for sync.SetTableSyncFlag (String pub_name, String tbl_name, boolean remove) Passing |
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)
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.
You can launch the msync
tool, so that the user can modify settings and initialize the synchronization, by executing the following:
OracleEngine.Synchronize(false)
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.
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:
OracleEngine.Synchronize(true)
You can execute the synchronize method with three input parameters that define a specific server: the server name, username and password.
OracleEngine.Synchronize("S11U1", "manager", "myserver.mydomain.com")
Alternatively, you can configure a string that contains the options listed in Table 13-2 with a single String
input parameter and synchronize, as follows:
OracleEngine.Synchronize(args)
In the above example, the String
args
input parameter is a combination of the options in Table 13-2.
String args = "S11U1/manager@myserver.mydomain.com /save /ssl /force"
Include as many of the options that you wish to enable in the String
.
Table 13-2 Command Line Options
Option | Description |
---|---|
|
Automatically synchronize to the specified server. |
|
Automatically synchronize to saved preferred server. |
|
Save user info and exit. |
|
Connect by specific proxy server and port. |
|
Synchronize with SSL encryption. |
|
Force refresh. |
|
Do not synchronize specific Web-to-Go application data. Synchronize with other applications. |
|
Do not synchronize specific publication data. Synchronize with other publications. |
|
Do not synchronize specific table data. Synchronize with other tables. |
|
Synchronize only specific Web-to-Go application data. Do not synchronize with other applications. |
|
Synchronize only specific publication data. Do not synchronize with other publications. |
|
Synchronize only specific table data. Do not synchronize with other tables. |
|
Enable high priority data synchronization. |
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).
Note:
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)
The following are limitations to the Oracel Database Lite ADO.NET provider:
Section 13.2.2, "Creating Multiple DataReader Objects Can Invalidate Each Other"
Section 13.2.3, "Calling DataReader.GetString Twice Results in a DbNull Object"
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.
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();
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); }
For an example of how to develop an ADO.NET application, see Chapter 20, "Tutorial for Building Mobile Applications for Windows CE".