How to update items in a RadGridView with VirtualQueryableCollectionView

[Jesse]

Let me start by saying I am new to DevTools and I have searched for several hours trying to find the answers to these questions. So here I am asking questions that probably have been answered elsewhere. If so just point me in the right direction.

I have an application that presents potentially large tables of read-only data to a user from a SQL Server 2012 database. The problem is that new rows are continually being added to the table and occasionally arbitrary rows in the table change due to external updates to the underlying database. I have external notification via a SQL Query Notification system when specific rows change or when new data is added to the table but I don't know how to best get these changes to the user interface.

I have a nice demo program running using a VirtualQueryableCollectionView linked to a wizard generated ADO.NET Entity Data Model object that becomes the DataContext for a RadGridView control. This code is almost directly copied from the Data Virtualization help. It seems to do a good job of paging through the table without excessive queries and without holding everything in memory. See the following code snips:

EntityConnectionStringBuilder ecb = new EntityConnectionStringBuilder();
ecb.Metadata = "res://*/SurveyModel.csdl|res://*/SurveyModel.ssdl|res://*/SurveyModel.msl";
ecb.Provider = "System.Data.SqlClient";
ecb.ProviderConnectionString = Database.MakeDataSource("DR1Search-bab33c3e-7117-4405-ad58-7b5f6fbfa4cd");
var entity = new MeasurementsEntities(ecb.ConnectionString);
var query = entity.MeasurementPoints.OrderBy(o => o.MeasurementID);
var view = new VirtualQueryableCollectionView(query) { LoadSize = 50 };
DataContext = view;

<telerik:RadGridView Name="radGridView1" ColumnWidth="*" DataLoadMode="Asynchronous" ItemsSource="{Binding}" Margin="0,45,0,0" />

Question 1: How do I trigger an update to the VirtualQueryableCollectionView and the RadGridView when new rows are added to the associated table without resetting the RadGridView and without re-querying rows that are already in the collection? One problem I want to avoid is that when a user is scrolling through the items in the RadGirdView using the up and down arrows I don't want the scroll position to reset every time a new row is added to the table. This system adds 1 row to the table every second.

Question 2: How can I update an item in the collection when the item changes in the table without re-querying a page of data or reseting the RadGridView? I already have the updated item data so all I really need to do is determine if the changed item is cached in the collection and update it. Also if the updated item is visible it needs to be updated in the RadGridView.

Question 3: This should be easy but I just haven't found the answer yet. Given the system described above, how do I scroll the RadGridView to make an arbitrary row in the underlying table visible? In this case I don't care about resetting the control or re-querying data. This only happens in response to a user action and it can take more time and resources to accomplish.

I don't require any automatic monitoring of database changes since I already have a change notification system built into my programs. All I need is to update the controls to be aware of the changes.

Thanks in advance,

Jesse

[Vlad]

Hello Jesse,

 Straight onto your questions:

1) You can use AddNew() or Add() methods of the collection to add new item(s). You will need also to increase VirtualItemCount. You can take as an example the project from this blog post and add the following to test it:
...
        DispatcherTimer timer;
        private VirtualQueryableCollectionView items;
        public VirtualQueryableCollectionView Items
        {
            get
            {
                if (this.items == null && this.SelectedQuery != null)
                {
                    this.items = new VirtualQueryableCollectionView(this.SelectedQuery.Select(this.SelectDescriptors)) { LoadSize = 10 };

                    timer = new DispatcherTimer() { Interval = TimeSpan.FromMilliseconds(1000) };
                    timer.Tick += (s, e) => 
                    {
                        this.items.AddNew();
                        this.items.VirtualItemCount++;
                    };
                    timer.Start();
                }

                return items;
            }
        }

        private void InvalidateItems()
        {
            this.items = null;
            
            if (this.timer != null)
            {
                this.timer.Stop();
                this.timer = null;
            }

            this.OnPropertyChanged(() => this.Items);
        }
...

2) You can use Load() method of the collection to replace some of the existing items with new ones. 

3) Normally you can use RadGridView ScrollIntoView/ScrollIntoViewAsync methods to scroll to some item however with the virtual collection the situation is a bit tricky since you will need first to request the item to be loaded and once loaded call ScrollIntoView. To request the item to be loaded you can simply access the indexer for desired item index. 

All the best,
Vlad
the Telerik team

[Jesse]

Vlad,

I tried calling AddNew() and incrementing the VirtualItemCount in my VirtaulQueryableCollectionView when new data became available in my table. This caused the bound RadGridView control to reload all of the data and reset the current selected item. I went to manually defined columns which made the data reload much faster but it still reloads all of the visible items (maybe all of the collection items).

This behavior won't work for me since rows are added to my table every second. Is there a way (even if it is complicated) to get the RadGridView to not reset the view but simply use the new data when it is required (i.e. when the user scrolls the data into view)? I don't want the control to do anything other than maybe adjust the size of the thumb when new data is added to the end of the table. I can even live with the thumb not changing if it will allow the user to interact with the existing items without them resetting every second.

Jesse

[Vlad]

Hello Jesse,

 Maybe in this case you do not need our virtual collection. You can use any other INotifyCollectionChange collection to achieve your goal. 

Greetings,
Vlad
the Telerik team

[Jesse]

My design requirement is to support up to 1 million records in this table so I think that requires some level of virtualization. If I abandoned your VirtualQueryableCollection and went to an observable collection that I manage myself, how would I accomplish my requirement to add items to the end of the list and have the bound RadGridView not reset?

The thing I need to understand is how to use your RadGridView control with any observable collection in a way that allows additions to the end of the collection without having the user's selection reset to. Currently, the user cannot use the arrow or page up/down keys to scroll through the collection because the down arrow jumps to the first item in the list after the collection is updated. Can you tell me what is causing the down arrow to jump back to the first item after the collection is updated?

I am willing to develop my own collection but the control must operate in a virtual recycling mode or else I will fill my memory with grid view items. I also need the user interface to the control to work smoothly while the collection is updated. Can your products do this?

[Vlad]

Hi,

 When you add an item in an ObservableCollection RadGridView will not reset anything. The grid will just add the new item. The reset can happen only if you raise Reset CollectionChanged from your collection. 

Regards,
Vlad
the Telerik team