Need example how to load large data into gridView in WPF

[Paweł]

Hi.
As in topic. I've checked lot of topics in forum, but still dont have answer for previous and this topic.
At this moment i'm getting data from SqlDataAdapter:

SqlCommand cmd = new SqlCommand("getProducts", conn);
                SqlParameter par = cmd.Parameters.Add("catID", CatID);
                cmd.CommandType = CommandType.StoredProcedure;

(...)
productsDataGrid.Items.Add(new Product{...});

But it tooks 5-10 sec to load gridView.
Help me please, I need good example step by step how can i load data to structure and then preview them in gridView.

Thanks

[Rossen Hristov]

Hi,

You should first fill all of your data in some kind of in-memory collection or a DataTable. Once the collection is full of data, you assign it to RadGridView.ItemsSource. More about data binding you can learn here.

If you have a lot of data, you can use paging to improve performance. You page the grid like this:

<Grid x:Name="LayoutRoot"
        Background="White">
    <Grid.RowDefinitions>
        <RowDefinition />
        <RowDefinition Height="Auto" />
    </Grid.RowDefinitions>
    <telerik:RadGridView x:Name="radGridView"
                         ItemsSource="{Binding Employees}"
                         AutoGenerateColumns="False">
        <telerik:RadGridView.Columns>
            <telerik:GridViewDataColumn DataMemberBinding="{Binding Name}" />
            <telerik:GridViewDataColumn DataMemberBinding="{Binding CompanyName}" />
            <telerik:GridViewDataColumn DataMemberBinding="{Binding Title}" />
        </telerik:RadGridView.Columns>
    </telerik:RadGridView>
    <telerik:RadDataPager x:Name="radDataPager"
                          Source="{Binding Items, ElementName=radGridView}"
                          PageSize="5" />
</Grid>

I hope this helps.

Regards,
Rossen Hristov
Telerik

[Paweł]

Ok, so thats what i did:
I've created method to load data:

Data = new ObservableCollection<Produkt>();
            
            int warehouseID = int.Parse(Application.Current.Properties["warehouseID"].ToString());
            using (SqlConnection conn = new SqlConnection(cString.c_String))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("getProducts", conn);
                cmd.Parameters.AddWithValue("catID", -2);
                cmd.Parameters.AddWithValue("warehouseID", warehouseID);
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    string _vat;
                    while (rdr.Read())
                    {

                        Data.Add(new Product
                        {
                            (...)
                        });
                    }
                }
            }

And becouse it's list of product, which i want to change when listviewitem (category) changed (its like i have grid with two columns, in first i got listview with categories, in second gridView, so after listviewitem changed, im populating gridView with items where categoryID = selectedCategory).
Problem is still that it tooks long to load data.

Maybe there is solution to do something like:
Load only 20 items after open window, when scrolling down load async more items, but filters should work for all items ?

[Rossen Hristov]

Hi,

Unfortunately, this is not possible with a technology such as SqlDataReader, which is like 10 years old. It is possible -- but you will have to implemented it yourself, which I don't know how.

Maybe you should switch to one of the more modern technologies such as LINQ-to-SQL or LINQ-to-Entities. The common thing between all these technologies is called LINQ and the IQueryable interface, which RadGridView perfectly binds to. By binding RadGridView to such an IQueryable you will be able to perform filtering, sorting and grouping directly on your SQL Server -- that is the whole idea behind LINQ.

If you throw a RadDataPager in, you will be able to page directly on the server as well.

If you are interested in Entity Framework, we have a control called RadEntityFrameworkDataSource which will let you do all operations directly on the server -- paging (Skip and Take), filtering (Where), sorting (OrderBy) and grouping (GroupBy). Simply bind a RadGridView and a RadDataPager to a RadEntityFrameworkDataSource and you will get this out-of-the-box. The rest is explained in documentation.

You can see the control in action if you open our online QSF and search for RadEntityFrameworkDataSource. The example contains a grid and a pager doing their stuff directly on the server.

Regards,

Rossen Hristov
Telerik

[Paweł]

Well ok, if u saying that reader is old then I'll of course try Entities Model.
Actually im in the middle of getting data using links u gave me.
If You can, check please if there is something wrong, becouse now i can't even see any data in gridView:

So first of all i Created new ADO.NET Entity Data Model and called it Model1.
Then in wizard i choose to use stored procedure called GetProducts (int catID, int warehouseID)
Now in my Model browser I can see that:
Model.Store
StoredProcedures -> GetProducts

I guess becouse of that it's a stored procedure i cannot display it as table in Model Designer.

Now in XAML where i want to show data:

            <my:RadEntityFrameworkDataSource Grid.Row="0" Name="getProductDS" QueryName="GetProducts" >
                <telerik:RadEntityFrameworkDataSource.ObjectContext>
                    <backofficewindows:ModelEntity></backofficewindows:ModelEntity>
                </telerik:RadEntityFrameworkDataSource.ObjectContext>
            </my:RadEntityFrameworkDataSource>

And GridView:

   <telerik:RadGridView Grid.Row="0"  Name="productsDataGrid"  ItemsSource="{Binding DataView, ElementName=getProductDS}" >
                <telerik:RadGridView.Columns>
                    <telerik:GridViewDataColumn Name="col0" DataMemberBinding="{Binding productCode}" Header="Code" />
(....)
</telerik:RadGridView.Columns>
            </telerik:RadGridView>


Is that right?


--------@EDIT
Ok. I'm getting ItemsSource now from code behind like:
productsDataGrid.ItemsSource = mcs.GetProducts(-1, 1);

where mcs is
mcs = new ModelCS(); //Entity from Model1.edmx

And i have to say loading data isnt really faster.. its slower. But i hope now, when Im using ADO.NET Entity Data Model i can load into dataGrid only (for example) 20 rows, and rest of them load async when changing page in dataPager or (without dataPager) when scrolling data down?

[Rossen Hristov]

Hi,

I am afraid that out-of-the-box paging works only when querying actual tables, because the pager appends Skip and Take calls to the IQueryable, i.e. the ObjectQuery which are later send to SQL server in the Select statement. 

Stored procedures are totally different -- they are no simple SELECT queries.

When using stored procedures, the paging will have to be implemented by you by passing a parameter to your stored procedure about which page to return. You can use the pager in its Unbound Mode. When in Unbound Mode, the pager is not bound to anything. You simply tell it the total number of items and the page size and the pager build its UI, i.e. the list of pages. Then when the user clicks somewhere, you listen for the PageIndexChanged event and react somehow to manually pull the page of data. In your case, when the user changes the page, you have to call your stored procedure with the current page index parameter and when the data returns to the client -- assign it to RadGridView.ItemsSource. Each time the user changes the page -- you do the same thing -- call your SP with the appropriate parameter and then assign the result to RadGridView.ItemsSource.

I am afraid that the thing with the scrolling down and asynchronously retrieving data is not possible in your scenario with the Stored Procedure. So you would have to use a pager for the paging.

This excellent blog post by Julie Lerman explains almost everything about stored procedures and the Entity Framework. I refer to it every time when I have to use a stored procedure with EF, since it is really kind of complex.

Regards,
Rossen Hristov
Telerik

[Paweł]

Thanks for answers, they are realy helpful ;)

I have question about filtering in gridView - is it possible to set default filter for each column to "Contains"? Now default filter is "Clear filter".

[Rossen Hristov]

Hello,

Please, consult the online documentation here. A lot of other things are thoroughly explained there as well.

Regards,
Rossen Hristov
Telerik

[Paweł]

Again great help ;)

Well.. One more question ;)
Is there something like Summary row in wpf radGridView ? I'd like to place that row below 4 columns of gridView so ti will summary whole column.

@EDIT
found it http://www.telerik.com/help/wpf/gridview-columns-aggregate-functions.html