Taoffi's blog

It is time now to set concepts aside for some more practical problems!

Last days, through the work on two different projects, I encountered DBNull twice, and faced a funny problem about locating the target TreeNode of a contextual menu!

Let us start by the first one:

DBNull in Silverlight and Windows Forms

I encountered a DBNull problem two times (in three days… that seems a bit much). The first time was in the context of a WCF service intended to feed a Silverlight application, and the second was in the context of a GridView control in a Windows Forms application.
For your information, DBNull is a .NET Type defined in namespace System (mscorlib.dll).

The first issue emerged with what appeared to be a communication problem between a Silverlight application and one related WCF service. After some research, DBNull was reported to be an unknown Type in XML serialization. The service was transmitting data read through a SQL Server database… We had a Cell class. Its Data member was an object.
A bit more research made it visible that the following code as the source of the annoyance:

In fact, when the field at cell_index was null, data_reader.GetValue() returned DBNull. Which was assigned as the cell’s Data. Serialized by WCF, DBNull was not recognized by the receiving Silverlight application.

Though the time needed to discover the source of the problem was quite long, the solution was quite simple:

// Which clearly means:

Now that the cell content is null, everything goes right (because null is a recognize Type).
It will be a great day when DBNull (which implements the ISerializable Interface) will simply be serialized as null… let us just wait.

Yet another DBNull problem!

Once the Silverlight DBNull problem solved… precisely: the next day J, I just found myself again confronted by another DBNull. This time in a Windows Forms application.
In this new case, I had a DataGridView control which was filled using a DataTable and I wanted to programmatically add a row to those displayed in the DataGridView control.
Obtaining the row’s data was simple (calling a web service or by directly querying a database).
The task was then to add the obtained data as a new row of the DataTable used as the source of the control. The problem was again related to the cells containing null values in the new added row.

In fact, if you assign the row’s cells data through a DataRow object you may not have a particular issue. Issues appear when you try to assign an object’s properties values to the row cells.

In this particular case, we had an object with some properties of nullable types: for instance int?  parent_id. The code below generated an error:

Some solutions are proposed in several forums. They mainly propose to handle some special events of the DataGridView (like DataError event) and/or play with custom interpretation of DBNull…
The solution I used seems more simple. It is based on the fact that when the row was created (using table.NewRow() method) it contained all required cells… each of which, a priori, filled with something (a sort of ‘null’ thing… we don’t really care about its type). So, we simply may assign cell values only if the data to be assigned is NOT null:

That seems to work!

Contextual menus… yes! but where is the target node?

TreeView control is a nice and useful control to show many data domains.
One nice and efficient thing of TreeView control is that it allows the developer to handle many aspects of tree nodes by their level in the tree hierarchy. You can for instance choose the icon of items according to their nest level in the tree hierarchy
Among the customizations you can do is to choose a specific contextual menu for each hierarchy level… very cool and simple to do.
When the user right clicks a node in the tree, he or she would see the assigned contextual menu and be able to execute operations in relation to the type of the node.
The thing is, when a menu is clicked, you developer should find out which node was selected when this menu was clicked… Curiously, this is not as simple as we may expect.

I naively thought, a contextual menu object will contain some relevant information about its Target object (in our case: the target Node)… unfortunately this was not true!
So, you simply find yourself with a command to execute (the menu clicked)… but don’t know on which object of the tree should you execute this command!
Few solutions are proposed to solve this problem, and none, of what I read, seemed sustainable.
Here again, we should ‘reinvent the wheel’…

Reinventing the contextual wheel!

The problem seems to be: “Locating the target tree node”. The problem’s definition starts by “Locate”… so let us think “location”.
I first started by trying to locate the mouse cursor at the moment of the click event (using MousePosition which returns the point of the current mouse position in screen coordinates)… but that seemed random, not always related to the node position (and also quite difficult to debugJ).
The next step was to think “location of controls”.
In fact, a contextual menu is a control and as such, it should have a location… hopefully relative to its parent control (which is the TreeView).
Our Click event is received from a contextual menu item (ContextMenuStrip object) which is located into a parent control (the contextual menu box), itself is a child control of the TreeView control containing the node.
The following figure illustrates this disposition 

The Owner property of the menu strip contains its parent menu control information. Through this information, we are able to obtain the location of the menu box, and thus, the location  of the first menu strip relative to the TreeView control. With some more simple arithmetic, we can finally locate the right-clicked node (the target node) using the TreeView control GetNodeAt(Point point) method :

 That works fine for contextual menus with few elements and in a ‘normal’ screen resolution.
If the contextual menu contains many items, the first item may be quite far away from the target node!...So, what?
Let us look for a more sustainable way to locate our item… we may again, rethink ‘mouse location’!

In this solution, we will keep track of the current ‘right-clicked node by responding to the MouseDown event of the TreeView.

Now, when a contextual menu is clicked, we can first see if the target node is not null… execute the command and reset the target node to null (until the next time): 

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