Virtual Earth Applications on Windows Azure (Part 3)

Accessing Table Storage

In the first part we had a look at creating applications for Windows Azure and deploying them into the cloud. This was very simple and straight forward. Now we want to have a look at the Windows Azure Table storage and this is going to be slightly more tricky but don’t worry we get to it.

A while ago I have blogged about connecting your Virtual Earth application to a SQL database and we will migrate one of these applications to the cloud. With a SQL database the approach was to attach an event to the map, that fires when we pan or zoom the map. The function that is then being called will determine the bounding box of our map view and create an asynchronous AJAX-call to a web service or web handler which will execute the SQL statement and create JSON-objects. The response from this service is now received by our AJAX-call and the VEShape-objects will be added to the map.


Tables in the Windows Azure Table storage are different from SQL tables. They are not relational but hierarchical tables and instead of primary keys we have to work with PartitionKey and RowKey. This might remind some of us of the old IMS database systems and we might wonder if we made a step back in time but if you give it a second thought and consider that this table storage is intended to supported massive scalable applications you will see the logic behind. After all what do you do if you scale out a SQL Server? You will create partitions and distribute the load over multiple servers. In the case of Windows Azure the resources are accessible through instances and as mentioned in the first part of this series you just need to change the number the number of instances in the configuration file to scale up or down. The biggest challenge might be that in the current CTP Windows Azure supports only a subset of the LINQ Query Operators and Comparison Operators. It is also worth mentioning that the current CTP will truncate the results of a query after 1,000 entities so you will need to design your queries accordingly.

To get some background on the Windows Azure Table storage I suggest this whitepaper on Programming Table Storage. Obviously it will be helpful to have the SDK at hand. I also really like this video in which Steve Marx creates a .NET application using the Windows Azure Table Storage. It is really a great starting point for a quick start and I will use some of the tips and tricks he describes there.

As mentioned in part 2 the Windows Azure SDK comes with a couple of samples and one of those – the StorageClient – contains a .NET library that I will use here. So let’s assume that I have already compiled this library. Now I go ahead and add this library as well as the System.Data.Services.Client library as references to my project.


Next I add the account details and credentials to my web.config file:

    <add key="AccountName" value="YOUR STORAGE ACCOUNT NAME"/>
    <add key="AccountSharedKey" value="YOUR STORAGE ACCOUNT KEY"/>
    <add key="TableStorageEndpoint" value=""/>

Note: Do not add the account name to the URL for the TableStorageEndPoint.

The access to the table in the Windows Azure Table Storage we need to create a class-file which defines the data model.


In our new class we import the 2 references that we added before: the StorageClient from our sample library and the System.Data.Services.Client. The first class in my code-file contains the description of the entities in my table. It is derived from the TableStorageEntity in our sample StorageClient library and has properties for a latitude, a longitude and a name. The first function in this class is meant to add data to our table and even though this is not within the scope of our web application we will need it while we migrate data from our SQL Server into the cloud. The second function in the first class is an empty one and is used when we return data from our table.

The second class defines the table and it is derived from the TableStorageDataServiceContext in our sample StorageClient library. The function in this class is called with a number of parameters that will host the latitudes and longitudes of the bounding box in our map view. When this class is called the function first retrieves the storage account info from the configuration file. Finally I add a property that executes the DataServiceQuery and actually queries the Windows Azure Storage Table with the parameters for the bounding box of our map as filter criteria.

Imports Microsoft.Samples.ServiceHosting.StorageClient
Imports System.Data.Services.Client

Public Class BPUKRecord
    Inherits TableStorageEntity

    Private _Lat As Double
    Public Property Lat() As Double
            Return _Lat
        End Get
        Set(ByVal value As Double)
            _Lat = value
        End Set
    End Property

    Private _Lon As Double
    Public Property Lon() As Double
            Return _Lon
        End Get
        Set(ByVal value As Double)
            _Lon = value
        End Set
    End Property

    Private _Name As String
    Public Property Name() As String
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property

    Public Sub New(ByVal _Lat As String, ByVal _Lon As String, ByVal _Name As String)
        MyBase.New("", String.Format("{0:d10}", DateTime.UtcNow.Ticks))
        Lat = _Lat
        Lon = _Lon
        Name = _Name
    End Sub

    Public Sub New()
    End Sub
End Class

Public Class BPUK
    Inherits TableStorageDataServiceContext

    Private ulLat As Double
    Private ulLon As Double
    Private brLat As Double
    Private brLon As Double

    Public Sub New(ByVal _ulLat As Double, ByVal _ulLon As Double, _
ByVal _brLat As Double, ByVal _brLon As Double) MyBase.New(StorageAccountInfo.GetDefaultTableStorageAccountFromConfiguration()) ulLat = _ulLat ulLon = _ulLon brLat = _brLat brLon = _brLon End Sub Public ReadOnly Property BPUKTable() As DataServiceQuery(Of BPUKRecord) Get Return From BPUKRecord In CreateQuery(Of BPUKRecord)("BPUKTable") Where _
((BPUKRecord.Lat < ulLat) And (BPUKRecord.Lat > brLat) _
And (BPUKRecord.Lon > ulLon) And (BPUKRecord.Lon < brLon)) End Get End Property End Class

Before we can query our table in our web application we have to create it add some data. For our example I will migrate a table from SQL Server to the Windows Azure Table storage. I basically use the class above in a small application that connects to my database, reads all records in a table and for each record that my SqlDataReader reads, I will add an entity to the Windows Azure table.


If we want to add data to our Windows Azure Table we have to retrieve the storage account information from the configuration file and first we need to make sure that the table exists. If it doesn’t I’ll create it with the code below.

Dim tables = TableStorage.Create(StorageAccountInfo.GetDefaultTableStorageAccountFromConfiguration())

Then we initialize our data model and add an object to our table and finally we save the changes. The lines below are executed for each record that our SqlDataReader reads from our SQL database.

Dim svc = New BPUK()
svc.AddObject("BPUKTable", New BPUKRecord(myReader(0).ToString, myReader(1).ToString, myReader(2).ToString))

You will find this little helper application here.

After this short excurse on the migration of my SQL Server table let’s come back to our web application. We already have our data model for the table in the Windows Azure Table storage described in the class above. Our JavaScript remains exactly the same as the one that we use to access the SQL database. It is called when we tick a checkbox in our website.

function AddTableData(control) {
    if (document.getElementById(control).checked == false) {
        //Delete POI

        //Detach Map-Events
        map.DetachEvent("onendpan", LoadData);
        map.DetachEvent("onendzoom", LoadData);
    else {
        //Add the Shape-Layer
        try {
        catch (e) {

        //Attach Map-Events
        map.AttachEvent("onendpan", LoadData);
        map.AttachEvent("onendzoom", LoadData);

function LoadData() {

    //Retrieve the boundaries of the mapview
    var ulPixel = new VEPixel(0, 0);
    var brPixel = new VEPixel(mapWidth, mapHeight);
    var ulLatLon = map.PixelToLatLong(ulPixel);
    var ulLat = ulLatLon.Latitude;
    var ulLon = ulLatLon.Longitude;
    var brLatLon = map.PixelToLatLong(brPixel);
    var brLat = brLatLon.Latitude;
    var brLon = brLatLon.Longitude;

    //Build URL to call the server
    var url = "./LoadFromAzure.ashx?";
    url += "&ulLat=" + ulLat;
    url += "&ulLon=" + ulLon;
    url += "&brLat=" + brLat;
    url += "&brLon=" + brLon;

    //Get the appropriate XMLHTTP object for the browser
    var xmlhttp = GetXmlHttp();

    //if we have a valid XMLHTTP object
    if (xmlhttp) {"GET", url, true); // varAsynx = true

        //set the callback
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4) //4 is a success
                //server code creates JavaScript "on the fly" for us to
                var result = xmlhttp.responseText
                //execute using eval()

What is left to do is the web handler that connects to my class and executes the query. So let’s go ahead and create a new generic web handler. In the ProcessRequest-function we fetch the URL-parameters from our AJAX-call. These parameters represent the coordinates of the bounding box of our map view. Then we call our class to query the Windows Azure table with these parameters and build our JavaScript that we return to the AJAX-call.

'Get the URL-Parameters
Dim ulLat As String = context.Request.Params("ulLat")
Dim brLat As String = context.Request.Params("brLat")
Dim ulLon As String = context.Request.Params("ulLon")
Dim brLon As String = context.Request.Params("brLon")

Dim i As Integer
Dim myPins As String = ""

Dim myTable As New BPUK(ulLat, ulLon, brLat, brLon)
For Each BPUKRecord In myTable.BPUKTable
    myPins = myPins + _
        "var shape" + i.ToString + "=new VEShape(VEShapeType.Pushpin, new VELatLong(" + _
BPUKRecord.Lat.ToString + ", " + BPUKRecord.Lon.ToString + "));" + _ "shape" + i.ToString + ".SetCustomIcon('./IMG/BPUK.bmp');" + _ "shape" + i.ToString + ".SetTitle(" + """" + BPUKRecord.Name + """" + ");" + _ "slBPUK.AddShape(shape" + i.ToString + ");" i = i + 1 Next context.Response.Write(myPins)

That’s it we’re done.


You find the application live on Windows Azure here and the source code here


Bookmark and Share

Technorati Tags: ,

This entry was posted in Virtual Earth. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s