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