VMware.Data.SQLFire.SQLFDataReader

VMware.Data.SQLFire.SQLFDataReader is an implementation of System.Data.IDataReader that provides for reading result sets obtained by executing the SQLFCommand.ExecuteReader method on a VMware.Data.SQLFire.SQLFCommand 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 SQLFire 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 VMware.Data.SQLFire.SQLFType 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 SQLFDataReader 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 SQLFire 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. SQLFire 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 SQLFire specific class for connection creation
using (SQLFClientConnection conn = new SQLFClientConnection(connStr)) {
  conn.Open();
  
  // create a table
  SQLFCommand cmd = new SQLFCommand(
      "create table t1 (id int primary key, addr varchar(20))", conn);
  cmd.ExecuteNonQuery();

  // insert into the table using positional parameters
  cmd = new SQLFCommand("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";
  SQLFDataReader 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 SQLFCommand("drop table t1", conn);
  cmd.ExecuteNonQuery();
  
  conn.Close();
}