iAnywhere.com - Connecting to a database

Connecting to a database

UltraLite applications must connect to a database before carrying out operations on the data in it. This section describes how to connect to an UltraLite database.

Using the Connection object

The following properties of the Connection object govern global application behavior.

Commit behavior By default, UltraLite.NET applications are in autocommit mode. Each insert, update, or delete statement is committed to the database immediately. You can use ULConnection.BeginTransaction() to build transactions into your application.
For more information, see Transaction processing in UltraLite.
User authentication You can change the user ID and password for the application from the default values of DBA and SQL by using methods to Grant and Revoke connection permissions. Each application can have a maximum of four user IDs.
For more information, see User authentication.
Synchronization A set of objects governing synchronization is accessed from the Connection object.
For more information, see Synchronizing UltraLite applications.
Tables UltraLite tables are accessed using methods of the Connection object.
For more information, see Accessing and manipulating data with the Table API.
Commands A set of objects is provided to handle the execution of dynamic SQL statements and to navigate result sets.
For more information, see Accessing and manipulating data using dynamic SQL.

For more information, see ULConnection class (iAnywhere.Data.UltraLite namespace) or Connection class (iAnywhere.UltraLite namespace).

Each Connection and all objects created from it should be used on a single thread. If your application requires multiple threads accessing the UltraLite database, each thread requires a separate connection.

To connect to an UltraLite database

Create and initialize a DatabaseManager object.

This step is not required if using the iAnywhere.Data.UltraLite namespace.

The DatabaseManager object is at the root of the object hierarchy. You create only one DatabaseManager object per application. It is often best to declare the DatabaseManager object as global to the application.
```
// iAnywhere.UltraLite namespace
DatabaseManager dbMgr = new DatabaseManager();
```
For more information, see DatabaseManager class (iAnywhere.UltraLite namespace).
Declare a Connection object.

Most applications use a single connection to an UltraLite database and leave the connection open. Multiple connections are only required for multi-threaded data access. For this reason, it is often best to declare the Connection object as global to the application.
```
// iAnywhere.Data.UltraLite namespace
ULConnection conn;
```
```
// iAnywhere.UltraLite namespace
Connection conn;
```

Open a connection to an existing database, or create a new database if the specified database file does not exist.

Most UltraLite applications deploy a schema file rather than a database file, and let UltraLite create the database file on the first connection attempt. Thus, the following code attempts to connect to an existing database. If the database file does not exist, the application creates a database file.

// iAnywhere.Data.UltraLite namespace
ULConnectionParms parms = new ULConnectionParms();
// specify the location of the database file
parms.DatabaseOnDesktop = "mydata.udb";
// specify the location of the schema file
parms.SchemaOnDesktop = "mydata.usm";
conn = new ULConnection( parms.ToString() );
conn.OpenWithCreate();

// iAnywhere.UltraLite namespace
ConnectionParms parms = new ConnectionParms();
// specify the location of the database file
parms.DatabaseOnDesktop = "mydata.udb";
try {
  conn = dbMgr.OpenConnection( parms );
} catch( SQLException econn ) {
  if( econn.GetErrorCode() ==
    SQLCode.SQLE_ULTRALITE_DATABASE_NOT_FOUND ){
    CreateParms parms = new CreateParms();
    parms.DatabaseOnDesktop = "mydata.udb";
    parms.Schema.SchemaOnDesktop = "mydata.usm";
    try {
      conn = dbMgr.CreateDatabase( parms );
  }
}

Example

The following code opens a connection to an UltraLite database named mydata.udb. If the database does not exist, it is created using the UltraLite schema file named mydata.usm.

// iAnywhere.Data.UltraLite namespace
ULConnectionParms parms = new ULConnectionParms();
parms.DatabaseOnDesktop = "mydata.udb";
parms.SchemaOnDesktop = "mydata.usm";
conn = new ULConnection( parms.ToString() );
conn.InfoMessage += new ULInfoMessageEventHandler(
    InfoMessageEventHandler );
conn.OpenWithCreate();

// iAnywhere.UltraLite namespace
CreateParms parms = new CreateParms();
parms.DatabaseOnDesktop = "mydata.udb";
parms.Schema.SchemaOnDesktop = "mydata.usm";
try {
  conn = dbMgr.OpenConnection( parms );
  System.Console.WriteLine(
    "Connected to an existing database." );
}
catch( SQLException econn ) {
  if( econn.GetErrorCode() ==
      SQLCode.SQLE_ULTRALITE_DATABASE_NOT_FOUND ){
    conn = dbMgr.CreateDatabase( parms );
    System.Console.WriteLine(
      "Connected to a new database." );
  } else {
    throw econn;
  }
}

The above code uses the InfoMessage handler defined below to detect if the database is new.

// iAnywhere.Data.UltraLite namespace
protected void InfoMessageHandler(
    object obj, ULInfoMessageEventArgs args )
{
  if( args.NativeError == ULSQLCode.SQLE_DATABASE_CREATED ){
    System.Console.WriteLine( args.Message );
  }
}

Connecting to a database

Using the Connection object

Multi-threaded applications

Example