Using an OleDbDataAdapter on a background thread causes my UI to not update correctly?

Question

I have an app where users can select an Excel file, that excel file is read using an OleDbDataAdapter on another thread, and once it is finished being read it updates the CanExecute property of a Command in my ViewModel to true so the Save button is enabled.

My problem is, even though the PropertyChanged event of the command gets raised AND the CanExecute is evaluated as true, the button on the UI never gets enabled until the user does something to interact with the application (click on it, select a textbox, etc)

Here is some sample code that shows the problem. Just hook it up to two buttons bound to SaveCommand and SelectExcelFileCommand, and create an excel file with a column called ID on Sheet1 to test it.

private ICommand _saveCommand;
public ICommand SaveCommand
{
    get 
    {
        if (_saveCommand == null)
            _saveCommand = new RelayCommand(Save, () => (FileContents != null && FileContents.Count > 0));

        // This runs after ReadExcelFile and it evaluates as True in the debug window, 
        // but the Button never gets enabled until after I interact with the application!
        Debug.WriteLine("SaveCommand: CanExecute = " + _saveCommand.CanExecute(null).ToString());
        return _saveCommand;
    }
}
private void Save() { }

private ICommand _selectExcelFileCommand;
public ICommand SelectExcelFileCommand
{
    get
    {
        if (_selectExcelFileCommand == null)
            _selectExcelFileCommand = new RelayCommand(SelectExcelFile);

        return _selectExcelFileCommand;
    }
}
private async void SelectExcelFile()
{
    var dlg = new Microsoft.Win32.OpenFileDialog();
    dlg.DefaultExt = ".xls|.xlsx";
    dlg.Filter = "Excel documents (*.xls, *.xlsx)|*.xls;*.xlsx";

    if (dlg.ShowDialog() == true)
    {
        await Task.Factory.StartNew(() => ReadExcelFile(dlg.FileName));
    }
}

private void ReadExcelFile(string fileName)
{
    try
    {
        using (var conn = new OleDbConnection(string.Format(@"Provider=Microsoft.Ace.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", fileName)))
        {
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT DISTINCT ID FROM [Sheet1$]", conn);
            var dt = new DataTable();

            // Commenting out this line makes the UI update correctly,
            // so I am assuming it is causing the problem
            da.Fill(dt);


            FileContents = new List<int>() { 1, 2, 3 };
            OnPropertyChanged("SaveCommand");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Unable to read contents:\n\n" + ex.Message, "Error");
    }
}

private List<int> _fileContents = new List<int>();
public List<int> FileContents
{
    get { return _fileContents; }
    set 
    {
        if (value != _fileContents)
        {
            _fileContents = value;
            OnPropertyChanged("FileContents");
        }
    }
}

EDIT

I've tried using the Dispatcher to send the PropertyChanged event at a later priority, and moving the PropertyChanged call outside of the async method, but neither solution works to update the UI correctly.

It DOES work if I either remove the threading, or launch the process that reads from Excel on the dispatcher thread, but both of these solutions cause the application to freeze up while the excel file is being read. The whole point of reading on a background thread is so the user can fill out the rest of the form while the file loads. The last file this app got used for had almost 40,000 records, and made the application freeze for a minute or two.

Unless you have something built into the OnPropertyChanged your code is just not thread-safe. See for example http://stackoverflow.com/questions/5953087/wpf-mvvm-threading-timer-and-timercallback-problems/5953177#5953177 — H H, Jul 21 '11 at 21:08
@Henk I tagged it as C# 5 because I am using async/await keywords, and if I setup the PropertyChanged event to run on DispatcherPriority.Loaded it still fails to update the UI correctly. — Rachel, Jul 22 '11 at 12:31
@Sres WPF doesn't have a `DoEvents()`, however the Dispatcher works in a similar fashion, which I have tried — Rachel, Jul 22 '11 at 13:05

Yahia · Answer 1 · 2011-07-23T00:35:08.630

1

not sure, but if you remove the await - does it help ?

EDIT:

I am no expert on C# 5 but what I gather that await wait for the launched task(s) to finish... it is a way to synchronize so the after the await the result be accessed without further checking whether the task(s) already finished... From the post I think that await is not needed and that it somehow "blocks" the OnPropertyChange call from the insise the launched Task.

EDIT 2 - another try:

if (dlg.ShowDialog() == true)
    {
        string FN = dlg.FileName;
        Task.Factory.StartNew(() => ReadExcelFile(FN));
    }

EDIT 3 - solution (without C# 5 though):

I created a fresh WPF app, put 2 buttons (button1 => select excel file, button2 => Save) in the designer... I removed all "OnPropertyChanged" calls (I used this.Dispatch.Invoke instead)... RelayCommand is 1:1 from http://msdn.microsoft.com/en-us/magazine/dd419663.aspx ... following is the relevant changed source:

private  void SelectExcelFile()
{
    var dlg = new Microsoft.Win32.OpenFileDialog();
    dlg.DefaultExt = ".xls|.xlsx";
    dlg.Filter = "Excel documents (*.xls, *.xlsx)|*.xls;*.xlsx";

    if (dlg.ShowDialog() == true)
    {
        Task.Factory.StartNew(() => ReadExcelFile(dlg.FileName));
    }
}

private List<int> _fileContents = new List<int>();

public List<int> FileContents
{
    get { return _fileContents; }
    set 
    {
        if (value != _fileContents)
        {
            _fileContents = value;

            this.Dispatcher.Invoke ( new Action (delegate() 
            {
                button2.IsEnabled = true;
                button2.Command = SaveCommand;
            }),null);
        }
    }
}

private void button1_Click(object sender, RoutedEventArgs e)
{
    button2.IsEnabled = false;
    button2.Command = null;
    SelectExcelFileCommand.Execute(null);
}

private void button2_Click(object sender, RoutedEventArgs e)
{
    SaveCommand.Execute(null);
}

all problems described by the OP are gone: the Excel reading is on another thread... the UI does not freeze... the Savecommand gets enabled if the Excelreading is successfull...

EDIT 4:

                this.Dispatcher.Invoke(new Action(delegate()
                {
                    CommandManager.InvalidateRequerySuggested();
                }), null);

you can use this instead of the IsEnabled... causes the CanExecuteChanged event to be fired without "rebuilding" the SaveCommand (which causes the CanExecuteChanged event to be unregistered and then reregistered)

edited Jul 23 '11 at 00:35

answered Jul 22 '11 at 16:47

Yahia

69,653
9
115
144

I already said it fixes the problem, but it also stops the application from reading the excel file on another thread, so it locks up while it is reading the file. The whole point of putting it on another thread was to stop it from freezing while reading files so users could continue to enter data. – Rachel Jul 22 '11 at 16:53
StartNew() already accomplishes what you want (starting it on another thread)... await just says "wait till the Task is finished... try my EDIT 2... – Yahia Jul 22 '11 at 16:57
I need the result of the excel file to know if the Save command can execute or not though. If the result is an invalid or empty list, the Save command cannot be executed. Also, the `Task.Factory.StartNew()` returns a `Task>` object, not a `List`. It is meant to be used with `await` – Rachel Jul 22 '11 at 17:10
another point: ReadExcelFile has no result (void)... I don't understand how this could result in List or Task> ? – Yahia Jul 22 '11 at 17:19
I was trying it both ways - Setting the FileContents property within the async method and outside of it. – Rachel Jul 22 '11 at 17:46
Removing await does not fix the problem – Rachel Jul 22 '11 at 18:08
Can you get it working without setting IsEnabled directly? I'd prefer to use the CanExecute method of the Command instead of needing to create a new boolean property. – Rachel Jul 22 '11 at 19:53
the IsEnabled is just to make the button clickable for the user... the CanExecute can still be used as before – Yahia Jul 22 '11 at 21:19
When I add the CanExecute() to the RelayCommand, the button stays disabled. I realized though that the act of re-binding the Command fixed the issue so I only need to set the `Command = null` to correct the issue. Thank you – Rachel Jul 22 '11 at 21:40
see the edit 4 => IsEnabled not needed, rebuilding SaveCommand not needed – Yahia Jul 23 '11 at 00:47
It does work, but is there a way I can have it only requery one Command when I call it instead of all of them? – Rachel Jul 25 '11 at 12:18
none that I know of :-( the requery happens often (for example when the user does something with the form) so I won't expect it to be "expensive" – Yahia Jul 25 '11 at 12:19

score 1 · Accepted Answer · answered Aug 08 '11 at 20:51

From what I can follow this might be what you need.

public static void ExecuteWait(Action action)
{
   var waitFrame = new DispatcherFrame();

   // Use callback to "pop" dispatcher frame
   action.BeginInvoke(dummy => waitFrame.Continue = false, null);

   // this method will wait here without blocking the UI thread
   Dispatcher.PushFrame(waitFrame);
}

And calling the following

    if (dlg.ShowDialog() == true)         
    {             
        ExecuteWait(()=>ReadExcelFile(dlg.FileName));
        OnPropertyChanged("SaveCommand");
    }

score 0 · Answer 3 · answered Jul 22 '11 at 20:06

I still have no idea what it's problem is, but I have found a workaround. I simply set my SaveCommand = null and raise a PropertyChanged event to re-create the Command (the set method on the command builds the RelayCommand if it is null).

I have no idea why simply raising the PropertyChanged event won't update the UI. According to my Debug, the get method is getting called again and evaluating at CanExecute = true even though the UI doesn't update.

private async void SelectExcelFile()
{
    var dlg = new Microsoft.Win32.OpenFileDialog();
    dlg.DefaultExt = ".xls|.xlsx";
    dlg.Filter = "Excel documents (*.xls, *.xlsx)|*.xls;*.xlsx";

    if (dlg.ShowDialog() == true)
    {
        await Task.Factory.StartNew(() => ReadExcelFile(dlg.FileName));
    }
}

private void ReadExcelFile(string fileName)
{
   try
    {
        using (var conn = new OleDbConnection(string.Format(@"Provider=Microsoft.Ace.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", fileName)))
        {
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT DISTINCT [File Number] FROM [Sheet1$]", conn);
            var dt = new DataTable();

            // Line that causes the problem
            da.Fill(dt);

            FileContents = new List<int>() { 1, 2, 3 };

            // Does NOT update the UI even though CanExecute gets evaluated at True after this runs
            // OnPropertyChanged("SaveCommand");

            // Forces the Command to rebuild which correctly updates the UI
            SaveCommand = null;  

        }
   }
    catch (Exception ex)
    {
        MessageBox.Show("Unable to read contents:\n\n" + ex.Message, "Error");
    }
}

private ICommand _saveCommand;
public ICommand SaveCommand
{
    get 
    {
        if (_saveCommand == null)
            _saveCommand = new RelayCommand(Save, () => (FileContents != null && FileContents.Count > 0));

        // This runs after ReadExcelFile and it evaluates as True in the debug window!
        Debug.WriteLine("SaveCommand: CanExecute = " + _saveCommand.CanExecute(null).ToString());
        return _saveCommand;
    }
    set
    {
        if (_saveCommand != value)
        {
            _saveCommand = value;
            OnPropertyChanged("SaveCommand");
        }
    }
}

Using an OleDbDataAdapter on a background thread causes my UI to not update correctly?

3 Answers3