Sorting and filtering database records
After having succeeded to display our server data into Silverlight DataGrid (see previous post), we will now continue the adventure to complete our solution with some useful and necessary features that Silverlight DataGrid originally proposes (for example, sorting records) and add some new features for filtering records.
Silverlight DataGrid allows the user to sort displayed rows by clicking into column headers.
That is nice and helpful. But, in the context of data provided by a server through a database, that doesn’t seem to be quite a correct approach.
In fact, in such context, if you click to sort a column in the ascending order, the displayed data records may not contain the entire values stored in the database. The data should be ‘refreshed’ to represent the effective sorted values according to currently stored database data in its entirety.
Sorting database data
How database data can be sorted?
The answer: this can be done using the SQL ‘ORDER BY’ clause.
We use SQL to query the data according to the following (simplified) statement template:
SELECT [field1], [field2],… [fieldn]
FROM [database table or ‘table-like’ store]
WHERE [conditions]
ORDER BY [field name] <sort direction>,
[field name] <sort direction>
The ORDER BY part, contains a list of field names (each of which should be one of the listed SELECT fields, or, at least, one of the queried table or ‘table-like’ fields)
Each field in the ORDER BY clause can have a specified sort direction (ASC for ascending, or DESC for descending). The default being the ascending direction (if the direction is omitted)
If we have to represent an ‘order by’ item as an object, the object may then be something like this:
public class SortItem
{
string m_field_name;
SortDirection m_direction = SortDirection.ASC;
...
...
So, in our case, we can simply sort our DataGrid by building a list (List<SortItem>) of clicked column names (met-data column names) and passing them to the server to compose the ORDER BY part of the query accordingly and return too us the desired sorted data.
Implementing the solution
Let’s begin, at the server side, by enriching our service’s SilverDataTable (see Part II) object by a new SortList object, which will simply be a List<> of SortItem.
public enum sort_direction
{
asc,
desc,
none
};
A SortItem object can be defined as follows:
[DataContract]
public class SilverSortItem
{
protected string m_target_column_name = "";
protected sort_direction m_sort_direction = sort_direction.asc;
...
...
We can now define a sort-list object which is mainly a List<SilverSortItem > that takes care for some constraints like, for example, not adding duplicate fields:
public class SilverSort : List<SilverSortItem>
{
The class exposes an indexer that returns the field by its name:
public SilverSortItem this[string field_name]
{
get
{
if( string.IsNullOrEmpty( field_name) || Count <= 0)
return null;
foreach (SilverSortItem e in this)
{
if( string.Compare( e.Target_column_name, field_name, true) == 0)
return e;
}
return null;
}
An Add method, to add or modify settings of an existing sort field:
public new void Add(SilverSortItem element)
{
if( element == null || string.IsNullOrEmpty( element.Target_column_name))
return;
SilverSortItem e = this[element.Target_column_name];
if( e == null)
base.Add( element);
else
e.CopyOther( element);
}
And a property that returns the SQL sort string of the contained fields:
public string StrSql
{
get
{
if (Count <= 0)
return "";
string str_ret = "";
foreach (SilverSortItem s in this)
{
if( str_ret.Length > 0)
str_ret += ", ";
str_ret += s.StrSql;
}
return str_ret;
}
}
Let’s now include a Sort list into our SilverDataTable class:
[DataContract]
public class SilverDataTable
{
protected SilverSort m_sort = new SilverSort();
We can now extend our SqlCommand property to include the sort string:
string str_sort = m_sort.StrSql;
if( string.IsNullOrEmpty( str_sort))
return "SELECT * FROM " + m_table_name;
return "SELECT * FROM " + m_table_name + " ORDER BY " + str_sort;
We will also change our WCF service method to include an optional list of sort field names:
[OperationContract]
public SilverDataTable GetData(string str_connet_string,
string str_sql_command,
SilverSort sort_items,
int n_records)
Sorting… the Client-side job
At the client-side, our DataGrid should send us a message each time a column should be added to the SilverDataTable sort list. And, unfortunately, that is not as ‘intuitive’ as we may like!
Manish Dalal published a very interesting paper about custom sorting and paging inside a DataGrid. His work presents a good (and extensible) start point.
The usable part of his work in our project is the CollectionView class which can give us more control on sorting in response to DataGrid Column Click events.
As you may see at Bea Stollnitz interesting blog, Silverlight DataGrid internally uses a CollectionViewSource (an object that implements ICollectionView interface).
That is: When we set DataGrid’s ItemsSource to an observable collection, it integrates it into its own ICollectionView object.
Fortunately, the DataGrid is smart enough to let us implement this object ourselves, i.e. if we set its ItemsSource to an object that implements the ICollectionView, the DataGrid will use this instead of its own one.
So, let’s create a class that implements the ICollectionView:
public class SilverCollectionView<T> : ObservableCollection<T>, ICollectionView
{
The ICollectionView exposes a collection of ‘SortDescriptions’ that we will use, for the requirements of our project, as a list of field names to be sorted. i.e. each time a column will be clicked, we will add the column name to the SortDescriptions list and generate a Refresh event to request the server’s data sorted as desired.
Here is the SilverDatasetView helper class that will integrate our SilverDataTable as an ICollectionView:
public class SilverDatasetView : SilverCollectionView<SilverDataRow>
{
public SilverDatasetView() : base()
{
}
public SilverDatasetView(SilverDataTable service_data_table) : base()
{
CopyDataset( service_data_table);
}
public bool CopyDataset(SilverDataTable data_table)
{
if( data_table == null || data_table.Rows == null)
return false;
base.SourceCollection = data_table.Rows;
return true;
}
Once we receive the data from our database server, we can now set the DataGrid’s ItemsSource to a SilverDatasetView object:
SilverDatasetView dataset_view = new SilverDatasetView(m_table.Rows);
data_grid.ItemsSource = dataset_view;
To tell the DataGridColumn how to send us desired sort items, we will set its SortMemberPath to the data column name:
Grid_col.CanUserSort = true;
Grid_col.SortMemberPath = data_col.Name;
The last part is to respond to the Refresh event of our SilverDatasetView in order to collect the desired sort field names and request the data accordingly:
dataset_view.OnRefresh += new EventHandler<RefreshEventArgs>(m_dataset_view_OnRefresh);
void m_dataset_view_OnRefresh(object sender, RefreshEventArgs e)
{
if( m_sort_fields == null)
m_sort_fields = new ObservableCollection<SilverSortItem>();
m_sort_fields.Clear();
SilverSortItem sort_element;
foreach (SortDescription s in e.SortDescriptions)
{
sort_element = new SilverSortItem();
sort_element.Target_column_name = s.PropertyName;
sort_element.Sort_direction =
(s.Direction == ListSortDirection.Ascending)
?(sort_direction.asc)
:(sort_direction.desc);
m_sort_fields.Add( sort_element);
}
RequestSilverDataset();
}
RequestSilverDataset() method will, mainly, do the following:
DataServiceClient cli = service_helpers.DataServiceProxy(5);
cli.GetDataCompleted += new EventHandler<GetDataCompletedEventArgs>(cli_GetDataCompleted);
cli.GetDataAsync( str_connet_string, str_sql_command, m_sort_fields, n_records);
That is it… your DataGrid can now be sorted according to the real data on the server.
Don’t forget: Click the column to sort/ shift + click to add the column to the sorted list…
Download the sample code:
SilverDbDataGrid-2-sorting.zip (1.36 mb)