Have you ever needed to get a download of your Umbraco content nodes in a cross-tab query e.g. download contact form data from Doc2Form? Using Umbraco's Contour product makes this a breeze but what about older systems? Thankfully, it's not actually that difficult.

We have a contact us form on one of our sites which uses an old version of Doc2Form which emails the customer details of the enquiry. One benefit is it also saves it to the recycle bin as a document with the name: "RE: SYSTEM DATA: umbraco master root". With that in mind, we can use SQL Server's PIVOT functionality to pull the data out in a nicely formatted manner.

Firstly you'll need to know the id's of the document type's properties, there are numerous ways to do this:

1. Just look at the cmsPropertyData table for a couple of content nodes (I could spot the ones I was after fairly easily)

2. Query the cmsPropertyType table:

Find the contentTypeId of the document type -you can do this by hovering your mouse over the document type in the tree and checking out the status bar (you can see the contentTypeId in brackets in the bottom left -mine here is 1074):

Once you have the contentTypeId of the document type, you can then get the ids of all the properties you're after by replacing "xxx" with your property id in the following script:

SELECT * FROM dbo.cmsPropertyType WHERE contentTypeId = xxx

3. Get it from the source of the document type editor

An alternative way is to examine the HTML of the Document Type editor. If you view the source on the "Generic Properties" tab and scroll to the section you're interested in (there'll be a h2 with the same name) you will find a ul that has the class of "genericPropertyList".

Each li of that ul will have the relevant id as part of it's id e.g. for a section called "Enquiry Form" the id will be: "EnquiryFormContents_49" where "49" is the id we're interested in. You can see mine (49, 50, 51 and 52 below):

Once you have these ids to hand (mine were 49, 50, 51 and 52) you just need to update the code below and run against your Umbraco database:

SELECT
	contentNodeId As [Id], 
	[49] As [Name], 
	[50] As [Telephone], 
	[51] As [Email Address], 
	[52] As [Notes]
FROM
(
	SELECT contentNodeId, propertytypeid, ISNULL(dataNvarchar, dataNtext) As [Value]
	FROM dbo.cmsPropertyData
) As src
PIVOT (
	MAX(Value) 
	FOR propertytypeid in ([49], [50], [51], [52])
) aS pvt
WHERE [50] IS NOT NULL OR [51] IS NOT NULL
ORDER BY contentNodeId

That will then produce some lovely formatted data for you, my example above produced:

It's also possible to automate this entire script so you don't need to find out the property ids, I'll post that separately.

After my last UCommerce post on how to delete test orders and baskets from UCommerce, Søren suggested I extended the delete all baskets code to take into account when it was created. As my last code was relating to deleting test orders/baskets (and so would want to get rid of them all), I decided to post this one separately.

Delete all baskets older than x days

To use this, all you need to do is change the @addedBefore parameter to whatever date/time you want (or just adjust the –7 which represents seven days in the past.

--Delete all carts purchaseorders and associated data within x days
DECLARE @addedBefore smalldatetime
--By default the script deletes everything older than 7 days
SET @addedBefore = DATEADD(dd, -7, GETDATE())

BEGIN TRAN

UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore

--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

Although Søren has posted a helpful post on how to delete entire purchase orders from the database here, we needed something a little less “all or nothing” so put the below together.

Delete a specific order id

--Delete purchaseorders and associated data based on order id
DECLARE @OrderNumber nvarchar(50)
SET @OrderNumber = 'TEST-40'

BEGIN TRAN

UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber = @OrderNumber

--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber

--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

Delete all baskets

--Delete all carts purchaseorders and associated data

BEGIN TRAN

UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL

--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL


--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

Update: At the request of Søren, I’ve altered the delete all baskets post so it allows you to delete all baskets older than a given date, see: Delete all UCommerce baskets older than x days

We’ve all done it, you’ve run into a problem while developing which you bash at for a few hours and before you know it, you’ve lost the day, not got anywhere and feel completely frustrated. What’s more, is it’s usually something so screamingly obvious and/or simple that you just know you’ll find the answer on Google.

Rather than pulling your hair out for hours on end, there’s a rather simple rule-of-thumb that you should follow:

If you’re able to bash at it for 30 minutes without feeling you’re getting any closer, you’re probably looking at it from the wrong direction and having someone else’s perspective on the problem will probably answer it within seconds. By walking away from the problem you’re also taking away the pressure and you’ll often find the solution comes to you.

Another advantage of putting a time limit on the issue is it avoids you losing the day and should also mean you’ve explored Google and the lists so when you ask your “friend”, it should stop you getting that annoying lmgtfy response when asking for help (it’s a similar concept to the “wait 1 minute before sending” facility within Outlook).

So the next time you realise something’s taking longer than you think it should, start the timer!

This is a great little tip that Andy Higgs shared with me a couple of months ago while we were developing Crisis Cover. If you write jQuery that hides the div when the user has JavaScript enabled, you can avoid the divs all being shown while the page loads by simply adding a class to the body of the page using jQuery and hide it using CSS like so:

<html>
<head></head>
<!-- Reference to jQuery here -->
<body>
<!-- This should be the first bit of code and don't wait until the page has loaded -->
<script type="text/javascript">$('body').addClass('js');</script>
<!-- The rest of your code here -->
<div class="jsHide">
	<p>This paragraph is hidden if the user has JavaScript enabled.</p>
</div>
</body>
</html>

Then you just need to add the css:

.js .jsHide

Your divs will now be hidden until you show them with JavaScript. Nice, simple solution to an ever annoying problem.

Note: For my demo to work you'll need to include jQuery

Update: As pointed out by Petr below and Andy Higgs/Trevor Morris, it would be better to target using JavaScript without jQuery and target the body for maximum flexibility (note the space at the front in case there is already a class):

<script type="text/javascript">document.getElementsByTagName('html')[0].className+=' js'</script>

Ever wanted to be able to collapse all items within Visual Studio's solution window? This is a nifty little Visual Studio macro that I came across a few years ago and have been using successfully in Visual Studio 2005, Visual Studio 2008 and now in the Visual Studio 2010 RC.

I'll overview how to install it below in case you're unsure how to do it but I have this bound to the key combination Ctrl+Shift+` as ReSharper now uses my previous key combination of Ctrl+` for it's new bookmark explorer.

Anyway, here's the Visual Studio Solution Explorer item Collapse All macro:

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics
'-----------------------------------------------------------
' CollapseAll Module
'-----------------------------------------------------------
' Simple macro that fully collapses all items in the 
' Solution Explorer rather than just the top level node
'
' To make live easier, bind it to a keyboard setting such
' as Ctrl+Shift+` which by default has no bindings (Ctrl+` is
' now used by Resharper
'
' Tested and works with:
' Visual Studio 2005
' Visual Studio 2008
' Visual Studio 2010
'
' Originally from: http://bit.ly/bmRu3W
'-----------------------------------------------------------
Public Module CollapseAll

    Sub CollapseTree()
        ' Get the the Solution Explorer tree
        Dim solutionExplorer As UIHierarchy
        solutionExplorer = DTE.Windows.Item(Constants.vsext_wk_SProjectWindow).Object()

        ' Check if there is any open solution
        If (solutionExplorer.UIHierarchyItems.Count = 0) Then
            Return
        End If

        ' Get the top node (the name of the solution)
        Dim rootNode As UIHierarchyItem = solutionExplorer.UIHierarchyItems.Item(1)
        rootNode.DTE.SuppressUI = True

        ' Collapse each project node
        Collapse(rootNode, solutionExplorer)

        ' Select the solution node, or else when you click 
        ' on the solution window
        ' scrollbar, it will synchronize the open document 
        ' with the tree and pop
        ' out the corresponding node which is probably not what you want.
        rootNode.Select(vsUISelectionType.vsUISelectionTypeSelect)
        rootNode.DTE.SuppressUI = False
    End Sub

    Private Sub Collapse(ByVal item As UIHierarchyItem, ByRef solutionExplorer As UIHierarchy)
        For Each innerItem As UIHierarchyItem In item.UIHierarchyItems
            If innerItem.UIHierarchyItems.Count > 0 Then
                ' Re-cursive call
                Collapse(innerItem, solutionExplorer)
                ' Collapse
                If innerItem.UIHierarchyItems.Expanded Then
                    innerItem.UIHierarchyItems.Expanded = False
                    If innerItem.UIHierarchyItems.Expanded = True Then
                        ' Bug in VS 2005
                        innerItem.Select(vsUISelectionType.vsUISelectionTypeSelect)
                        solutionExplorer.DoDefaultAction()
                    End If
                End If

            End If
        Next
    End Sub
End Module

In case you've never installed a Visual Studio macro before, here's a couple of instructions:

1. In Visual Studio, press Alt+F11 to load up the Visual Studio Macro editor (or View > Other Windows > Macro Explorer > Double Click on "Module1" in "My Macros")
2. Either create a new module of it it's not in use, you can edit Module1 and past in the code above
3. Save and close the Visual Studio Macro editor
4. You should be back in Visual Studio so click "Tools > Options > Environment > Keyboard"
5. In the "Show commands containing" text box, enter "CollapseTree" and the macro you just created should be shown.
6. Make sure "Global" is selected in the "Use new shortcut in:" drop down list
7. Press Ctrl+Shift+` in the "Press shortcut keys:" text box
8. Click Assign
9. Click OK

You're done :)

This was one of those irritating errors that you get when you're trying to do something quickly before you go home and you can't for the life of you fathom the issue.

I had the following code (simple enough):

FileInfo f = new FileInfo("## File's Path ");
try

{...}

{
    f.MoveTo("## DROP OFF DIRECTORY ##"));
}

catch (Exception e)

{...}

{
    //Log the exception here
}

The fix was simple, you just have to remember to specify the new filename too. (DOH!). Here's the "correct" code.

FileInfo f = new FileInfo("## File's Path ");
try

{...}

{
    f.MoveTo(Path.Combine("## DROP OFF DIRECTORY ##", f.Name));
}

catch (Exception e)

{...}

{
    //Log the exception here
}

Hope that helps you out ;)

Ok it might be a little less than half side but it's near enough. I've been sitting on this for a while and needed to reference it for someone so I thought I'd post quickly about it. One of the most common complaints about .Net is that you have a lot of hidden "content" by the way of hidden inputs and the likes throughout your site. This can easily get corrupt on postback/slowdown the page load times etc.

Really you should be optimising each control on the page (enabling/disabling where relevant) but if you want to cheat (lets face it, we all do):

1. Download the files: PageStateAdapterv1.0.zip (3KB)
2. Put PageStateAdapter.browser into your /App_Browsers/ folder (or create one and add it)
3. Put TSDPageStateAdapter.dll into your website's /bin/ folder
4. Load up your website and checkout your ViewState :)

Incase you're interested in the source for it:

PageStateAdapter.browser

<browsers>
    <browser refID="Default">
        <controlAdapters>
            <adapter controlType="System.Web.UI.Page" adapterType="TheSiteDoctor.PageStateAdapter.PageStateAdapter" />
        </controlAdapters>
        <capabilities>
            <capability name="requiresControlStateInSession" value="true" />
        </capabilities>
    </browser>
</browsers>

PageStateAdapter.cs

using System.Web.UI;

namespace TheSiteDoctor.PageStateAdapter

{...}

{
    public class PageStateAdapter : System.Web.UI.Adapters.PageAdapter

    {...}

    {
        public override PageStatePersister GetStatePersister()

        {...}

        {
            return new SessionPageStatePersister(this.Page);
        }

    }

}

The best example of how much this reduces ViewState by is when you add a large DataGrid to your site.

Post files: PageStateAdapterv1.0.zip (3KB)

Update: Apologies to those of you who downloaded and found it wouldn't compile, the .browser file was a little off (missing the second "PageStateAdapter"). I've updated it and changed the zip file download. Enjoy!

This one made me laugh today, Chris Anderson alerted me to it but you would have thought the MS guys would have picked up on it...

Incidentally, it's the first time I've seen this message on any site...

When uploading some new media items for a client today we noticed that if you selected "Remove" before saving, it doesn't actually remove the file from the FileSystem. Having a quick look around the forums I saw there are a few posts already pointing this out so I thought I'd fix it.

This is a little application that simply checks the media items in the database and then compares it against a folder you select on your machine. If the file is in use according to the database then it's ignored otherwise it will remove it.

To use:

1. Enter your server's login details
2. Click "Test Connection"
3. Select the relevant database from the drop down
4. Check the "Media Folder Name" matches your Umbraco's installation
5. Locate your Media Folder on your computer
6. Click "Check Media Folder" -this will then list all the orphan files
7. If it looks right, click "Delete" -with caution
8. Job done

There are a few checks in place to avoid mishap but it's not 100% foolproof as I needed something rough and ready to sort a couple of installations out. If this is something that's seen as useful I'll extend it a touch, some ideas I've got already:

• Check that the selected media folder matches that of the database
• Check that the media id's are the same (to avoid wiping another installation)
• Save config settings for easy re-use
• Use webservices rather than a direct connection to the database
• Enable FTP useage

Please note: I accept no responsibility if anything was to go horribly wrong with this. I would backup your folder first just in case!

You can download the MediaFolderCleaner application here

As it's my Birthday today I thought I'd post a silly ditty. I'm currently altering Protx's old ASP.Net library to accommodate their changes in regards 3D Secure and while reflecting some of the code came across an enum with their number representations as Hexadecimal strings. I needed to convert these to decimals so thought I'd share a quick and easy way to do it.

Open up Window's Calculator (Windows Key + R then type in calc) under the View menu select "Scientific". Press the F5 key to switch over to Hex entry. Type in the value after the 0x and hit F6

Simple, easy and will help you convert all those Hexadecimal strings (ones that look like this: 0x01 or 0x1a).

Right, time for a coffee :)

In a previous post about CodeGarden 08, I asked people to get in touch if they'd be interested in a UK Umbraco meet up. I've had a fair few people get in touch so I think it's something worthwhile pursuing further. The nest stage from my POV is working out the location and potential content of the meet so I thought I'd open it up to the floor.

With the forthcoming DDD7, I thought it might be a ready-built platform that we could use but I agree with Phil that DDD7 may not be a suitable platform for a multitude of reasons.

As I've had people from the South West and Scotland voice an interest, I don't think it'll suit the majority of people to have it based in London so suggest it is based in the Midlands -probably Birmingham as it's easy to get to (M6 from the North, M4 from London, M5 from the South -or train!) and there are plenty of places to have the meet.

In regards the format/content of the meet, does anyone have any suggestions? We could follow Niels' and Per's open format or we can have a more structured theme? I've not had too much of a think as to subject matter but some I have come up with so far:

• An introduction to Umbraco and what it is (many of the people I've spoken to have only just started using Umbraco)
• Examples of Umbraco how Umbraco can be used
• More advanced Umbraco functionality (membership etc)
• Getting to grips with XSLT
• How to sell Umbraco to your clients

So that's where I've got to so far, does anyone have anything to add?

BTW the logo is just a working logo atm, need to have Niels approve it ;)

Update: I have posted a post on the Umbraco forums about a UK Umbraco meet here

I don't mind when I get told I've made a mistake -or there's a problem with the system but this error message kinda takes the P! Quite what the developers were thinking when they wrote this one I'm not sure!

What do I do? celebrate that it went through ok or commiserate because it failed?

The "Ok." relates to the transaction completing without an issue, the "Stop" actually says that it failed so it's not even "Part A was ok, but Part B failed". Really odd, someone needs to look into testing their system.

Looks pretty though!

So things have been manic here the past week, for those of you who didn't know, I popped over to Denmark at the last minute to attend Umbraco's CodeGarden 08. It was great fun and I have to thank Niels Hartvig and Per Ploug Hansen for putting on a great couple of days.

You can check out my photos from the event on Flickr (bear with me, I'm just getting started with Flickr).

I'm sure a fair few people have blogged about the highlights (if you're interested check www.umbraco.org) but the biggy was announcing the release of Umbraco v3.14.0 which is pretty exciting news as it has a ton of feature enhancements and UI improvements. Also, you'll be pleased to hear that they're making 2008 the year of Umbraco documentation!

Another interesting points from the conference was the pending release of Umbraco.TV which will feature tutorial videos and insights from the core team on how to use Umbraco and the Umbraco store which allows you to easily distribute the packages you make :) All in all some interesting developments.

There were also a fair few English developers at the conference so discussion inevitably turned to a UK meet (I know there are a fair few designers and developers here that couldn't justify the expense) so that's something that I'm going to look into setting up. If this is something you'd be interested in, leave a comment or drop me an email and we'll see how much interest there is.

To all the rest of you -it was great to meet you, you're all a lovely bunch and I look forward to meeting you again at CodeGarden 09!

The other thing I've finally clarified (this is for you Simon!) is the Umbraco licensing rules so if you're unsure on those, check out my post on when you need to purchase an Umbraco license (the answer is always -or never, it's up to you!).

This may seem a slightly obvious/silly post but the answer is simple -it's just not *that* well documented/explained.

In a nutshell there are three scenarios you need to worry about:

• Using Umbraco in a non-commercial environment with the branding (logos etc) intact -no fee
• Using Umbraco in a commercial environment with the branding (logos etc) intact -no fee
• Using Umbraco in a commercial environment without the branding (logos etc) -fee

So there you have it. But to be fair, you should always pay for it if you're using it in a commercial environment just because it's a great product (and it's good for your karma!)

Doug Setzer posted this comment in response to my recent "A seriously elegant SQL Injection" post and I thought it may be of interest to others so have promoted it to a post...

Well, I'll step up and say that I am the "mate" who had this done.  Tim's right - *always* sanitize your inputs.  In my defence, this was a site that I inherited from a previous contractor.  I'm not entirely absent of blame, I still should have done a security sweep through the code.

I'd like to document the steps that I went through once this was identified to try and avoid this kind of thing in the future.

1. Edit every web page that executes a query to sanitize any parameters that are passed in.  Since the site was classic ASP, I used my "SQLStringFieldValue" function:
   www.27seconds.com/kb/article_view.aspx?id=50
2. Modify the DB user account that is used to have *read only* access to the database
3. Modify the pages that DO write to the database to have *read/write* access to the specific tables that are being changed.  This limits the number of places that SQL Injection can occur to a smaller set than was previously possible.  I still sanitize all of my input, but I'm extra spastic in these database calls.
4. Add database auditing (triggers writing to mirror tables with audit event indicator & date/time) to see when data changes occur.  This is still problematic with the pages that have "write" permissions to the tables, but again- that footprint is much smaller.

My future plans are to move to a view/stored procedure based architecture.  I can then limit write permissions to just the stored procedures and read permissions to just the views.  My grand gusto plans are to move to using command objects & parameters, but I'd sooner re-write the entire site.

Although Doug's attack wasn't the same nihaorr1.com attack that's going around atm it was similar so I would imagine other's will find this useful.

It still amazes me how many developers still fail to sanitise strings, only last week I came across another site (in PHP) that was allowing simple SQL injections to be used to log into their administration system. It was down to a problem with the sanitization string, but why not at least check your site before it goes live? It takes 2 minutes and even less to fix...

For those of you who need a few pointers, there's a good discussion or two about sanitising strings on the 4 Guys From Rolla site.