Wednesday, February 13, 2008

Direct Table Access in ADO.NET

Dealing with the disconnected recordset paradigm in ADO.NET can often present hurdles, especially if you come from an ISAM/navigational background and are used to dealing with direct table opens.

The Advantage .NET Data Provider provides a class that is meant for all of you ISAM lovers out there (we know you're out there), the AdsExtendedReader class.

While this class is a descendant of the AdsDataReader class, its functionality is not limited to reading data. The class can be used to set scopes, perform index seeks, lock specific records, update records, etc. It is basically a class that exposes much of the Advantage ISAM functionality to ADO.NET users, and is part of what makes Advantage unique.

One powerful use of this class is to provide lookup combo boxes that do not require the overhead of multiple SQL statement executions. An AdsExtendedReader can be used to quickly scope table contents based on the current contents of the combo box. Let's take a look at some example code that does just that.

I have built an example application with two controls; a button and a combo box. The button is used to open a table and set an active index:
    private void button1_Click( object sender, EventArgs e )
    {
      // connect
      adsConnection1.Open();
      
      // open the table, adsCommand1 is just a "SELECT *" statement
      oExtendedReader = adsCommand1.ExecuteExtendedReader();
      
      // Set the active index
      oExtendedReader.ActiveIndex = "lastname";
    }

The combo box has one event defined; the TextUpdate event. This event fires whenever the text in the combo box has been modified. As the user types, it sets a scope (sometimes also called a range) on the table and fills the drop down with values from the table that pass the scope conditions.

Setting a scope results in a simple seek operation on the table. No files are closed and reopened. No SQL engine is involved in re-executing a query. An index seek is the fastest way for Advantage to locate a record.
    private void comboBox1_TextUpdate( object sender, EventArgs e )
    {
      Object[] oKeys = new Object[1] {comboBox1.Text};
      int i = 5;
      int iCursor;
      
      // Here's the fun part, a connected dataset scope inside .net
      // This table could have a million records, but they are not all read
      // into an in-memory table on the client. We are using an index on
      // the server for a very fast and efficient scope.
      oExtendedReader.SetRange( oKeys, oKeys );
      
      // Save current cursor position
      iCursor = comboBox1.SelectionStart;
      
      // Clear the combobox list and add a few items from the reader's current
      // position.
      comboBox1.Items.Clear();    
      oExtendedReader.Read();
      while ( ( !oExtendedReader.EOF ) && ( i > 0 ) )
        {
        comboBox1.Items.Add( oExtendedReader["lastname"].ToString() );
        i--;
        oExtendedReader.Read();
        }
      
      // If there are some matches, show the drop down
      if ( comboBox1.Items.Count > 0 )  
        comboBox1.DroppedDown = true;  
        
      // Set the cursor back to where it was
      comboBox1.SelectionStart = iCursor;             
    }

The video below shows the example application in action.



What are you using the AdsExtendedReader for? Any functionality you would like to see added?

1 comment:

Anonymous said...

AdsExtendedReader - We started using this about 10 months ago as part of a ws/wcf implementation against our dbf/ntx tables. We're in the middle of a conversion to a dictionary/adt schema (using xharbour & .net) and this provides us the perfect cross over tool to access our data and let our migration move forward. It's funny, I didn't know about this reader at first so I was using the ACE API thorugh C++/CLI for table navigation. Needless to say, once I found this little gem, the project went into C# - and you know what? - it was faster. The performance is spectacular. - Bradley Bell, Ceridian Florida.

Post a Comment