Take the 2-minute tour ×

I am writing a wpf destop application, and would like to use SQL Server CE as a backend. I'm trying to come up with a good way to do efficient data paging. In SQL Server Express, I can do something like this:

Select ID, FirstName, LastName
 AS Row, ID, FirstName, LastName
 From TestTable                             
WHERE  Row > 1 AND Row <= 10

Is there anything comparable in SQL Server CE? I'm not completely sure what is and is not supported. I want to only return 10 rows at a time from the database, and not have to pull back all the data and then filter it down to display to the user, since that is much slower. Thanks.

share|improve this question
Neil, see my comment on my answer. –  Bob King Aug 8 '09 at 22:46
add comment

6 Answers

up vote 2 down vote accepted

Honestly, probably the fastest thing to do is use an SqlCeDataReader and call .Read() 10 times. Then when the user moves to the next page, you're already pointing at the 11th result, and can read 10 more. If you need to go backwards, you can either cache your results or switch to an SqlCeResultSet which supports seeking.

Also, SqlCeDataReader/Result is, from experience, the absolute fastest way to interact with the database on the desktop. It can be literally 100 times faster than using DataSets/DataAdapters.

share|improve this answer
Does the SqlCeResultSet pull back everything in the result set from the database at once (like a disconnected DataSet), or does it just open the connection to the database and read the rows as they're requested? If it leaves a connection to the database open, maybe I can use a combination of the SqlCeResultSet and the ReadAbsolute method to jump to the records I need, read them into a list, and then return the list and close the connection. –  Neil Aug 7 '09 at 13:16
@Neil, ResultSet/DataReader need a persistent connection. They're pretty much just an SQL Cursor, for all intents and purposes. What's especially nice is that you can also open the whole table as TableDirect, give it an index, and seek on that index and it's so fast. –  Bob King Aug 8 '09 at 22:45
add comment

I'm also currently working on a WPF application which uses SQL Server CE as the persistence mechanism. We have several (40+) tables and some of them are pretty big (50k records, at least that's big for my standards).

My advice about data paging directly in SQL CE is this: avoid it if you can! I have used the approach described by Bob King, and at least for me it resulted in Very Ugly code, a real maintenance nightmare.

Unless you'll need to page over tens of thousands of records, I believe the best approach is to load them all using SqlCeDataReader into a collection of custom classes and then page over the collection in memory. I found this approach to be more responsive than re-executing the SQL query every time, even with caching. What happened is that in my case the queries were fairly complex, and SqlCeDataReader performance was good enough so that the performance hit was almost imperceptible. No need to point that, after the first batch load, every page change happens almost instantaneously because everything is kept in memory.

The general opinion of my users were that it's ok to wait a little longer for the first results to appear, if that's going to lead to faster paging afterwards. And using LINQ, paging is as easy as calling Skip and Take methods. I have implemented this logic inside a Pager<T> class, making it very DRY and nice.

share|improve this answer
add comment

There are a few ways, but the most simplistic way would be like the following:


  1. Page Size = 10
  2. Page = 2


  1. First TOP = PageSize (10)
  2. Second TOP = PageSize * Page (20)

  [TestTable].[ID] ASC
 ) AS [FirstRows]
 [FirstRows].[ID] DESC
) AS [Page]
  [Page].[ID] ASC
share|improve this answer
add comment

In the case that somebody reaches this page looking for the answer ... I came across of this post: Support for Paging Queries in SQL Server CE 4.0


Hope this helps

share|improve this answer
Thanks lailalta, SQL Server CE 4.0 was not available at the time I originally posted this question, but I think this would definitely be the way to approach paging going forward. –  Neil Jan 26 '11 at 20:00
add comment

Right, SQL Server CE 4.0 add paging, but LINQ support is gone.

share|improve this answer
add comment

I did implement custom paging for datagrid using SQL CE. I implemented method of using top in select statement and skipping records using subquery as discussed in above answer. But it works good with small amount of data. As the records grow in thousand the above methodology becomes less helpful and becomes slow in performance.

I solved poor performance issue by using my own technique. What i did is i store id of first and last record on each page in variables.

dim firstRecord=dt.rows(0)("id")


dim lastRecord=dt.Rows(dt.rows.count-1)("id")

I initialise these variables after grid is binded for every page.

If user click next button i fetch top(Pagsize) records from database greater than lastRecord If user click on previous button i fetch top(PageSize) records from database less than firstRecord. Also i order by Id desc in this case. And reorder datatable using dataview to asc before binding to datagrid.

It made my paging most efficient. Though i had to put some extra effort for insert and delete of records cases. But i was able to handle that.

share|improve this answer
add comment

Your Answer


