Taoffi's blog

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)

Intro…

I wanted to post a new article in the series about Silverlight (client) and server’s databases. The subject of the article was ‘data forms in Silverlight’. But, because a ‘Data Form’ is the ultimate place where the user directly interacts with the data, and where the concrete sense of a ‘rich user interface’ comes to life… I finally preferred to begin by discussing one of the basic questions: the User Interface (GUI, UI… as it got so many titles and names through the time!)

The UI frontiers

Where does the UI sit inside the entire image of a software project?

Are there any definitive… (kind of ‘waterproof’) frontiers between the UI tasks and the rest of the software project’s tasks?

There is, occasionally, some confusion about this subject. Software industry literature teaches us how to separate ‘objects’ from their ‘presentation’ (‘data’ from ‘views’). But does this mean that building a User Interface should be done in a separate bureau (or on the beach)?...

Although no literature ever discussed the question, such a simplistic approach is applied in some projects in the real world. The concept of separating objects from their presentation is sometimes applied in the form of separating the development processes: Object tasks vs. UI tasks, Object modules vs. UI modules, Object workers vs. UI workers!

Although each task, in any project, has particular skills and knowledge requirements, the success (or failure) of the entire project obviously depends on the global understanding, at all levels, of what the final product is and how its parts articulate.

What is the ‘User Interface’?

To avoid taking the risk of giving a formal definition of what a user interface is, let’s try to define what the user interface’s role is.

In most cases, software solutions propose automated (accelerated) tools for accomplishing well-known human tasks. Most of these tools run using some new ‘media’ (screens, keyboards, mice… etc.). This context implies new ways for representing tasks’ items and new ways for manipulating (processing) theses items. The initial presentation approach was, naturally, to mimic the ‘real world’ way of presenting and manipulating the task items (remember the too large accountancy book displayed on the too small screen… that was probably when the scroll bars have been invented!). Many of today’s iconic presentations still use this approach (just have a look at a ‘button’ and its press/release interaction!)

Presentation (and human-machine interaction process) is largely related to the social and cultural context in a given period or era (objects we use, fashion and elegance trends…). The advent of software and its related objects (virtual and/or material) also affects and participates in the evolution of this cultural and social context. That is why, I think, that a successful user interface is achieved when we mimic the ‘real world’ objects and processes to some extent and, in the same time, instigate new (futuristic) objects and processes (obviously, what may seem ‘futuristic’ today will sooner or later become outdated!).

Anyway, if we may admit that user interface is, briefly, about presenting data through visual graphical objects (controls), then, many new things have to (will) be done at this level!

For example, take the ComboBox: which is basically a selection list of items. With the time going, a selection list can now have several thousands of items… may be we now need a ‘paged combo-box’?... or may be a brand new control to make the selection process easier, faster, less cumbersome…

Many interesting work have been done to solve this kind of concrete problems (you may have a look at some ‘virtualization’ approaches here, here and/or here) fewer have been done for inventing new UI objects (since how many years do we still use: the button, the text-box, the list/combo-box…?)

UI role

From a functional point of view, data presentation (User Interface) role may be summarized as:

§  Give access to different data areas (through menu commands, for example);

§  Show the data;

§  Let the user interact with the data (modify, add, delete…);

§  In some cases, validate the data;

§  Send the updated data back to the data store (server).

In these tasks, the user interface makes use of:

§  Controls to present the data;

§  And transitions (animations or animation-like, between presentation and processing stages).

The success of user interface (in a given era!) is mainly related to the best (harmonious) choice of controls and transitions according to each context of data presentation and/or processing stage.

Silverlight (and WPF) added value

One of the most interesting aspects of Silverlight (and WPF) is that it doesn’t imprison you in a special form of any of its proposed controls. You can, for example, reformat, entirely or partially, the proposed ‘traditional’ ComboBox to make it present the data and behave the way you want. You can, of course, invent something new.

UI controls… the server choice

A UI control presents data. The data is stored and ultimately validated and processed at the server side. So, at the client side, the presented data has a semantic aspect and a presentation (visual) aspect. Although we cannot pretend of any strict rule (valid for all contexts), the data semantic aspect should better be defined at the server side. i.e. questions like: is this a read-only, read/write data?, can the value be omitted (nullable)?, should the value be one of specified choice list items?... etc. should better be defined at the server-side.

The presentation visual aspect (and transitions) should logically be defined at the client side (the UI module).

How can we setup and manage the relationship between the data semantic/visual aspects?

Again, there no ‘all purpose’ rule!

I will present some work on this subject in a following post.

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)

*******************************************************************************

NOTE: I found that Dick Brass's article is an interesting insider view. That is why I decided to publish it here in its entirety.

*******************************************************************************

Microsoft’s Creative Destruction

By DICK BRASS

Published: February 4, 2010

International Herald Tribune

Dick Brass was a vice president at Microsoft from 1997 to 2004.

AS they marvel at Apple’s new iPad tablet computer, the technorati seem to be focusing on where this leaves Amazon’s popular e-book business. But the much more important question is why Microsoft, America’s most famous and prosperous technology company, no longer brings us the future, whether it’s tablet computers like the iPad, e-books like Amazon’s Kindle, smartphones like the BlackBerry and iPhone, search engines like Google, digital music systems like iPod and iTunes or popular Web services like Facebook and Twitter.

Some people take joy in Microsoft’s struggles, as the popular view in recent years paints the company as an unrepentant intentional monopolist. Good riddance if it fails. But those of us who worked there know it differently. At worst, you can say it’s a highly repentant, largely accidental monopolist. It employs thousands of the smartest, most capable engineers in the world. More than any other firm, it made using computers both ubiquitous and affordable. Microsoft’s Windows operating system and Office applications suite still utterly rule their markets.

The company’s chief executive, Steve Ballmer, has continued to deliver huge profits. They totaled well over $100 billion in the past 10 years alone and help sustain the economies of Seattle, Washington State and the nation as a whole. Its founder, Bill Gates, is not only the most generous philanthropist in history, but has also inspired thousands of his employees to give generously themselves. No one in his right mind should wish Microsoft failure.

And yet it is failing, even as it reports record earnings. As the fellow who tried (and largely failed) to make tablet PCs and e-books happen at Microsoft a decade ago, I could say this is because the company placed too much faith in people like me. But the decline is so broad and so striking that it would be presumptuous of me to take responsibility for it.

Microsoft has become a clumsy, uncompetitive innovator. Its products are lampooned, often unfairly but sometimes with good reason. Its image has never recovered from the antitrust prosecution of the 1990s. Its marketing has been inept for years; remember the 2008 ad in which Bill Gates was somehow persuaded to literally wiggle his behind at the camera?

While Apple continues to gain market share in many products, Microsoft has lost share in Web browsers, high-end laptops and smartphones. Despite billions in investment, its Xbox line is still at best an equal contender in the game console business. It first ignored and then stumbled in personal music players until that business was locked up by Apple.

Microsoft’s huge profits — $6.7 billion for the past quarter — come almost entirely from Windows and Office programs first developed decades ago. Like G.M. with its trucks and S.U.V.’s, Microsoft can’t count on these venerable products to sustain it forever. Perhaps worst of all, Microsoft is no longer considered the cool or cutting-edge place to work. There has been a steady exit of its best and brightest.

What happened? Unlike other companies, Microsoft never developed a true system for innovation. Some of my former colleagues argue that it actually developed a system to thwart innovation. Despite having one of the largest and best corporate laboratories in the world, and the luxury of not one but three chief technology officers, the company routinely manages to frustrate the efforts of its visionary thinkers.

For example, early in my tenure, our group of very clever graphics experts invented a way to display text on screen called ClearType. It worked by using the color dots of liquid crystal displays to make type much more readable on the screen. Although we built it to help sell e-books, it gave Microsoft a huge potential advantage for every device with a screen. But it also annoyed other Microsoft groups that felt threatened by our success.

Engineers in the Windows group falsely claimed it made the display go haywire when certain colors were used. The head of Office products said it was fuzzy and gave him headaches. The vice president for pocket devices was blunter: he’d support ClearType and use it, but only if I transferred the program and the programmers to his control. As a result, even though it received much public praise, internal promotion and patents, a decade passed before a fully operational version of ClearType finally made it into Windows.

Another example: When we were building the tablet PC in 2001, the vice president in charge of Office at the time decided he didn’t like the concept. The tablet required a stylus, and he much preferred keyboards to pens and thought our efforts doomed. To guarantee they were, he refused to modify the popular Office applications to work properly with the tablet. So if you wanted to enter a number into a spreadsheet or correct a word in an e-mail message, you had to write it in a special pop-up box, which then transferred the information to Office. Annoying, clumsy and slow.

Skip to next paragraphSo once again, even though our tablet had the enthusiastic support of top management and had cost hundreds of millions to develop, it was essentially allowed to be sabotaged. To this day, you still can’t use Office directly on a Tablet PC. And despite the certainty that an Apple tablet was coming this year, the tablet group at Microsoft was eliminated.

Not everything that has gone wrong at Microsoft is due to internecine warfare. Part of the problem is a historic preference to develop (highly profitable) software without undertaking (highly risky) hardware. This made economic sense when the company was founded in 1975, but now makes it far more difficult to create tightly integrated, beautifully designed products like an iPhone or TiVo. And, yes, part of the problem has been an understandable caution in the wake of the antitrust settlement. Timing has also been poor — too soon on Web TV, too late on iPods.

Internal competition is common at great companies. It can be wisely encouraged to force ideas to compete. The problem comes when the competition becomes uncontrolled and destructive. At Microsoft, it has created a dysfunctional corporate culture in which the big established groups are allowed to prey upon emerging teams, belittle their efforts, compete unfairly against them for resources, and over time hector them out of existence. It’s not an accident that almost all the executives in charge of Microsoft’s music, e-books, phone, online, search and tablet efforts over the past decade have left.

As a result, while the company has had a truly amazing past and an enviably prosperous present, unless it regains its creative spark, it’s an open question whether it has much of a future.

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:

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).

Introduction

As I exposed in an earlier post, Database structure can be declined into the following hierarchy

§ A table

§ Containing rows

§ Containing data //(arranged into columns)

This schema cannot live without the following meta-data structure

§ Table definition

§ Column definition:

§ Data type / data size (storage space reservation maximum size)

One important thing to observe is the relation between the meta-data definitions and the data storage or presentation:

.

All these structures and concepts have been used for years through several database access technologies (ado.net for example) using some well known objects like Dataset, DataTable… etc. and everybody was easily able to read and display data into grid controls in Windows and Web forms.

Now this question is once again exposed with Silverlight: How to read and display data into a DataGrid!

The reason for this is, mainly, that Silverlight is a “multiplatform browser plug-in”, and, as such, don’t know about database facility objects like Datasets, DataTables…

Also, Silverlight being a “client”, it cannot directly have access to what is stored or otherwise going-on on the server-side end. Accessing such information is done by calling Xml web services hosted at the server.

Interesting solutions

Some interesting work has been done to try to solve this dilemma. Some of the solutions proposed a (very interesting) workaround which requests the server’s xml dataset, and entirely creates database objects at runtime (MSIL!) on the Silverlight client side.

Although this seems quite fascinating, it is still a sort of ‘black magic’ solution, and lets me uncomfortable to use it as a sustainable solution. It also misses some of the interesting features of Silverlight, like Binding Converters.

Starting at the bottom line

One may think: A Dataset already has all what we need: Meta data definitions, Data rows… etc.

Let’s create a Dataset on the server-side and then write it down to the client through a web service. The client can then consume the received dataset by incorporating it into the required classes.

I tried this solution, but ended up by finding myself in front of a great deal of ‘raw’ data that should be reformatted on the client… And, worse, this data reformatting altered some important business logic that should be decided on the server-side.

For example, in a sales representative client application, if a ‘customer’ field value inside an ‘order’ should be only one of the current user’s authorized customers; this would obviously be better decided on the server not the client.

Even the fact of presenting the ‘customer’ field as selection option (combo box) or as a plain text field is better to be decided at the server end.

The ‘Dataset’ is a great and useful object, but it only provides ‘raw’ relational data. It needs to be complemented by other features in order for the business logic (at the server-side) to take control over data presentation and manipulation process (at the client-side).

The diagram below illustrates the basic schema used by the proposed solution to represent the data stored in a database.

The diagram’s classes represent the 2 levels of a database table:

§  At the meta-data level : SilverMetatTable, composed of one or more SilverMetaColumn(s);

§  The data storage level is represented by SilverDataTable, which has a meta-data table definition (SilverMetaTable). The data table contains zero or more rows (SilverDataRow), each of which containing data cells (SilverDataCell). Each data cell is linked to the corresponding meta-data column.

The meta-data column represents the basic properties of a database column (data type, default value, is/is not nullable… etc.), but can now expose all the desired presentation features and functionalities as required by the business logic.

It may, for example, expose the desired presentation type (plain text / option list… etc.), for an option list: the choice-elements (or where to request them), the data access options (read-only / read-write…) according to session context or application context… etc.

Surprisingly coding the required classes for this part of the solution was a matter of a few hundreds of lines of code (approx 4 hours + 2 for repairing some of my ‘chronic’ errors… I do many!)

In the next post, I will talk about ‘The client side’ job… and deliver a fully-functional sample application.

See part II

In a previous post, I talked about a solution to manipulate (animate for example) the Silverlight control inside the hosting html page.

The reverse side of the problem (displaying html content inside a Silverlight control), is a requirement which comes out in the context of numerous web projects.

Some of the proposed solutions suggest html translators in order to obtain the final text into a sort of ‘Rich Edit’ control.

Although it is very good to have a rich edit Silverlight control, the solution seems too tedious to solve a relatively simple problem: displaying html content into an html page (i.e. the page hosting our Silverlight control).

Building on the previous sample of animating the Silverlight control into the hosting page, I tried to make a user control that dynamically creates an iFrame to display the desired html content inside the main Silverlight page.

You can have a look at the proof of concept Here!

Dynamically create an html control

Suppose we want to display the html page: http://mycompany.com/page.html inside our Silverlight control.

The solution can be:

§  Use the HtmlPage to locate the hosting HtmlElement (where our Silverlight control lives: that can be straightforward:  HtmlPage.Plugin.Parent;)

§  Create a new html container (for example, a DIV)

§  Place an iframe html element inside this container

§  Set the iframe source to the desired page

§  Insert the container into the hosting HtmlElement

Here is a sample code illustrating these steps

private void CreateHtmlContent()

{

       HtmlElement  plugin_div   = HtmlPage.Plugin.Parent,

                    new_div      = HtmlPage.Document.CreateElement("div"),

                    iframe       = HtmlPage.Document.CreateElement("iframe");

       /// set the new div position to absolute

       new_div.SetStyleAttribute("position", "absolute");

       new_div.SetStyleAttribute("z-order", "5");

       new_div.SetStyleAttribute("left", "200px");

       new_div.SetStyleAttribute("top", "40px");

       new_div.SetStyleAttribute("width", "400px");

       new_div.SetStyleAttribute("height", "400px");

       /// setup the iframe style, attributes and target page

       iframe.SetStyleAttribute("width", "100%");

       iframe.SetStyleAttribute("height", "100%");

       iframe.SetAttribute("src", "http://www.isosoft.org/taoffi/");

       /// add the iframe to the new div

       new_div.AppendChild(iframe);

       /// add the new div to the hosting html page

       plugin_div.AppendChild(new_div);

}

The solution exposed here can be a suitable foundation to solve many situations where html content can be composed and/or displayed inline within a Silverlight control.

Let us begin by making a custom control, a HtmlContentViewer say!

The sample code exposes some more!

SilverHtmlContent.zip (49.34 kb)

I am working on a new Silverlight user interface version of Simplesite.net and, as anyone can imagine, that involves a good dive into Silverlight as a ‘Line Of Business’ application technology.

One of the problems I encountered was to make the Combo box control able to respond to Mouse Wheel messages.

After having searched on different blogs and web sites, I didn’t find a suitable solution and ended up by delving into the subject.

Some of the proposed solutions consisted in ‘bringing into view’ one of the combo box items to force the list to scroll up or down as required… that seemed a little bit like a good workaround but was not very efficient and had a bad visual aspect.

What is a Silverlight ComboBox?

One of the nice (and pedagogic) aspects in Expression Blend is to be able to edit any existent control template (right click the control, select Edit Template / Edit a copy).

In Expression Blend, a Control Template reveals all the components of the control… this helps to understand some of the internal mechanisms of the control and how to use them efficiently.

To know more about the ComboBox control, I used Blend to insert one somewhere, and selected to edit a copy of its template.

That revealed the main components of the control:

§ A Layout Grid, containing:

§ A border (content presenter border) containing the static part of the combobox;

§ A Popup containing a ScrollViewer which will display the items.

It now seems clear that the scroll problem should be handled at the ScrollViewer component.

The SetIsMouseWheelScrollingEnabled method of the ScrollViewer can simply be used to activate the Mouse Wheel message handling for this component.

The solution

I saved my template to the application resources (App.xaml) with x:key=”combo_box_template”

<!-- ************* scrollable combo box template *******************************-->

<ControlTemplate x:Key="combo_box_template" TargetType="ComboBox">

I then created a class named ScrollableComboBox deriving from ComboBox

The control template is loaded and applied at class’s instantiation… and Mouse Wheel handling activated on the corresponding ScrollViewer component.

public partial class ScrollableComboBox : ComboBox

{

       protected ScrollViewer            m_scroll_viewer            = null;

       public ScrollableComboBox()

       {

             this.Style   = (Style) App.Current.Resources["ScrollableComboBoxStyle"];

             this.Template = (ControlTemplate) App.Current.Resources["combo_box_template"];

             this.ApplyTemplate();

             m_scroll_viewer = (ScrollViewer)GetTemplateChild("ScrollViewer");

             if (m_scroll_viewer != null)

             {

                    m_scroll_viewer.SetIsMouseWheelScrollingEnabled(true);

             }

    }

Surprisingly, that is all we need to activate scrolling on a Silverlight ComboBox!

We can now insert a ScrollableComboBox anywhere we need.

Live demo

.

A Silverlight control can communicate and interact with other HTML elements of the page on which it is placed.

This is done by using the HtmlPage object (namespace System.Windows.Browser)

By using HtmlPage, you can have access to all HTML elements of the current page and be able to get and set many of their properties and attributes, and, accessorily, call DHTML script functions that may be located there.

This means, among other things, that you can get and set style attributes of the HTML element inside which your own Silverlight control is hosted (the DIV element having the id commonly named "silverlightControlHost")

<div id="silverlightControlHost"

       ...

This gave me an idea about creating a custom control which would represent this same hosting html DIV element and, through this custom control, interact with the hosting element properties (width, height, location… etc.)

One of the nice features of Silverlight is the Storyboard animations usually used to animate Silverlight controls.

So, what if we try to use a storyboard to animate the Silverlight hosting html element!

Suppose that our animation should move the html host element across the page. From an html viewpoint, this would mean to change its top/left coordinates.

A Silverlight user control doesn’t contain Top / Left properties required to do such a job. So we have to add those properties to our custom control.

I first created an (empty) custom user control:

Xaml:

<UserControl x:Class="SilverHtmlInteraction.HostingDivControl"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

       Background="Transparent"

       SizeChanged="UserControl_SizeChanged"

    Width="200" Height="200">

    <Grid x:Name="LayoutRoot" Background="White">

    </Grid>

</UserControl>

The control members:

private HtmlElement m_div        = null;

public HtmlElement Div

{

       get { return m_div; }

       set { m_div = value; }

}

The new Left and Top properties

public double Left

{

       get { return attrib = GetDimension("left", "offsetLeft", 0.0); }

       set { SetDimentionAttribute("left", value); }

}

public double Top

{

       get { return attrib = GetDimension("top", "offsetTop", 0.0); }

       set { SetDimentionAttribute("top", value); }

}

How to obtain html element dimensions (width, height, left, top... etc.)

// get the html element’s dimension (example: width, height, left...)

// first try to find the dimension in the element’s style (width, height, left, top...)

// if not found, try to obtain the element’s dimension property (offsetWidth, offsetHeight...)

private double GetDimension(string attrib_name, string property_name, double default_value)

{

       double attrib = GetDimensionAttribute(attrib_name, default_value);

       // if the style attribute is not present, try to get the dimension property

       if (attrib <= 0.0)

             return GetDimensionProperty( property_name, default_value);

       return attrib;

}

// get the html element’s style attribute (example: width, height, left...)

protected double GetDimensionAttribute(string attrib_name, double default_value)

{

       if (m_div == null)

       {

             debug_display_div_error();

             return default_value;

       }

       // try to get the style attribute’s value

       string str_value = m_div.GetStyleAttribute(attrib_name);

       if (string.IsNullOrEmpty(str_value))

       {

             return default_value;

       }

       // remove the dimension's unit (px)

       str_value = str_value.Replace("px", "");

       double value = default_value;

       // try to convert to numeric value

       if (double.TryParse(str_value, out value) == false)

             return default_value;

       return value;

}

// get the html element’s dimension property (example: offsetHeight, offsetWidth...)

protected double GetDimensionProperty(string property_name, double default_value)

{

       if( m_div == null)

       {

             debug_display_div_error();

             return default_value;

       }

       string str_value    = m_div.GetProperty( property_name).ToString();

       if (string.IsNullOrEmpty(str_value))

       {

             return default_value;

       }

       double value = default_value;

       // try to convert to numeric value

       if (double.TryParse(str_value, out value) == false)

             return default_value;

       return value;

}

I then registered the new Left and Top properties with the dependency system:

public static DependencyProperty LeftProperty = DependencyProperty.Register(

                    "Left", typeof(double), typeof(HostingDivControl),

                    new PropertyMetadata(

                           new PropertyChangedCallback(LeftPropertyChanged) ));

public static DependencyProperty TopProperty = DependencyProperty.Register(

                    "Top", typeof(double), typeof(HostingDivControl),

                    new PropertyMetadata(

                           new PropertyChangedCallback(TopPropertyChanged)));

private static void LeftPropertyChanged(DependencyObject obj,

                           DependencyPropertyChangedEventArgs e)

{

       if (obj == null)

             return;

       if (obj is HostingDivControl != true)

             return;

       HostingDivControl ctrl = (HostingDivControl)obj;

       ctrl.Left    = (double)e.NewValue;

}

private static void TopPropertyChanged(DependencyObject obj,

                           DependencyPropertyChangedEventArgs e)

{

       if( obj == null)

             return;

       if( obj is HostingDivControl !=true)

             return;

       HostingDivControl   ctrl   = (HostingDivControl) obj;

       ctrl.Top     = (double) e.NewValue;

}

The html element accessor should do some work each time this member is changed:

public HtmlElement Div

{

       get { return m_div; }

       set

       {

             m_div = value;

             if (m_div != null)

             {

                    base.Width   = this.Width;

                    base.Height  = this.Height;

                    Left         = GetDimension("left", "offsetLeft", 0.0);

                    Top          = GetDimension("top", "offsetTop", 0.0);

                    // to be able to change the left and top propertites,

                    // the html element should have its position style set

                    // to relative or absolute.

                    If (string.IsNullOrEmpty( m_div.GetStyleAttribute("position")))

                           m_div.SetStyleAttribute("position", "relative");

             }

             else

                    m_div_id = "";

       }

}

We are now ready to use our new custom control to animate the Silverlight control inside its hosting html page.

<Grid x:Name="LayoutRoot" Background="Transparent">

       <local:HostingDivControl x:Name="host_div" Margin="0"/>

The animation Storyboard

Let’s create a storyboard that changes the top/left properties of our control (which is the silver light control itself)

Note: I couldn’t use Expression Blend to create the storyboard. Blend seemed confused about the properties to be animated for this ‘special’ control. So I ended up by creating the storyboard by hand!

<UserControl.Resources>

       <Storyboard x:Name="Storyboard1">

             <DoubleAnimationUsingKeyFrames BeginTime="00:00:00"

             Storyboard.TargetName="host_div" Storyboard.TargetProperty="(Top)">

                    <EasingDoubleKeyFrame KeyTime="00:00:00" Value="0"/>

                    <EasingDoubleKeyFrame KeyTime="00:00:00.50" Value="50"/>

                    <EasingDoubleKeyFrame KeyTime="00:00:00.80" Value="85"/>

                    <EasingDoubleKeyFrame KeyTime="00:00:01.00" Value="130"/>

             </DoubleAnimationUsingKeyFrames>

             <DoubleAnimationUsingKeyFrames BeginTime="00:00:00"

             Storyboard.TargetName="host_div" Storyboard.TargetProperty="(Left)">

                    <EasingDoubleKeyFrame KeyTime="00:00:00" Value="0"/>

                    <EasingDoubleKeyFrame KeyTime="00:00:00.50" Value="100"/>

                    <EasingDoubleKeyFrame KeyTime="00:00:00.80" Value="225"/>

                    <EasingDoubleKeyFrame KeyTime="00:00:01.00" Value="350"/>

             </DoubleAnimationUsingKeyFrames>

                    ...

                    ...

       </Storyboard>

</UserControl.Resources>

Live demo

Download the sample code

AnimatedSilverHtmlHost.zip (83.45 kb)

System Attributes can be used to simply tag and retrieve object properties and methods in particular context.

In my case here, I wanted to be able to know which properties are contained in objects in order to be able to dynamically assign or change their values using simple text entries in a database.

I created an (empty) Attribute and named it xSysPropertyAttribute:

[global::System.AttributeUsage(

             AttributeTargets.Property,

             Inherited = false,

             AllowMultiple = true)]

public sealed class xSysPropertyAttribute : Attribute

{

       public xSysPropertyAttribute()

       {

       }

}

I then used this attribute to ‘tag’ the desired objects’ properties:

[xSysProperty]

public bool BoolProperty

{

       get { return m_bool_property; }

       set { m_bool_property = value; }

}

[xSysProperty]

public string StringProperty

{

       get { return m_string_property; }

       set { m_string_property = value; }

}

Using System.Reflection, I, now, can retrieve these properties and dynamically list them:

foreach (PropertyInfo p in obj.GetType().GetProperties())

{

       bool   has_sys_attribute   = Attribute.IsDefined( p, typeof(xSysProperty), false);

       if(has_sys_attribute)

       {

             // Do something with this property

       }

}

We can use this simple code to dynamically build lists of object’s (tagged) properties.

Once the list of the desired properties has been built, we can now read and modify their values (using user input, xml, text files or database records):

Example of Read the property’s value:

public object GetValue(MyObject obj, string property_name)

{

       if( obj == null || string.IsNullOrEmpty( property_name))

             return null;

       PropertyInfo pinfo  = this[property_name];

       if( pinfo == null)

             return null;

       return pinfo.GetValue(obj, null);

}

Example of Set the property’s value:

public bool SetValue(MyObject obj, string property_name, object value)

{

       if( obj == null)

             return false;

       PropertyInfo pinfo  = this[property_name];

       if(pinfo == null)

             return false;

       // is this a readonly property?

       if(pinfo.CanWrite == false)

             return false;

       // get the property's data type

       Type   property_type = pinfo.PropertyType;

       object new_value;

       try

       {

             // try to convert the given value to property's data type

             new_value    = Convert.ChangeType( value, property_type);

       }

       catch (Exception)

       {

             return false;

       }

       try

       {

             // try to assign the converted value to the object

             pinfo.SetValue( obj, new_value, null);

       }

       catch (Exception)

       {

             return false;

       }

       return true;

}

Still we have another problem: we need a solid link between listed properties and the objects on which we may get and set properties’ values. i.e. we need to be sure that a property is actually part of the target object without having to check this each time a get or set is requested.

Here comes one the benefits of template classes.

We can implement our pattern using a template list like the following:

public class xBuiltinPropertiesListBase<PROPERTY, OBJ_TYPE, PROPERTY_SYS_ATTRIB>

             : List<PROPERTY> where PROPERTY : PropertyInfo

The code above defines a list template that will contain objects of type PROPERTY and that type should be a PropertyInfo (or one derived class)

It also defines that for the list to be initialized, a class (System.Type) OBJ_TYPE and a System Attribute PROPERTY_SYS_ATTRIB should be provided.

This way, our list explicitly integrates the Type on which it will operate.

Providing the System Attribute may also allow extending the list template for future use in other contexts.

On list initialization, the template will proceed to listing all properties with the attribute PROPERTY_SYS_ATTRIB defined within the class of OBJ_TYPE:

public bool LoadClassAttributeProperties()

{

       Clear();

       Type   obj_type     = typeof(OBJ_TYPE);

       // loop through all class's properties.

       // insert those properties defined with the specified System.Attribute.

       foreach (PropertyInfo p in obj_type.GetProperties())

       {

             // Does this property define our System.Attribute?. add it to the list.

             bool   has_sys_attribute = Attribute.IsDefined( p,

                                        typeof( PROPERTY_SYS_ATTRIB), false);

             if(has_sys_attribute)

             {

                    PROPERTY     new_property = (PROPERTY)p;

                    Add(new_property);

             }

       }

       return true;

}

The above GetValue and SetValue methods will also have to be slightly modified before being integrated in the list template. Example:

public bool SetValue(OBJ_TYPE obj, string property_name, object value)

{

       if( obj == null)

             return false;

       PROPERTY     my_property  = this[property_name];

       if( my_property == null)

             return false;

       // is this a readonly property?

       if( my_property.CanWrite == false)

             return false;

       // get the property's data type

       Type   property_type = my_property.PropertyType;

       object new_value;

       ...

       ...

Download the sample code ExtensibleObjectProperties.zip (46.35 kb)