Hierarchical SQL Query that sorts items properly

I wrote a previous post about using a SQL CTE to create a Hierarchical result set. Today I needed to do the same thing except I also needed it to sort items properly. The table has a sortOrder column, and that sortOrder applies to all the items under each parent Item. You can’t just use the sort order to sort your result set because there are many items with sortOrder=1, sortOrder=2, sortOrder=3, etc.

In order to accomplish this I added a nvarchar(max) column and create string sortOrder that built up at each level of recursion. This is simple and fast on the small resultSets I’m working with.

I hope this saves someone an hour of their life.

<pre>

with CTE as

(

SELECT  f.Id,f.ParentId,f.AccountId, f.UserId, f.Description,f.SortOrder, f.FolderTypeId,

CAST( f.Name as nvarchar(max)) as Name,

CAST( f.SortOrder as nvarchar(max)) as MasterSort

FROM Folder f

WHERE f.UserId = @userId

and ParentId = 0

UNIONALL

SELECT    fChild.Id,fChild.ParentId,fChild.AccountId, fChild.UserId, fChild.Description,fChild.SortOrder, fChild.FolderTypeId, CAST(   ‘    ‘ + fChild.Name as nvarchar(max)) as Name,

  CAST(CTE.MasterSort + ‘:’ + cast (fChild.SortOrder as nvarchar(max))  as nvarchar(max)) as MasterSort

FROM Folder fChild

JOIN CTE

ON fChild.ParentId = CTE.Id

)

Select* from CTE

order by mastersort

</pre>

Setting up a redirect from MVC folder to another URL using IIS.

Today I was asked to setup a redirect from a url like http://www.mysite.com/Greensboro to the root of the website http://www.mysite.com/.

First of all this is a MVC4 site running on an IIS server. I am a developer, but in this case, the site is built by a 3rd party and while I have access to the IIS server, I don’t have access to the source code, so I can’t change route rules in the Global.asax. Being that it is MVC, I have issues I wouldn’t have in a non-MVC site (routing issues).

FYI: The business reason for this is that the 3rd party building this website won’t complete the code under the /Greensboro section before we release print materials with that url, so we need to send visitors to the root site rather than showing a 404 or other error message.

My first thought was to drop in a HTML page with a meta refresh redirect tag, but this being MVC, and because meta tags are 100% reliable, I skipped that.

My second thought was to add an Application under the site in IIS and then use the Http Redirect in IIS. This failed because of issues with the web.config file and dlls not being present in the application I created. I tried fixing the web.config and placing copies of the main bin in the application folder, but fixing each issue leads to another issue, (and on and on) and it is getting really messy, so I decided to look at other alternatives.

The final solution was quite simple so I won’t bore you with all the mis-steps.

  1. To make this work, create a virtual directory under your website (not an Application). I’ve named my virtual directory ‘Greensboro’ and pointed it to a new folder I created (you just create a new folder in your site and point it to that).
  2. In IIS, click on your new virtual Directory, then double click on the Http Redirect icon.
  3. Set the redirect route to where you want customer redirected (http://www.mysite.com/).
  4. Check the box for ‘Redirect all request to exact destination (instead of relative to destination)’
  5. Click ‘Apply’

So this works very nicely as a Virtual Directory, but can have you pulling your hair out if you were to use an ‘Application‘.

Cheers!

Using the Swift playground to make request with bearer token

Assuming you have a bearer token and you want to call a resource passing that bearer token from a Swift Playround, here is a simple example of how it is done. Note the import XCPlayground so you run this in the playground:


import UIKit
import Foundation
import XCPlayground


let token = "abcXKabclhpY1LLI123LzXfdsKlabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsKabcXKabclhpY1LLI123LzXfdsK"

let url = NSURL(string: "http://api.yourwebsite.com/api/Test/itemsByType/1")


let request = NSMutableURLRequest(URL: url!)
request.HTTPMethod = "GET"
request.setValue( "Bearer \(token)", forHTTPHeaderField: "Authorization")

//The following is so I can test in Playground:
NSURLConnection.sendAsynchronousRequest(request, queue: NSOperationQueue.currentQueue()) { response, maybeData, error in
    if let data = maybeData {
        let contents = NSString(data:data, encoding:NSUTF8StringEncoding)
        println(contents)
    } else {
        println(error.localizedDescription)
    }
}

XCPSetExecutionShouldContinueIndefinitely()
 //end Playground specific connection code...