Loading from database into grid in a background thread

[NickThissen]

Hi,

I have a grid that is supposed to show records from a database that depict scientific papers (title, authors, year, journal, etc). The user can categorize each paper into multiple categories.

Beside the grid I now have a CheckedListbox that lists all categories. The user can check or uncheck categories, and the grid should then display only the papers that belong to the checked categories.

I would now like to load the grid asynchronously in a background thread, because it might take a while to load all papers from the database and then check them against the checked categories.

I tried two different approaches:

1. Using a BackgroundWorker
2. Creating a thread manually

For the BackgroundWorker, I have this code:

private EntityCollection<Paper> papers = new EntityCollection<Paper>();
        
        // Called when the user checks an item in the categories list
        public override void RefreshData()
        {
            dataLoader.CancelAsync();
            dataLoader.RunWorkerAsync();
        }
        
        private void dataLoader_DoWork(object sender, DoWorkEventArgs e)
        {
            // Get the checked categories:
            var categories = ViewFormObserver.Instance.MainForm.GetSelectedPaperCategories();
 
            // Get all papers:
            var allPapers = PaperManager.Instance.Load();
 
            // Clear the list of papers
            papers.Clear();
 
            // Add only those papers that have any of the checked categories
            bool found = false;
            papers.Clear();
            foreach (var paper in allPapers)
            {
                found = false;
 
                foreach (var category in categories)
                {
                    if (!found)
                    {
                        if (paper.Categories.ContainsId(category.Id))
                        {
                            papers.Add(paper);
                            found = true;
                            continue;
                        }
                    }
                }
            }
        }
 
        private void dataLoader_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            // Set the datasource of the grid
            this.Grid.DataSource = papers;
            this.Grid.DataBind();
        }

'dataLoader' is the BackgroundWorker component.

EntityCollection<T> inherits List<T> and provides some useful methods for my database code (such as ContainsId, which checks if the collection contains a database record with the specified Id).

The PaperManager.Instance.Load() method loads all papers from the database (this is the method that probably takes the longest). Then a loop runs through every paper and every selected category and 'filters out' the papers that don't have any selected category. For a large number of papers this could also take a while.


Anyway, this works, but not if I click too quickly in the CheckedListBox. If I check an item while the grid is loading, for example I can doubleclick an item so it's checked and immediately unchecked again, then the BackgroundWorker throws an exception on RunWorkerAsync:

This BackgroundWorker is currently busy and cannot run multiple tasks concurrently.

I suppose it is not done cancelling yet thus cannot start another load operation.


So, I switched to using my own thread manually:

private EntityCollection<Paper> papers = new EntityCollection<Paper>();
        private Thread loadingThread;
        
        public override void RefreshData()
        {
            // If we are already loading, abort
            if (loadingThread != null)
            {
                loadingThread.Abort();
                loadingThread = null;
            }
            
            // Create a new thread and start it
            var finishedCallback = new MethodInvoker(LoadDataFinished);
            loadingThread = new Thread(LoadData);
            loadingThread.IsBackground = true;
            loadingThread.Start(finishedCallback);
        }
 
        private void LoadData(object finishedCallback)
        {
            var callback = (MethodInvoker)finishedCallback;
 
            var categories = ViewFormObserver.Instance.MainForm.GetSelectedPaperCategories();
            var allPapers = PaperManager.Instance.Load();
 
            bool found = false;
            papers.Clear();
            foreach (var paper in allPapers)
            {
                found = false;
 
                foreach (var category in categories)
                {
                    if (!found)
                    {
                        if (paper.Categories.ContainsId(category.Id))
                        {
                            papers.Add(paper);
                            found = true;
                            continue;
                        }
                    }
                }
            }
 
            callback.Invoke();
        }
 
        private void LoadDataFinished()
        {
            this.Grid.DataSource = papers;
            this.Grid.DataBind();
        }

Code is basically the same, except instead of calling BackgroundWorker.RunWorkerAsync I create a new thread and start it. I pass a MethodInvoker to the thread which it can invoke when the loading is finished.

The problem here is that I have to Abort the thread if it's already running. As far as I'm aware you should never abort a thread unless you're application is exiting, so this seems completely wrong...

Also, sometimes it seems to grid 'crashes'. It throws an unhandled exception and draws a red cross through the control. The exception is thrown on a seemingly random method, somewhere inside the grid control (the grid is a third party grid, but that shouldn't matter I think...).

So there's definitely something wrong with my threading....



Any idea what I'm doing wrong and how I can avoid it?

In case it isn't clear, here's what I'm trying to do:

- User checks or unchecks a category in a listbox.
- Grid starts to refresh asynchronously, in the background
- If the user checks or unchecks another category while the grid is loading, the current loading operation is aborted, and it starts to load again.

There should obviously not be a 'queue' of loading operations, if the user checks 4 items in a row I want it to abort the first three loading operations and only finish the fourth, I don't want it to load the first, then load the second, then the third and finally the fourth...

Thanks!