Generic Coding with the SQLFire ADO.NET Driver

This release of the ADO.NET driver does not support DbProviderFactory. However, all implementation classes extend the corresponding classes in the System.Data.Common namespace. This allows applications to use the base classes of that namespace in the code and resort to SQLFire-specific classes only for creating class objects, or when SQLFire-specific methods must be invoked.

If an application creates a SQLFClientConnection explicitly, then it can continue to use the base DbConnection class as far as possible. The classes VMWare.Data.SQLFire.SQLFClientConnection, VMware.Data.SQLFire.SQLFDataAdapter and VMware.Data.SQLFire.SQLFCommandBuilder require explicit creation, while the remaining class objects can be obtained using the base classes of the System.Data.Common namespace.

For example, you need not create the SQLFCommand object explicitly because the DbConnection.CreateCommand() method provides a base DbCommand object. (This object is actually a SQLFCommand when it is using a SQLFClientConnection object.) Applications should use the base classes from the System.Data.Common namespace when writing ADO.NET code that can be easily changed to use a different driver, or when multiple drivers are used from the same code base.

The table shows the mapping of the SQLFire ADO.NET driver classes to their base classes in the System.Data.Common namespace that you should use for generic coding. Also listed are the interfaces in System.Data implemented by the class.

Table 1. Mapping SQLFire driver classes to System.Data.Common base classes
SQLFire ADO.NET Driver Class ADO.NET base class (in System.Data.Common) ADO.NET interfaces (in System.Data)
SQLFClientConnection DbConnection IDbConnection
SQLFCommand DbCommand IDbCommand
SQLFCommandBuilder DbCommandBuilder n/a
SQLFDataAdapter DbDataAdapter IDbDataAdapter
SQLFDataReader DbDataReader IDataReader
SQLFException DbException n/a
SQLFParameter DbParameter IDataParameter, IDbDataParameter
SQLFParameterCollection DbParameterCollection IDataParameterCollection
SQLFRowUpdatedEventArgs RowUpdatedEventArgs n/a
SQLFRowUpdatingEventArgs RowUpdatingEventArgs n/a
SQLFTransaction DbTransaction IDbTransaction
SQLFType (use DbTypes as in Table 1) See VMware.Data.SQLFire.SQLFType.
For example:
// Open a new connection to the network server running on localhost:1527
string host = "localhost";
int port = 1527;
string connectionStr = string.Format("server={0}:{1}", host, port);
// use the SQLFire specific class for connection creation
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr)) {
  conn.Open();

  // create a table
  // using the base DbCommand class rather than SQLFire specific class
  DbCommand cmd = conn.CreateCommand();
  cmd.CommandText = "create table t1 (id int primary key, addr varchar(20))";
  cmd.ExecuteNonQuery();

  // insert into the table using named parameters
  // using an abstracted method that can deal with difference in the
  // conventions of named parameters in different drivers
  cmd = conn.CreateCommand();
  string idPrm = GetEscapedParameterName("ID");
  string addrPrm = GetEscapedParameterName("ADDR");
  cmd.CommandText = "insert into t1 values (" + idPrm + "," + addrPrm + ")";
  cmd.Prepare();
  // using the base DbParameter class
  DbParameter prm;
  for (int i = 0; i < 1000; i++) {
    // first the parameter for ID
    cmd.Parameters.Clear();
    prm = cmd.CreateParameter();
    prm.ParameterName = "ID";
    prm.DbType = DbType.Int32;
    prm.Value = i;
    cmd.Parameters.Add(prm);
    // next the parameter for ADDR
    prm = cmd.CreateParameter();
    prm.ParameterName = "ADDR";
    prm.DbType = DbType.String;
    prm.Value = "addr" + i;
    cmd.Parameters.Add(prm);

    cmd.ExecuteNonQuery();
  }

  // drop the table
  cmd = conn.CreateCommand();
  cmd.CommandText = "drop table t1";
  cmd.ExecuteNonQuery();

  conn.Close();
}