SqlCeResultSet and datagrid

[Ben Schwehn]

Hello,

I've read a few times [1] that you can bind the DataGrid to an
SqlCeResultSet in a way that when the DataGrid is first displayed only
the visible data is retrieved from the (Sql Mobile) database. Further
data is only retrieved when the use scrolls the datagrid. I have not
been able to reproduce this behaviour. What I've tried is:

a)
1. get an SqlCeResultSet by using SqlCeCommand::ExecuteResultSet()
2. set DataGrid:ataSource to the resultset

b) used Visual Studios Data Source (with the datasouce designer
configured to use the SqlCeResultSet instead of the usual DataSet) to
create an DataGrid by drag'n'dropping it on a form

Both test retrieved all data from the database before displaying the
resultset.

So my question is: how can I make the DataGrid only retrieve the
absolutely neccessary data from the db and retrieve furhter data only
when the user scrolls the DataGrid?

Thanks
Ben

[1] e.g. here:
http://groups.google.co.uk/group/mi...read/thread/a124ddaac6bc38c7/547330b85d2e54e6

Quote Ilya Tumanov [MS]: "For SQL Mobile (SQL CE 3.0) you can use new
SqlCeResultSet class which
supports data binding directly.

It's really fast if bound to DataGrid because only visible rows would be
actually retrieved. "

 

[Ilya Tumanov [MS]]

DataGrid indeed retrieves data from the DataSource only as it paints cells
and it could not operate in any other way.

Why do you believe all data it actually retrieved from data base?


--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

 

[Ginny Caughey [MVP]]

Ben,

You can prove it to yourself that SqlCeResultSet and DataGrid databinding
only fetches the data for visible rows by timing how long it takes to
databind to a DataGrid vs. a list control like a ListBox, which fetches all
the data before the control is displayed.

 

[Ben Schwehn]

DataGrid indeed retrieves data from the DataSource only as it paints cells
and it could not operate in any other way.

Why do you believe all data it actually retrieved from data base?

Hello Ilya,

I believed all data is retrieved for the follwing reasons:
(I don't believe this anymore, but I've already written the reasoning so
i might as well include it)

a) it paints the scrollbar position correctly (as far as I know
SqlCeResultSet does not provide the actual number of rows before
scrolling all the way through it).


b) the windows form with the datagrid takes very long to load
(40-50seconds), longer when the datatable i get the data from is bigger,
just as you expect when all data is retrieved before display

c) when using a really large datatable, i get an OUtOfMemory exception
before the datagrid is even diplayed. This should not happen if only the
currently displayed data is retrieved

d) there is no (at least no big) performance benefit compared to using a
dataset; even creating an IList<DataObject> first from the data (i.e.
reading all rows and creating an data object for each row, putting it
into an list) and binding to that list is not much slower.
I'd epect a huge performance benefit for the initial load, considering
only the first 20 rows should be retrieved instead of the full
8.000/450.000 rows I used in my tests


Now it turns out there was a small and silly error in my test programm
that caused the lack of performance gain. Without this bug I indeed see
a significant increase in performance.

Besides the percieved lack of performance improvements, the other reason
I suspected the SqlCeResultSet not working as advertised was
(mis)reading your post
http://groups.google.co.uk/group/mi...read/thread/6f1fa4618ed76f52/6b6936026c51ff49
saying that you would have to do a select count to tell the datagrid the
number of expected rows first, missing that you talked about other dbs,
not Sql Mobile with SqlCeResultSet.

So apparently SqlCeResultSet does provide the approximate number of rows
up front (possibly via ResultSetView that implements ICollection), which
explains the correct scrollbar and would also explain the OutOfMemory
exception, as the datagrid might try to reserve some needed memory up
front as well or so. (I know its not particulary reasonable trying to
fill a datagrid with 450.000 rows on a mobile device...

So all works well now, scrolling is real fast too, don't notice any
difference compared to when binding to an resultset. Cool!

Thanks
Ben

 

[Ben Schwehn]

Ben,

You can prove it to yourself that SqlCeResultSet and DataGrid databinding
only fetches the data for visible rows by timing how long it takes to
databind to a DataGrid vs. a list control like a ListBox, which fetches all
the data before the control is displayed.

I did a performance test, unfortunately my testprogramm suffered from
the same bug as the program that first made me falsely believe there was
no performance improvement...

That bug fixed, ther peformance gain is indeed quite noticeable.

Thanks
Ben

 

[Ginny Caughey [MVP]]

Ben,

I'm glad you got it sorted out. The more I work with SqlCeResultSet, the
more impressed I am with it.