Pivotal.Data.GemFireXD.GFXDCommand

Pivotal.Data.GemFireXD.GFXDCommand executes SQL commands and reads results, if any, as a scalar value or by using a Pivotal.Data.GemFireXD.GFXDDataReader. This class implements ADO.NET's System.Data.IDbCommand interface and extends the abstract System.Data.Common.DbCommand class.

An object of this class bound to GFXDClientConnection is returned by the CreateCommand() method of the GFXDClientConnection class. When using that method, the ConnectionText property of the command must be set to a valid SQL statement before execution of the command.

Constructor

GFXDCommand(string commandText, GFXDConnection connection) initializes a new command object with given SQL command text. The connection parameter requires a GFXDConnection class object which is currently implemented only by GFXDClientConnection.

Named and positional parameters in commands

You can provide parameters to GFXDCommands in two forms:

  • As Pivotal.Data.GemFireXD.GFXDParameters
  • Directly as raw .NET objects in the parameter collection (the Parameters property of the command object)
In the latter case the driver automatically determines the type, size, precision, and other applicable parameters. In either case the GemFire XD servers try to convert the parameter type to the actual type (as determined from table column types and so forth, per SQL rules), or return an exception if the conversion fails. With GFXDParameter, the expected type of parameter must match the GFXDType of the parameter, if specified. With raw objects, the type used is the default one as per the type of the .NET class of the object. See the table in Pivotal.Data.GemFireXD.GFXDType for the mapping of .NET object classes to corresponding SQL types.

Second option (named parameters) is to refer to the parameters by the name of the parameter prefixed with a ":" in which case the parameters are bound by the matching name, if any, in the GFXDParameterCollection list of a Pivotal.Data.GemFireXD.GFXDParameter. In this case the position of the parameter in the parameter list is immaterial and name alone decides the parameter to be bound. There is no provision to use raw objects as parameters for this option because objects as such have no associated names.

It is also possible to use a combination of the above two approaches, with some parameters bound by name while others are bound by wildcard. The named parameters are first bound by the respective names, and the remaining parameters are bound by their position in the parameter list. This approach is not recommended due to possibility of ambiguities and lack of clarity.

Using positional parameters with wildcards is a bit faster than using named parameters, although the difference is negligible. However, the wildcard approach also allows raw objects directly as parameters so can have some benefit in terms of smaller number of objects being created and collected by the GC.

Prepare()

Unlike other drivers, you can explicitly invoke Prepare() before adding any parameters. If this method is not invoked explicitly and parameters are added, or if it has been invoked without any parameters, then it is invoked implicitly before the first execution of the command. If the CommandText of the command is changed, the existing parameter list is cleared and prepare is invoked implicitly again before execution if the command has parameters.

Two variations are provided to enable specification of parameters in the command string, either using wildcards (positional parameters), or by explicitly specifying a name (named parameters). First option (positional parameters) is to refer to the parameters using the wildcard "?" in which case the parameters are bound solely by their position in the parameter list.

Batch operations using GFXDCommand

In addition to the capabilities provided by ADO.NET's DbCommand abstract class, this driver adds a feature to enable execution of batch operations directly rather than with DataAdapters. The typical flow is to use the AddBatch method to add new commands in the batch, then bind the required parameters and use an ExecuteBatch at the end to get the results of each element of the batch. Note that select statements cannot be added in a batch.

Note: GFXDire does not support providing multiple SQL commands separated by semi-colons in a single string. Users must either make use of the batching support as described in this section, or use DataAdapters (Pivotal.Data.GemFireXD.GFXDDataReader). In addition to passing multiple SQL commands as strings, the batching support enables you to add multiple parameter lists for the same prepared command.
The batching operates in two modes:
  • Commands without any parameters
  • Prepared statements with parameters
In the case of command strings without any parameters, these can be added to the batch one by one, and their results are returned by ExecuteBatch() as an array of integers, with each element being the update count of each DML string in order.

In the case of statements with parameters, each batch is a new set of parameter values, and the command string itself cannot be altered. The statement itself is prepared implicitly if it has not been prepared explicitly (by invoking the Prepare method). The results returned by ExecuteBatch() are again an array of integers with each element being the update count for command execution with the parameters at the respective position in the batch.

The table describes API methods for batching.

Method Description
AddBatch() Enables binding a new set of parameters for the given statement with parameters. The command is implicitly prepared if not prepared already. The new set of parameters are subsequently appended to the batch in order and the result of ExecuteBatch() will have the update count in the integer array result in that order. You cannot alter the CommandText once this method is executed. If you attempt to do so, the statement is implicitly closed and a new one created that loses all current elements in the batch, if any. If the command takes no parameters, each AddBatch execution essentially executes the same command repeatedly. For an example of usage see the ExecuteBatch() method.
AddBatch(string) Adds a new command text to the batch of execution. The command string cannot have any parameters and cannot have been prepared explicitly (by invoking Prepare). The new command string will be added to the batch in order and the result of ExecuteBatch() will have the update count in the integer array result in that order. For an example of usage see the ExecuteBatch() method.
ClearBatch() Clears the list of commands or parameter lists in the current batch.
ExecuteBatch() Submits the current batch of commands for execution to the GemFire XD system. If all commands in the batch execute successfully, it returns the result as an array of update counts in order. If one or more commands throw an GFXDException, a Pivotal.Data.GemFireXD.BatchUpdateException is thrown, with all exceptions linked in the NextException field.

Additional GemFire XD extensions

Additional GemFire XD extension methods and properties are described below. The MSDN documentation for IDbCommand and DbCommand provides details on all other API methods.

Method or property name Description
CreateParameter(string name, GFXDType type) Creates a named Pivotal.Data.GemFireXD.GFXDParameter with given Pivotal.Data.GemFireXD.GFXDType.
CreateDataAdapter() Returns a new Pivotal.Data.GemFireXD.GFXDDataAdapter associated with this command.
FetchSize Gets or sets the number of rows that are attempted to be fetched from the server in one group when using a DataReader (Pivotal.Data.GemFireXD.GFXDDataReader). A value of <= 0 indicates using the default fetch size of the GemFire XD servers (currently 16). A larger number can reduce the number of round-trips between the client and servers, but requires more memory and can result in a higher latency due to larger transportation size in some rare cases. A small number can cause a large number of round-trips between the client and servers that can hit both the latency and throughput badly. Increasing this size can boost performance for cases when you expect large number of rows as the result.
ReaderType
Property to get or set the type of DataReader returned by the ExecuteReader methods. Possible values for the DataReaderType enumeration of the type of this property are:
  • ForwardOnly: Indicates that the DataReader can move only in one direction, forward. This is the default value.

  • ScrollInsensitive: Indicates that the DataReader can freely move forward or back on the results, and corresponds to the scroll-insensitive ResultSets in JDBC. The underlying result set is insensitive to any changes made to the underlying data.

  • ScrollSensitive: Indicates that the DataReader can freely move forward or back on the results, and corresponds to the scroll-sensitive ResultSets in JDBC. The underlying result set is sensitive to any changes made to the underlying data and may reflect the changes made via the cursor in the reader.

ReaderLockForUpdate If set, this boolean indicates that the underlying rows are requested to be locked on the GemFire XD servers when an ExecuteReader is invoked. However, rows are not locked unless the SQL statement is a "SELECT … FOR UPDATE" statement. In addition this takes effect only in the context of a transaction and not otherwise, and these follow the normal transactional semantics of GemFire XD. No explicit updatable cursor on the returned DataReader is provided. Instead users can fire explicit update DML statements as required while holding the locks on the rows. These locks are released at the end of the transaction.
Close() Close this command object. Subsequent operations throw an exception.
IsClosed Returns true if the command has been closed by an explicit invocation of Close(), or if the command object has been disposed.

Example: Using positional parameters

// 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);
using (GFXDClientConnection conn = new GFXDClientConnection(connectionStr)) {
  conn.Open();

  // create a table
  GFXDCommand cmd = new GFXDCommand(
      "create table t1 (id int primary key, addr varchar(20))", conn);
  cmd.ExecuteNonQuery();

  // insert into the table using positional parameters
  cmd = new GFXDCommand("insert into t1 (id, addr) values (?, ?)", conn);
  cmd.Prepare();
  for (int i = 0; i < 1000; i++) {
    cmd.Parameters.Clear();
    cmd.Parameters.Add(i);
    cmd.Parameters.Add("addr" + i);

    cmd.ExecuteNonQuery();
  }

  // drop the table
  cmd = new GFXDCommand("drop table t1", conn);
  cmd.ExecuteNonQuery();

  conn.Close();
}

Example: Using named parameters

// 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);
using (GFXDClientConnection conn = new GFXDClientConnection(connectionStr)) {
  conn.Open();

  // create a table
  GFXDCommand cmd = new GFXDCommand(
      "create table t1 (id int primary key, addr varchar(20))", conn);
  cmd.ExecuteNonQuery();

  // insert into the table using named parameters
  cmd = new GFXDCommand("insert into t1 values (:ID, :ADDR)", conn);
  cmd.Prepare();
  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);
    // then 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 = new GFXDCommand("drop table t1", conn);
  cmd.ExecuteNonQuery();

  conn.Close();
}

Example: Using both positional and named parameters

// 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);
using (GFXDClientConnection conn = new GFXDClientConnection(connectionStr)) {
  conn.Open();

  // create a table
  GFXDCommand cmd = new GFXDCommand(
      "create table t1 (id int primary key, addr varchar(20))", conn);
  cmd.ExecuteNonQuery();

  // insert into the table using a mix of named and positional parameters
  cmd = new GFXDCommand("insert into t1 values (:ID, ?)", conn);
  cmd.Prepare();
  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);
    // then the parameter for ADDR is bound by position;
    // note that this could even have been first in the list since the other
    // parameter is bound by name
    prm = cmd.CreateParameter();
    prm.ParameterName = "ADDR";
    prm.DbType = DbType.String;
    prm.Value = "addr" + i;
    cmd.Parameters.Add(prm);

    cmd.ExecuteNonQuery();
  }

  // drop the table
  cmd = new GFXDCommand("drop table t1", conn);
  cmd.ExecuteNonQuery();

  conn.Close();
}

Example: Using parameterized commands for batch operations

// 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);
using (GFXDClientConnection conn = new GFXDClientConnection(connectionStr)) {
  conn.Open();

  // create a table
  GFXDCommand cmd = new GFXDCommand(
      "create table t1 (id int primary key, addr varchar(20))", conn);
  cmd.ExecuteNonQuery();

  // insert into the table using positional parameters
  cmd = new GFXDCommand("insert into t1 values (?, ?)", conn);
  cmd.Prepare();
  for (int i = 0; i < 1000; i++) {
    cmd.Parameters.Add(i);
    cmd.Parameters.Add("addr" + i);
    cmd.AddBatch();
  }
  int[] results = cmd.ExecuteBatch();

  // drop the table
  cmd = new GFXDCommand("drop table t1", conn);

  conn.Close();
}

Example: Using commands having no parameters for batch operations

// 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);
using (GFXDClientConnection conn = new GFXDClientConnection(connectionStr)) {
  conn.Open();

  // create a table
  GFXDCommand cmd = new GFXDCommand(
      "create table t1 (id int primary key, addr varchar(20))", conn);
  cmd.ExecuteNonQuery();

  string cmdStr;
  // insert into the table using different command strings
  for (int i = 0; i < 1000; i++) {
    cmdStr = "insert into t1 values (" + i + ", 'addr" + i + "')";
    cmd.AddBatch(cmdStr);
  }
  int[] results = cmd.ExecuteBatch();
  Console.WriteLine(results);
  // drop the table
  cmd = new GFXDCommand("drop table t1", conn);

  conn.Close();