Sterling Database for Windows Phone 7, Silverlight and Bing Maps

A bit of a mouthful of a title you may think, but this post should hopefully show you just that. How to create your first Bing Maps enabled, Windows Phone 7 application using Silverlight, and the lovely little database for Windows Phone 7, called Sterling. Best of all, everything you will need for this project is free!

The application will display a Bing Map on your phone, load some POI data in, and then allow you to search for POIs and pan and zoom the map on that location. Think of it as the prototype for a mobile ‘City Guide’ application.

So lets get started. The first bits you will need:

  • Visual Studio 2010 Express
  • Windows Phone Emulator
  • Silverlight 4
  • .NET Framework 4

You can download all this from the Windows Phone Development Site at: http://create.msdn.com/en-us/home/getting_started

Unless you already own a Windows 7 phone, my guess is that you will do most of the work in the emulator. One important note to make is that the emulator does not work (either at all or not well) on Virtual Machines. It took me a while to figure this out so beware!

Windows Phone 7 does not have a native embedded database so I used this little gem of a database called Sterling written by Jeremy Likness. Download the latest Sterling and extract the zip file to a folder of your choice.

Have a play with the sample project by opening the SterlingPhoneExample.sln solution in Visual Studio under the SterlingDB\src\SterlingSln folder (This will also build the Wintellect.Sterling.WindowsPhone.csproj which we will then reference in our project).

In order for the map control to work correctly, you will also need a Bing Maps Key. Click here for more information.

We are now ready to start our project.

  1. Launch Visual Studio 2010 Express for Windows Phone from the Windows Start menu.

  2. Create a new project by clicking the File | New Project menu command.

  3. The New Project window will be displayed. Select the Windows Phone Application template. Name your project PoiFinderSample.image

  4. Click OK. A new project will be created and MainPage.xaml will be opened in the Visual Studio designer window.

    image

5. Add a reference to Wintelect.Sterling.WindowsPhone.dll by navigating to \SterlingDB\src\SterlingSln\Wintellect.Sterling.WindowsPhone\Bin\Debug folder.

6. Add a reference to Microsoft.Phone.Controls.Maps.dll, System.Xml.Linq and System.Device.dll which should be available as .NET assemblies.

7. Replace the XAML code with the following:

<phone:PhoneApplicationPage 
    x:Class="PoiFinderSample.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
    xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"
    shell:SystemTray.IsVisible="True" 
    xmlns:my="clr-namespace:Microsoft.Phone.Controls.Maps;
assembly=Microsoft.Phone.Controls.Maps"> <!--LayoutRoot is the root grid where all page content is placed--> <Grid x:Name="LayoutRoot" Background="Transparent"> <Grid.RowDefinitions> <RowDefinition Height="Auto" /> <RowDefinition Height="*" /> </Grid.RowDefinitions> <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28"> <TextBlock x:Name="ApplicationTitle" Text="Bing Map Demo" Style="{StaticResource PhoneTextNormalStyle}" /> <TextBlock x:Name="PageTitle" Text="POI Finder" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}" /> </StackPanel> <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0"> <Grid.RowDefinitions> <RowDefinition Height="150"></RowDefinition> <RowDefinition Height="*"></RowDefinition> </Grid.RowDefinitions> <Grid x:Name="ctls" Grid.Row="0"> <Grid.ColumnDefinitions> <ColumnDefinition></ColumnDefinition> <ColumnDefinition></ColumnDefinition> </Grid.ColumnDefinitions> <TextBox Name="txbPoi" Width="400" HorizontalAlignment="Left" VerticalAlignment="Center"></TextBox> <Button Name="Test" Content="Find" Width="100" Height="90" Grid.Column="1" /> </Grid> <my:Map Name="map1" Width="Auto" Height="Auto" Grid.Row="1"
VerticalAlignment="Stretch" HorizontalAlignment="Stretch"
CredentialsProvider="<put your bing maps key here>" /> </Grid> </Grid> </phone:PhoneApplicationPage>

8. Compile and run the application. You should be presented with the following:

image

It doesn’t do much currently but the idea is that you will enter a POI name in the textbox, and then press the [Find] button to pan and zoom to that POI.

9. The next thing we will need to do is get some sample POIs, and in this example I used an XML which contains the POI definition. I took the data from an SQLServer 2008 table by running the following statement. Feel free to skip this step if you don’t have anything similar  the XML output from the query  is defined in the code behind for this sample:

SELECT TOP 10 [PoiID]

     ,[Name]

      ,[Lat]

      ,[Lon]

  FROM [dbo].[Pois]

  WHERE Lat is not null

  and Lon is not null

  FOR XML PATH(‘POIS’), TYPE,  ROOT(‘ALLPOIS’),ELEMENTS

 
10. We then need to create our POI class. To do this create a new class, called POI.cs and copy/paste this code:
 

using System;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Ink;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

namespace PoiFinderSample

{

    public class Poi

    {

        public int PoiID { get; set; }   
        public string PoiName { get; set; }

        public double PoiLat { get; set; }

        public double PoiLon { get; set; }

    }

}

11. Next, we need to create the required by Sterling database class. Call this class PoiFinderDB:

using System;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Ink;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using Wintellect.Sterling.Database;

namespace PoiFinderSample

{

    public class PoiFinderDB : BaseDatabaseInstance

    {

        public const string POINAME_INDEX = "POINAME";

        public override string Name

        {

            get { return "PoiFinderDatabase"; }

        }

        protected override System.Collections.Generic.List<ITableDefinition> _RegisterTables()

        {

            return new System.Collections.Generic.List<ITableDefinition>

            {

               CreateTableDefinition<Poi, int>(p => p.PoiID).WithIndex<Poi, string, int>(POINAME_INDEX, p => p.PoiName)

            };

        }

    }

}

Note here that I created the database defining my POI class as a table, where the key is the PoiID column and has an index on the PoiName column. For more information on Sterling’s keys, indexes and tables refer to the comprehensive Sterling Users Guide
 
11. We now turn to the code behind to bring this all together.
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using Microsoft.Phone.Controls;

using Wintellect.Sterling;

using Microsoft.Phone.Controls.Maps;

using System.Xml.Linq;

using System.IO;

using System.Device.Location;

namespace PoiFinderSample

{

    public partial class MainPage : PhoneApplicationPage

    {

        private SterlingEngine _engine;

        private ISterlingDatabaseInstance _databaseInstance;

        private List<Poi> _allPois = new List<Poi>();

        public const string poixml = @"<?xml version=’1.0′ encoding=’UTF-8′ ?>

                                    <ALLPOIS>

                                      <POIS>

                                        <PoiID>8</PoiID>

                                        <Name>Azzuro Bar</Name>

                                        <Lat>40.6135782878</Lat>

                                        <Lon>22.9570902507</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>19</PoiID>

                                        <Name>Goodys</Name>

                                        <Lat>40.6288333338</Lat>

                                        <Lon>22.9539250000</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>20</PoiID>

                                        <Name>The Green Bottle</Name>

                                        <Lat>40.5822834885</Lat>

                                        <Lon>22.9569421118</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>21</PoiID>

                                        <Name>Everest</Name>

                                        <Lat>40.5842518311</Lat>

                                        <Lon>22.9514642883</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>22</PoiID>

                                        <Name>St. Paul</Name>

                                        <Lat>40.5860727577</Lat>

                                        <Lon>22.9555716119</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>23</PoiID>

                                        <Name>ABC</Name>

                                        <Lat>40.5785368598</Lat>

                                        <Lon>22.9520733962</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>24</PoiID>

                                        <Name>Zoop</Name>

                                        <Lat>40.5775640617</Lat>

                                        <Lon>22.9520885767</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>25</PoiID>

                                        <Name>Alcohol</Name>

                                        <Lat>40.5784540771</Lat>

                                        <Lon>22.9508931755</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>26</PoiID>

                                        <Name>The Shop</Name>

                                        <Lat>40.6298566672</Lat>

                                        <Lon>22.9505833333</Lon>

                                      </POIS>

                                      <POIS>

                                        <PoiID>27</PoiID>

                                        <Name>Disc Shop</Name>

                                        <Lat>40.5819934899</Lat>

                                        <Lon>22.9478130433</Lon>

                                      </POIS>

                                    </ALLPOIS>";

        // Constructor

        public MainPage()

        {

            InitializeComponent();

            Loaded += new RoutedEventHandler(MainPage_Loaded);

        }

        private void MainPage_Loaded(object sender, RoutedEventArgs e)

        {

            Test.Click += new System.Windows.RoutedEventHandler(Query_Click);

            StartSterling();

            DisplayPoisFromXml(poixml);

        }

        private void StartSterling()

        {

            _engine = new SterlingEngine();

            _engine.Activate();

            _databaseInstance = _engine.SterlingDatabase.RegisterDatabase<PoiFinderDB>();

        }


        private void DisplayPoisFromXml(string xmlPois)

        {

            var xmlrdr = new StringReader(xmlPois);

            var doc = XDocument.Load(xmlrdr);

            // Populate the POIS list

            _allPois = (from pointrec

                           in doc.Element("ALLPOIS").Elements("POIS")

                        select new Poi

                        {

                            PoiID = Convert.ToInt32(pointrec.Element("PoiID").Value),

                            PoiName = pointrec.Element("Name").Value,

                            PoiLat = Convert.ToDouble(pointrec.Element("Lat").Value),

                            PoiLon = Convert.ToDouble(pointrec.Element("Lon").Value)

                        }).ToList();

            //Loop through the list to save the Pois into Sterling

            // and display them in the map

            for (var idx = 0; idx < _allPois.Count – 1; ++idx)

            {

                Poi thisPoi = _allPois[idx];

                var key = _databaseInstance.Save(thisPoi);

                // Create a pushpin

                Pushpin pin1 = new Pushpin();

                //Create POI location

                GeoCoordinate thisLoc = new GeoCoordinate(thisPoi.PoiLat, thisPoi.PoiLon);

                pin1.Location = thisLoc;

                pin1.Content = thisPoi.PoiName;

                //Add pin to the map

                map1.Children.Add(pin1);

            }

            //Flush to storage

            _databaseInstance.Flush();

            //Zoom the map

            GeoCoordinate mapCenter = new GeoCoordinate(40.6293172201, 22.9462402344);

            map1.SetView(mapCenter, 12);

        }

        private void Query_Click(object sender, RoutedEventArgs e)

        {

            if (txbPoi.Text != string.Empty)

            {

                var q1 = _databaseInstance.Query<Poi, string, int>(PoiFinderDB.POINAME_INDEX).Where(p => p.Index == txbPoi.Text).FirstOrDefault();

                if (q1 != null)

                {

                    //Get POI location and zoom the map

                    GeoCoordinate poiloc = new GeoCoordinate(q1.LazyValue.Value.PoiLat, q1.LazyValue.Value.PoiLon);

                    map1.SetView(poiloc, 18);

                }

            }

        }

    }

}

12. You should be all done. Compile and run the project. The map should zoom somewhere around downtown Thessaloniki, Greece displaying all 10 POIs. Now enter the name of any Poi in the text box and press [Find]. The map should zoom to that point (e.g. try ‘Everest’) as shown below. A final note to remember:  queries are case-sensitive so for example ‘everest’ will not return any results.

image

 

You can download the full source code from the Box.Net  widget on the left (PoiFinderSample.rar)

2 thoughts on “Sterling Database for Windows Phone 7, Silverlight and Bing Maps

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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