Free!  FoxPro to WPF screen converter
   Almost free!  One day of FREE VFP to WPF evaluation of your FoxPro app ONSITE
   Free!  Read the foreword to Les' new book (available here)...
Skip Navigation Links

"Progressive Fetch" using delegates and StrataFrame

Download and try StrataFrame; it'll pay for itself the first day you use it.


Les Pinter

Login or register to download source code

A reader of mine from my newsletter publishing days recently called me to ask how to do something that was trivial in FoxPro. He wanted to populate a grid with some records from a SQL table, then return the rest in background. That way, his users could start looking at the records in the grid immediately, without waiting for them to finish downloading. That seems reasonable. Turns out, FoxPro has it, .NET doesn't.

But .NET can do anything. How hard can it be?

It wasn't hard, but neither was it as simple as checking a checkbox, as it is in FoxPro. It required using delegates. And I used StrataFrame, which always reduces the work load. Here's how I did it.

The test case

For this demonstration, I'll return the first 100 orders in (what else?) the NorthWind Database orders table. It only contains 730 records, so we'll have to simulate the delay that you get from returning records from SQL. But you'll see how it works, and you can try it on a large table of your own in just a few minutes.

<Rant>I frequently hear from FoxPro developers who are shocked at how slowly a dataset fills with records from a large table in .NET. It's not SQL Server; try the Query Analyzer, or run a SELECT query in SQL Server Management Studio, and the performance is similar to that of FoxPro. What is slow is the loading of a dataset. If you use a SQLDataReader, speed is not a problem. But for people used to returning a table and then manipulating it in memory, the DataSet approach seems to make more sense. A word to the wise: The DataReader exists because it's often the best way to do what you need to do. So use it!</Rant>

The form that I'm using for this demo is shown in Fig. 1:

Form with a grid for customer orders
Fig. 1 - Form with a grid for customer orders

The extra-wide button at the bottom of the form offers to return the first 100 records immediately, then return the rest in background while you scroll down through the grid. Run the sample program, and it does just that, with a one-second delay. Here's the code for the button.

//Form1 code-behind:

using System.Threading;

namespace AsyncSQL {
  public partial class Form1 : MicroFour.StrataFrame.UI.Windows.Forms.StandardForm {

    public Form1() { InitializeComponent(); }

    public delegate void RefreshTheGrid();
    public delegate void SetTextCallback(string text);

    private void Button1_Click(object sender, System.EventArgs e) {

      OrdersBO1.First100Orders();   // StrataFrame business object call (2 lines of code, below)
      dataGridView1.DataSource = OrdersBO1.CurrentView;
      label1.Text = "First 100 orders loaded"; label1.Refresh();

      Thread t = new Thread(GetRest);
      t.Start();
    }

    public void GetRest() {

      Thread.Sleep(1000);  // simulate delay; comment out to test with a large table

      OrdersBO1.RestOfOrders();// StrataFrame business object call (1 line of code, below)
      string TextLoaded = "Rest of orders loaded ( " + OrdersBO1.Count.ToString() + " records )";

      RefreshTheGrid cb1 = new RefreshTheGrid(GridRefresher); // delegate, declared above
      Invoke(cb1);
      SetTextCallback cb2 = new SetTextCallback(SetText);     // delegate, declared above
      Invoke(cb2, new object[] { TextLoaded });
    }

    private void GridRefresher() { dataGridView1.DataSource = OrdersBO1.CurrentView; }

    private void SetText(string text) { label1.Text = text; }

  }
}

// Business object code:

using System;
using System.ComponentModel;
using System.Runtime.Serialization;
using MicroFour.StrataFrame.Business;

namespace AsyncSQL {
  [Serializable()]
  public partial class OrdersBO : MicroFour.StrataFrame.Business.BusinessLayer {

    public int lastorder;

    #region Constructors
    ( no customization )
    #endregion

    #region Data Retrieval Methods

    public void First100Orders()
    { FillDataTable("SELECT TOP 100 * FROM Orders"); MoveLast(); lastorder = (int)this["OrderID"]; }

    public void RestOfOrders()
      { string s = "SELECT * FROM ORDERS WHERE OrderID > " + lastorder.ToString();
        AppendDataTable(s, AppendDataTableOptions.ReplaceExistingDuplicates);
    }

    #endregion

    #region Event Handlers
    (no customization)
    #endregion

  }
}

I'll comment between chunks of code:

using System.Threading;

namespace AsyncSQL {
  public partial class Form1 : MicroFour.StrataFrame.UI.Windows.Forms.StandardForm {

    public Form1() { InitializeComponent(); }

I collapse the sparse source code favored by the C# editor into fewer lines where it doesn't do any harm; if you'd rather see this:

    public Form1()
     {
       InitializeComponent();
     }

feel free to reformat the code if your personal value system demands it. I'm trying to save a little vertical real estate.

I needed two delegate declarations which will be used later. One has a parameter, because threads can't see fields that were declared in the calling code.

    public delegate void RefreshTheGrid();
    public delegate void SetTextCallback(string text);

The Button-Click method calls the OrdersBO business object's First100_Orders method, which loads the OrdersBO business object with the first 100 records using StrataFrame's FillDataTable method.

Note: StrataFrame has two types of methods to load business objects; Fill methods are tied to databinding; Get methods don't move the record pointer or otherwise meddle with the UI. You use them (for example) to return a table that you want to loop through without reflecting the location of the record pointer on the screen. Since what we're doing here is all tied to the Grid, the Fill method is the one to use. The business object's CurrentView is like a dataset's DefaultView, and we assign it to the DataSource of the DataGridView.

Finally, I refresh the text of a little label that you can just barely see to the right of the Button (see the "..."?):

    private void Button1_Click(object sender, System.EventArgs e) {

      OrdersBO1.First100Orders();
      dataGridView1.DataSource = OrdersBO1.CurrentView;
      label1.Text = "First 100 orders loaded"; label1.Refresh();

Here's where it gets interesting. I start a new thread, which is as simple as declaring it and calling its Start() method:

      Thread t = new Thread(GetRest);
      t.Start();
    }

GetRest() can be whatever you want; it will start up and run independently, finishing whenever it finishes. Your users can go on with whatever they were doing. You're responsible for notifying your users when it's done; I just updated a label on the form, as you'll see below.

Recall that we used a business object method called First100Orders that I wrote for the OrdersBO class (see above). At the end of that method, I saved the OrderID if the 100th (last) record in the dataset by declaring an int called lastrecord in the business object, then saving the OrderID value to it at the end of First100Orders:

    public void First100Orders()
    { FillDataTable("SELECT TOP 100 * FROM Orders");
      MoveLast(); lastorder = (int)this["OrderID"]; }

Since we don't have very many records in the Northwind ORDERS table, I had to simulate a delay: That's what the Thread.Sleep(1000) statement is for; in your test of this technique (which you'll do right after you finish reading this article in order to master the technique once and for all, right?) you don't need that line of code.

So all that's left is to load the rest of the orders, starting right after the last one the we loaded in the First100Orders method, then refresh the grid and redisplay the record count. Here's how it's done:

      OrdersBO1.RestOfOrders();
      string TextLoaded = "Rest of orders loaded ( " + OrdersBO1.Count.ToString() + " records )";

      RefreshTheGrid cb1 = new RefreshTheGrid(GridRefresher); // delegate, declared above
      Invoke(cb1);
      SetTextCallback cb2 = new SetTextCallback(SetText);     // delegate, declared above
      Invoke(cb2, new object[] { TextLoaded });
    }

The RestOfOrders uses the saved value of the last OrderID in the 100 orders returned in the First100Orders method to load the rest of the orders into the OrdersBO business object. They just get appended to the bottom of the first 100 records, because unless you clear out the business object before you fill it with more data, the data stays there and the new data goes at the end of it. Makes sense...

    public void RestOfOrders()
    { string s = "SELECT * FROM ORDERS WHERE OrderID > " + lastorder.ToString();
      AppendDataTable(s, AppendDataTableOptions.ReplaceExistingDuplicates);
    }

How delegates are used

Calling a function in a thread requires creating an instance of the delegate using the method you want to call as its parameter. Let me say that again slowly:

    RefreshTheGrid                    cb1 = new RefreshTheGrid( GridRefresher); Invoke(cb1);
    Create one of these guys, call it cb1,  by using it to call GridRefresher;  So do it already!

Kinda sucks, but I can't think of a better way to do it either. It's certainly not intuitively obvious. However, the good news is, it works as advertised.

Each of the two methods called by the delegates consists of a single line of code: Again, if you prefer to turn this into four lines of code, knock yourself out. Note that once we've called the RestOfOrders() method of the OrdersBO business object, the delegate has to be used to assign the CurrentView of OrdersBO to the grid's DataSource, since, again, threads can't communicate with form objects directly; that's why we're using delegates.

    private void GridRefresher() { dataGridView1.DataSource = OrdersBO1.CurrentView; }

    private void SetText(string text) { label1.Text = text; }

...and you're done.

The partially populated grid waiting for the rest of the records
Fig. 2 - The partially populated grid waiting for the rest of the records


The fully populated grid
Fig. 3 - The fully populated grid

The source code download contains both C# and VB versions so you can learn the other language like you've been intending to do for...how long?

If you haven't used delegates, you might have to concentrate for a few seconds. I'll admit they're funny. But that's the only way to run something in a thread that can communicate with the calling program. Stroll around the grounds until you feel at home...


Conclusion

I avoided using delegates for years, and somehow managed to get the job done without them. But if it's actually only a few lines of code, and StrataFrame's Fill methods and generated business objects really do reduce the job to the bare minimum.

See 'ya

Les

qqq


Copyright(C) Pinter Consulting, 2012Tel: +1 (650) 464-6924
Automated conversion between C# and VB by Visible C#/Visible VB from Tangible Software Solutions