Advantage Developer Zone

 
 
 

Advantage Extended Data Reader

Monday, December 08, 2008

The AdsExtendedDataReader extends the functionality of a standard DataReader by encapsulating Advantage specific functionality into the object. This includes functionality such as direct record locking, field manipulation and index operations. This allows developers to have very fine control of the data through the .NET Data Provider. Additionally, many xBase specific calls have been included in the Extended Data Reader including; Pack, Zap, and ReIndex.

Direct Record Manipulation

The AdsExtendedDataReader is unlike a .NET DataReader in that it is not a forward-only and read-only object. The extended reader allows for complete navigation through the recordset as well as getting and setting individual field values.

The extended reader is populated using an AdsCommand object with a command type of TableDirect. Once the reader has been populated you can begin reading and manipulating the field values.

   1: cmd = cn.CreateCommand();
   2: cmd.CommandText = "customer";
   3: cmd.CommandType = CommandType.TableDirect;
   4: rdrEdit = cmd.ExecuteExtendedReader();
   5: 
   6: // Getting Values
   7: if (!(rdr.IsDBNull(0)))
   8:   tbFname.Text = (rdr.GetString(0));
   9: if (!(rdr.IsDBNull(1)))
  10:   tbLName.Text = (rdr.GetString(1));
  11: if (!(rdr.IsDBNull(2)))
  12:   tbCity.Text = (rdr.GetString(2));
  13: if (!(rdr.IsDBNull(3)))
  14:   tbState.Text = (rdr.GetString(3));
  15: if (!(rdr.IsDBNull(4)))
  16:   tbCustID.Text = (rdr.GetInt16(4).ToString());
  17: 
  18: // Setting Values
  19: rdrEdit.SetString(0, tbFname.Text.Trim());
  20: rdrEdit.SetString(1, tbLName.Text.Trim());
  21: rdrEdit.SetString(2, tbCity.Text.Trim());
  22: rdrEdit.SetString(3, tbState.Text.Trim());
  23: rdrEdit.SetInt16(4, Int16.Parse(tbCustID.Text));
  24: rdrEdit.WriteRecord();
Working with Indexes

You can provide fast searches and incremental searches with an extended reader. This can be done using the ActiveIndex property along with the Seek method. The Seek method takes an object and a SeekType as parameters. The object parameter type is used to support searching for either single segment or muli-segmented keys. The seek methods are listed below:

  • SoftSeek Allows record to be found with the next higher key value if the given key does not exist.
  • HardSeek Seeks for an exact match.
  • SeekLast Seeks for the last key that matches the seek value.
  • SeekGT Seeks for the next possible index key after the given key.
   1: rdr.ActiveIndex = "LastName";
   2: 
   3: // HardSeek Example 
   4: if (rdr.Seek(new object[] {"Franz"}, AdsExtendedReader.SeekType.HardSeek)
   5:   // Show the matching record 
   6: else
   7:   // No match found 
   8: 
   9: Rdr.ActiveIndex = "FullName" // Index is LastName;FirstName 
  10: 
  11: // SoftSeek Example 
  12: if (rdr.Seek(new object[] {"Franz", "Chris"}, AdsExtendedReader.SeekType.SoftSeek)
  13:   // Show the matching record or next closest match 

The extended reader also allows developers to filter the data through the use of the Filter property or the SetRange method. The Filter property can be set to any valid Advantage expression (see Advantage Expression Engine functions). This will set an Advantage Optimized Filter (AOF) on the server and position the record pointer at the first record that passes the filter expression.

A Range or Scope can also be set which includes a top and bottom value. Records which meet the scope criteria will be returned. The values are set by passing in an object for the top and bottom values of the range. As with setting the Filter property, the pointer will be positioned at the first passing record when the range is applied. To clear a range use the ClearRange method.

xBase Functionality

You can lock individual records in the table to ensure that a record can only be edited by one user at a time. This can be especially useful in ticketing type applications where you must ensure that the same seat is not sold more than once. The following code demonstrates checking for an existing lock and locking the record if it is not already locked.

   1: AdsCommand cmdEdit;
   2: AdsDataReader rdrErr = null;
   3: AdsCommand cmdErr = null;
   4: 
   5: cmdEdit = cn.CreateCommand();
   6: cmdEdit.CommandText = "customer";
   7: cmdEdit.CommandType = CommandType.TableDirect;
   8: 
   9: rdrEdit = cmdEdit.ExecuteExtendedReader();
  10: rdrEdit.ActiveIndex = "customer id";
  11: 
  12: //turn off record caching to see updates when re-reading record.
  13: rdrEdit.RecordCache = 0;
  14: 
  15: rdrEdit.Seek(new object[] {Convert.ToInt16(tbCustID.Text)},
  16:              AdsExtendedReader.SeekType.SoftSeek);
  17: 
  18: try
  19: {
  20:     rdrEdit.LockRecord();
  21:     lbStatus.Text = "User #1 Editing Record";
  22:     btnEdit.Enabled = false;
  23: }
  24: catch (AdsException ex)
  25: {
  26:     if (ex.Number == 5035 )
  27:         cmdErr = cn.CreateCommand();
  28: 
  29:     cmdErr.CommandText = "EXECUTE PROCEDURE sp_mgGetLockOwner('"
  30:         + System.IO.Path.GetDirectoryName(System.IO.Path.GetFullPath(DataPath)).ToString() +
  31:         "\\customer.adt', " + rdrEdit.RecordNumber.ToString() + ")";
  32:     try
  33:     {
  34:         rdrErr = cmdErr.ExecuteReader();
  35:         rdrErr.Read();
  36:     }
  37:     catch (Exception exLock)
  38:     {
  39:         MessageBox.Show(exLock.ToString());
  40:     }
  41: 
  42:     MessageBox.Show("Record is locked by:  " + rdrErr.GetString(0));
  43:     rdrEdit.Close();
  44:     rdrErr.Close();
  45: } // if (ex.Number == 5035 )

The extended data reader has methods which provide additional Advantage functionality. You can re-index a table using the Reindex method. This method requires that the table be opened directly and exclusively. The index page size can be specified as well.

Other xBase functions such as Pack and Zap are also provided by the extended reader. These methods remove deleted records or all records respectively. Each of these methods also performs a Reindex on the table.

Summary

The Advantage Extended Data Reader provides a fine level of control to data manipulation within the .NET environment. It can be used to provide common xBase functionality such as locks, seeks, ranges, re-index, pack and zap functions. Unlike a normal .NET DataReader object the extended reader provides forward and backward navigation as well as the ability to edit data. For more information about the AdsExtendedDataReader please refer to the help file.