Introduction
The paging solution is a common problem in Web Application, but it not so common in Windows applications. I need to paging in my actual
work and I discovered that I cannot found a control in WPF to controlling the paging action. Then I decide to construct a simple control to paging the DataGrid
or another Data Control in WPF. The control is not attached to the grid control, but can be used with other data controls. You can see the control at work in the article first image.
Background
I my solution I need to paging the user result in order to stop large processing of data without a real use for the users.
I am not found a developed control for WPF (maybe I don't make a big research) but I found a very useful article, that was used as basis to design this control.
The article in question can be found in [1] and it was written by Ben Foster. The article does not develop the control itself, but gave me the basis to develop a paging control. In fact the articles describes de basic interface for a paging class, and methods to access different data origin
The work principia for a paging control is to be aware the application when a user want to change the data page. Besides the paging should do the following task:
- Track the total record count to be show,
- The quantity (in items row) to be show by each page in the data bound control (Page Size).
- Track the Index position of the page index.
- Control the visibility of the control buttons, to aid the user in does not send index changed without sense.
A description of the function can be see in Image 2.
Besides this, the control should offer support to configure the control in the AXML code. In order to get this, the three necessary properties are declared as dependency properties, and can be configured in the XAML code. Also the
ChangeindexEvent
is rerouted as a Command in order to be executed by the windows or by an MVVC architecture.
The next picture show the reveled properties and visual interactions that the user can do with the control, and the program can use to operate it:
Both the index button and the Change Page Size, generate a
ChangeIndex
Event to notify the container that the page index was changed. In case of
PageSize
the index in automatically set to 1 to avoid possible false values because the page size changed.
In the following section is described how to use this control in a simple application.
Using the code
To use the example and code you need the following:
- SQL Server or SQL Express installed in your computer (2005 or 2008).
- The Northwind
database ( you can find the proper version for you at http://sqlserversamples.codeplex.com/)
- Before you running the example or run the code, please open the config file or the properties in code, and make the necessary changes in your computer in order to work with your configuration.
We describe here step by step with an example how to use this control in the code behind of your windows.
As you see in the source code, all files of the control are in
the
GridPaging folder.
You can copy de folder to your project, or you can compile a individual
DLL as you wish. When you build the project, you can see in your toolbox the
GridPaging
control. Drag and drop it in your windows surface in the position that you want to use it.
Personalize your control as you want, you can see it in the Source
tab as the following:
To use the control you should implement the custom command to be used as communication with the control. To implement a command as you can see in the example you do the following actions:
public MainWindow()
{
InitializeComponent();
this.listorders = new List<Orders>();
this.changedIndex = new RoutedUICommand("ChangedIndex", "ChangedIndex", typeof(MainWindow));
gridPaging1.ChangedIndexCommand = this.changedIndex;
CommandBinding abinding = new CommandBinding { Command = this.changedIndex };
abinding.Executed += this.OnChangeIndexCommandHandler;
this.CommandBindings.Add(abinding);
}
The upper code declare a command to be used with the GridPaging
control, and assigned it to the control. Then you need to create your command handler
(that is named in the code as OnChangeIndexCommandHandler
) you can see the implementation in the following piece of code:
private void OnChangeIndexCommandHandler(object sender, ExecutedRoutedEventArgs e)
{
var pageIndex = gridPaging1.PageIndex;
var pageSize = gridPaging1.PageSize;
gridPaging1.TotalCount = this.ExecuteQueryReturnTotalItem(pageIndex, pageSize);
}
The implementation is always the same, only vary the nature of the method to get your data. In the code example comes a method to paging a MS SQL
Server table.
But you can also query a Oracle or other DB and also entities, list or nHibernate code.
You can see that you use three properties on GridPaging
, Page Index, and
PageSize
. This are used as output parameters, to inform the query about the necessary position to get the data.
Very important, your query should also return the whole data count without paging. This is very important to hold a correct total page index count. That is you must query first the total count of row in your DB for the actual query, and then return only the rows in the page. A example of this query for MS SQL
Server is in the following example:
private int ExecuteQueryReturnTotalItem(int pageIndex, int pageSize)
{
var initialRow = (pageIndex - 1) * pageSize;
var finalRow = initialRow + pageSize;
string connString = Properties.Settings.Default.NordConnection;
var conn = new SqlConnection(connString);
const string SqlQueryCount = "select count(OrderId) from dbo.Orders";
try
{
SqlCommand commcount = new SqlCommand(SqlQueryCount, conn);
conn.Open();
var rowCount = Convert.ToInt32(commcount.ExecuteScalar());
const string SqlQuery = @"use Northwind;
WITH Members AS ( select ROW_NUMBER()
OVER (ORDER BY OrderID DESC) as row,
OrderDate, OrderId, ShipName, ShipPostalCode from dbo.Orders )
Select row, OrderDate, OrderId, ShipName, ShipPostalCode
FROM Members
WHERE row BETWEEN @InitialRow AND @EndRow
ORDER BY row ASC;";
SqlCommand command = new SqlCommand(SqlQuery, conn);
command.Parameters.AddWithValue("@InitialRow", initialRow);
command.Parameters.AddWithValue("@EndRow", finalRow);
var reader = command.ExecuteReader();
this.listorders = new List <Orders>();
while (reader.Read())
{
var o = new Orders
{
OrderDate = Convert.ToDateTime(reader["OrderDate"]),
OrderId = Convert.ToInt32(reader["OrderId"]),
ShipName = reader["ShipName"].ToString(),
ShipPostalCode = reader["ShipPostalCode"].ToString()
};
this.listorders.Add(o);
}
this.dgOrdersGrid.ItemsSource = this.listorders; return rowCount;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return 0;
}
finally
{
if (conn.State != ConnectionState.Closed) { conn.Close(); }
}
The control is initialized with a value if Page of 500, you can modified this with assigned the
PageSize
property if the control with values of 50, 100,500 or 1000.
If you want other values you can modify the control code. You can enter in the page size combobox items other values.
Use of GridPaging in MVVC development:
GridPaging
exposes the properties PageSize
, PageIndex
, and
TotalCount
as Dependency properties. and also the command ChangedIndexCommand
.
Then you can use they in the XAML code of control in order to be linked with properties in your ViewModel. Also you can assign the command direct in your AXML.
Points of Interest
As you can see the use of this control is not complicated. You can use it to prevent that the user return a large amount of row data.
This avoid that the UI becomes unresponsive or your auto filter in grid control begin to be slow.
Bibliography
[1] Simple paging with ASP.NET MVC and Nhibernate.
Ben Foster. Mayo 2010
History
- 30.06.2012 - Initial release.