WPF Menu using XAML and PowerShell

Success - Inspired by a blog entry from The PowerShell GUY (link) I managed to get a solution on the last WPF example, but this time using XAML:

Add-Type –assemblyName PresentationFramework

[xml]$XAML = @'
<window
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="SQLAdmin" Height="250" Width="400">
<Grid>
<Menu Height="22" Name="MainMenu" VerticalAlignment="Top" HorizontalAlignment="Stretch">
<MenuItem Header="_Systems" Name="SystemsMenuItem">
<MenuItem Header="_Tools">
<MenuItem Header="Management Studio" Name="MgmtStudMenuItem">
</menuitem>
</menu>
<Canvas Name="ContentCanvas" Margin="0,22,0,0">
</grid>
</window>
'@
$Reader = New-Object System.Xml.XmlNodeReader $XAML
$Form = [Windows.Markup.XamlReader]::Load( $Reader )

$Label = New-Object Windows.Controls.Label
$Label.Content = "This initial laben contain several lines`nof text."

$ContentCanvas = $Form.FindName("ContentCanvas")
$ContentCanvas.Children.Add($Label)

$SystemsMenuItem = $Form.FindName("SystemsMenuItem")
$SystemsMenuItem.add_click({ $Label.Content = 'SQLAdmin' })

$MgmtStudMenuItem = $Form.FindName("MgmtStudMenuItem")
$MgmtStudMenuItem.Add_Click({ $Label.Content = 'Starting SQL Server Management Studio...' })

$Form.ShowDialog() | out-null

The result is similar to that of the former WPF MenuItem example.

WPF Menu using PowerShell

Usually I use HTA for a quick application, but with PowerShell V2 it's possible to use Windows Presentation Foundation (WPF) with a lot of nice features I don't have to code from the bottom.

This little script is a investigation into the MenuItem class and click events:

$Window = New-Object Windows.Window
$Window.Title="SQLAdmin"
$Window.Height="250"
$Window.Width="400"

$SystemMenuItem = New-Object Windows.Controls.MenuItem
$SystemMenuItem.Header = "_Systems"
$SystemMenuItem.add_Click({ $Label.Content = 'SQLAdmin' })

$ManStudToolMenuItem = New-Object Windows.Controls.MenuItem
$ManStudToolMenuItem.Header = "_Management Studio"
$ManStudToolMenuItem.add_Click({ $Label.Content = 'Starting SQL Server Management Studio...' })

$ToolMenuItem = New-Object Windows.Controls.MenuItem
$ToolMenuItem.Header = "_Tools"
$ToolMenuItem.Items.Add($ManStudToolMenuItem)

$Menu = New-Object Windows.Controls.Menu
$Menu.Height="22"
$Menu.VerticalAlignment="Top"
$Menu.HorizontalAlignment="Stretch"
$Menu.Items.Add($SystemMenuItem)
$Menu.Items.Add($ToolMenuItem)

$Label = New-Object Windows.Controls.Label
$Label.Content = "This initial laben contain several lines`nof text."

$ContentCanvas = New-Object Windows.Controls.Canvas
$ContentCanvas.Margin="0,22,0,0"
$ContentCanvas.Children.Add($Label)

$Grid = New-Object Windows.Controls.Grid
$Grid.Children.Add($Menu)
$Grid.Children.Add($ContentCanvas)

$Window.Content = $Grid

[void]$Window.ShowDialog()

When the script starts, this window is shown:



Clicking the menu item "Systems" changes the contents of the white area:


The menu item "Tools" has a sub-item "Management Studio":

Clicking the menu item "Management Studio" changes the contents of the white area (again...):

Well - not exactly Rocket Science, but I found out how to use WPF MenuItem :-)
Still the formatting is done programatically. I would like to know how to use XAML for formatting and PowerShell for functionality.
References
MSDN Library > Windows Presentation Foundation
Windows PowerShell Blog : WPF & PowerShell -- Part 3 (Handling Events)
Huddled Masses : WPF From PowerShell - Select-Grid
Charles Petzold : Applications = Code + Markup (ISBN-13: 978-0-7356-1957-9)

Selectivity

After a breif discussion with a collegue about selectivity this script was created to have something concrete to work with.

-- Single key
SELECT TOP(10)
 SalesOrderID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID
ORDER BY [percent] DESC

-- Combined key
SELECT TOP(10)
 SalesOrderID,
 SalesOrderDetailID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID, SalesOrderDetailID
ORDER BY [percent] DESC

On SQL Server 2005 the database is [AdventureWorks] and on SQL Server 2008 the database is [Adventureworks2008].

Still I'm (a little) uncertain if the % calculation is correct - right now the percentage is low (good), but should it be high (good)? I'm looking for a more precise calculation definition.

Differential database backup using SMO

A differential database backup of a single database can be done like this using SMO and PowerShell:

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$SsdbName = 'Sandbox\Ssdb0'
$BackupPath = 'Z:\Backup\Ssdb0'
$Server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $SsdbName
$Database = $Server.Databases['msdb']
$Backup = New-Object 'Microsoft.SqlServer.Management.Smo.Backup'
$Backup.Checksum = $true
$Backup.Database = $Database.Name
$Backup.Incremental = $true
$Backup.BackupSetDescription = 'Differential backup of the database [' + $Database.Name + '].'
$BackupFileName = $Database.Name + '_Diff.bak'
$BackupDevice = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem'
$BackupDevice.DeviceType = 'File'
$BackupDevice.Name = [System.IO.Path]::Combine($BackupPath, $BackupFileName)
$Backup.Devices.Add($BackupDevice)
$Backup.BackupSetName = $Database.Name + ' ' + $BackupType + ' backup'
$Backup.SqlBackup($Server)
$Backup.Wait()
"[{0}] is backed up to the file '{1}'." -f $Database.Name, $BackupDevice.Name

The generated backup file is named "msdb_Diff.bak". A more unique filename is preferred, but this is just a simple example.

The output of the script is:
[msdb] is backed up to the file 'T:\Backup\Ssdb0\msdb_Diff.bak'.

A more detailed description of SMO backup using PowerShell is done by Muthusamy Anantha Kumar in a article at Database Journal (link).

PowerShell V2 CTP3 released

Just before Christmas the Community Technology Preview 3 (CTP) of PowerShell was released.
It is not yet posted at the TechNet PowerShell site (http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx), but the download is at Microsoft Download Center (http://www.microsoft.com/downloads/details.aspx?FamilyID=c913aeab-d7b4-4bb1-a958-ee6d7fe307bc&DisplayLang=en).
The PowerShell Team has made a short blog post about the release (http://blogs.msdn.com/powershell/archive/2008/12/23/early-christmas-present-from-powershell-team-community-technology-preview-3-ctp3-of-windows-powershell-v2.aspx).

Let's take a look at it...