Taoffi's blog

prisonniers du temps

Silverlight database to DataGrid, yet another approach- Part IV

Filtering database data

As we have seen in Part III, the SQL Select statement is composed of the following parts:

SELECT                 [field1], [field2],… [fieldn]

FROM                   [database table or ‘table-like’ store]

WHERE                [conditions]

ORDER BY           [field name] <sort direction>,

                               [field name] <sort direction>

 

It is the ‘WHERE’ clause of the SQL statement that is used to filter the data.

‘WHERE’ is followed by ‘conditions’. Several conditions are concatenated using AND or OR operator.

What is a filter condition?

A ‘condition item’ can be presented as:

[Field name] [Comparison operator] [Filter value]

 

A collection of ‘condition items’ can be presented as something like:

<Condition item> <Concatenation operator> <Other condition item>

 

Examples:

Name = ‘John’

Amount >= 100

(Last_name LIKE ‘%bama’) AND (Age >= 20)

 

That seems easy to be represented by an object:

public class SilverFiltertItem

{

       string              m_field_name,

                           m_filter_value;

       CompareOperator     m_compare_operator  = CompareOperator.EQUAL;

       ConcatOperator      m_concat_operator   = ConcatOperator.AND;

       ...

       ...

The concatenation and comparison operators are members of the following enum types:

public enum filter_concat_operator

{

       and,

       or,

       none

};

 

public enum filter_compare_operator

{

       Equal,

       Not_equal,

       Like,

 

       GreatertThan,

       GreatertThanOrEqual,

       LessThan,

       LessThanOrEqual

       ...

       ...

};

 

Two properties can give us the Compare and Concatenate strings when required:

protected string ConcatString

{

       get

       {

             if( m_concat_opertaor == filter_concat_operator.none)

                    return "";

 

             // return ‘AND’ or ‘OR’

             return Enum.GetName( typeof( filter_concat_operator), m_concat_opertaor).ToUpper();

       }

}

 

protected string CompareString

{

       get

       {

             switch (m_compare_operator)

             {

                    case filter_compare_operator.Equal:

                           return "=";

 

                    case filter_compare_operator.Like:

                           return " Like ";

 

                    case filter_compare_operator.Not_equal:

                           return "!=";

 

                    case filter_compare_operator.GreatertThan:

                           return ">";

 

                    case filter_compare_operator.GreatertThanOrEqual:

                           return ">=";

 

                    case filter_compare_operator.LessThan:

                           return "<";

 

                    case filter_compare_operator.LessThanOrEqual:

                           return "<=";

 

                    default:

                           throw new Exception("Unknown Compare opertor encoutered!");

             }

       }

}

 

The FilterItem object can now provide us with its SQL string through a property like the following:

public string SqlFilterString

{

       get

       {

             if( string.IsNullOrEmpty(m_filter_value)

                           || string.IsNullOrEmpty( m_target_column_name))

                    return "";

 

             string       field_name   = "[" + m_target_column_name + "]",

                          str_ret      = ConcatString + "(";

 

             string str_value    = m_filter_value;

            

             str_ret      += field_name + " " + CompareString + " " + str_value + ") ";

             return str_ret;

       }

 

A filter collection class may look like this:

public class SilverFilter : List<SilverFilterItem>

{

       ...

       ...

 

The filter collection can return the entire SQL filter string through successive calls to its member items:

public string SqlFilterString

{

       get

       {

             if( Count <= 0)

                    return "";

 

             int    ndx          = 0;

             string str_ret      = "";

 

             foreach (SilverFilterItem f in this)

             {

                    if (ndx <= 0)

                           f.Concat_operator = filter_concat_operator.none;

                    else

                    {

                           if( f.Concat_operator == filter_concat_operator.none)

                                  f.Concat_operator   = filter_concat_operator.and;

                    }

 

                    str_ret      += f.SqlFilterString;

                    ndx++;

             }

 

             return str_ret;

       }

 

 

Our DataTable object (see previous posts) can now include a Filter collection object and construct the SQL Where clause:

 

protected SilverFilter    m_filter     = new SilverFilter();

...

...

 

public string SqlCommand

{

       get

       {

             ...

             ...

             string       str_cmd      = "SELECT * FROM " + m_table_name;

             string       str_sort     = m_sort.StrSql;

             string       str_filter   = m_filter.SqlFilterString;

 

             if( ! string.IsNullOrEmpty( str_filter))

                    str_cmd      += " WHERE(" + str_filter + ")";

 

             if( ! string.IsNullOrEmpty( str_sort))

                    str_cmd      += " ORDER BY " + str_sort;

 

             return str_cmd;

       }

 

Note: a better approach would, of course, be to map the filter field names to the table’s meta-data table fields… or directly use MetaDataTable’s columns as filter members (instead of using field names as strings). In this last case, we would be able to check columns’ data types and also act more closely to database’s business logic.

 

In other words: what is proposed here is a simple approach that can evolve according to your needs.

 

 

You can download the sample code: SilverDbDataGrid-2-sorting&filtering.zip (1.87 mb)

Silverlight database to DataGrid, yet another approach- Part III

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)

Silverlight database to DataGrid, yet another approach- Part II

This is the second part on how to format/adapt data to be displayed in a Silverlight DataGrid in a way that allows the preservation of business logic.

 

Server side objects

As we have seen, in part I, the server will prepare our data into the designed classes before transmitting it to the Silverlight client application.

On the server side, we have the following classes:

 

Data level classes

SilverDataTable

Represents the table (or view, or function…) data.

Contains:

A meta-data table

A list of data rows

SilverDataRow

Represents one record of data.

Contains:

List of data cells

Linked to:

A parent table

SilverDataCell

Represents one record’s data cell.

Linked to:

A parent row

A parent meta-data column

Meta-data level classes

SilverMetaTable

Represents the table’s meta-data (schema)

Contains:

A list of meta-data columns

Linked to:

A parent table

SilverMetaColumn

Represents one column’s schema information.

Note: This is the ‘Key Class’ where you can insert all your required business logic.

Linked to:

A parent meta-data table

 

The server uses these classes’ methods to expose a data service that returns a set of requested data:

 

[AspNetCompatibilityRequirements(RequirementsMode =

                    AspNetCompatibilityRequirementsMode.Allowed)]

public class DataService

{

       [OperationContract]

       public SilverDataTable GetData(string str_connect_string,

                                  string str_sql_command)

       {

             SilverDataTable sl_table = new SilverDataTable();

 

             sl_table.UserDefinedSqlCommand = str_sql_command;

             sl_table.GetData(str_connect_string);

 

             return sl_table;

       }

}

 

The GetData method of the SilverDataTable logic is as follows:

§  Open the database connection.

§  Read the meta-data structure of the SQL command.

§  Read the data rows of the SQL command.

 

Reading table’s meta-data (table schema)

For reading the table schema, SilverDataTable asks an OleDbDataAdapter to fill a DataTable (System.Data) schema and passes this DataTable it to its SilverMetaTable for reading its information:

 

OleDbDataAdapter    adapter      = new OleDbDataAdapter(str_sql_cmd, connection);

DataTable           table  = new DataTable();

 

table  = adapter.FillSchema(table, SchemaType.Mapped);

MetaDataTable.ReadDbTableColumns( table);

 

The Meta-data table (SilverMetaTable) offers a method to read a DataTable (System.Data) schema and create its own meta-data columns accordingly:

 

public bool ReadDbTableColumns(DataTable db_table)

{

       /// start with a ‘traditional’ checking!

       if( db_table == null || db_table.Columns == null)

                    return false;

 

       Columns.Clear();

 

       foreach (DataColumn col in db_table.Columns)

       {

             Add( col);

       }

 

       return true;

}

 

The Add method of this same class proceeds as in the following code:

 

public void Add(DataColumn data_column)

{

       /// start with a ‘traditional’ checking!

       if( data_column == null

                    || string.IsNullOrEmpty( data_column.ColumnName)

                    || data_column.DataType == null)

             return;

 

       /// does this column already exist?: if so, only update its information

       /// otherwise add a new column

       SilverMetaColumn    col    = this[data_column.ColumnName];

 

       if( col == null)

             Columns.Add(new SilverMetaColumn(data_column));

       else

             col.ReadColumnInfo( data_column);

}

 

As you may have already guessed through the above code, our meta-data table offers an indexer which returns the meta-data column by name:

 

public SilverMetaColumn this[string column_name]

{

       get

       {

             if( string.IsNullOrEmpty( column_name) || Count <= 0)

                    return null;

 

             foreach (SilverMetaColumn col in m_columns)

             {

                    if( string.Compare( col.Name, column_name, true) == 0)

                           return col;

             }

             return null;

       }

}

 

And the meta-data column offers a constructor using a DataColumn (System.Data) object:

 

public SilverMetaColumn(DataColumn data_column)

{

       ReadColumnInfo( data_column);

}

 

public bool ReadColumnInfo(DataColumn data_column)

{

       if( data_column == null)

             return false;

 

       m_name       = data_column.ColumnName;

       m_caption    = data_column.Caption;

       m_data_type  = data_column.DataType;

 

       return true;

}

 

Reading data records

Inside the SilverDataTable, reading the data rows (records) is straightforward:

 

OleDbCommand        cmd    = new OleDbCommand( str_cmd, conn);

OleDbDataReader     dr     = null;

SilverDataRow       row;

 

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())

{

       row          = new SilverDataRow(this);

       row.ReadDataReader( dr);

       m_rows.Add( row);

}

 

The ReadDataReader method of the SilverDataRow class looks like the following pseudo-code:

 

int                 n_fields     = data_reader.FieldCount;

string              field_name,

                    field_value;

SilverMetaColumn    meta_column;

 

for( int ndx = 0; ndx < n_fields; ndx++)

{

       field_name   = data_reader.GetName( ndx);

       meta_column  = meta_table[field_name];

       field_value  = data_reader.GetValue(ndx).ToString();

 

       m_cells.Add(new SilverDataCell(this, meta_column, field_value));

}

 

The Silverlight client side

At the client side, our Silverlight application will uses (references) the server’s web service in order to obtain the data.

As we have seen above, the data will be received as a SilverDataTable object (containing the data rows + the meta-data table)

 

Binding the Silverlight DataGrid to the data

To bind the received data to the DataGrid, we will, basically, proceed according to the following steps:

§  Set the DataGrid to NOT auto generate its columns (we will do this ourselves)

§  Bind the DataGrid to our received SilverDataTable’s Rows (List<> of rows, often presented in Silverlight as an ObservableCollection<SilverDataRow> when referencing the wcf service)

§  For each SilverMetaColumn in our received meta-data table’s meta-columns:

§  Create a DataGridColumn according to the meta-column data type (and business logic constraints)

§  Bind the created data grid column using a converter that will be in charge of interpreting the related data cell’s data for all column’s rows

§  Add this DataGridColumn to the DataGrid

 

Here is a sample code (where some artifacts have been removed for better readability):

 

foreach (SilverMetaColumn met_col in meta_table.Columns)

{

       DataGridBoundColumn col    = CreateDataGridColumn( meta_col);

       Binding                    binding      = new Binding();

 

       col.Header                 = cell.Caption;

       binding.Path               = new PropertyPath("Cells");

       binding.Mode               = BindingMode.OneWay;

       binding.Converter          = (SilverRowConverter) this.Resources["row_converter"];

       binding.ConverterParameter = col_index;

 

       col.Binding         = binding;

 

       data_grid.Columns.Add(col);

}

 

Note: The converter is defined inside the Xaml code, like the following:

 

<UserControl.Resources>

       <local:SilverRowConverter x:Key="row_converter" />

       ...

       ...

</UserControl.Resources>

 

As you may have guessed, each DataGrid row will receive the corresponding data row’s Cells as its DataContext. And to present any row cells, it will call our designated converter.

To interpret one cell’s data, our converter takes one parameter: the cell index.

Using he cell index, the converter will be able to identify the related cell, its data and its meta-data column information (data type, or any other business logic constraints)

 

Here is a sample code for the converter:

 

public object Convert(object            value,

                    Type         targetType,

                    object              parameter,

                    CultureInfo culture)

{

       ObservableCollection<SilverDataCell>    row    =

                    (ObservableCollection<SilverDataCell>) value;

       int    col_index    = (int) parameter;

 

       SilverDataCell      cell         = row[col_index];

 

       return cell.ValueAsString;

}

 

Sample user interface to test the solution

In the joined sample code, to test our solution, the user interface proposes:

§  A TextBox control where you can enter the desired connection string to your database;

§  Another TextBox where you can type your SQL command;

§  A data grid where the received data will be displayed.

 

Any comments are welcome: you can write me at tnassar[at]isosoft.org

 

Download the sample application (1.32 mb).