A client of mine wanted to visualize his customers by city they live in. His customer addresses reside in the SQL server database I've set up for him.
Since I have started development of a small sales force Silverlight app for him, I looked at what could be done with Silverlight to visualize his data. There are several possibilities, such as visualization on a Bing map, but to start off and keep it simple and to avoid spending too much time on this, I decided to use the Treemap control.
The data is stored in an Address table in a database in SQL Server 2008. All the Addresses are located in Belgium and have all been given a City name and zip code.
The Silverlight project is written in Visual Studio 2008 with Silverlight 3 and WCF RIA services beta.
Treemap in Silverlight 3 article by Diptimaya Patra in c-sharpcorner.
Using stored procedures in the Entity Framework by Julie Lerman in thedatafarm.
Articles about WCF RIA services by Brad Abrams, Tim Heuer and many others.
The screenshot at the top of this article shows the result: a Treemap control that visualizes the addresses per city. The cursor was hovering over the city of Bruges (Brugge) with 70 customers.
The SQL server database needs a view or stored procedure that groups the addresses by city and zipcode. Larger cities have different zipcodes for one city name, so the city and zipcode need to be concatenated. Since my middle-tier is going to be WCF RIA services and the Entity Framework, I need a result that has a primary key too. And because of a blatant problem with the Entity Framework designer that does not allow you to specify a primary key field on a view, I'll have to fake my primary key.
Use a stored procedure to make it fit in my Entity Framework. The stored procedure uses ROW_NUMBER() as a column with name ID and this will be my primary key column.
The procedures returns a recordset with 4 columns: ID, Gemeente, Tooltip, Aantal.
CREATE Procedure dbo.upAddressTreemap
AS
BEGIN
SET NOCOUNT OFF;
SELECT CONVERT(int,ROW_NUMBER() OVER (ORDER BY Gemeente, Zipcode)) AS 'ID'
,RTRIM(City + ' ' + Zipcode) AS Gemeente
, City + '-' + Zipcode + ' Aantal:' + Convert(varchar,COUNT(*)) AS Tooltip
, COUNT(*) AS Aantal
FROM dbo.Contact
GROUP BY City, Zipcode
END
The first 3 records of the recordset can be seen here:
ID | Gemeente | Tooltip | Aantal |
1 | Aalbeke 8511 | Aalbeke-8511 Aantal:7 | 7 |
2 | Aalst 9300 | Aalst-9300 Aantal:5 | 5 |
3 | Aalter 9880 | Aalter-9880 Aantal:18 | 18 |
Because I do not like to edit xml in the entity framework edmx file, I create a dummy table that will only be read by the Entity Framework designer.
The table contains the same fields as the resulting recordset of my stored procedure. This table will be used by the framework designer, later I will add the stored procedure in the Entity framework and map it to this table.
CREATE
TABLE [dummy].[AddressTreemap](
[ID] [int] NOT
NULL,
[Gemeente] [varchar](100)
NOT
NULL,
[Tooltip] [varchar](100)
NOT
NULL,
Aantal [int] NULL,
CONSTRAINT [PK_ AddressTreemap] PRIMARY
KEY
CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX
=
OFF,
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON [PRIMARY]
)
ON [PRIMARY]
The standard procedure to make a Silverlight database application is to create an entity framework edmx file in the Web project and to add a Domain Service class based on this entity framework.
The Entity Framework designer has problems using views when it cannot find a primary key. My solution is to create a dummy table with the necessary fields and the primary key. Then to add a stored procedure to the EF and to map this procedure to the dummy table.
The solution to this is based on the article by Julie Lerman.
In Visual Studio 2008 in my Silverlight.Web project I had already added an ADO.NET Entity Data Model.
Create the Entity with the designer wizard to add the dummy table and the stored procedure.
The dummy table is shown as an entity on the design surface.
The stored procedure can be found in the model browser in the Stored procedures section of the model store.
Save and build this model.
Add an import function in the model browser, by rightclicking on the stored procedure name in the model browser. The return type is the AddressTreemap Entity.
The result in the model browser
To be checked.
Save and build the project.
Add a domain service class to the project. I work with partial classes so I can recreate the Domain service class whenever my entity framework changes.
I make the generated AdressenService class partial and then add a new class with the filename AdressenService.partial.cs. I use new this partial class to add all my custom methods. Whenever the Entity model changes, I can regenerate my DomainService class without losing my custom code. This tip I found in an blog post by someone else, but I cannot remember the link any longer.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace Adressen.Web
{
public
partial
class
AdressenService
{
public
IQueryable<AddressTreemap> GetAddressTreemapSproc()
{
AddressTreemap entities = new
AddressTreemap ();
System.Data.Objects.ObjectResult< AddressTreemap > dbResults =
this.ObjectContext.upAddressTreemap();
return (IQueryable< AddressTreemap >)dbResults.AsQueryable();
}
}
}
Save and build the project. This finalizes our middle-tier section.
To finish off, the result will be displayed in a Silverlight Page with a Treemap control and with the RIA services DomainDataSource control. Everything will be handled in xaml, no code behind will be needed.
In the UserControl attributes section add a reference to the Silverlight.web application
xmlns:App="clr-namespace:AdressenTreemap.Web"
In the Grid section add a Grid resources collection that refers to the App namespace
<Grid.Resources>
<App:AdressenContext
x:Key="adressenContext"/>
</Grid.Resources>
Drag a DomainDataSource control from the toolbox in the Grid section. This will automatically add the namespaces.
xmlns:riaControls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Ria"
The DomainDataSource has the following properties: Autoload True, QueryName refers to the method we made in the partial class.
<riaControls:DomainDataSource x:Name="ddsTreemap" AutoLoad="True"
QueryName="GetAddressTreemapSprocQuery"
DomainContext="{Binding Source={StaticResource
adressenContext}}">
</riaControls:DomainDataSource>
Sorting has been added out of the experience I've had with datagrids: If I do not specifiy sorting , then most often you get errors.
For sorting, add the following namespace
xmlns:datagroup="clr-namespace:System.Windows.Data;assembly=System.Windows.Controls.Ria"
Sorting is done on the ID column of the datasaource.
<riaControls:DomainDataSource … <riaControls:DomainDataSource.SortDescriptors>
<datagroup:SortDescriptor PropertyPath="ID"/>
</riaControls:DomainDataSource.SortDescriptors>
</riaControls:DomainDataSource>
Drag a treemap control onto the grid, below the DomainDataSource. This automatically adds a reference to the following namespace:
xmlns:visualizationToolkit="clr-namespace:System.Windows.Controls.DataVisualization;assembly=System.Windows.Controls.DataVisualization.Toolkit"
The treemap has to use the data from the DomainDataSource. A Data range from Blue (most) to Magenta (least) is specified.
<visualizationToolkit:TreeMap x:Name="trm" ItemsSource="{Binding Data, ElementName=ddsTreemap}"
>
<visualizationToolkit:TreeMap.Interpolators>
<visualizationToolkit:SolidColorBrushInterpolator TargetName="itemBorder" TargetProperty="Background"
DataRangeBinding="{Binding
Aantal}" From="Blue" To="Magenta" />
</visualizationToolkit:TreeMap.Interpolators>
<visualizationToolkit:TreeMap.ItemDefinition>
<visualizationToolkit:TreeMapItemDefinition ValuePath="Aantal">
<DataTemplate>
<Border x:Name="itemBorder" BorderBrush="Black" BorderThickness="1" ToolTipService.ToolTip="{Binding
Tooltip}">
<TextBlock x:Name="trmGemeente" Foreground="White"
Text="{Binding
Gemeente}" VerticalAlignment="Center"
TextAlignment="Center" TextWrapping="Wrap"/>
</Border>
</DataTemplate>
</visualizationToolkit:TreeMapItemDefinition>
</visualizationToolkit:TreeMap.ItemDefinition>
</visualizationToolkit:TreeMap>
</controlsToolkit:DockPanel>
To round up, I added grid columns and rows to center my treemap in the page.
To show the user the data is loading I've added a BusyIndicator.
To explain what is being displayed, a Dockpanel was used in which a textbox was added with an axplanatory title together with the t
Below is the final xaml code (example was made in a masterpage).
<UserControl
xmlns:visualizationToolkit="clr-namespace:System.Windows.Controls.DataVisualization;assembly=System.Windows.Controls.DataVisualization.Toolkit"
xmlns:controlsToolkit="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Toolkit"
xmlns:datagroup="clr-namespace:System.Windows.Data;assembly=System.Windows.Controls.Ria"
xmlns:App="clr-namespace:AddressTreemap.Web"
xmlns:riaControls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Ria"
xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"
x:Class="AddressTreemap.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d" d:DesignWidth="640" d:DesignHeight="480">
<controlsToolkit:BusyIndicator x:Name="busyIndicator"
IsBusy="{Binding IsBusy, ElementName=ddsTreemap}"
BusyContent="Bezig met laden" Grid.RowSpan="3" Grid.ColumnSpan="3">
<Grid x:Name="LayoutRoot" Margin="-8,8,8,-8">
<Grid.Resources>
<App:AdressenContext
x:Key="adressenContext"/>
</Grid.Resources>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="0.1*"/>
<ColumnDefinition Width="0.8*"/>
<ColumnDefinition Width="0.1*"/>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="0.1*"/>
<RowDefinition Height="0.8*"/>
<RowDefinition Height="0.1*"/>
</Grid.RowDefinitions>
<controlsToolkit:DockPanel LastChildFill="True"
VerticalAlignment="Top" Grid.Column="1" Grid.Row="1" d:LayoutOverrides="Width" Height="384">
<riaControls:DomainDataSource x:Name="ddsTreemap" AutoLoad="True"
QueryName="GetAddressTreemapSprocQuery"
DomainContext="{Binding Source={StaticResource adressenContext}}">
<riaControls:DomainDataSource.SortDescriptors>
<datagroup:SortDescriptor PropertyPath="ID"/>
</riaControls:DomainDataSource.SortDescriptors>
</riaControls:DomainDataSource>
<Grid x:Name="gridTitel" Background="DarkGray"
controlsToolkit:DockPanel.Dock="Top">
<TextBlock x:Name="txtTitel" Text="Verdeling per gemeente van adressen voor mailing met de post"
TextAlignment="Center" TextWrapping="Wrap" Height="32" FontSize="18"
Foreground="White"
Margin="2"/>
</Grid>
<visualizationToolkit:TreeMap x:Name="trm" ItemsSource="{Binding Data, ElementName=ddsTreemap}"
>
<visualizationToolkit:TreeMap.Interpolators>
<visualizationToolkit:SolidColorBrushInterpolator TargetName="itemBorder" TargetProperty="Background"
DataRangeBinding="{Binding Aantal}" From="Blue" To="Magenta" />
</visualizationToolkit:TreeMap.Interpolators>
<visualizationToolkit:TreeMap.ItemDefinition>
<visualizationToolkit:TreeMapItemDefinition ValuePath="Aantal">
<DataTemplate>
<Border x:Name="itemBorder" BorderBrush="Black" BorderThickness="1" ToolTipService.ToolTip="{Binding Tooltip}">
<TextBlock x:Name="trmGemeente" Foreground="White"
Text="{Binding Gemeente}" VerticalAlignment="Center"
TextAlignment="Center" TextWrapping="Wrap"/>
</Border>
</DataTemplate>
</visualizationToolkit:TreeMapItemDefinition>
</visualizationToolkit:TreeMap.ItemDefinition>
</visualizationToolkit:TreeMap>
</controlsToolkit:DockPanel>
</Grid>
</
controlsToolkit:BusyIndicator>
</UserControl>
With some creative computing on the database side, I was able to give my client a new visual presentation of his business data with minimal development time.
