find a location for property in a new city

Friday, 17 May 2013

SQL MERGE that changes the ON constraint as it runs

What happens when your MERGE statement INSERTs a row that now satisfies the ON constraint. If a new row comes along that satisfies the ON constraint will it fall into WHEN MATCHED or WHEN NOT MATCHED?

Let's use an example piece of code to decide what happens. Here I have two table variables created and seeded with some random data:

DECLARE @t1 Table (id int, name varchar(12))
INSERT INTO @t1 VALUES(1, 'hi')

DECLARE @t2 Table (id int, name varchar(12))
INSERT INTO @t2 VALUES(1, 'bye')
INSERT INTO @t2 VALUES(3, 'colin')
INSERT INTO @t2 VALUES(3, 'sarah')

Now I will attempt to MERGE @t2 into @t1. If you have not come across MERGE before it is basically a more efficient way of saying UPDATE if it exists or INSERT if it is new.

MERGE @t1 AS t1
 USING(SELECT * FROM @t2) AS t2
 ON t2.id = t1.id
WHEN MATCHED THEN
 UPDATE SET t1.name = t2.name
WHEN NOT MATCHED THEN
 INSERT(id, name)
 VALUES(t2.id, t2.name);

So, what we are saying is:

  • USING this source of data (SELECT * FROM @t2)
  • ON this decider (matching the ID's to judge if this row already exists)
  • WHEN the ON clause is MATCHED update this row with new values
  • WHEN NOT MATCHED we should INSERT the new row

But, given the values in @t2, what will happen?

  1. The 1st row will match on ID 1 and so will do an UPDATE
  2. The 2nd row doesn't have a match for an ID of 3 so will INSERT
  3. The 3rd row didn't have a match for an ID of 3 before but since the last INSERT it now does. So INSERT or UPDATE?

A SELECT * FROM @t1 will show you that it has INSERTed twice...

idname
1bye
3colin
3sarah

So watch out for this. The constraint is decided about the source and destination once at the beginning and not again so you should be sure that the source table being MERGED is complete in itself. You can do with using a GROUP BY or DISTINCT on the USING table. Whichever is most appropriate for your scenario.

Follow britishdev on Twitter

How to solve Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

I was writing my Entity Framework 5 Code First data models one day when I received a dramatic sounding error: "Introducing FOREIGN KEY constraint 'FK_dbo.Days_dbo.Weeks_WeekID' on table 'Days' may cause cycles or multiple cascade paths." I was instructed to "Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." And then simply the message, "Could not create constraint." So what happened with my Foreign Key that makes it cyclic?"

First a spot of code that can nicely demonstrate this scenario:

public class Year
{
    public int ID { get; set; }
    public string Name { get; set; }

    public ICollection<Month> Months { get; set; }
}
public class Month
{
    public int ID { get; set; }
    public string Name { get; set; }

    public Year Year { get; set; }
    public ICollection<Day> Days { get; set; }
}
public class Day
{
    public int ID { get; set; }
    public string Name { get; set; }

    public Month Month { get; set; }
    public Week Week { get; set; }
}

//problem time
public class Week
{
    public int ID { get; set; }
    public string Name { get; set; }

    public Year Year { get; set; }
    public ICollection<Day> Days { get; set; }
}

So, let's explain this. A Year has Months and a Month has Days - all is well at this point and it will build and generate all your tables happily. The problem comes when you add the highlighted parts to add Weeks into the data model.

By making those Navigation Properties you have implicitly instructed EF Code First to create Foreign Key constraints for you and each of these will have cascading deletes on them. This means, upon deleting a Year, the database will cascade that delete to that Year's Months and in turn those Month's Days. This is to, quite rightly, hold referential integrity; you will not have Yearless Months.

So if you think about it, now that we have added a Week entity that also has a relationship with Days this too will cascade deletes. So as well as the cascading deletes that occur when you delete a Year as I described above, now deleting a Year will also delete its Weeks and those Weeks will delete the Days. But those Days would have already been deleted by the cascade that went via Months. So who wins? Who gets there first? Don't know... that's why you need to design an answer to this conundrum.

Removing the multiple cascade paths

The way I found to solve this issue is you need to remove one of the cascades, since there is nothing wrong with the Foreign Keys, it is only the multiple cascade paths. So lets remove the cascading delete on the Week -> Days relationship since that delete will be taken care of by each Month cascading its deletes to their Days.

public class CalenderContext : DbContext
{
    public DbSet<Year> Years{ get; set; }
    public DbSet<Month> Months { get; set; }
    public DbSet<Day> Days { get; set; }
    public DbSet<Week> Weeks { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Day>()
            .HasRequired(d => d.Week)
            .WithMany(w => w.Days)
            .WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);
    }
}

If you already have a ForeignKey field explicitly written into your code model (as I usually do - I removed them from the above example for clarity) you will need to specify this existing Foreign Key in the code just written. So if your Day entity looked like this:

public class Day
{
    public int ID { get; set; }
    public string Name { get; set; }

    [ForeignKey("Month")]
    public int MonthID { get; set; }
    public Month Month { get; set; }

    [ForeignKey("Week")]
    public int WeekID { get; set; }
    public Week Week { get; set; }
}

You will need your OnModelCreating method to look like this:

modelBuilder.Entity<Day>()
    .HasRequired(d => d.Week)
    .WithMany(w => w.Days)
    .HasForeignKey(d => d.WeekID)
    .WillCascadeOnDelete(false);

Have fun deleting safely!

Follow britishdev on Twitter

Thursday, 28 February 2013

The request filtering module is configured to deny a request that contains a double escape sequence

In IIS 7.5 I have a site that contains a page that takes an encrypted part of a URL. This encrypted string includes a plus sign '+' which causes IIS to throw a "HTTP Error 404.11 - Not Found" error stating "The request filtering module is configured to deny a request that contains a double escape sequence."

The problem is that a + sign used to be acceptable in earlier versions of IIS so these URLs need to remain for legacy reasons. So, I need to make them allowed again in IIS.

The quick fix

This can be easily achieved with a simple web.config change:

<system.webServer>
    <security>
        <requestFiltering allowDoubleEscaping="true" />
    </security>
</system.webServer>

This allows URLs to contain this plus symbol '+'.

The warning

There are consequences to this which unsurprisingly are security related so please read Double Encoding to familiarise yourself with the risk for your situation. If it is a risk to you maybe the best solution is to redesign those URLs?

Follow britishdev on Twitter

Saturday, 19 January 2013

Handler “ExtensionlessUrlHandler-Integrated-4.0” has a bad module “ManagedPipelineHandler” in its module list

How to fix the error 'Handler “ExtensionlessUrlHandler-Integrated-4.0” has a bad module “ManagedPipelineHandler” in its module list'.

This error occurred when I moved my moved my ASP.NET 4.5 from an old Windows 7 machine to a brand new Windows 8 machine running IIS 8.

I was thinking I was in need of an aspnet_regiis -I but this threw back a message at me saying "Start installing ASP.NET (4.0.30319.17929). This option is not supported on this version of the operating system. Administrators should instead install/uninstall ASP.NET 4.5 with IIS8 using the "Turn Windows Features On/Off" dialog, the Server Manager management tool, or the dism.exe command line tool. For more details please see http://go.microsoft.com/fwlink/?LinkID=216771. Finished installing ASP.NET (4.0.30319.17929)."

So I turned to turning windows features on/off (Win+X then hit F). I had already turned IIS on but then I had found I hadn't done enough.

Previously when I had turned IIS on I had left it with the default but actually I needed more to get ASP.NET 4.5 to run. You need to go to:

  1. Internet Information Services
  2. World Wide Web Services
  3. Application Development Features
  4. Then check ASP.NET 4.5

This will select a few other bits for you and you should be good to go.

Follow britishdev on Twitter

Thursday, 17 January 2013

Finding Outlook Web Access (OWA) URL from Outlook 2010

I was struggling to find out my Outlook webmail address from Outlook 2010 on my machine so that I can access my work email from home or on my mobile. The OWA URL is hidden quite deep into Outlook 2010 so I have written down the location of the URL so I can remember for next time.

It is rather hidden away, probably because it is assumed you would just ask IT instead of finding out for yourself but that isn't like you is it. That's why you're here!

Finding your web mail address in Outlook 2010

  1. Click the File tab near the top left
  2. Click the Info tab
  3. Click the big "Account Settings" button and then "Account Settings" again on the pop out menu
  4. On E-mail tab ensure your address is selected and click the "Change..." button just above it
  5. Click the "More Settings ..." button at the bottom
  6. Go to the Connection tab and click "Exchange Proxy Settings..." button at the bottom
  7. It is the first URL on this window. Something like https://mail.mydomain.com

Follow britishdev on Twitter

Monday, 14 January 2013

Web forms button click event not firing in IE

There are probably many reasons why a button would not fire in an ASP.NET web forms application. Some can be fixed with deleting cookies and history, some are to do with when the event is registered. I found a new one though.

In my scenario I had a text box that was submitted with a button next to it. We didn't want to display the button though but instead wanted the form submitted with a click of the Enter button. The button was therefore wrapped in a display:none span, like this:

<input type="text" id="txtInput" onchange="btnGo_Click" runat="server"/>
<asp:Button style="display:none;" runat="server" OnClick="btnGo_Click" />
<asp:Label ID="lblOutput" runat="server" />

It turns out that this worked just fine in Chrome and Firefox but not in IE9. This was because not all browsers post data that is hidden with CSS. Security possibly? You could see this by looking at the request in Fiddler. There was no value for the button field in IE but there was for the others.

How to submit a form by clicking Enter and hiding the button

The best way I can think of is to hide the button without hiding it... How? You can push it miles of the page to hide the button with some CSS like this:

<input type="text" id="txtInput" onchange="btnGo_Click" runat="server"/>
<asp:Button style="position:absolute;left:-9999px;" runat="server" OnClick="btnGo_Click" />
<asp:Label ID="lblOutput" runat="server" />

This will put the button miles off to the left never to be seen by the user.

Follow britishdev on Twitter

Friday, 11 January 2013

Add rel="nofollow" to all external links with one line of jQuery

You may want to change all external links on a page to do something different such as add a target="_blank" to each one or add rel="nofollow" to every external link. This post will show you how this can be done in one line of jQuery!

SEO SEO SEO... How tiresome a concept... Anyway, people make their living with it apparently and while that is the case I get all kinds of weird requirements like this. This time I had to add rel="nofollow" to all external links for latest whim of an SEO consultant.

Since SEO requirements seem to chop and change fairly unpredictably I wanted to add rel="nofollow" to all external links in the quickest and least interfering way possible. I managed it with one line of jQuery so that is fairly innocuous and I can just remove it if it needs to be undone at some point. This can be used to add target="blank" to also.

Add rel="nofollow" to all external links

$("div.content a[href^='http']:not([href*='mysite.co.uk'])").attr("rel", "follow");

Add target="_blank" to all external links

$("div.content a[href^='http']:not([href*='mysite.co.uk'])").attr("target", "_blank");

Hope this helps you spend as little time on this as possible :)

Update:

I did some reasearch into whether adding a nofollow in this way will work on Google and came to the conclusion that it probably wont. Colin Asquith commented similar thoughts. So this should probably be considered if using this for adding rel="nofollow" to links but technically this is a good way of going about it or anything similar like adding target="_blank" for example.

Follow britishdev on Twitter

Sunday, 11 November 2012

Upgrading Azure Storage Client Library to v2.0 from 1.7

I upgraded Azure Storage to version 2.0 from 1.7 and I've found a number of differences when using storage. I thought how I'd document how I upgraded these more awkward bits of Azure Storage in version 2.0.

DownloadByteArray has gone missing

For whatever reason DownloadByteArray has been taken from me. So has DownloadToFile, DownloadText, UploadFromFile, UploadByteArray, and UploadText

Without too much whinging I'm just going to get on and fix it. This is what was working PERFECTLY FINE in v1.7:

public byte[] GetBytes(string fileName)
{
    var blob = Container.GetBlobReference(fileName);
    return blob.DownloadByteArray();
}

And here is the code modified to account for the face that DownloadByteArray no longer exists in Azure Storage v2.0:

public byte[] GetBytes(string fileName)
{
    var blob = Container.GetBlockBlobReference(fileName);
    using (var ms = new MemoryStream())
    {
        blob.DownloadToStream(ms);
        ms.Position = 0;
        return ms.ToArray();
    }
}

How to get your CloudStorageAccount

Another apparently random change is that you can't get your storage account info in the same way as you used to. You used to be able to get it like this in Storage Client v1.7:

var storageAccountInfo = CloudStorageAccount.FromConfigurationSetting(configSetting);
var tableStorage = storageAccountInfo.CreateCloudTableClient();

But in Azure Storage v2.0 you must get it like this:

var storageAccountInfo = CloudStorageAccount.Parse(
            CloudConfigurationManager.GetSetting(configSetting));
var tableStorage = storageAccountInfo.CreateCloudTableClient();

Why?.. not sure. I have had problems with getting storage account information before so maybe this resolve that.

What happened to CreateTableIfNotExist?

Again, it's disappeared but who cares.. Oh you do? Right well let's fix that up. So, in Azure Storage Client v1.7 you did this:

var tableStorage = storageAccountInfo.CreateCloudTableClient();
tableStorage.CreateTableIfNotExist(tableName);

But now in Azure Storage Client Library v2.0 you must do this:

var tableStorage = storageAccountInfo.CreateCloudTableClient();
var table = tableStorage.GetTableReference(tableName);
table.CreateIfNotExists();

Attributes seem to have disappeared and LastModifiedUtc has gone

Another random change that possibly doesn't achieve anything other than making you refactor your code. This was my old code from Storage Library Client v1.7:

var blob = BlobService.FetchAttributes(FileName);
if (blob == null || blob.Attributes.Properties.LastModifiedUtc < DateTime.UtcNow.AddHours(-1))
{
    ...
}

But now it should read like this because thought it looks prettier (which it does in fairness).

var blob = BlobService.FetchAttributes(FileName);
if (blob == null || blob.Properties.LastModified < DateTimeOffset.UtcNow.AddHours(-1))
{
    ...
}

Change your development storage connection string

This is just a straight bug so that's excellent. I was getting a useless exception stating "The given key was not present in the dictionary" when trying to create a CloudStorageAccount reference. To resolve this change your development environment connection string from UseDevelopmentStorage=true to UseDevelopmentStorage=true;DevelopmentStorageProxyUri=http://127.0.0.1 then it will magically work.

Bitch and moan

Apologies for the whingy nature of this post, I'm quite a fan of Azure but I have wasted about 3-4 hours with this "upgrade" from Azure Storage Client Library 1.7 to 2.0. It's been incredibly frustrating particularly since there seems to be no obvious reason why these changes were made. I just can't believe the amount of breaking changes when I haven't really written that much Azure storage code.

Randomly taking out nice methods like DownloadByteArray and DownloadText is surely a step backwards no? Or randomly renaming CreateIfNotExist() to CreateIfNotExists()... what is the point in that?!

I remember when upgrading to ASP.NET 4 from 3.5, I spent very little time working through breaking changes and I have 100 times more .NET code than I do Azure Storage code. As well as that, I was well aware of the many improvements with that .NET version update, with this Azure Storage update I have no idea what I'm getting. No matter the improvements, it is just an Azure storage API and this number of breaking changes, often for the benefit of syntax niceties is just unnacceptable.

Oh, if you are still in pain doing this I have found a complete list of breaking changes in this update along with minimal explanations here.

Follow britishdev on Twitter

Saturday, 27 October 2012

Unexpected "string" keyword after "@" character. Once inside code, you do not need to prefix constructs like "string" with "@"

After upgrading an ASP.NET MVC 3 project to MVC 4 I noticed a change in the Razor parser that threw a Parser Error saying: 'Unexpected "string" keyword after "@" character. Once inside code, you do not need to prefix constructs like "string" with "@"'

Firstly this has always working when it was MVC 3 and Razor v1. I may have been getting the syntax wrong all along but if the syntax allowed it was it really wrong?

What I was doing was trying to put some server code in a Razor helper with no surrounding HTML tags, like this example:

@helper Currency1000s(int? value)
{
    if(value == null)
    {
        -
    }
    else
    {
        @string.Format("{0:C0}k", value / 1000.0)
    }
}

Interestingly if I were to replace line 9 with @value all would be fine. Anyway, it is an easy enough fix, you just need to wrap the string.Format in HTML tags or the text tags as I did here:

@helper Currency1000s(int? value)
{
    if(value == null)
    {
        -
    }
    else
    {
        @string.Format("{0:C0}k", value / 1000.0)
    }
}

Follow britishdev on Twitter

Tuesday, 23 October 2012

Build Visual Studio solutions without Visual Studio

I have a project that has four separate Visual Studio solutions. It is a bit annoying when I do an update from SVN and have to open each solution in Visual Studio just so I can build them. Visual Studio is hardly a lightweight program so surely there's a simpler way?

Most sys admins or developers accustomed to automated builds will probably start to titter at this point, but you can do it easily using NotePad (and the various stuff already installed on your dev machine). I don't want a mega complex system to deploy in a special way to a different server using expensive software, I just want to build everything without having to load many Visual Studio instances. So here is how.

Simple way to build all solutions with a batch file

First open NotePad and write the following code:

@echo off
CALL "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\vcvarsall.bat"
MSBUILD /v:q C:\Projects\Forums\Forums.sln
MSBUILD /v:q C:\Projects\MainSite.sln
MSBUILD /v:q C:\Projects\Users\UserMgmt.sln
MSBUILD /v:q C:\Projects\Core\Global.sln
PAUSE

Save this as something like BuildAll.bat then whenever you want to build everything just double click this file.

What was that?! Explain (a bit)

To use MSBUILD you must be running the Visual Studio command prompt but by default batch files run in normal compand prompt so line 2 enables all the Visual Studio-ness. Also, I added the /v:q parameter so that MSBUILD wont output every little detail about the build, just the important bits (PASS/FAIL).

Follow britishdev on Twitter