Target Updates in a SQL Merge statement

I have recently spent some time using SSIS to import CRM data into a friendly SQL relational table structure.  Part of the SSIS package uses a stored procedure to synchronize a target table with updates/inserts/deletes from a staging table.  To this end I employed a  SQL MERGE statement. The MERGE statement is a really powerful way to create what’s called “upserts”.  My first attempt at the MERGE statement worked but I was not 100% happy.  The reason for this was the UPDATE part.  I found that it was updating every single record in the target table even if there had been no changes in the staging table.


So how can one limit the scope of MERGE operations?  When working with large result sets, particularly with incremental updates, most of the matched rows will probably already be in sync.  The answer is to add additional condition within the MATCHED clause.

WHEN MATCHED AND (TRGT.modifiedon <> SRCE.modifiedon) THEN 

This can provide a huge performance boost.

Posted in Uncategorized | Leave a comment


What are the best practices for combining REST and RPC in ASP.NET Web API?  Let’s first go over a couple of definitions. A REST based API exposes data as resources (or nouns) at URIs that clients interact with via HTTP verb based methods e.g. GET and POST. Remote procedure calls (RPC) are essentially endpoints that perform arbitrary actions, not necessarily tied to a particular resource e.g. SendTimesheet.

Web API automatically routes requests to API methods by convention, matching the request’s HTTP verb to a named method in your API class e.g. GetTimeSheet. This is all well and good but what happens when we want to call a method that doesn’t correspond to a verb e.g. SendTimesheet? The good news is that Web API supports RPC-style routing:

name: "DefaultApi",
routeTemplate: "api/{controller}/{action}/{id}",
defaults: new { id = RouteParameter.Optional }

OK great but what about the controller.  Should we mix REST and RPC methods in the same controller?  I initially tried this yesterday but quickly found that it was a recipe for route conflicts due to overlapping method signatures (a result of using up the HTTP verbs).  In the end I created an additional controller specifically for the action based RPC methods.  So I now have two controllers.  One for REST methods and one for the custom actions.  My WebApiConfig looks like this:

name: "rpcapi",
routeTemplate: "rpcapi/{controller}/{action}/{id}",
defaults: new { id = RouteParameter.Optional }

name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }

Posted in Uncategorized | Tagged , | 1 Comment

Returning plain old text\html from MVC Web API

OK so I’ve been getting my hands dirty with MVC 4 Web API and must say that I love this technology. Today I have actually been using it to mock out calls to an external third party web service. I wanted to write a Web API GET controller method that simply returned a raw HTML string. Now I know that I could have done this using MVC 4, but my remit was to simply mock a third party REST service as part of our latest testing strategy.

So this should have been a 5 minute task right? Wrong. The problem was that Web API was adding a XML wrapper around the actual HTML string that was returned from my mock service. It was clear that this was due to the global formatter that we had set up within the Global.asax.cs:

GlobalConfiguration.Configuration.Formatters.Insert(0,new System.Net.Http.Formatting.XmlMediaTypeFormatter());

So how does one override the XmlMediaTypeFormatter for a specific controller method? The solution was to return a HttpResponseMessage type e.g:

public HttpResponseMessage Get()

var response = new HttpResponseMessage();
response.Content = new StringContent(_myHTMLString);
response.Content.Headers.ContentType = new MediaTypeHeaderValue("text/html");
return response;

Posted in Uncategorized | Tagged , | Leave a comment

Mystery Silverlight error: “Navigation to a fragment requires the Frame to have content currently”

One of our users has recently reported the following error popping up on our production Silverlight application:

“Navigation to a fragment requires the Frame to have content currently”

I’ve Googled it but there doesn’t seem to be too much information regarding the error. Does anyone know exactly what this error means? Further investigating show us that this appears to be an issue when users navigate to our app from saved links in their browsers

Posted in Uncategorized | Tagged | 1 Comment

XAML ItemsTemplate elements

Do you have a ListBox with it’s ItemTemplate bound to a DataTemplate. Having problems getting the elements in the template to stretch to the full width of the ListBox? You don’t have to override the entire default style for the ListBoxItem. The desired result can be achieved by simply overriding just the HorizontalContentAlignment property in theListBox.ItemContainerStyle section. Try this:

<ListBox x:Name="ClassList"
   ItemsSource="{Binding LineClasses}"
   VerticalAlignment="Stretch" Grid.Row="0">
         <Style TargetType="ListBoxItem">
            <Setter Property="HorizontalContentAlignment" Value="Stretch"></Setter>
        <Border BorderBrush="Black"
                   HorizontalAlignment="Stretch" >
                             <Grid Background="Transparent" HorizontalAlignment="Stretch" >
                                     <ColumnDefinition Width="*" />
                                 <TextBlock Grid.Column="0"
                                                 Text="{Binding DisplayClassNm}"/>
Posted in Uncategorized | Tagged , | Leave a comment


Mango IT Ltd design and implement IT business solutions based on core Microsoft technologies.

Posted in Uncategorized | 1 Comment