Gal Ratner
Gal Ratner is a Techie who lives and works in Los Angeles. Follow galratner on Twitter Google
Paging SQL Server CE 3.5 results on a WPF DataGrid using Entity Framework

SQL Server Compact 3.5 is an embedded database engine that developers can add to Windows Desktop and mobile applications.

It runs as a part of the app and provides an internal data store. It is similar to SQLite and is available from Visual Studio.  Unfortunately SQL Server Compact 3.5 has a few limitations, one of them being the lack of support for SKIP expressions in paging queries when it is used with the Entity Framework. In this blog post I am going to describe a workaround I used for a WPF application.


First we are going to define the list that will hold the data and the paging variables

int resultsPerPage = 5;
int currentPage = 0;
List<Content> content = new List<Content>();

Now let’s look at the DataGrid and pager


<DataGrid Name="dataGrid1" ItemsSource="{Binding}">
</DataGrid>


<StackPanel Grid.Column="2" Grid.Row="5" Name="NavigationStackPanel" Visibility="Hidden" Orientation="Horizontal" Margin="1">
<Button Content="&lt;&lt;" Name="FirstButton" Margin="1" Click="FirstButton_Click" />
<Button Content="&lt;" Name="PreviousButton" Margin="1" Click="PreviousButton_Click" />
<TextBlock Name="NavTextBlock" Text="Showing out of" Margin="1" VerticalAlignment="Center" />
<Button Content="&gt;" Name="NextButton" Margin="1" Click="NextButton_Click" />
<Button Content="&gt;&gt;" Name="LastButton" Margin="1" Click="LastButton_Click" />
</StackPanel>

Binding the grid using our entity model is done using a background Task in order to keep UI responsiveness.

 

Task.Factory.StartNew(() =>
   {
    using (ContentDatabaseEntities db = new ContentDatabaseEntities())
    {
      currentPage = 0;
      content = db.Contents.Where(c => c.Website == entry.Title.Text).OrderByDescending(c => c.ScreensotDate).ToList();
    }
   }).ContinueWith((result) =>
    {
      Dispatcher.BeginInvoke(new Action(() =>
      {
        if (content.Count > resultsPerPage)
        {
            NavigationStackPanel.Visibility = Visibility.Visible;
            NextButton.IsEnabled = true;
            PreviousButton.IsEnabled = false;
        }
        else
            NavigationStackPanel.Visibility = Visibility.Hidden;
                     
        BindPage();
      }));
   });

All that is left is to bind the DataGrid to the current page.

 

private void FirstButton_Click(object sender, RoutedEventArgs e)
        {
            PreviousButton.IsEnabled = false;
            NextButton.IsEnabled = true;
            currentPage = 0;
            BindPage();
        }

        private void PreviousButton_Click(object sender, RoutedEventArgs e)
        {
            NextButton.IsEnabled = true;
            currentPage--;
            BindPage();
            if (currentPage <= 0)
            {
                PreviousButton.IsEnabled = false;
                return;
            }
        }

        private void NextButton_Click(object sender, RoutedEventArgs e)
        {
            PreviousButton.IsEnabled = true;
            currentPage++;
            BindPage();
            if (currentPage >= (int)((content.Count + resultsPerPage - 1) / resultsPerPage) - 1)
            {
                NextButton.IsEnabled = false;
                return;
            }
        }

        private void LastButton_Click(object sender, RoutedEventArgs e)
        {
            PreviousButton.IsEnabled = true;
            NextButton.IsEnabled = false;
            currentPage = (int)((content.Count + resultsPerPage - 1) / resultsPerPage) - 1;
            BindPage();
        }

        private void BindPage()
        {
            System.Windows.Data.CollectionViewSource myCollectionViewSource = (System.Windows.Data.CollectionViewSource)this.Resources["contentsViewSource"];
            myCollectionViewSource.Source = content.Skip(resultsPerPage * currentPage).Take(resultsPerPage);
            NavTextBlock.Text = "Showing Page " + (currentPage + 1) + " out of " + (int)((content.Count + resultsPerPage - 1) / resultsPerPage);
        }

This was a workaround and if you noticed we loaded all of the records into memory and paged the in memory collection. In memory collection paging is faster than SQL paging however, there is a memory limit and if you plan to use this code you will have to know what size of collections you will be working with and if you might run into out of memory exceptions. SQL Server CE 4.0 is currently in CTP and includes the ability to write paging queries using the OFFSET and FETCH T-SQL Syntaxes. It is a part of Microsoft WebMatrix. If you are still using SQL Server Compact 3.5 and working with small to medium collections this solution might be for you.


 

Shout it


Posted 27 Nov 2010 8:33 PM by Gal Ratner

Powered by Community Server (Non-Commercial Edition), by Telligent Systems