Despite being a rather “dry” topic, there are times when you simply need to change the schema of a database and migrate that data. In my current scenario the “core” schema relationships are reasonable, but the naming conventions (or lack there of) make it very difficult to work with. Also, the previous DBA’s fetish for GUIDs as primary keys simply must be remedied.

Thus I’ve been spending some time migrating data. Since I’m working with SQL Server, I have a couple of options.

SQL Server Business Intelligence Developer Studio

Aside from the terrible name, this Visual Studio add-in/package allows you to to all sorts of data migration & manipulation in a graphical environment. Think of it as ArcGIS Model Builder for your tabular database (along the same lines, please please please don’t use Model Builder to migrate tabular data!)

There are two levels – the “Control Flow”, which is the order in which the operations occur, and then the “Data Flow” which are the details. This next image shows the “Control Flow” for migrating the Projects section of the schema.

While some of these boxed represent execution of SQL code, most of them represent a data flow for migrating a particular table. This is one of those data flows…

So this is relatively straight forward – lots of dragging and dropping, repetitive picking of values, and mapping of columns between tables.

Once you have a flow setup, you can execute it – and this is where things get a little more dicey.

Since this toolset is designed to do almost anything, it has some issues when doing everything. Take unicode conversions. As part of the migration, we are changing all the text fields to unicode equivalents – basically CHAR(3) becomes NCHAR(3). But since SSBIS does all this fun stuff in managed code, it sees these two types as being cosmically different, thus we have to add in a extra step to convert to unicode. However, at the database level, you can just directly insert a CHAR into a NCHAR without an explicit casting. Anyhow – it’s just an extra step that seems like a waste of time.

Another issue is that the error messaging is feeble at best. When something fails, it will let you know that it failed, and a whole bunch about the internal state of things, but almost nothing about WHY it failed – as in what value in the data caused the failure. Interesting, but not useful.

In addition, it seems that the packages fail randomly. Some days it works flawlessly, the next day it fails. The source data has not changed, and we wipe out the target data, so your guess is as good as mine as to why this occurs. Sunspots? Air Pressure? Who knows, but it does not inspire a whole lot of confidence.

The final issue I had was that while Brian Noyle and I both have the same version of this tool on our systems, we can not open the packages on each other’s systems. We are not sure why, but it’s problematic. I will say that we are using the SQL 2005 version of this tool, and apparently there is a SQL2008 / Visual Studio 2008 version is also available, so that may be better.

So I decided to check out my other option…

Raw SQL

Yep, hand coded(ish) raw SQL. I was concerned that this would be a giant time-suck, and be really painful, but it was not too bad. Will say that for those who don’t speak fluent SQL this is going to be much harder than SSBIS. Interestingly it turned out to be about the same amount of effort, but with some definite up-sides.

First, much of what I was doing was “templatable” – at a high-level, I’m really just creating a new table from an old one, changing the field names to something readable, doing the CHAR –> NCHAR change, and more or less copying the data over. And doing the same for associated lookup tables. This is my empty template…

-------------------------------------------------------
-- IMPORT-<TABLENAME>.sql
-------------------------------------------------------

use <TARGETDB>

-- ==================================
-- Load Lookup Tables
-- ==================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].<LUTNAME>') AND type in (N'U'))
    drop table <LUTNAME>
select lkp_key as <LUTNAME>_Code, lkp_Value as <LUTNAME>
into <LUTNAME>
from <OLDLUTNAME>
alter table <LUTNAME> add CONSTRAINT [PK_<LUTNAME>] PRIMARY KEY CLUSTERED (<LUTNAME>_Code ASC )

-- ==================================
-- Load Data Table
-- ==================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].<TABLENAME>') AND type in (N'U'))
    drop table <TABLENAME>
CREATE TABLE <TABLENAME>(
    ... FIELDS ...

PRIMARY KEY CLUSTERED
(
    [TABLENAME_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]

I recreate the constraints in a separate master-blaster script.

The upsides with using SQL is that it’s wicked fast to execute, you have complete control, and when something fails, it gives you a very direct message… and it looks really bad ass when you’ve got three instances of SQL Management Studio open with all sorts of data/scripts up

(click for full size)

Have fun with your data migrations!

I’ve been doing some research comparing the use of ASP.NET MVC + Dojo and ADO.NET Data Services (aka Astoria) + Silverlight for Rich Internet Applications, and thought I’d share my findings and conclusions.

Context

Before diving in, I should mention that the context for this comparison is line-of-business, forms-over-data applications. Mapping is an element of the application, but we are not talking about map-centric applications (I may do another post on that later). The application that I’m specifically looking at has more than 40 screens of forms, backing into a data model with more than 100 tables.

ASP.NET MVC + Dojo

The client side library could just as easily be ExtJS, or any other javascript based UI toolset. That said, here’s a screen cap of the sort of site I’m talking about.

The flow is Search in the upper left, results listed below that, and details on the right. The details for a Project are broken out onto 6 tabs, including a map.

In this case, we are using the ESRI Javascript API – which is convenient because we are already loading Dojo, so adding this map into the mix does not require an additional javascript framework.

Service Model Design

In order to drive this UI, we have a lot of moving parts behind the scenes.

In front of the database we have a data access layer, which is generated using SubSonic. We front that with an interfaced based Repository, which exposes a set of domain objects. These domain objects are consumed by ASP.NET MVC Controllers, which in-turn create either JSON responses, Views, or Partial Views depending on the scenario. Once in the browser, Dojo takes over orchestration. Where convenient, we just send JSON data to the UI, but in other cases it’s simply more efficient to create the HTML on the server, and ship that across as a partial view which is then parsed by Dojo after being inserted into the DOM. The following image shows how all these come together.

(click to view larger version)

The key thing here is that the vast majority of this system is hand coded. This allows complete control, but it takes a lot of work – even once we’ve got good patterns setup. Another downside is that even with the cross browser support built into Dojo, there are still small oddities which take a lot of time to track down.

We are more than half way through this application, so I doubt we’d change at this point, but since we have everything in place, it seemed like a good candidate to do a comparison to…

ADO.NET Data Services + Silverlight

The quick story on ADO.NET Data Services (besides having a really lame name ) is that it can very easily provide a REST layer on top of your data access layer. This is done via a WCF service, and as long as your data access layer supports IQueryable<> and IUpdateable<> you can configure the service to use your DAL. MSDN Magazine has a good article that covers the basics of ADO.NET Data Services.

While we have a data access layer in the application, it’s not setup for IQueryable or IUpdatable. So the other option is to use the Entity Framework. While the Entity Framework has taken a lot of grief, it seemed like a reasonable option to try since it’s baked in, relatively easy to configure, and purports to do what I need.

Since this is pretty new stuff, I’ll go through the basic steps here. For more detailed information, check out the Data Driven Services with Silverlight 2 book.

So, in Visual Studio, I simply add a new Entity Data Model to my ASP.NET MVC application (which I’m using to host the Silverlight app)

And then connected it to my database, and selected some tables I wanted to work with. This created the Model as shown below…

After that, I added an ADO.NET Data Service to the project…

Bound it to the Entity Framework model (ProjectEntities) and set some data access rules in the service.

Build, and shazam! My model is exposed as REST resources. By default queries return ATOM format, but by changing the accept header on the request, you can get JSON back.

On the Silverlight side of things, there is a little more work to do, but essentially we include the Data Services client side library, make a reference to the service which creates the proxies, extend the proxy classes to support INotifyPropertyChanged, and then setup bi-directional data binding. (See the MSDN article referenced above for more details)

Which makes the Service Model looks like this…

This is a much simpler model, and much of the dirty work can be done via configuration vs. coding.

Conclusions

In my mind, the ability to rapidly configure a database for access via REST, and at the same time setup a browser agnostic, rich user interface with data binding sets the Silverlight + ADO.NET Data Services far ahead of the more traditional ASP.NET MVC + Dojo model, and I’ve your team already has .NET chops, this is just an extension of the existing skills. Additionally, I think that Silverlight 3 will up the ante again, with more controls, and even better tooling to streamline this development process.

That said, for the particular application was looking at, we are sticking with ASP.NET MVC + Dojo for two reasons: 1) the database “sub optimal” so we’d have a LOT of work making it play nice with the Entity Framework, and 2) we are about half done, and the client wants a consistent look/feel across the entire application. Switching to Silverlight would require re-doing what we already have, and while I think development would be faster, it would be able to make up the difference. We will seriously look at Silverlight + ADO.NET Data Services for new line-of-business projects, including some upcoming product development.

I’ve been creating some proof of concept applications in Silverlight, and one thing I needed to do was apply a unique value renderer using PictureMarkerSymbols to some points on a FeatureLayer.

Initially I looked in the Samples and Reference sections of the ESRI Silverlight Resource Center. However, I was some what stymied. The Samples had a simple example of loading a feature layer, but it used the built-in clustering. I could find all sorts of details in the API Reference, but that does not help sort out how to setup a renderer in XAML.

Finally I stumbled into the Concepts section. I had figured that this section would be some very basic stuff – but there are a number of very useful code examples in there – specifically an example creating renderers in XAML. The example is for polygons, and is pretty comprehensive, but I thought I’d share a code sample for a unique value renderer using a PictureMarkerSymbol, as this is very much akin to slapping push-pins into other map canvasses like Google Maps or Virtual Earth.

The idea is pretty simple – in the Grid.Resources section of your XAML, define a bunch of PictureMarkerSymbols with names, then create a unique value renderer, and add in unique value items, which tie the attribute value to the specific marker symbol using data binding to the symbols we just defined. Follow that? It makes more sense in XAML…

<Grid.Resources>
    <esriSymbols:PictureMarkerSymbol x:Name="HelicopterSymbol" Height="40" Width="40" Source="Assets/images/i_helicopter.png" />
    <esriSymbols:PictureMarkerSymbol x:Name="DozerSymbol" Height="40" Width="40"  Source="Assets/images/i_truck.png" />
    <esriSymbols:PictureMarkerSymbol x:Name="PumperSymbol"  Height="40" Width="40" Source="Assets/images/i_firetruck.png"/>
    <esriSymbols:PictureMarkerSymbol x:Name="JumperSymbol"  Height="40" Width="40" Source="Assets/images/i_fireman.png"/>
    <esri:UniqueValueRenderer x:Name="FireResourcesRenderer" Attribute="Type" >
        <esri:UniqueValueRenderer.Infos>
            <esri:UniqueValueInfo Value="HELO" Symbol="{StaticResource HelicopterSymbol}" />
            <esri:UniqueValueInfo Value="DOZER" Symbol="{StaticResource DozerSymbol}" />
            <esri:UniqueValueInfo Value="PUMPER" Symbol="{StaticResource PumperSymbol}" />
            <esri:UniqueValueInfo Value="JUMPER" Symbol="{StaticResource JumperSymbol}" />
        </esri:UniqueValueRenderer.Infos>
    </esri:UniqueValueRenderer>
</Grid.Resources>

Then in the FeatureLayer definition we bind the renderer property to the renderer we defined in the Grid.Resources.

<esri:FeatureLayer
       ID=”FireResources”
       Url=”http://yourserver/ArcGIS/rest/services/yourmapeservice/MapServer/0″
       Where=”1=1″
       ClusterFeatures=”False”                     
       Renderer=”{StaticResource FireResourcesRenderer}”>
    <esri:FeatureLayer.OutFields>
        <sys:String>Type</sys:String>                               
    </esri:FeatureLayer.OutFields>
</esri:FeatureLayer>

And that’s it!

I’ve been doing some work with ADO.NET Data Services (formerly known by them much cooler name “Astoria”). Since this is a totally new ball of wax for me, I’ve been reading Data Driven Services with Silverlight 2 for guidance.

All was going pretty smoothly, and I had the sample app working… but just in Cassini. Under IIS7, updates to the database would fail.

Using Fiddler I could see the following…

<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>
<error xmlns=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”>
  <code></code>
  <message xml:lang=”en-US”>Update not allowed. Must be an administrator.</message>
</error>

In order to get this far, I had to meddle with some IIS settings, and so I assumed that this was somehow related.

Being a “Google-Assisted” developer, I dropped “Update not allowed. Must be an administrator” into Google.

And what does it come back with …

One hit. And it’s from the book that I’m “reading” (apparently I’m really just skimming!). Clicking the link and shazam… page 306…

Turns out that the message I’m seeing is coming from the sample code, not IIS (which makes sense in hind-sight).

The service was checking the identity making the update request to see if it was in the local administrators group. Since Cassini runs as the identity who launched the process (”dbouwman” in my case), and since I’m a member of local administrators on my workstation, the update request would succeed. Of course IIS runs as Network Service (assuming no impersonation), which is not a local admin, and the code would stop the update. Obviously.

What totally rocks is that Google was able to find me the answer, in the book that was sitting right in front of me. Despite being right here, the likelihood of me finding this was low – I was on completely the wrong path! Woot Google!

While I much prefer reading a book over reading on the web, but the lack of deep indexing is a pain. Google Book Search = winning combination in my… um… book.