Pivotal.Data.GemFireXD.GFXDDataReader

Pivotal.Data.GemFireXD.GFXDDataReader is an implementation of System.Data.IDataReader that provides for reading result sets obtained by executing the GFXDCommand.ExecuteReader method on a Pivotal.Data.GemFireXD.GFXDCommand object. It cannot be constructed directly.

This class extends the abstract System.Data.Common.DbDataReader class so users can continue to use that as the base class for generic coding.

The implementation corresponds to the java.sql.ResultSet interface of JDBC.

It is possible to lock the set of selected rows for update later. In order to actually lock the selected rows, these conditions must be met:

Applications can then update the selected rows (or any others for that matter) in the usual manner by invoking the update SQL statements, or using a DataAdapter and so forth.

The ability to update a row directly at the current cursor position in the DataReader is not available because of a GemFire XD server limitation for client-server connections. The locks on the rows are released when the active transaction ends because of a commit or rollback.

See the table in Pivotal.Data.GemFireXD.GFXDType for a mapping of expected types of SQL column and expression query results. For further mapping of the types to corresponding Get* methods in the GFXDDataReader class for individual SQL types, see Data Types.

A DataReader can access multiple result sets, if applicable for the DML. You move to the next result set by invoking the NextResult() method as in the DbReader class. For GemFire XD the only case where multiple result sets can be returned is when invoking a procedure that returns multiple dynamic result sets. See CREATE PROCEDURE (DAP and non-DAP) for more details. GemFire XD does not allow for passing multiple semi-colon separated queries unlike some other database engines.

The MSDN documentation for IDataReader and DbDataReader provides details about other API methods and usage.

Example

// Open a new connection to the network server running on localhost:1527
string host = "localhost";
int port = 1527;
string connStr = string.Format("server={0}:{1}", host, port);
// use the GemFire XD specific class for connection creation
using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) {
  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 = 1; i <= 1000; i++) {
    cmd.Parameters.Clear();
    cmd.Parameters.Add(i);
    cmd.Parameters.Add("addr" + i);
    
    cmd.ExecuteNonQuery();
  }

  // now query the table using a DataReader
  cmd.Parameters.Clear();
  cmd.CommandText = "select * from t1";
  GFXDDataReader reader = cmd.ExecuteReader();
  int[] ids = new int[1000];
  int numResults = 0;
  while (reader.Read()) {
    int id = reader.GetInt32(0);
    string addr = reader.GetString(1);
    Console.WriteLine("Read ID=" + id + ", addr=" + addr);
    if (ids[id - 1] != 0) {
      throw new Exception("Duplicate value for ID=" + id);
    }
    ids[id - 1] = id;
    numResults++;
  }
  reader.Close();
  if (numResults != 1000) {
    throw new Exception("unexpected number of results " + numResults);
  }

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