Silverlight Data Pager, fake ItemsCount and stored procedures

Silverlight data pager control in real world

What is the better thing one could wish to blog about after a year of blog silence then about something as simple as it gets: How to use Silverlight DataPager control with server side paging?

There’s a swarm of blog posts showing how to do that but each one of them I saw does that with WCF data services, using DataService<T> and  entity framework which is really great way to do it except it violates one of the holiest of all enterprise software laws

Thou should obey and use always only and only
stored procedures when accessing the database.

Well, as simple as it looks I couldn’t google it with Bing so here’s a blog post showing how to do such a dumb thing. It’s a solution which “works-for-me” but it does feel hacky so take it with a grain of salt and please take it just as an illustration of the idea.

Source code with the sample used in this post can be downloaded from here

What is exactly the problem?

The problem is that in a given database I can have million rows which I don’t want to download all to client so I ‘m using DataPager control to get page by page of result.

If I would be allowed to use WCF DataService (ex. RIA service) the solution would be quite trivial: my WCF service would expose IQueryable<User>, SL client would use a Linq statement which would transfer over the wire to the server, on the server would be translated to entity framework ORM call and the data flows back in a second.

Unfortunately, I am not allowed to use ORMs so the dynamic SQL based solutions are out of the question.

The solution

Solution used in this blog post is a vanilla Silverlight project with one web project containing:

• a WCF service called UsersService which gets from Silverlight client desired page index and page size and calls the stored procedure using DAL.
• A User DTO class which only has 3 properties: Id, Name and Birthdate
• UserDAL class which imitates the DAL code simulating the DB with 200 rows in a table and stored procedure which gets result page for given parameters

Here's that dummy DAL code

namespace SilverlightApplication1.Web.Model
{
    public class UserDAL
    {
        private static readonly IList<User> _users = new List<User>();

        static UserDAL()
        {
            for (int i = 0; i < 200; i++)
            {
                _users.Add(new User { Id = i, Name = "User " + i, BirthDate = DateTime.Now.AddDays(-i) });
            }
        }

        public static IList<User> InvokeStoreProcedure(int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = _users.Count;
            int startIndex = pageIndex * pageSize;
            return _users.Skip(startIndex).Take(pageSize).ToList();
        }
    }
}

On Silverlight project client side we are having:

• a UserService wcf service proxy
• MainPage.xaml containing in markup data grid and data pager
• MainPageViewModel class which is view model of the main page

Here’s the view xaml which data binds the DataGrid and DataPager to some Users collection property:

<UserControl x:Class="SilverlightApplication1.MainPage"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"
             Height="600"
             Width="800">
    <Grid x:Name="LayoutRoot">
        <Grid.RowDefinitions>
            <RowDefinition Height="*" />
            <RowDefinition Height="Auto" />
        </Grid.RowDefinitions>
        <sdk:DataGrid ItemsSource="{Binding Path=Users, Mode=TwoWay}"
                      AutoGenerateColumns="True" />
        <sdk:DataPager Grid.Row="1"
                       Source="{Binding Path=Users}" />

    </Grid>
</UserControl>

The code behind that xaml only wires up the view model and view:

namespace SilverlightApplication1
{
    public partial class MainPage
    {
        public MainPage()
        {
            InitializeComponent();

            Loaded += (sender, args) =>
                          {
                              ViewModel = new MainPageViewModel();
                          };
        }

        public MainPageViewModel ViewModel
        {
            get { return (MainPageViewModel) DataContext; }
            set { DataContext = value; }
        }
    }
}

And here's the view model class:

using System.ComponentModel;

namespace SilverlightApplication1
{
    public class MainPageViewModel : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        public MainPageViewModel()
        {
            Users = new UserPagedCollectionView() { PageIndex = 0, PageSize = 25 };
            Users.Init();
        }

        private UserPagedCollectionView users;
        public UserPagedCollectionView Users
        {
            get { return this.users; }
            set { this.users = value; OnPropertyChanged("Users"); }
        }

        public void OnPropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null) handler(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

No magic happening in a constructor of a view model :

• sets a view model Users property to a instance of UserPagedCollectionView
• invokes the User.Init();

Secret sauce

Obviously the only thing left not shown is the UserPagedCollectionView instance where the magic happens. The class  itself is a bit longer to be pasted so here are just the juicy stuff

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using SilverlightApplication1.UsersServiceProxy;

namespace SilverlightApplication1
{
    ///
 /// Endless paged collection view for testing purposes. /// 

    public class UserPagedCollectionView : IEnumerable, IPagedCollectionView,
                                           INotifyPropertyChanged, INotifyCollectionChanged
    {
        private bool isPageChanging;

        private int pageSize;

        private readonly UsersServiceClient proxy;
        readonly Dictionary<int, User> users = new Dictionary<int, User>();

        private int GetPageCount()
        {
            var result = ItemCount / pageSize;
            if (result * pageSize < ItemCount)
            {
                result++;
            }

            return result;
        }

        public UserPagedCollectionView()
        {
            proxy = new UsersServiceClient();
            proxy.GetResultsCompleted += (sender, ea) = >
                                             {
                                                 ItemCount = ea.Result.TotalCount;
                                                 int position = ea.Result.StartIndex;
                                                 foreach (var user in ea.Result.Items)
                                                 {
                                                    if (!users.ContainsKey(position))
                                                    {
                                                        users.Add(position, user);
                                                    }
                                                    position++;
                                                 }
                                                 OnCollectionChanged();
                                             };

            PageChanged += (sender, ea) => Init();
        }

        public void Init()
        {
           proxy.GetResultsAsync(PageIndex, pageSize);
        }

        public IEnumerator GetEnumerator()
        {
            var startIndex = PageIndex * pageSize + 1;
            var endIndex = startIndex + pageSize;

            var result = new List<User>();

            for (int i = startIndex; i < endIndex; i++)
            {
                if (users.ContainsKey(i))
                {
                    result.Add(users[i]);
                }
            }

            return result.GetEnumerator();
        }
    }
}

Major points:

• The class implements the IPagedCollectionView
• It has the Init() method which just calls the service on the server and gets the page of data for a page index and page size with values from a DataPager.
• it has a dictionary<int, user> field users which is caching the user data retrieved from server where the key is position.
• It has a method GetPageCount() which returns the total number of the pages
  (regardless of the number of items data pager  is bind to)
• In its constructor it is constructing an instance of the WCF service proxy and it subscribes to the GetResultsCompleted event where it takes the service results (total number of items, current page index and page users and store it all as data pager context) and raise an CollectionChanged event informing the UI that it needs to refresh
• Every time a PageChanged event is invoked (clicking one of the arrows, entering the page number etc.) it invokes the Init method  which again makes the call to the server getting the data for the new page.

Result

That’s it – on initial page load only 25 rows are retrieved but the page count is set to 8 (as 8 x 25 = 200) – exactly what I needed. Clicking the next page gets again from server only the next 25 rows etc. There are no requirements regarding using the ORM and the page data is retrieved using the SP (mocked in this post with a simple in memory method).

Can it be better? Sure, add sorting in game, preemptive loading of the future pages, do not hit the server if you already have the data etc. As I said this is not a production code just an illustration of how I solve a problem I couldn’t find a solution on the net so I hope it will save some time someone in the future – that’s all.