WP7Contrib: Criterion Factory - calculating a Route

Carrying on my series about the Criterion Factory in the WP7Contrib I thought I'd show the how we do a route search. We support multiple Criterion Factory methods for creating criterion these all depend on how much information you have available.

Calculating a route using Bing Maps REST API can be tricky and confusing there are over 10 optional parameters which you could configure to get a route between 2 locations. As with other sets of method on the Criterion Factory we've attempt to reduce the complexity by providing this abstraction.

Shown below are the methods on the Criterion Factory (for route search). As you can see all of the overloaded methods are rooted to final method which validates the Waypoints parameter. These are used like all other Criterion Factory methods to make creating criterion easier for use with the Bing Maps Wrapper service.

#region CreateRouteSearch

public static IRouteSearchCriterion CreateRouteSearch(IList<WayPoint> wayPoints)
{
    return CreateRouteSearch(wayPoints, ModeOfTravel.Driving, DistanceUnit.Kilometer, null, null, null, RoutePathOutput.Points, null, TimeType.Departure, null, null);
}

public static IRouteSearchCriterion CreateRouteSearch(IList<WayPoint> wayPoints, ModeOfTravel modeOfTravel)
{
    return CreateRouteSearch(wayPoints, modeOfTravel, DistanceUnit.Kilometer, null, null, null, RoutePathOutput.Points, null, TimeType.Departure, null, null);
}

public static IRouteSearchCriterion CreateRouteSearch(IList<WayPoint> wayPoints, ModeOfTravel modeOfTravel, DistanceUnit distanceUnit, Optimize? optimize)
{
    return CreateRouteSearch(wayPoints, modeOfTravel, distanceUnit, optimize, null, null, RoutePathOutput.Points, null, TimeType.Departure, null, null);
}

public static IRouteSearchCriterion CreateRouteSearch(IList<WayPoint> wayPoints, ModeOfTravel modeOfTravel, DistanceUnit distanceUnit, Optimize? optimize, Avoid avoid, int? heading, RoutePathOutput routePathOutput, DateTime? dateTime, TimeType timeType, int? maxSolutions, string pointOfInterest)
{
    if (wayPoints == null || wayPoints.Count < 2)
        throw new ArgumentException("Minimum number of WayPoints is 2.", "wayPoints");

    var criterion = new RouteSearchCriterion
    {
        TravelMode = modeOfTravel,
        DistanceUnit = distanceUnit,
        Optimize = optimize,
        Avoid = avoid ?? new Avoid(),
        Heading = heading,
        PathOutput = routePathOutput,
        DateTime = dateTime,
        TimeType = timeType,
        MaxSolutions = maxSolutions.HasValue ? maxSolutions.GetValueOrDefault() : 1,
        PointOfInterest = pointOfInterest
    };
    criterion.WayPoints.AddRange(wayPoints);
    return criterion;
}

#endregion

These methods allow you to easily create the required criterion for the Bing Maps Wrapper service. Obviously as your requirement gets more detailed you can specify travel types, places to avoid, road types to avoid etc.

The important and probably most complex parameter is the Waypoint list. As the names suggest all this is a set of location the route must contain. I hope its obvious but this list must contain at least 2 items - the start and destination locations for the route, you can't really have a route without these!

A Waypoint is simple the notion of a place of interest. It can be either exact geo-location specified by latitude & longitude, a landmark specified by a place name and\or post code or an address. Specifying a geo-location would be the most accurate but any of them will do.

The code below shows easy it is to get a route between 2 places with the Bing Maps Wrapper service - 8 lines of code including creating the criterion. This code is from an example called 'RouteSearch' in the Spikes directory of the WP7Contrib.

private void HandleCalculateRouteClick(object sender, RoutedEventArgs e)
{
    var waypoints = new List<WayPoint>
                        {
                            new WayPoint {Landmark = new Landmark {AreaName = this.startText.Text}},
                            new WayPoint {Landmark = new Landmark {AreaName = this.finishText.Text}}
                        };

    var criterion = CriterionFactory.CreateRouteSearch(waypoints);
    

    bingMapsService.CalculateARoute(criterion)
        .ObserveOnDispatcher()
        .Subscribe(this.DisplayRoute, this.FailedRoute);
}

This event handler is used for the following UI. This is a very simple UI, allows you to enter 2 locations and click calculate. If successful it displays the high level information about the route and then inserts the complete itinerary into a list box.

The DisplayRoute method is the Rx (reactive extensions) subscriber method, this does the binding of the results to the UI and is shown below:

private void DisplayRoute(RouteSearchResult result)
{
    this.resultStatus.Text = string.Format("Response: {0} - {1}", result.StatusCode, result.StatusDescription);

    if (result.HasSucceeded)
    {
        this.resultDistance.Text = string.Format("Distance: {0} {1}", result.TravelDistance, result.DistanceUnit);
        this.resultDuration.Text = string.Format("Duration: {0} {1}", result.TravelDuration, result.DurationUnit);

        this.resultRouteLegs.Text = string.Format("Iternary Count: {0}", result.RouteLegs[0].ItineraryItems.Count);

        this.routeLegs.ItemsSource = result.RouteLegs;
    }
}

I'm not going to go any further with examples of how to use the parameters you can specify with the Criterion Factory - hopefully they should be self explanatory.

What I am going to show is a real world example of where Rich & I are using the route search. This app is currently awaiting certification and therefore we don't want to release the name at the moment, hence the big black marks across the screen shots. The app uses the route search to find a route between 2 exact geo-locations. This is then overlaid as a set of pins and route line on the Bing Maps control. What is interesting with the results is the difference in total distance for each route - both routes start and end at the same locations but the difference is mode of travel. The left screenshot is by car and the right by foot.

As you can see from the code snippet below, we use the Criterion Factory and Bing Maps Wrapper service in exactly the same manner as the demo. Except this time we are specifying the Waypoints as exact geo-locations, the mode of travel and the distance units.

private void CalculateRoute(GeoCoordinate geoCoordinate)
{
    var waypoints = new List<WayPoint>
            {
                new WayPoint {Point = geoCoordinate},
                new WayPoint {Point = this.CurrentlySelectedProperty.GeoCoordinate},
            };

    var distanceUnit = this.settings.CurrentlySelectedCountry.IsMetric ? DistanceUnit.Kilometer : DistanceUnit.Mile;
    var modeOfTravel = this.IsCarRoute ? ModeOfTravel.Driving : ModeOfTravel.Walking;

    var criterion = CriterionFactory.CreateRouteSearch(waypoints, modeOfTravel, distanceUnit, Optimize.Time);

    this.bingMapService.CalculateARoute(criterion)
        .ObserveOnDispatcher()
        .Subscribe(this.ProcessResponse,
                   exception => this.FailedBingRouteSearch(exception, geoCoordinate),
                   this.CompletedBingRouteSearch);
}

If you want to use the demo you can, it can found in the WP7Contrib Spikes in the 'BingMaps_CriterionFactory' directory. You'll have to have a Bing AppID to use the service & demo, you can register here.

Read More

Geo-location on WP7 - don't trust the first value returned

Rich & I have been working on app which makes heavy use of geo-location information provided by the GeoCoordinateWatcher class on the WP7 platform. As the documentation on MSDN states this class exposes the Windows Phone location services. The GeoCoordinateWatcher class implements the INotifyPropertyChanged interface and all the events generated will be fired on the UI thread allowing you to easily bind the class to the UI, but as we already knew this is not always a good idea - for a detailed reason why this can be a bad idea check out this post by Jaime Rodriguez.

What we also discovered is that you can't always trust the first position event generated by this class.

We have a requirement to get the current location when a user clicks a button, the user could do this at any time when using the application - they could click once, a dozen times or not at all. Every time they click the button we need to get an accurate single value. The accuracy of the value depend on both the actual value and the age of the value. Every value generated by the GeoCoordinateWatcher class is published by via the PositionChanged event and this is made up of 2 components - the location value and the timestamp when the location value was generated, this is exposed as the GeoPosition<T> class.

The PositionChanged event is defined as follows. I've included the StatusChanged event to show all the event signatures for the class:

public class GeoCoordinateWatcher : IDisposable, INotifyPropertyChanged, IGeoPositionWatcher<GeoCoordinate>

{
    public event EventHandler<GeoPositionChangedEventArgs<GeoCoordinate>> PositionChanged;
    public event EventHandler<GeoPositionStatusChangedEventArgs> StatusChanged;

    ...
}

The point of interest is the type used with the event handler. The GeoPositionChangedEventArgs class has the following definition.The Position property exposing the GeoPosition<T> value, this has the time stamp value.

public class GeoPositionChangedEventArgs<T> : EventArgs
{
    public GeoPositionChangedEventArgs(GeoPosition<T> position);
    public GeoPosition<T> Position { get; }
}

public class GeoPosition<T>
{

    public GeoPosition();
    public GeoPosition(DateTimeOffset timestamp, T position);
    public T Location { get; set; }
    public DateTimeOffset Timestamp { get; set; }
}

We use the following code to return a single geo-location value when the button is clicked. It uses Rx (reactive extensions) to wrap up the call to the GeoCoordinateWatcher class. We don't just use the 'FromEventPattern' Rx method because we don't want the instance of the GeoCoordinateWatcher running riot and generating a gazillion location results and killing the UI thread!

The highlighted areas show how I am using the time offset to determine the validity of the location published by the GeoCoordinateWatcher class. In this demo instance I am saying - 'if the position timestamp is greater than 20 seconds ago then ignore the value...'. When valid the new location is published using the Rx method 'OnNext' on the returned Subject<T>. We also call the Rx 'OnCompleted' method to make the 'Finally' method execute and shut down the watcher by calling 'watcher.Stop()'.



Note: We also do the 'Start' of the GeoCoordinateWatcher  on a background thread to prevent blocking of the current thread - in this case the UI thread.

This class is the used in the page class as follows:

When run for the first time (on the emulator) I get the following in the output window of visual studio:

When this is run for subsequent times the debug statements in the output window of visual studio depend on how long the interval between clicking the button.

The following shows 2 values because the time interval between clicks is greater than 20 seconds. The first value generated by the instance of the GeoCoordinateWatcher class is ignored.

To show this is not an attribute of the currently executing application session, I've added an explicit finalise to the main page as well as a debug statement in the constructor. This shows that once the location services are initialised on the phone and the current location is requested then the last value is always stored by the device and published as the first event when the GeoCoordinateWatcher is started.

The above screen shot shows 3 distinct activations of the application after tomb-stoning the device twice.

As you can see the first run shows the last geo-location generated by the device was 4 hours prior, then we tomb-stoned the app for 30 seconds, then re-activated the app with a back press. Again because the previous geo-location was generated greater than 20 seconds ago it is ignored. Finally the last activation after tomb stoning was not greater than 20 seconds so it was not ignored.

This code has been incorporated into the WP7Contrib as the LocationService. It has method for getting the geo-location using time & distance thresholds as well. It also has provides the GeoCoordinateWatcher  Status event as an observable method.

The code is available in this change-set or will be part of the 1.4 release due at the end of the month.

Read More

How many pins can Bing Maps handle in a WP7 app - part 1

part2 - http://awkwardcoder.blogspot.com/2011/10/how-many-pins-can-bing-maps-handle-in.html
part3 - http://awkwardcoder.blogspot.com/2011/11/how-many-pins-can-bing-maps-handle-in.html

Rich & I've been working on a WP7 app recently which annotates the Bing Maps control with push pins. These pins represent a point of interest - a reported crime. We've been using the UK crime stats which are provided free of charge, all you have to do is register for an account here. They expose the data using a RESTful service which exposes the data as JSON over HTTP GET requests. It was relatively easy to build a service layer to consume and map this data into a set of model classes for binding to a UI. The RESTful service has multiple endpoints, some are designed to build complex queries spanning multiple HTTP requests and others are single requests based around your current location. The later is what I'll be using for this post.

The API I'm using is the 'street-level crime' endpoint, detailed here. It provides all crimes that have occurred in a 1 mile radius of a geo-location and does not guarantee accuracy of crime locations. An example URL is shown below:

http://policeapi2.rkh.co.uk/api/crimes-street/all-crime?lat=51.5565555035054&lng=-0.0768184661865234

The data returned is truly dynamic - I have no idea how many results will be returned for a geo-location, the amount will vary over time as well as geo-location.

My first task was to get this working and returning data, I wasn't binding the data to the UI at this stage only making sure data was being returned. Shown below is the high level code I used. 

The code snippet is the constructor for the page class and an event handler for the map control. The highlighted area shows how I get the data, when ever the view has finished changing the event handler is called and we then request the crime data. You can see from the second highlighted area the service request, it uses Rx (reactive extensions) to handle the asynchronous nature of making a request over the internet. The result count (the crime count) is written out to a log. The log happens to write out to the debug output and is shown below along with the app.

As you can see the crime count for the 1 mile radius around the geo-location (51.556555, -0.0768184) is 1695!

Baseline memory

The UI for this version consists of a Bing Maps control with a couple of text boxes at the bottom showing the peak and current memory. These are updated regularly (< 250 ms) using a DispatcherTimer class and querying the device extended properties for current and peak memory usage.

The screenshot shown on the left has values of around 21.12 Mb - the baseline the map control needs to show a map centred on the above location without panning\zooming the map control.

The next stage was to overlay the push pins and see what happens. I started with overlaying all the returned crime data in one go - iterating over the returned data and adding to the MapItemsControl class. This class was defined in the XAML as shown below, an item template was then applied to the class to get the pin to render. We use a converter to parse the crime category and give it a different colour.

All 1695 pins

You'll notice straight away problems with this approach.

Firstly I can't use the map whilst its adding the pins, the app freezes and does not response to any input. IT takes over 20 seconds from receiving the response from the RESTful service to the pins being shown and the app being usable again. This is a symptom of trying to show to much information to the user and all it will do is confuse and give the perception of a bad user experience. This is in part due to the location where the search was done and the resolution of the map control (zoom = 16).

Shown below is the debug output and the highlighted lines show the time taken to add the pins to the map control - over 20 seconds.

Secondly the memory usage has jumped a whopping 53 Mb! Its well on its way to the 90 Mb limit. Imagine if this page was part of a large app, I can easily see this causing the app to surpass the 90 Mb limit. 

This is due to the fact we are only observing a fraction of the pins added to the map control - the view port is only showing a subset of the available data. I could see them all by zooming out but the following graphics represents the problem perfectly - the pins are being added to the map control even though they are not currently being displayed.

Thirdly every time you scroll the map control it will make another request for data. This in its self is not the problem, its the frequency of the firing of the ViewChangeEnd event. Every time I lift my finger off the screen after scrolling the event would fire, so if I want to scroll more than once the event would fire multiple times. The debug output shows multiple requests being made simultaneously and this is not what we want:

A side effect of multiple requests to the RESTful service is the memory usage for the device rockets! Its easy to get the value above 200 Mb and eventually you'll get an OutOfMemory exception.

And Fourthly when adding pins to the MapItemsControl class it only checks if a pins exists using reference equality, therefore if you have to separate instances for the same pin then it will be added twice to the map control. I suspect this is causing the majority of the memory allocation shown above.

Tackling the third & fourth problems first I should be able to reduce the memory usage and provide good code for future versions of the code base. 

The first task was to deal with multiple requests happening at the same time. This is done by cancelling any currently executing request and then starting the new request. This was easy to do because we are using Rx. All we have to do is dispose of the current subscriber ( 'currentSubscriber') and initiate a new request.

Shown below is the modified code, it has the null check and explicit dispose of the 'currentSubscriber' variable before the assigment of the new subscriber.

private void HandleViewChangeEnd(object sender, MapEventArgs mapEventArgs)
{
    var criterion = new StreetLevelCrimeCriterion { Latitude = this.map.Center.Latitude, Longitude = this.map.Center.Longitude };
            
    this.log.Write(string.Format("Started - ({0}, {1})", criterion.Latitude, criterion.Longitude));

    if (this.crimeSubscriber != null)
    {
        this.crimeSubscriber.Dispose();
    }

    this.crimeSubscriber = this.crimeService.SearchCrimeRelatedStreetLevelCrime(criterion)
        .ObserveOnDispatcher()
        .Subscribe(result =>
                        {
                            this.log.Write(string.Format("Crime count = {0}, ({1}, {2})", result.Crimes.Count, criterion.Latitude, criterion.Longitude));

                            foreach (var crime in result.Crimes)
                            {
                                this.MapPins.Items.Add(crime);
                            }
                        },
                    exception => this.log.Write(string.Format("Exception, message - '{0}'", exception.Message)),
                    () => this.log.Write(string.Format("Completed - ({0}, {1})", criterion.Latitude, criterion.Longitude)));
}

Now when the code executes I see something similar to below. It shows 5 requests being made simultaneously but only 1 set of results being processed and the results being process are the result from the last request. You will also notice the highlighted screenshot of the device emulator - the memory consumption is considerably less then 200 Mb, still not perfect but better.

Next to address is multiple pins for the same location. As I said it appears the MapItemsControl class uses reference equality to check for multiple pins and since there are multiple instances for the same location all I need to do is filter any existing before adding. This is done with a LINQ query:

foreach (var crime in result.Crimes.Where(crime => !this.MapPins.Items.Cast<StreetLevelCrime>().Any(c => c.Id == crime.Id)))
{
    this.MapPins.Items.Add(crime);
}

this.count.Text = "Pin count - " + this.MapPins.Items.Count;

The screenshot below shows 3 requests being made to the RESTful service. The aggregated pin count would be 5172 (1695 + 1809 + 1668) without the above code but as you can see from the highlighted emulator device the actual pin count is 2245.

The memory usage is still not acceptable, we are not seeing memory usage around the 200 Mb any more but 90Mb is still too much.

I can now address the first and second problems - the time taken to add pins and the amount of memory consumed. To tackle these issues we are going to have to reduce the number of pins on the map control.

The simplest way to do this is to only add the pins required to be shown to the collection of the MapItemsControl  class. This is achieved by taking the current viewable bounding rectangle of the map control and only adding pins which fall inside this rectangle. This does require the removing of any existing pins added to the collection which do not fall inside the viewable bounding rectangle. The bounding rectangle is expressed as a LocationRect class which is a set of geo-locations - north, west, east, south. All I have to do is calculate if a pins fall inside this bounding rectangle. To do this I have updated the LINQ query:

var rectangle = this.map.BoundingRectangle;

this.MapPins.Items.Clear();
foreach (var crime in result.Crimes.Where(crime => (crime.Location.Latitude <= rectangle.North) &&
                                        (crime.Location.Latitude >= rectangle.South) &&
                                        (crime.Location.Longitude >= rectangle.West) &&
                                        (crime.Location.Longitude <= rectangle.East)))
{
    this.MapPins.Items.Add(crime);
}
                                   
this.count.Text = "Pin count - " + this.MapPins.Items.Count;

You can now see from the screenshot below the pin count has greatly reduced to between 110 - 240 and more importantly the memory usage is at a more acceptable level. In fact the peak memory usage is now only 61.86 Mb.

This is now approaching a usable solution, but there a couple issues still affecting the UI. Firstly the updating of the map is not very fluid, in fact its rather jumpy - when the new pins are added they suddenly appear on the map without warning and there isn't any feedback to user about what is going on.

To address this I have added a progress bar to the UI. This is controlled by the ViewChangeEnd event handler - the progress bar is started before the request to the RESTful service and stopped when either request completes successfully or fails.

And secondly we 'trickle' the data to the map. We use an implementation of the  ITrickleToCollection<T> interface in the WP7Contrib. The implementation trickles data from a source collection to a destination collection based on the interval of a DispatcherTime, this is to get round the performance penalty of adding a large number of items to UI bound collection - stop it hogging the UI dispatcher thread!

The trickler interface is defined as follows:

public interface ITrickleToCollection<T>
{
    Queue<T> Source { get; }
    bool Pending { get; }
    bool IsTrickling { get; }

    void Start(int trickleDelay, IEnumerable<T> sourceCollection, IList<T> destinationCollection);
    void Stop();

    void Suspend();
    void Resume();
}

The update ViewChangeEnd event handler now looks like this, I could refactor this more into separate method but that is not the purpose of this post.

private void HandleViewChangeEnd(object sender, MapEventArgs mapEventArgs)
{
    if (this.crimeSubscriber != null)
    {
        this.crimeSubscriber.Dispose();
    }

    var criterion = new StreetLevelCrimeCriterion { Latitude = this.map.Center.Latitude, Longitude = this.map.Center.Longitude };

    // Start progress bar at top of the view...
    this.mapBusy.IsIndeterminate = true;

    // Stop any trickling of pins...
    this.trickler.Stop();
            
    this.crimeSubscriber = this.crimeService.SearchCrimeRelatedStreetLevelCrime(criterion)
        .ObserveOnDispatcher()
        .Subscribe(result =>
                        {
                            var rectangle = this.map.BoundingRectangle;

                            // All the pins to add to the map control...
                            var allPinsToAdd = result.Crimes.Where(crime => (crime.Location.Latitude <= rectangle.North) &&
                                                                (crime.Location.Latitude >= rectangle.South) &&
                                                                (crime.Location.Longitude >= rectangle.West) &&
                                                                (crime.Location.Longitude <= rectangle.East)).Distinct().ToList();

                            // All the pins already added to the map control we want to keep...
                            var alreadyAdded = allPinsToAdd.Intersect(this.mapPins.Items.Cast<StreetLevelCrime>()).ToList();

                            // The new pins to be added to the mapp control...
                            var pinsToAdd = allPinsToAdd.Except(alreadyAdded).ToList();
                                   
                            // The existing pins to be removed which aren't visible...
                            var pinsToRemove = this.mapPins.Items.Cast<StreetLevelCrime>().Except(alreadyAdded).ToList();

                            // Remove the pins...
                            pinsToRemove.ForEach(p => this.mapPins.Items.Remove(p));

                            // Trickle the pins to map control (10 ms delay)...
                            this.trickler.Start(10, pinsToAdd.Cast<object>(), this.mapPins.Items);
                        },
                    exception =>
                        {
                            // Stop the progress bar at top of the view...
                            this.mapBusy.IsIndeterminate = false;
                        },
                    () => {});
}

Shown below are some static screenshots of the above code executing, you can see the application starting, adding pins (x2) and finally when it has completed, you'l notice the progress bar is no longer visible. I've also highlighted the memory usage, it appears now we are trickling data to the map control we aren't see as high peak memory usage as well.

There are a couple of scenarios I haven't covered off - they relate to the ability of the user to understand and interpret the data when there are so many pins. This can occur when there's a lot of pins for a small area (like above) or when the user zooms out to such a level there is also to many pins to see the map. I'm not going to cover this here. Simply I would limit the number of pins that can be added to the map control. This could be via some business logic or via a simple max count value.

Back to the original question - How many pins can Bing Maps handle in a WP7 app?

I think the answer depends ;)  but in general I'm seeing a map control with a zoom level of 16 can easily handle 100 pins and at this level the responsiveness of the control is not the problem but the density of the pins. You can if you want add over a 1000 pins to a map and it will still be lower than the 90 Mb limit but this doesn't really give the rest of your app much room to manoeuvre with respect to memory.

 I've put the code up on SkyDrive, you'll need a username & password for the UK crime stats service to run the code.

Read More

Attaching multiple sqlite databases in WP7

Following on from my previous post Rich & I are developing an app which has 2 versions - one has only a single sqlite database but the other could use multiple sqlite databases to store the data - in effect the data is partitioned (sharded) alphabetically for the second version.

Our existing code used for the single database version uses a Simple ORM to do our data access. The primary reason being the code is 2 lines and importantly the performance cost of using the Simple ORM is not an issue at the moment. If it ever becomes an issue we'll switch it out.

What follows is how we approached attaching multiple databases together. Sqlite allows multiple databases to be used under the same connection. The databases don't have to share the same schema at all, all that is required to use the attached databases is to use the syntax database-name.table-name. More info can be found at the sqlite.org website here.

The first attempt was to do this from Vici CoolStorage, I didn't expect this to work because it only has the notion of setting the database you are about to access, I couldn't find anyway to attach a second database. The code is shown below and the runtime exception afterwards, as I said this was hopeful and failed.

    private void databaseAttach_Click(object sender, RoutedEventArgs e)
    {
        CSConfig.SetDB("database1.sql", SqliteOption.None);
        CSDatabase.ExecuteNonQuery("ATTACH 'database2.sql' AS db2;");

        var results = CSDatabase.RunQuery<search_db_column_names>("SELECT db2.search_db_column_names.pk FROM db2.search_db_column_names");

        Debug.WriteLine(results.ToString());
     }

Blew up with the following exception:

The second attempt was to use C# Sqlite For WP7 of codeplex. Vici CoolStorage is written on top of this so my thought were it would be less of an abstraction and therefore more likely to succeed. This time the code has more traditional DAL feel about - the use of connection, command & reader objects.

Success this worked!

The code is shown below and it's pretty much the same as for a single database call apart from the execute no  query to attach the second database and the modified SQL statement.

As stated earlier the statement is now explicit about which columns and tables are being referenced:

"SELECT * FROM main.PostCodes UNION SELECT * FROM db2.PostCodes"

In this case all I'm doing is the union between 2 tables as they have the same structure and none over lapping data.

        private IList<PostCode> ExecuteAllDatabase1()
        {
            var postcodes = new List<PostCode>();
            using (var conn = new SqliteConnection("Version=3,uri=file:" + Database1))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "ATTACH '" + Database2 + "' AS db2;";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT * FROM main.PostCodes UNION SELECT * FROM db2.PostCodes";

                    using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
                    {
                        while (reader.Read())
                        {
                            var postCode = new PostCode
                            {
                                Value = reader.GetString(1),
                                District = reader.GetString(2),
                                Latitude = reader.GetDouble(3),
                                Longitude = reader.GetDouble(4),
                            };

                            postcodes.Add(postCode);
                        }
                    }

                    conn.Close();
                }
            }
            
            return postcodes;
        }

Okay got it working but what about if I got as close to the metal as possible - C# Sqlite For WP7 is written using the csharp-sqlite project up on google code. Shown below is the same functionality written using the C style sqlite.org API.

        private IList<PostCode> ExecuteAllDatabase2()
        {
            var postcodes = new List<PostCode>();
            var database1 = new Sqlite3.sqlite3();

            if (Sqlite3.sqlite3_open(Database1, ref database1) == Sqlite3.SQLITE_OK)
            {
                var attachSql = string.Format("ATTACH DATABASE '{0}' AS db2", Database2);
                var errorMessage = string.Empty;

                if (Sqlite3.sqlite3_exec(database1, attachSql, null, null, ref errorMessage) == Sqlite3.SQLITE_OK)
                {
                    var selectStmt = new Sqlite3.Vdbe();
                    var selectSql = @"SELECT * FROM main.PostCodes UNION SELECT * FROM db2.PostCodes";
                    var stringTail = string.Empty;

                    if (Sqlite3.sqlite3_prepare_v2(database1, selectSql, -1, ref selectStmt, ref stringTail) == Sqlite3.SQLITE_OK)
                    {
                        var n = 0;
                        while (Sqlite3.sqlite3_step(selectStmt) == Sqlite3.SQLITE_ROW)
                        {
                            var col1 = Sqlite3.sqlite3_column_int(selectStmt, 0);
                            var postCode = Sqlite3.sqlite3_column_text(selectStmt, 1);
                            var district = Sqlite3.sqlite3_column_text(selectStmt, 2);
                            var latitude = Sqlite3.sqlite3_column_double(selectStmt, 3);
                            var longitude = Sqlite3.sqlite3_column_double(selectStmt, 4);

                            postcodes.Add(new PostCode { District = district, Value = postCode, Latitude = latitude, Longitude = longitude});
                        }
                    }
                }
                else {}
            }
            else {}

            Sqlite3.sqlite3_close(database1);

            return postcodes;
        }

The only thing left to do was compare the performance of the 2 working solutions. Interestingly it appears the C# Sqlite For WP7 gives better performance than the sqlite API.

C# Sqlite For WP7 average = 85 ms
sqlite API average = 95 ms

I suggest this is probably due to the fact I haven't tweaked the sqlite API code to be as per-formant as possible. Again I have ignored the first result for both implementations.

Read More