virtualmode Datagridview with just in time data loading via async-await data retriever

Question

I followed the example given here How to: Implement Virtual Mode with Just-In-Time Data Loading in the Windows Forms DataGridView Control to implement a just in time loading for a virtual mode DataGridView. This worked nice but given the size of the database I noticed a blocking of the UI thread during calls to my IDataPageRetriever. To address that, I implemented the async-await pattern into the class for the IDataPageRetriever. However, now there are a lot of rows not displaying any values or I need to click them to make them display the value. There must be something not straight forward in combining virtual-mode DataGridView with async-await.

I assume there is a typical pattern around and I miss something basic.

Thank you for your input!

Edit 1: adding code

DataGridView's CellValueNeeded

private async void dgvCompound_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
    {
        var dgv = (DataGridView)sender;
        try
        {
            e.Value = await memoryCache.RetrieveElement(e.RowIndex, dgv.Columns[e.ColumnIndex].DataPropertyName);
        }
        catch (OperationCanceledException)
        {
        }
        dgv.InvalidateRow(e.RowIndex);
    }

Cache

 public class Cache
{
    private static int RowsPerPage;
    public event EventHandler Initialised;
    public event EventHandler CacheChanged;

    // Represents one page of data.  
    public struct DataPage
    {
        public CompoundDataTable table;

        public DataPage(CompoundDataTable table, int rowIndex)
        {
            this.table = table;
            LowestIndex = MapToLowerBoundary(rowIndex);
            HighestIndex = MapToUpperBoundary(rowIndex);
            System.Diagnostics.Debug.Assert(LowestIndex >= 0);
            System.Diagnostics.Debug.Assert(HighestIndex >= 0);
        }

        public int LowestIndex { get; private set; }

        public int HighestIndex { get; private set; }

        public static int MapToLowerBoundary(int rowIndex)
        {
            // Return the lowest index of a page containing the given index.
            return (rowIndex / RowsPerPage) * RowsPerPage;
        }

        private static int MapToUpperBoundary(int rowIndex)
        {
            // Return the highest index of a page containing the given index.
            return MapToLowerBoundary(rowIndex) + RowsPerPage - 1;
        }
    }

    private DataPage[] cachePages;
    private IDataPageRetriever dataSupply;

    public Cache(IDataPageRetriever dataSupplier, int rowsPerPage)
    {
        dataSupply = dataSupplier;
        Cache.RowsPerPage = rowsPerPage;
        LoadFirstTwoPages();
    }

    public System.Data.SqlClient.SortOrder sortOrder
    {
        get { return dataSupply.sortOrder; }
        set { dataSupply.sortOrder = value; }
    }

    public string sortByColumn
    {
        get { return dataSupply.sortByColumn; }
        set
        {
            dataSupply.sortByColumn = value;
            Reload();
        }
    }

    public Dictionary<int, float> sortBySimilaritySeachResult
    {
        get { return dataSupply.sortBySimilaritySeachResult; }
        set
        {
            dataSupply.sortBySimilaritySeachResult = value;
            Reload();
        }
    }

    // Sets the value of the element parameter if the value is in the cache.
    private bool IfPageCached_ThenSetElement(int rowIndex, int columnIndex, ref string element)
    {
        if (IsRowCachedInPage(0, rowIndex))
        {
            if (cachePages[0].table == null || cachePages[0].table.Rows.Count == 0)
            {
                return true;
            }

            try
            {
                element = cachePages[0].table.Rows[rowIndex % RowsPerPage][columnIndex].ToString();

            }
            catch (Exception exx)
            {

                throw;
            }
            return true;
        }
        else if (IsRowCachedInPage(1, rowIndex))
        {
            if (cachePages[1].table == null || cachePages[1].table.Rows.Count == 0)
            {
                return true;
            }

            try
            {
                element = cachePages[1].table.Rows[rowIndex % RowsPerPage][columnIndex].ToString();
            }
            catch (Exception exx)
            {

                throw;
            }
            return true;
        }

        return false;
    }

    public async Task<string> RetrieveElement(int rowIndex, int columnIndex)
    {
        string element = null;

        if (IfPageCached_ThenSetElement(rowIndex, columnIndex, ref element))
        {
            return element;
        }
        else
        {
            return await RetrieveData_CacheIt_ThenReturnElement(rowIndex, columnIndex);
        }
    }

    static readonly CompoundDataTable c = new CompoundDataTable();
    public async Task<string> RetrieveElement(int rowIndex, string colName) => await RetrieveElement(rowIndex, c.Columns[colName].Ordinal);

    private async void LoadFirstTwoPages()
    {
        cachePages = new DataPage[]{
            new DataPage(await dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(0), RowsPerPage), 0),
            new DataPage(await dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(RowsPerPage),RowsPerPage), RowsPerPage)
        };
        Initialised?.Invoke(this, EventArgs.Empty);
        CacheChanged?.Invoke(this, EventArgs.Empty);
    }

    public async void Reload()
    {
        cachePages[0].table = await dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(0), RowsPerPage);
        cachePages[1].table = await dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(RowsPerPage), RowsPerPage);
        CacheChanged?.Invoke(this, EventArgs.Empty);
    }

    private async Task<string> RetrieveData_CacheIt_ThenReturnElement(int rowIndex, int columnIndex)
    {
        var IndexToUnusedPage = GetIndexToUnusedPage(rowIndex);
        // Retrieve a page worth of data containing the requested value.
        try
        {
            CompoundDataTable table = await dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(rowIndex), RowsPerPage);
            // Replace the cached page furthest from the requested cell
            // with a new page containing the newly retrieved data.
            cachePages[IndexToUnusedPage] = new DataPage(table, rowIndex);

            return await RetrieveElement(rowIndex, columnIndex);
        }
        catch (OperationCanceledException)
        {
            cachePages[IndexToUnusedPage] = new DataPage(null, rowIndex);
            throw;
        }
    }

    // Returns the index of the cached page most distant from the given index
    // and therefore least likely to be reused.
    private int GetIndexToUnusedPage(int rowIndex)
    {
        if (rowIndex > cachePages[0].HighestIndex && rowIndex > cachePages[1].HighestIndex)
        {
            int offsetFromPage0 = rowIndex - cachePages[0].HighestIndex;
            int offsetFromPage1 = rowIndex - cachePages[1].HighestIndex;
            if (offsetFromPage0 < offsetFromPage1)
            {
                return 1;
            }
            return 0;
        }
        else
        {
            int offsetFromPage0 = cachePages[0].LowestIndex - rowIndex;
            int offsetFromPage1 = cachePages[1].LowestIndex - rowIndex;
            if (offsetFromPage0 < offsetFromPage1)
            {
                return 1;
            }
            return 0;
        }
    }

    // Returns a value indicating whether the given row index is contained
    // in the given DataPage. 
    private bool IsRowCachedInPage(int pageNumber, int rowIndex)
    {
        return rowIndex <= cachePages[pageNumber].HighestIndex &&
            rowIndex >= cachePages[pageNumber].LowestIndex;
    }
}

DataRetriver

    public class DataRetriever : IDataPageRetriever
{
    private SemaphoreSlim _throttle;
    private static Queue<CancellationTokenSource> _tasklist;

    public DataRetriever()
    {
        sortByColumn = "Id";
        _throttle = new SemaphoreSlim(2);
        _tasklist = new Queue<CancellationTokenSource>();
        //just add two cancelation dummies
        for (int i = 0; i < _throttle.CurrentCount; i++)
        {
            _tasklist.Enqueue(new CancellationTokenSource());
        }
    }

    public int RowCount
    {
        get { return DB.dsTgxChemTableAdapters.CompoundTableAdapter.RowCount(); }
    }

    // Declare variables to be reused by the SupplyPageOfData method.
    private string _sortByColumn;
    public string sortByColumn
    {
        get { return _sortByColumn; }
        set
        {
            if (_sortByColumn == value)
            {
                sortOrder = sortOrder == SortOrder.Ascending ? SortOrder.Descending : SortOrder.Ascending;
            }
            else
            {
                _sortByColumn = value;
                sortOrder = SortOrder.Ascending;
            }
        }
    }

    public SortOrder sortOrder { get; set; }

    List<int> exclusion = new List<int>();

    public async Task<CompoundDataTable> SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
    {
        CompoundDataTable dt = new CompoundDataTable();
        bool dowork = false;
        lock (exclusion)
        {
            if (!exclusion.Contains(lowerPageBoundary))
            {
                exclusion.Add(lowerPageBoundary);
                dowork = true;
            }
        }

        if (dowork)
        {
            CancellationTokenSource cts = new CancellationTokenSource();
            _tasklist.Enqueue(cts);
            CancellationTokenSource prevous = _tasklist.Dequeue();
            prevous.Cancel();
            prevous.Dispose();

            await _throttle.WaitAsync(cts.Token);
            try
            {
                if (!cts.IsCancellationRequested)
                {
                    await DB.dsTgxChemTableAdapters.CompoundTableAdapter.FillAsync(dt, lowerPageBoundary, rowsPerPage, sortByColumn, sortOrder, cts.Token);
                }
            }
            finally
            {
                _throttle.Release();
                lock (exclusion)
                {
                    exclusion.Remove(lowerPageBoundary);
                }
            }
        }
        return dt;
    }
}

Answer 1

In my case I did this: I have exposed a few things on the Cache (which I call "data source") - a method LoadNextPage() and an event PageLoaded (this could be just one async method, but I found this split resulted in a bit cleaner code), and a cached row count (in your case it would be HighestIndex of the last cached page).

LoadNextPage() starts an asynchronous loading process of the data, and when the data is loaded and cached, the PageLoaded event fires.

The UI class is first calling LoadNextPage() to load the first page, then PageLoaded fires and I set the grid view's RowCount to the loaded cached row count.

After that the grid view starts calling CellValueNeeded for all cells, and you can fill it synchronously from the cache. When it requires data for the last row of the cache, I call LoadNextPage() again, and the process repeats.

So all the time the grid view is tricked that it only has cached rows and nothing else.

One catch was that CellValueNeeded might be called multiple times for the same row, so make sure you are not loading things twice in parallel this case.

I did it for the Git log in NitroGit Git client for Windows, so there it is a one-way loading process meaning that pages from 1 to N are always cached. If you have a different case, e.g. want to start in the middle and scroll up, or even populate random pages, you need to do more work, but it is possible with the same principle of tricking the grid view to just have the same number of rows as your cache has, and then mapping between the grid view row index and the real data index, while populating data "on a side" when the borders of the cache are reached in the grid on the screen.