Use ADO.NET Driver Objects Directly

To use the ADO.NET driver directly from any VisualBasic, C#, or Managed C++ application, add the NuoDB.Data.Client.dll file to the list of referenced assemblies and write standard ADO.NET client code such as the following:

string cs="Server=localhost;Database=test;User=dba;Password=goalie;Schema=test";

using (NuoDbConnection connection = new NuoDbConnection(cs))
{
    DbCommand command = new NuoDbCommand("select * from hockey", connection);

    connection.Open();
    DbDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine("\t{0}\t{1}\t{2}", reader[0], reader[1], reader[2]);
    }
    reader.Close();
}

The NuoDb.Data.Client namespace defines classes that inherit from the standard ADO.NET classes. For example, DbConnection is the base class for NuoDbConnection. The NuoDB ADO.NET driver classes you are most likely to use include:

  • NuoDbConnection

  • NuoDbConnectionStringBuilder

  • NuoDbCommand

  • NuoDbReader

  • NuoDbParameter

  • NuoDbBulkLoader

The steps for directly using NuoDB ADO.NET driver objects are:

  1. Create a connection to the NuoDB database. Use NuoDbConnection and NuoDbConnectionStringBuilder.

  2. Create one or more SQL commands. Use NuoDbCommand and NuoDbParameter.

  3. Execute one or more SQL commands. Use the NuoDbCommand.Execute Xxx() methods.

  4. Navigate the results of executing the SQL commands. Use NuoDbReader.

  5. Load multiple rows into a database table in one operation. Use NuoDbBulkLoader.

The default location for sample code that shows how to use the NuoDB ADO.NET driver is the C:\Program Files (x86)\ADO.NET Driver for NuoDB .

Defining a Connection String

In your program, specify configuration information in a connection string. The ADO.NET driver provides the NuoDbConnectionStringBuilder helper class to help you do this. For example:

NuoDbConnectionStringBuilder builder = new NuoDbConnectionStringBuilder();
    builder.Server = "localhost";
    builder.Database = dbName;
    builder.User = "dba";
    builder.Password = "goalie";
    builder.Schema = "hello";
string connectionString = builder.ConnectionString;

Connection string options are described in the following table:

Option Name Description

Server

Required. Address of at least one NuoDB broker in the format hostname[:[.var]port]. Separate multiple broker addresses with commas, for example:

NuoDbConnection connection = new NuoDbConnection(
    "Server=first-host,second-host:48004;Database=dbTest;User=dba;Password=dba"))

If you specify multiple brokers, the NuoDB ADO.NET driver tries each broker in the order they are specified, until one broker accepts the connection. The driver continues to use this broker unless an error occurs. If there is an error then the driver continues to try the brokers until the driver finds a new broker that accepts the connection.

Database

Required. Name of the database you want to open.

User

Required. Name of database user.

Password

Required. Password of that database user.

Schema

Optional. Schema to be used by default. If you do not specify a schema then you must fully specify all tables in SQL queries, for example, HOCKEY.HOCKEY.

Pooling

Optional. Indicates whether or not connection pooling is enabled. Connection pooling saves the connection handle for reuse upon connection object close. The default is that connection pooling is enabled. The value of this option must be True or False.

ConnectionLifetime

Optional. The maximum length of time that an idle connection can be in the connection pool. If a connection is not used for this amount of time, it will be discarded. This avoids keeping too many connections open when the application is not doing actual work. When a peak in the workload occurs then new connections will be created. Before the number of seconds specified for ConnectionLifetime elapses the connection is still available if needed. If the specified number of seconds passes and the connection has not been used then NuoDB closes the connection. The default is 10 seconds.

MaxLifetime

Optional. Maximum number of seconds that an underlying connection can exist before the driver closes the underlying connection instead of returning it to the connection pool upon connection object close. Idle connections are closed and removed from the pool if they reach the defined value for MaxLifetime.

The default is 10,000 seconds (2.77 hours). A value of zero means that the underlying connections can remain open for an indefinite length of time; that is, the driver will never close the connection.

You can use the MaxLifetime option to ensure that even if a connection is used and returned continuosly, it will be discarded when too much time has passed since its creation. In clustered configurations, this is useful to force load balancing between a running server and a server just brought online.

MaxConnections

Optional. Specifies the maximum number of connections that can be open at the same time. The default is 100.

TimeZone

Optional. Time zone to use to convert local date/time values. The default is the current time zone as reported by the operating system.

IsolationLevel

Optional. The default transaction isolation level for the connection to use. This can be ReadCommitted, WriteCommitted, ConsistentRead, Serializable.

Cipher

Optional. Sets the encryption method to be used when communicating with NuoDB processes. By default, NuoDB processes use RC4 for encryption. Cipher can be set to None which means no encryption will be used.

ClientInfo

Optional. Arbitrary info about the connecting client. This will be available in the CLIENTINFO column of the SYSTEM.CONNECTIONS and SYSTEM.LOCALCONNECTIONS system tables.

If you do not use a NuoDbConnectionStringBuilder object, the syntax rules for specifying a connection string are as follows:

  • Specify the name of the option followed by = followed by the value.

  • Separate options with semicolons.

  • Leading and trailing whitespace when specifying an option name or a value is ignored.

  • Enclose an option value in single or double quotation marks if it must contain a semicolon or leading or trailing whitespace.

The default behavior is that connection pooling is enabled. Connection pools are global. This means that no matter where in your application you open or close the connection it is handled by the same pool. You can open connections to different databases through different connection strings and it will work correctly.

A connection string is used as a key to distinguish different connections. If two connection strings specify the same options in different orders then they open two separate connections. For example, suppose you open a connection by specifying the following string:

“Database=hockey;Server=localhost;User=domain;Password=myPassword;MaxLifetime=14400”

You close that connection and then open a connection with the following string. Even though the following connection string specifies the same connection options you get a new connection because the order of the options is different in this connection string than it was in the first connection string.

“Server=localhost;Database=hockey;User=domain;Password=myPassword;MaxLifetime=14400”

Connecting to a Database

To connect to a NuoDB database, create a NuoDBConnection object with a valid connection string. Then invoke the NuoDBConnection.Open() method on the new connection. For example:

class HelloDB : IDisposable
    {
        private NuoDbConnection connection;
        /*
         * Creates an instance of HelloDB connected to the database with
         * the given name on the localhost. This example class uses the
         * default testing name & password.
         */
        public HelloDB(string dbName)
        {
            NuoDbConnectionStringBuilder builder = new NuoDbConnectionStringBuilder();
               builder.Server = "localhost";
               builder.Database = dbName;
               builder.User = "dba";
               builder.Password = "goalie";
               builder.Schema = "hello";

            this.connection = new NuoDbConnection(builder.ConnectionString);
            this.connection.Open();
        }

Closing a Database Connection

When a connection is no longer needed ensure that you invoke the Close() method on it. If connection pooling is enabled then the connection is made available for further work. If connection pooling is not enabled then the Database server is notified to release the resources allocated to support this connection.

Creating SQL Statements

To create SQL statements, invoke NuoDBConnection.CreateCommand(). For example:

public void CreateTable()
    {
        try
        {
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = "create table names (id int generated always as identity primary key, name string)";
                command.ExecuteNonQuery();
                Console.Out.WriteLine("The table 'NAMES' was created.");
            }
        }
        catch (NuoDbSqlException)
        {
            Console.Out.WriteLine("The table 'NAMES' already exists, re-using it.");
            }
    }

Creating Prepared Commands

When using the ADO.NET driver a prepared command is a NuoDbCommand object on which the Prepare() method has been called. You can keep a reference to this object, then update the parameter values and then re-run the Execute Xxx() methods.

There are several ways to implement a query that specifies parameters. The following sample relies on the NuoDbCommand.Prepare() method to create an object for each parameter and then assign a value to each parameter.

public void AddNames()
   {
      try
      {
         using (DbCommand command = connection.CreateCommand())
         {
            command.CommandText = "insert into names (name) values (?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?)";
            command.Prepare();
            for (int i = 0; i < 15; i++)
            {
               string name = String.Format("Fred # {0}", i+1);
               command.Parameters[i].Value = name;
            }
        ...
      catch (NuoDbSqlException e)
      {
         throw e;
      }
   }

Another way to use parameters is by manually adding them to the command, as shown in the following example:

public string GetName(int id)
   {
      try
      {
         using (DbCommand command = connection.CreateCommand())
         {
            command.CommandText = "select name from names where id=?";
            command.Parameters.Add(id);
            return command.ExecuteScalar();
         }
         return null;
      }
      catch (NuoDbSqlException e)
      {
         throw e;
      }
   }

Executing SQL Statements

The NuoDbConnection.CreateCommand() method creates an object on which you can call the following methods:

  • ExecuteReader() returns a DbReader object that lets you iterate over results.

  • ExecuteScalar() returns the value that is in the first column of the first row of the result data. This is a shortcut for SQL statements such as select count(*) from table when you know that you are getting just one value.

  • ExecuteNonQuery() returns the number of rows that have been updated or inserted by the command.

Controlling Transactions

Call the NuoDbConnection.BeginTransaction() method to open a transaction. The method returns a DbTransaction object. After opening a transaction, all commands are created in that transaction and you can subsequently commit or rollback the transaction. For example:

using (NuoDbConnection connection = new NuoDbConnection(connectionString))
   {
      connection.Open();
      DbTransaction transaction = connection.BeginTransaction();

      DbCommand updateCommand = connection.CreateCommand();
      updateCommand.CommandText = "insert into hockey (number, name) values (99, 'xxxx')";

      updateCommand.ExecuteUpdate();
      updateCommand.ExecuteUpdate();

      transaction.Commit();
   }

Transaction Isolation Levels

The ADO.NET driver for NuoDB supports several transaction isolation levels. The NuoDbConnection.BeginTransaction() method has an overloading that passes a transaction isolation level enumerator value. For more information about NuoDB transaction isolation levels, see Supported Transaction Isolation Levels. The following table shows how the ADO.NET driver enumerator values for transaction isolation levels map to the NuoDB transaction isolation levels.

ADO.NET Driver Enumerator Value NuoDB Transaction Isolation Level Notes

IsolationLevel.Unspecified

CONSISTENT READ

This is the default. Provides complete isolation from changes made by concurrent transactions.

IsolationLevel.ReadUncommitted

Not supported

Use ReadCommitted.

IsolationLevel.ReadCommitted

READ COMMITTED

A transaction reads the most recently committed version of a record.

IsolationLevel.RepeatableRead

Not supported

Use SERIALIZABLE.

IsolationLevel.Serializable

SERIALIZABLE

Behaves as CONSISTENT READ.

The ADO.NET driver for NuoDB does not support the ADO.NET Snapshot and Chaos transaction isolation levels.

The ADO.NET interface does not have a way to control the SQL AUTOCOMMIT feature directly. When using the ADO.NET driver for NuoDB, SQL AUTOCOMMIT is turned on by default and automatically turned off during a transaction.

Handling Exceptions

To handle an exception in your client application, if you can, use the .NET Framework language constructs to ensure the connection is automatically released. This is similar to the using instruction in C#.

The ADO.NET driver for NuoDB provides the NuoDbSQLException object for handling exceptions. It provides the same exception types as provided by the JDBC driver for NuoDB. For details about these exception types, see Exception Handling in the documentation for the JDBC driver for NuoDB.