>>
>>My current idea on how to solve this is to make a grid and fill it with only the number of records that can be seen on screen, then handle the vertical scroll bar myself. I then replace those records when the scroll bar moves.
>
>I've done something like this in VFP and then I tried in C# (only the scroll bar (distinct object) handling part)
>It worked very well and very fast (like VFP local access speed for 100k rows).
>
>Basically, you will need the total count of records first, then set scrollbar.Maximum to that record count. It is possible to load before/after pages, based on PgUp/PgDn keys or positioning top/bottom when Ctrl+Home/End pressed.
>I had this code that worked in C# version. Take into account that I am a beginner in C# :)
>
>

>        private void Form1_Load(object sender, EventArgs e)
>        {
>            //this.vScrollBar1.Maximum
>            conn.Open();
>            cmd.Connection = conn ;
>            cmd.CommandText = "select count(*) from customers" ;
>            SqlDataReader reader = cmd.ExecuteReader();
>
>            while (reader.Read())
>            {
>                this.vScrollBar1.Maximum = reader.GetInt32(0);
>            }
>            reader.Close();
>
>        }
>
>
>        private void vScrollBar1_ValueChanged(object sender, EventArgs e)
>        {
>                tbl = new DataTable();
>                cmd.CommandText = String.Format(@"select * from
>                    ( select firstname, lastname, state, address, row_number() over 
                        (order by lastname,firstname) as recno from customers ) temp
>                    where recno between {0} and {1}", vScrollBar1.Value, vScrollBar1.Value + 20);
>                da = new SqlDataAdapter(cmd);
>                da.Fill(tbl);
>                bsrc.DataSource = tbl;
>                dataGridView1.DataSource = bsrc;
>        }
>
>

That's pretty similar to what I'm doing. But I decided to go direct to VFP since I'm not sure how much overhead I will wind up with using other methods. And I will probably need to be able to run VFP commands on the tables anyways. VFP does an excellent job of caching the data so I'll let it do that part.

So I have this in a VFP DLL:

  FUNCTION GetRecord(SelectArea as Integer,RecordNumber as Integer)
    DIMENSION THIS.aCurrentRecord[1]
    SELECT (SelectArea)
    GO RecordNumber 
    SCATTER TO THIS.aCurrentRecord MEMO
    RETURN @THIS.aCurrentRecord
  ENDFUNC

I then create a c# collection:

  public class cdsVFPTableView : ObservableCollection<cdsVFPRecord>
    {
    public cdsVFPTable Table { get; set; }
    public int            Size   { get; set; } // Number of records to display

    public cdsVFPTableView(cdsVFPSession session, string dbf)
      {
      Size  = 0;  // Display program sets this prior to first fill
      Table = new cdsVFPTable(session, dbf);  // sesion is the ole object, dbf is the file name to use
      }

    public void Fill(double perc)
      {
      this.Clear();
      int size = Math.Min(Table.RecCount, Size);
      int start = (int)(Table.RecCount * perc);
      if (start + size > Table.RecCount)
        {
        start = Table.RecCount - size;
        }
      for (int i = 0; i < size; i++)
        {
        this.Add(Table.GetRecord(start));
        start++;
        }
      }
    }

I'm using a WPF ListView to display the data with the vertical scroll bar disabled. This is just junk code that I thew up to test the concept. And shockingly it is working great with no optimizations. My list view is scrolling smoothly just as if it was normal list view.

I'll have to go to Skip() and Take() like Viv pointed out when dealing with ADO data sets but I think this will work well for VFP.

I think I'm going to go ahead and clean this up and go with it. Looks like it is going to work well for me. But if anyone sees a draw back to this approach that I missed, please let me know.