Dynamic Data Display in Silverlight

8/18/2007

Introduction

For my first attempt at a Silverlight application, instead of the usual fancy eye-catching demos, I wanted to work out how I might display an image, based on data from SQL Server. (Proof that I am a coder, not a designer)  In this article I will demonstrate creating a basic, dynamic bar chart in Silverlight.  This isn't really just an article about Silverlight, but an end-to-end demonstration using Silverlight, XAML, ASP.Net, and SQL Server.  You will understand it better if you have some background with with those technologies.  I'll try to point you in the right direction for background material on some specific subjects.

I should note that I did a very similar demonstration project OVER 6 YEARS AGO with a technology called Scalable Vector Graphics (SVG) and a browser plug in from Adobe.  I saw the power of it then and thought it would simply take over as a presentation method on the web, since it went way beyond html, and since it was markup based, it could be created on the fly by server side code.  Well nothing really ever became of it, but now Microsoft has invented XAML, a vector based graphic markup language, and Silverlight, the browser plug in to render XAML.  Everything old is new again...

I don't want to write a whole lot introducing Silverlight and XAML, but rather get right down to showing you the sample and how I did it.  To get up to speed on Silverlight, go here.

The goal of this sample is to create a simple bar chart in Silverlight, based on some data in a database.  This isn't meant to be a full featured charting application.  You can bet the component making companies out there will put out Silverlight enabled charting components.

The goals, specifically:

  • Create a bar chart in XAML to be displayed in Silverlight 1.0RC.
  • Use as little code as possible.  I am already seeing some Silverlight examples with long code listings, but in keeping with my theme of less code is better, I will show you how to maximize the toolsets available, specifically:
  • Use the XML capabilities of SQL Server 2005 to do some of the grunt work of formatting XAML content.
  • Use an ASP.Net handler page (.ashx) to generate and serve the XAML for the Silverlight control.  There is a lot of talk about using web services for data retrieval for Silverlight, and in some cases that might be the best solution.  But here, we are getting data from our own web server so we don't have to worry about compatibility issues, and besides, handlers blow away web services as far as performance.

Architecture

The architecture is very simple for this project.  There is a web page, default.aspx that contains JavaScript to load the Silverlight plug in.  This script is taken from the Quickstart examples that you get when you download the Silverlight 1.0RC SDK.  The XAML source for the plug in is a handler, GetBarChart.ashx.  The handler calls a stored procedure in SQL Server to retrieve the bars and text of the bar chart, as XML.  It then merges it with a template file containing the rest of the XAML markup and sends it back to the caller.  The stored procedure uses the Northwind database and gets the top 10 selling products and their total sales.

Sample Architecture

Since the Silverlight 1.0 version is scriptable with JavaScript in the browser, many examples you will see demonstrate refreshing the contents of the Silverlight plug in without refreshing the whole page.  That is a good thing.  But for this example, I'm not doing that.  The data is retrieved each time the page is shown by the browser.  I'll probably use this as an excuse to do a follow on article later, to make it more interactive.

There are several other design decisions to be made, and I will address them as we build each part. The rest of the article will show you piece by piece how to set up the project, starting from the bottom up.

The Database

SQL Server 2005 has some great new features in T-SQL, many of which I think people don't really know about.  These features give you a lot of power in producing data outputs.  By using them, you can reduce or eliminate procedural code to do data processing.

You might think that the obvious solution here is to open a Dataset in the handler code, loop through the rows, creating XAML markup as we go.  You could do that, but why, if you can get SQL Server to do most of the work for you?

One of these great features is the T-SQL clause FOR XML PATH that provides a flexible way to create XML output from a query.  It is much better than what was available in SQL Server 2000.  I wrote another article that introduces this concept to create an RSS feed with SQL Server.  Check it out for a quick primer.

Another new feature that I used is the Row_Number() function in T-SQL.  This function sequentially numbers the rows of a query output.  I use it here since I need to figure the positions of the bars in the chart based on their sequence in the data.  Here is the MSDN reference for Row_Number().

DECLARE @Scale float

SET @Scale = (SELECT 400/MAX(ProductSales) FROM [Northwind].[dbo].[Sales by Category])

SELECT 

(SELECT TOP 10 
	ProductSales * @Scale AS "@Height"
	,(Row_Number() OVER(ORDER BY ProductSales DESC) - 1) * 55 + 5 AS "@Canvas.Left"
	, 0 AS "@Canvas.Top"
	, 50 AS "@Width"
	, 5 AS "@RadiusX"
	, 5 AS "@RadiusY"
	, 'Black' AS "@Stroke"
	, 1 AS "@StrokeThickness"
	, 'SlateBlue' AS "@Fill"
  FROM [Northwind].[dbo].[Sales by Category]
  ORDER BY ProductSales DESC
  FOR XML PATH('Rectangle'), type ),

(SELECT TOP 10 
	CONVERT(varchar(10), ProductSales, 1) AS "@Text"
	,(Row_Number() OVER(ORDER BY ProductSales DESC) - 1) * 55 + 5 AS "@Canvas.Left"
	, ProductSales * @Scale + 15 AS "@Canvas.Top"
	, 12 AS "@FontSize"
	, 'Arial' AS "@FontFamily"
	, 1 AS "TextBlock.RenderTransform/ScaleTransform/@ScaleX"
	, -1 AS "TextBlock.RenderTransform/ScaleTransform/@ScaleY"
  FROM [Northwind].[dbo].[Sales by Category]
  ORDER BY ProductSales DESC
  FOR XML PATH('TextBlock'), type )

FOR XML PATH('Canvas')

Stored Procedure Code

This query is based on a view in the Northwind database called Sales by Category.  It provides sales totals for products and categories.  I am just selecting the top 10 sales totals from this view.  The sales totals values run into the tens of thousands of dollars.  I want the length of the bars in the bar chart to reflect the values.  But I don't want the bars to be tens of thousands of pixels high.  So I have to do some scaling.  It is certainly possible to scale drawings in XAML, but I found it easier to do that here in the data.  I want the chart to be 400px high, so I use the @Scale variable to 'normalize' the heights of the bars, so that the highest one will be nearly 400px, and the others proportionally shorter.  The variable gets set to a scaling factor, which is then applied to the sales total to get the height of the bar.  Numeric properties in XAML such as Height are floating point (double), so you can use the float data type in SQL Server. 

The outer most Select statement provides a wrapper <Canvas> tag for the contents.  The first nested query creates the bars, which are <Rectangle> tags in XAML.  Several of the attributes are hard coded in the SQL statement.  You could of course make these parameters of the stored procedure.  The Canvas.Left attribute is the left edge of the rectangle.  This is calculated based on the sequence of the bars.  Each bar is 50px wide with a 5px space.  So the formula is 55 times the sequence number (starting at 0) plus 5.  Each item has an alias name that begins with '@' so that it will be an attribute of the <Rectangle> tag, versus a sub element.

The second nested query creates the text, which is the sales total.  This text will appear at the top of each bar.  The <TextBlock> tags also include some nested tags to do a transformation, in this case, flip the text over.  You'll see what this is all about in the section about the bar chart XAML below.

<Canvas>
<Rectangle Height="3.985045289999999e+002" Canvas.Left="5" Canvas.Top="0" Width="50" 
	RadiusX="5" RadiusY="5" Stroke="Black" StrokeThickness="1" Fill="SlateBlue" />
<Rectangle Height="2.897799300000000e+002" Canvas.Left="60" Canvas.Top="0" Width="50" 
	RadiusX="5" RadiusY="5" Stroke="Black" StrokeThickness="1" Fill="SlateBlue" />
........
<TextBlock Text="49,198.09" Canvas.Left="5" Canvas.Top="4.135045289999999e+002" 
	FontSize="12" FontFamily="Arial">
<TextBlock.RenderTransform>
<ScaleTransform ScaleX="1" ScaleY="-1" />
</TextBlock.RenderTransform>
</TextBlock>
<TextBlock Text="35,775.30" Canvas.Left="60" Canvas.Top="3.047799300000000e+002" 
	FontSize="12" FontFamily="Arial">
<TextBlock.RenderTransform>
<ScaleTransform ScaleX="1" ScaleY="-1" />
</TextBlock.RenderTransform>
</TextBlock>
.........
</Canvas>

Stored Procedure Results

The Handler

The handler is a file in the ASP.Net web site with an .ashx extension that does some server side processing and returns data to the requester.  If you aren't familiar with them, I highly encourage you to look into handlers.  They are great for this kind of scenario, where we want the server to process some data and return something, but not a whole page.  Scott Mitchell did a great article on MSDN that will give you a good introduction.  You could use an .aspx page to do this, but there is no sense in incurring the extra processing costs of an .aspx page over a handler.  You could also use a web service here.  That would mean some client-side code to make the web service call, and deal with the results.  With the handler, it's just a reference to the URL in the script that creates the plug-in. 

The handler's job here is to execute the stored procedure, merge the results with a template file, and return the completed XAML.  The code is is pretty simple.  This is where you could be seeing a lot of procedural code to loop through data and construct the XAML.  But thanks to SQL Server's capabilities, we don't have much to do.

The stored procedure returns the data portion of the bar chart; the bars and the text.  But there are other static parts of the file as well.  So I created a template file that contains the markup for the chart XAML, minus the data portion.  I had a decision point here.  Since XAML is XML, I was tempted to load the template file into an XMLDocument object.  But since I don't really have to do anything except add the data markup, I decided instead to load the template into a StringBuilder object.  The template has {0} in the place where the data goes, so I can use String.Format to embed the data markup.  Either way is fine, but I am betting that doing one text merge with the Format function is faster than loading and parsing XML in the XMLDocument object.

Public Sub ProcessRequest(ByVal context As HttpContext) _
	Implements IHttpHandler.ProcessRequest
    Dim cmd As SqlClient.SqlCommand
    Dim reader As System.Xml.XmlReader
    Dim sb As New StringBuilder

    context.Response.ContentType = "text/xml"

    sb.Append(My.Computer.FileSystem.ReadAllText(context.Server.MapPath("Barchart.txt")))
        
    Using conn As New SqlClient.SqlConnection( _
    	ConfigurationManager.ConnectionStrings("MyConnstr"))
        conn.Open()
            
        cmd = New SqlClient.SqlCommand("usp_GetBarchart", conn)
            
        With cmd
            .CommandType = CommandType.StoredProcedure
            reader = .ExecuteXmlReader
        End With
            
        reader.MoveToContent()
            
        context.Response.Write(String.Format(sb.ToString, reader.ReadOuterXml))
            
        conn.Close()
            
    End Using

End Sub

GetBarChart.ashx Processing Code

You do your work in the ProcessRequest event handler in a Handler.  First I set the content type to XML.  Then I grab the template file and read it into a StringBuilder. (One line of code!  Try that in VB6.)  Then I connect to the database and execute the stored procedure, getting the results into an XMLReader.  Lastly I write out the template text, merged with the data markup. 

The Bar Chart in XAML

In terms of XAML, the bar chart is just a collection of rectangles, with a text block above each rectangle.  Bar charts are typically displayed on top of a 2-D grid with the origin (the 0,0 point) at the lower left of the image.  This causes a design issue, because the origin of the layout grid in XAML is the top-left corner.  Somehow you need to plot the graphic objects so they look like a normal bar chart. There are probably several ways you could handle this, i.e. do some offset calculations to figure the actual positions of the rectangle for each item.  But I found a simpler way. 

XAML provides transformations, which allow you to rotate, flip, resize, offset, or skew graphic elements, or a whole canvas.  Rather than do more calculations, I decided to draw the bar chart upside down, with the X-axis along the top of the picture and the bars heading downward.  Then I use a transformation to flip the chart right side up.  Sort of like writing on a square piece of glass with color markers, then flipping the glass over.

To do this, you use a transformation called ScaleTransform on the canvas.  This is normally used to resize things.  But if you make the scale values negative, it causes the object being transformed to be flipped over.  So to flip the bar chart over along the horizontal axis, I set ScaleY = "-1".  Then I set CenterY="250".  This sets the axis around which the image will be flipped to the middle of the canvas.  (The plug in height is set to 500px).   

<Canvas>
	<Canvas.RenderTransform>
		<ScaleTransform ScaleX="1" ScaleY="-1" CenterY="250"/>
	</Canvas.RenderTransform>
</Canvas>

Remember in the stored procedure I write out a transformation for the text?  The text blocks are placed on the canvas before it is flipped.  Once you flip it, the text is upside down.  To get it right, the text is written upside down to begin with.  Then after the canvas is flipped, it is right side up again.

The Web Page and Silverlight

We are finally ready to display the chart on a web page.  It may seem like I have done a lot up to this point, but there really isn't that much code.  I followed the example from the QuickStart document found in the Silverlight 1.0RC SDK.  The page, default.aspx links to the silverlight.js file provided by Microsoft and my JavaScript file that has the function to get the plug-in initialized.  A <div> tag marks the location of the plug-in on the page.  

<!-- Where the Silverlight plugin will go-->
<div id="mySilverlightPluginHost">
</div>
<script type="text/javascript">

// Retrieve the div element you created in the previous step.
var parentElement = 
document.getElementById("mySilverlightPluginHost");

// This function creates the Silverlight plugin.
createMySilverlightPlugin();

</script>

Default.aspx

When the page loads, it calls the initialization function that sets up the Silverlight plug-in.  Among other things it tells the plug-in to get its source from GetBarChart.ashx, the handler.  You might notice that taken by itself, this is not the most efficient way to do things.  I could have had the web page do the XAML processing without having to make a separate call to the web server.  But I did it this way because in the future, this application should be able to refresh the chart without having to reload the whole page.

Finally, below you see the finished bar chart in a Silverlight window.  If you don't see a bar chart, but rather a blue image telling you to get the Silverlight plug in, then just click on the image to install it.  It is a very easy, painless installation.  Note that this image is from a static XAML file, saved from the handler. 

Conclusion

This certainly isn't the most exciting Silverlight image you will ever see.  But it accomplished my goal, which was to do a simple end-to-end prototype project to get data from a database and show it, visually in Silverlight.  Hopefully this will get you thinking about how you are going to implement a solution for creating data aware Silverlight content.  There are many ways to do it, some more efficient than others.  It is my understanding that Silverlight 1.1 will have built in support for AJAX calls.  We'll see.  For now, the ASP.Net handler provides a simple and very efficient way to return data, in this case XAML, from a web server.