Working with Databases

A database is a collection of tables and a session may contain several different databases. A database object is required before any table or data operations may take place. The following are typical operations performed on a database:

• Create a database object
• Connect to a database by calling CTDatabase.Connect()
• Perform table, index, field and record operations
• When you are done with the database, disconnect by calling CTDatabase.Disconnect()

Creating a Database Object

A valid database object is required to perform any database operation. You need to pass a valid CTSession object to the CTDatabase() constructor.

// create a CTDatabase object
CTDatabase ADatabase = new CTDatabase(ASession);
try
{
   // connect to database "MyDatabase"
   ADatabase.Connect("MyDatabase");
}
catch (CTException err)
{
   Console.Write("Database connect failed with error {0}\n", err.GetErrorCode);
}

Connecting to a Database

Before performing any operations with a database, a database object must be connected to a session by calling the CTDatabase.Connect() method. The database should already have been created or added to the session.

// connect to a database
CTDatabase ADatabase = new CTDatabase(ASession);

try
{
   ADatabase.Connect("MyDatabase");
}
catch (CTException err)
{
   Console.Write("Connect to database failed with error {0}\n", err.GetErrorCode());
}

When a database is connected to the session, it is considered "active". Use CTDatabase.IsActive() to check if a particular database is active.

When the database connection is no longer needed, it must be disconnected from the session by calling the CTDatabase.Disconnect() method.

// disconnect from a database
try
{
   ADatabase.Disconnect();
}
catch (CTException err)
{
   Console.Write("Disconnect from database failed with error {0}\n", err.GetErrorCode());
}

Database Properties

The default database properties are suitable for most c-tree Plus for .NET applications. Advanced developers may need to tune c-tree Plus for .NET to meet application requirements.

Database Name

Once the database object connects, retrieve the database name with CTDatabase.GetName().

// display the database name
Console.Write("Database: {0}\n", ADatabase.GetName());

Database Path

The database path, by default, is the server directory for client/server applications, or the application directory for non-server applications. To set a different database path, when the database is being created, just insert the appropriate path as the second parameter of CTSession.CreateDatabase(). With this information stored in the Session dictionary, the user doesn't need to know where it is located, since it will be automatically retrieved given the database name.

Once the database is successfully connected to a session, obtain the database path with CTDatabase.GetPath().

// display database properties
void DisplayDatabaseProperties(CTDatabase ADatabase)
{
   Console.Write("Database: {0}\n", ADatabase.GetName());
   Console.Write("Path: {0}\n", ADatabase.GetPath());
   Console.Write("Number of tables: {0}\n", ADatabase.GetTableCount());

Table Count

CTDatabase.GetTableCount() retrieves the number of tables associated with a database. CTDatabase.GetTableCount() returns -1 (minus 1) if the database is not connected or if the database object is invalid.

// display the number of tables in database
Console.Write("Number of tables: {0}\n", ADatabase.GetTableCount());

Managing Tables

Once the user performs a successful database connect, the database handle can be used to operate on tables.

Every time a new table is created, or an existing table is added to a database, some of the table properties such as table name, path, data file extension, index file extension, etc, are placed in an entry of the database dictionary file.

Every time a user activates a table by opening it with CTTable.Open() method, the table is placed in a list of active (opened) tables within the database. When a table is deactivated, or closed by calling CTTable.Close() method, the table is removed from the list of active tables in the database.

Adding an Existing Table

An existing table may be added or imported to a database by calling the CTDatabase.AddTable() method. CTDatabase:AddTable() takes as parameters the table name and the table path.

// add MyTable to the current database
try
{
   ADatabase.AddTable("MyTable", "");
}
catch (CTException err)
{
   Console.Write("Add table failed with error {0}\n", err.GetErrorCode());
}

Adding an Existing Table Under Transaction Control

An extra level of data integrity can be achieved when you add an existing table to a database under transaction control. When the transaction is committed, the database dictionary data for the table is committed to disk. If the transaction is aborted, the dictionary data for the table is automatically removed from the database dictionary.

The code fragment below shows how to add an existing table under transaction control.

// begin a transaction
ADatabase.Begin();
try
{
   // add MyTable to the current database
   ADatabase.AddTable("MyTable", "");
   // commit the transaction
   ADatabase.Commit();
}
catch (CTException err)
{
   // abort the transaction
   ADatabase.Abort();
   Console.Write("Add table failed with code {0}\n", err.GetErrorCode());
}

Dropping a Table

When you drop a table from a database, the table information is removed from the database dictionary, but the table data and index files are left untouched. The drop table operation can be reversed with an add table operation. Drop a table from a database by calling CTDatabase.DropTable() method.

// drop MyTable from current database
try
{
   ADatabase.DropTable("MyTable");
}
catch (CTException err)
{
   Console.Write("Drop table failed with code {0}\n", err.GetErrorCode());
}

Dropping a Table Under Transaction Control

An extra level of data integrity can be achieved when you drop a table from a database under transaction control. When the transaction is committed, the changes to the database dictionary data for the table are committed to disk. If the transaction is aborted, the dictionary data for the table is automatically restored to the database dictionary.

The code fragment below shows how to drop an existing table under transaction control. No error checking is included in the sample code:

// start a transaction
ADatabase.Begin();
try
{
   // drop MyTable from current database
   ADatabase.DropTable("MyTable");
   // commit the transaction
   ADatabase.Commit();
}
catch (CTException err)
{
   // abort the transaction
   ADatabase.Abort();
   Console.Write("Drop table failed with code {0}\n", err.GetErrorCode());
}

Deleting a Table

When you delete a table from a database, the table information is removed from the database dictionary and the table data and index files are deleted from disk. The delete table operation can be reversed only when used under transaction control. Without transaction control, a delete table operation will delete the data and index files and the table data will be lost. Delete a table from a database by calling CTDatabase.DeleteTable() method. Example:

// delete MyTable from current database
try
{
   string password = "";
   ADatabase.DeleteTable("MyTable", password);
}
catch (CTException err)
{
   Console.Write("Delete table failed with code {0}\n", err.GetErrorCode());
}

Note: The DeleteTable() method takes as parameters the table name and the table password. Set the password parameter to an empty String object if a table was created without passwords.

Deleting a Table Under Transaction Control

An extra level of data integrity can be achieved when you delete a table from a database under transaction control. When the transaction is committed, the changes to the database dictionary data for the table are committed to disk and the table and index files are deleted from disk. If the transaction is aborted, the dictionary data for the table is automatically restored to the database dictionary and the original data and index files are restored to their original state.

The code fragment below shows how to delete an existing table under transaction control. No error checking is included in the sample code:

// start a transaction
try
{
   // delete MyTable from current database
   string password;
   ADatabase.DeleteTable("MyTable", password);
   // commit the transaction
ADatabase.Commit();
}
catch (CTException err)
{
   // abort the transaction
   ADatabase.Abort();
   Console.Write("Delete table failed with code {0}\n", err.GetErrorCode());
}

First Table

CTDatabase.FirstTable() retrieves the name and path of the first table in a session. If the session has no tables, CTDatabase.FirstTable() returns false (false). See the example in Next Table().

Next Table

CTDatabase.NextTable() retrieves the name and path of the next table in a database. CTDatabase.NextTable() returns false (false) when no more tables exist for the current database.

// Display all tables in a database
void DisplayTables(CTDatabase ADatabase)
{
   StringBuilder Name = new StringBuilder();
   StringBuilder Path = new StringBuilder();
  
   if (ADatabase.FirstTable(out Name, out Path))
   {
      do
      {
         Console.Write("Table: {0} Path: {1}\n", Name.ToString(), 
         Path.ToString());
      }
      while (ADatabase.NextTable(out Name, out Path);
   }

Find Table

CTDatabase.FindTable() locates a specific table given the table name and, if the table exists, retrieves the table path. If a table cannot be found, CTDatabase.FindTable() returns false.

// return true if table exist or false if table does not exit
CTBOOL TableExist(CTDatabase ADatabase, String tblName)
{
   StringBuilder tblPath = new StringBuilder();

   return ADatabase.FindTable(TblName, out tblPath);
}

Table UID (Unique Identifier)

When a table is created or added to a database, an automatic and unique identifier (UID) is associated with the table. A table UID is unique within the database that the table is associated with.

A table UID is an unsigned long value that can be used as an alternative method to operate on tables once the table is created or added to a database.

Find Table by UID

The overloaded method CTDatabase.FindTable() locates a table in the database given the table UID and retrieves the table name and path. The following example shows how to implement a table open function using the table UID instead of the table name.

Example

// open table using UID
CTTable OpenByUID(CTDatabase ADatabase, ULONG uid, OPEN_MODE OpenMode)
{
   StringBuilder tblName = new StringBuilder();
   StringBuilder tblPath = new StringBuilder();
   CTTable Retval;

   // locate the table in the database by uid
   if (ADatabase.FindTable(uid, out tblName, out tblPath))
   {
      Retval = new CTTable(ADatabase);

      if (!Retval)
         throw CTException(CTDBRET_NOMEMORY);
      try
      {
          Retval->Open(tblName.ToString(), OpenMode);
      }
      catch (CTException err)
      {
         throw;
      }
   }
   else
   {
      // table not found
      throw new CTException(INOT_ERR);
   }
   return Retval;
}

Database Dictionary

A database must have a database dictionary; this file has the database name with extension .fdd. It is created with CTSession.CreateDatabase(), and must exist in order for the user to connect to the database. Each database dictionary maintains information about the tables associated with the database. When a database is connected to the session, it is considered "active".

Database Dictionary Layout

This information is included for general information on the database dictionary structure. FairCom DB API has appropriate functions to retrieve any needed information from the database dictionary, such as the table UID or path.

A database dictionary file has several different record types:

• Table records (the field Type set to 2) holds information about a data file (table).
• Index records (the field Type set to 3) holds information about an index file.
• A special type four record which maintained the COUNTER field has been been deprecated as of FairCom DB V9.0.