Octopus deploy – Script Steps sourced from a package

February 11, 2016 by boro

In the latest version of Octopus deploy you can now choose to run script steps where the files exist in a package. This might sound like a minor change but opens up some very neat options. You can read more on the details of the change in https://octopus.com/blog/octopus-deploy-3.3#ScriptsInPackages. Note at the time of writing this is only available in the beta of 3.3 (https://octopus.com/downloads/3.3.0-beta0001)

Ok, so why is this such a good thing?
Step templates are great – there is even a large library of pre-existing templates to download (https://library.octopusdeploy.com/#!/listing). If you’ve not used them before, step templates allow additional scripts to run during your deployment.

Examples could be: post to slack, create certain folders, delete given files etc. Basically anything you can achieve with powershell can be done in step templates.

Lets just stick with step templates then?
If you’ve gone through the process of setting up several deployments with Octopus and find you want to replicate the same functionality across several projects or installs then you need to re-create all the step template configurations each time. It’s not the slowest process but the idea below helps streamline things.

Now that you can run scripts from a package, why not source control the steps you want to run? One key advantage is that you can then see things like history of all the deployment steps.

What needs setting up?
You need to be running version 3.3 or higher of Octopus – see above for the link.

I’ve been using a simple test deployment of an out the box MVC project along with a new project specifically for the scripts.:

In Octopus this has 2 steps:

The first is a vanilla website deployment of ‘WebApplication1’. The second the startup scripts:

Note the package id. The idea behind using a separate projects is that the powershell scripts never need to exist in the website project.

The startup script project

I chose to use a class library for the simple reason that I could include a reference to Octopack and hence building the output nuget file was trivial.

The nuspec file is important as it tells the packaging to include all powershell files:

<?xml version="1.0"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>WebApplication1.Startup</id>
    <title>TODO Your Web Application</title>
    <version>1.0.0</version>
    <authors>TODO Your name</authors>
    <owners>TODO Your name</owners>
    <licenseUrl>http://TODO.com</licenseUrl>
    <projectUrl>http://TODO.com</projectUrl>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>TODO A sample project</description>
    <releaseNotes>TODO This release contains the following changes...</releaseNotes>	
  </metadata>
  <files>
	  <file src="**\*.ps1" target="\" />
  </files>
</package>

<?xml version="1.0"?>

<id>WebApplication1.Startup</id>

<title>TODO Your Web Application</title>

<requireLicenseAcceptance>false</requireLicenseAcceptance>

<description>TODO A sample project</description>

<releaseNotes>TODO This release contains the following changes...</releaseNotes>

</metadata>

<files>

</files>

</package>

Packages simply contains a reference to Octopack:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="OctoPack" version="3.0.53" targetFramework="net46" developmentDependency="true" />
</packages>

<?xml version="1.0" encoding="utf-8"?>

</packages>

And finally the scripts:
Helloworld.ps1

Write-Host "Hello World PS script example"

if ($OctopusParameters) {
	##enable the next line to show all Octopus variables
	#$OctopusParameters.GetEnumerator() | Sort-Object Name | % { "$($_.Key)=$($_.Value)" }
}

#use this approach to pull in additional script files
. .\Startup.ps1

Write-Host "Hello World PS script example"

if ($OctopusParameters) {

##enable the next line to show all Octopus variables

#$OctopusParameters.GetEnumerator() | Sort-Object Name | % { "$($_.Key)=$($_.Value)" }

}

#use this approach to pull in additional script files

. .\Startup.ps1

And the more important one, Startup.ps1

function createFolderIfNotExists{
	param([string]$folder)
	if (!(Test-Path $folder)) {
		Write-Host "Creating $folder"
		New-Item -Path $folder -ItemType Directory
	} else {
		Write-Host "Directory already exists!"
	}
}

function assignReadPermissionsTo {
    param([string] $item, [string] $readPermissionsTo)
    if(!(Test-Path $item)) {
        throw "$item does not exist"
    }
    $users = $readPermissionsTo.Split(",")
    foreach($user in $users) {
        Write-Host "Adding read permissions for $user"
        $acl = Get-Acl $item
        $acl.SetAccessRuleProtection($False, $False)
        $rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, "Read", "ContainerInherit, ObjectInherit", "None", "Allow")
        $acl.AddAccessRule($rule)
        Set-Acl $item $acl
    }
}

function assignWritePermissionsTo {
    param([string] $item, [string] $writePermissionsTo)
    if(!(Test-Path $item)) {
        throw "$item does not exist"
    }
    $users = $writePermissionsTo.Split(",")
    foreach($user in $users) {
        Write-Host "Adding write permissions for $user"
        $acl = Get-Acl $item
        $acl.SetAccessRuleProtection($False, $False)
        $rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, "Write", "ContainerInherit, ObjectInherit", "None", "Allow")
        $acl.AddAccessRule($rule)
        Set-Acl $item $acl
    }
}

$foldersToCreate = @("_logs","test\deep")
$usersToGiveAccess = "IIS_IUSRS"

if ($OctopusParameters) {
	$installFolder = $OctopusParameters['Octopus.Action[Deploy website].Output.Package.InstallationDirectoryPath']

	Write-Host "Environment: " $OctopusParameters['Octopus.Environment.Name']
	Write-Host "Deployment folder: " $installFolder
	
    Foreach ($folder in $foldersToCreate) {
        $newFolder = [io.path]::combine($installFolder,$folder)
        createFolderIfNotExists -folder $newFolder
        assignWritePermissionsTo -item $newFolder -writePermissionsTo $usersToGiveAccess
    }	
}
$PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
Write-Host "Current running folder is: " $PSScriptRoot

function createFolderIfNotExists{

param([string]$folder)

if (!(Test-Path $folder)) {

Write-Host "Creating $folder"

New-Item -Path $folder -ItemType Directory

} else {

Write-Host "Directory already exists!"

}

function assignReadPermissionsTo {

param([string] $item, [string] $readPermissionsTo)

if(!(Test-Path $item)) {

throw "$item does not exist"

}

$users = $readPermissionsTo.Split(",")

foreach($user in $users) {

Write-Host "Adding read permissions for $user"

$acl = Get-Acl $item

$acl.SetAccessRuleProtection($False, $False)

$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, "Read", "ContainerInherit, ObjectInherit", "None", "Allow")

$acl.AddAccessRule($rule)

Set-Acl $item $acl

}

function assignWritePermissionsTo {

param([string] $item, [string] $writePermissionsTo)

if(!(Test-Path $item)) {

throw "$item does not exist"

}

$users = $writePermissionsTo.Split(",")

foreach($user in $users) {

Write-Host "Adding write permissions for $user"

$acl = Get-Acl $item

$acl.SetAccessRuleProtection($False, $False)

$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, "Write", "ContainerInherit, ObjectInherit", "None", "Allow")

$acl.AddAccessRule($rule)

Set-Acl $item $acl

}

$foldersToCreate = @("_logs","test\deep")

$usersToGiveAccess = "IIS_IUSRS"

if ($OctopusParameters) {

$installFolder = $OctopusParameters['Octopus.Action[Deploy website].Output.Package.InstallationDirectoryPath']

Write-Host "Environment: " $OctopusParameters['Octopus.Environment.Name']

Write-Host "Deployment folder: " $installFolder

Foreach ($folder in $foldersToCreate) {

$newFolder = [io.path]::combine($installFolder,$folder)

createFolderIfNotExists -folder $newFolder

assignWritePermissionsTo -item $newFolder -writePermissionsTo $usersToGiveAccess

}

$PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition

Write-Host "Current running folder is: " $PSScriptRoot

It’s worth noting this should be considered a POC of the approach. The next steps would be to split the scripts up into more meaningful units, remove hello world and update the nuspec with more valid information.

If you struggle with accessing the Octopus Parameters you require, the script in helloworld allows you to dump out all parameters and their values. In the startup script the parameter: $OctopusParameters[‘Octopus.Action[Deploy website].Output.Package.InstallationDirectoryPath’] depends on the name of your deployment in step 1 of your deployment process (Deploy website)

How to write a basic powershell module that includes multiple scripts

March 27, 2015 by boro

As part of a recent project I’ve needed to work closely with some of the Azure api’s. You can achieve some really cool things pretty quickly once you’ve learnt a few basics.

A good tip I picked up was that if you can implement something in .net/c# chance are you can replicate in PowerShell. If there isn’t a specific PowerShell module/api available you can call into the .net dll. This can be loaded via e.g.:

function CurrentDirectory
{
    return (split-path $SCRIPT:MyInvocation.MyCommand.Path -parent);
}
[System.Reflection.Assembly]::LoadFrom((CurrentDirectory) + '\Microsoft.WindowsAzure.Storage.dll')
$credentials = New-Object Microsoft.WindowsAzure.Storage.Auth.StorageCredentials($accountName, $accountKey)

function CurrentDirectory

{

return (split-path $SCRIPT:MyInvocation.MyCommand.Path -parent);

}

[System.Reflection.Assembly]::LoadFrom((CurrentDirectory) + '\Microsoft.WindowsAzure.Storage.dll')

$credentials = New-Object Microsoft.WindowsAzure.Storage.Auth.StorageCredentials($accountName, $accountKey)

In order to structure your various functions the recommended approach is to use modules. A quick google will bring up more details. What I couldn’t find was how to setup a basic module that includes multiple scripts.

When you create new modules they can live in a couple places, $env:PSModulePath will show you this.

A module structure is pretty basic, you need a psm1 file (and psd1 – see https://www.simple-talk.com/sysadmin/powershell/an-introduction-to-powershell-modules/)

Script.ps1 is basic, it contains a simple function:

function HiThere
{
    return "hi there";
}

function HiThere

{

return "hi there";

}

Custom.psm1 then looks to load all the scripts you have. Note, make sure the folder name matches the psm1 name:

$functions = Get-ChildItem -Recurse "$PSScriptRoot\Subfolder" -Include *.ps1 

# dot source the individual scripts that make-up this module
foreach ($function in $functions) { . $function.FullName }

Write-Host -ForegroundColor Green "Module $(Split-Path $PSScriptRoot -Leaf) was successfully loaded."

$functions = Get-ChildItem -Recurse "$PSScriptRoot\Subfolder" -Include *.ps1

# dot source the individual scripts that make-up this module

foreach ($function in $functions) { . $function.FullName }

Write-Host -ForegroundColor Green "Module $(Split-Path $PSScriptRoot -Leaf) was successfully loaded."

These files need to live in one of your module folders e.g. %UserProfile%\Documents\WindowsPowerShell\Modules

It’s then a case of installing your module:
Check what modules are available to install via: Get-Module -ListAvailable
And install the one you want via: Import-Module Custom. Note, Custom is the name of the folders and psm1 file.

Provided it’s all gone to plan you should now be able to call HiThere from a ps prompt

Debugging Azure web-role startup tasks

March 23, 2015 by boro

One feature Azure offers for getting your boxes configured is the notion of startup tasks – I won’t go into too much detail here as there is lots available online e.g. https://msdn.microsoft.com/en-us/library/azure/hh180155.aspx

As part of setting these up I thought I’d share a few tips / gotchas that caught me out when running powershell from the cmd tasks.

My solution setup was:

Site root
- StartupScripts
  - Script.cmd
  - Script.ps1

So then I’d reference in the task:

<Task commandLine=”StartupScripts\script.cmd” executionContext=”elevated” taskType=”simple” />

Nothing rocket science so far! So, why didn’t the script work? I could jump on the box and run the cmd and it would be fine.

How to debug the process?
I found the most useful way was to add markers from the cmd and the ps1. The cmd file looked like:

md c:\test
echo "Running" > "c:\test\log.txt"
powershell -command "Set-ExecutionPolicy Unrestricted" 2>> "c:\test\log.txt"
powershell .\startupScripts\script.ps1 %~dp0 2>> "c:\test\log.txt"
EXIT /B 0

md c:\test

echo "Running" > "c:\test\log.txt"

powershell -command "Set-ExecutionPolicy Unrestricted" 2>> "c:\test\log.txt"

powershell .\startupScripts\script.ps1 %~dp0 2>> "c:\test\log.txt"

EXIT /B 0

Note, the .\startupScripts part of the ps1 path is v important!

Then the powershell:

$tempFolder = "c:\test"
$logFile = "ps_log.txt"
$siterootFolder = "\sitesroot\0\"
If (!(Test-Path "e:\"))
{
    $siterootFolder = "f:" + $siterootFolder
}
else
{
    $siterootFolder = "e:" + $siterootFolder
}

function Write-Log
{
    param( [string] $text )
    Write-Host $text;	
    $path = [System.IO.Path]::Combine("$tempFolder\$logFile")

    $fs = New-Object IO.FileStream($path, [System.IO.FileMode]::Append, [System.IO.FileAccess]::Write, [IO.FileShare]::Read)
    $sw = New-Object System.IO.StreamWriter($fs)

    $sw.WriteLine($text)
    $sw.Dispose()
    $fs.Dispose()
}

Write-Log "Output folder is $siterootFolder"

$tempFolder = "c:\test"

$logFile = "ps_log.txt"

$siterootFolder = "\sitesroot\0\"

If (!(Test-Path "e:\"))

{

$siterootFolder = "f:" + $siterootFolder

}

else

{

$siterootFolder = "e:" + $siterootFolder

}

function Write-Log

{

param( [string] $text )

Write-Host $text;

$path = [System.IO.Path]::Combine("$tempFolder\$logFile")

$fs = New-Object IO.FileStream($path, [System.IO.FileMode]::Append, [System.IO.FileAccess]::Write, [IO.FileShare]::Read)

$sw = New-Object System.IO.StreamWriter($fs)

$sw.WriteLine($text)

$sw.Dispose()

$fs.Dispose()

}

Write-Log "Output folder is $siterootFolder"

Note, if you try to write to log.txt you will get process locked exceptions as the cmd holds locks on the file.
There are all sorts of techniques for writing to a file, this example uses a StreamWriter. Hit up google for different examples.

Running sql queries from Powershell

January 21, 2015 by boro

Have you ever needed to test out the performance of sets of sql queries?

Recently we had some performance issues on a live infrastructure which needed diagnosing. Getting code released was slightly tricky so instead we concocted some scripts to allow us to run sql queries from Powershell. The advantage of these, we could run from different boxes in the farm to try and isolate where the issues arrived.

The key settings in this setup are:

# Specify SQL Connection String. 
$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;" 
$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;" 
# Specify SQL Connection String. 

$runs = 10

# Specify SQL Connection String.

$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;"

$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;"

# Specify SQL Connection String.

$runs = 10

The connection strings are much like your standard application connection strings.

To then run in bulk you can use the script shown below. Note, adjust the specific queries you want to execute:

function ExecNonQuery 
{ 
    param ($conStr, $cmdText) 
   
    # Determine if parameters were correctly populated. 
    if (!$conStr -or !$cmdText) 
    { 
        # One or more parameters didn't contain values. 
        write-Host "ExecNonQuery function called with no connection string and/or command text." 
    } 
    else 
    { 
        write-Host "Creating SQL Connection..." 
        # Instantiate new SqlConnection object. 
        $Connection = New-Object System.Data.SQLClient.SQLConnection 
         
        # Set the SqlConnection object's connection string to the passed value. 
        $Connection.ConnectionString = $conStr 
         
        # Perform database operations in try-catch-finally block since database operations often fail. 
        try 
        { 
            #write-Host "Opening SQL Connection..." 
            # Open the connection to the database. 
            $Connection.Open() 
             
            #write-Host "Creating SQL Command..." 
            # Instantiate a SqlCommand object. 
            $Command = New-Object System.Data.SQLClient.SQLCommand 
            # Set the SqlCommand's connection to the SqlConnection object above. 
            $Command.Connection = $Connection 
            # Set the SqlCommand's command text to the query value passed in. 
            $Command.CommandText = $cmdText 
             
            #write-Host "Executing SQL Command..."   
            # Note, to see a result back here, ExecuteNonQuery has been changed to ExecuteScalar
            write-host $Command.ExecuteScalar() 
        } 
        catch [System.Data.SqlClient.SqlException] 
        { 
            # A SqlException occurred. According to documentation, this happens when a command is executed against a locked row. 
            #write-Host "One or more of the rows being affected were locked. Please check your query and data then try again." 
        } 
        catch 
        { 
            # An generic error occurred somewhere in the try area. 
            write-Host "An error occurred while attempting to open the database connection and execute a command." 
        } 
        finally { 
            # Determine if the connection was opened. 
            if ($Connection.State -eq "Open") 
            { 
                write-Host "Closing Connection..." 
                # Close the currently open connection. 
                $Connection.Close() 
            } 
        } 
    } 
} 

 
# Specify SQL Connection String. 
$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;" 
$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;" 
# Specify SQL Connection String. 

$runs = 10
$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()

for ($i=0; $i -le $runs; $i++)
{
ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_Users" 

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_UsersInRoles" 

ExecNonQuery -conStr $core -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [TemplateID]=@p0 OR [TemplateID]=@p1 OR [TemplateID]=@p2 OR [TemplateID]=@p3 OR [TemplateID]=@p4 OR [TemplateID]=@p5 OR [TemplateID]=@p6 OR [ID]=@p7 OR [ParentID]=@p8 OR [TemplateID]=@p9 OR [TemplateID]=@p10 OR [ParentID]=@p11) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier',@p0='AB86861A-6030-46C5-B394-E8F99E8B87DB',@p1='E269FBB5-3750-427A-9149-7AA950B49301',@p2='455A3E98-A627-4B40-8035-E683A0331AC7',@p3='239F9CF4-E5A0-44E0-B342-0F32CD4C6D8B',@p4='A87A00B1-E6DB-45AB-8B54-636FEC3B5523',@p5='F68F13A6-3395-426A-B9A1-FA2DC60D94EB',@p6='B6F7EEB4-E8D7-476F-8936-5ACE6A76F20B',@p7='11111111-1111-1111-1111-111111111111',@p8='11111111-1111-1111-1111-111111111111',@p9='EB06CEC0-5E2D-4DC4-875B-01ADCC577D13',@p10='B2A1FF6C-6B1F-41EF-B2E1-AA598CF7335F',@p11='C74AC643-53C8-4F1E-9508-840CDC72AACA'" 
 
ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [ParentID] = @itemId) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='734FB52F-33E3-4BCF-ADA9-27F1A4D8B4E1'" 

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [ParentID] = @itemId) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='11A81FBC-77FB-40D9-A3B3-0078106B07FC'" 
 
write-Host "Query Complete!"
}

$stopWatch.Stop()

Write-Host "total " $stopWatch.ElapsedMilliseconds "ms"
Write-Host "average " ($stopWatch.ElapsedMilliseconds/$runs) "ms"

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

function ExecNonQuery

{

param ($conStr, $cmdText)

# Determine if parameters were correctly populated.

if (!$conStr -or !$cmdText)

{

# One or more parameters didn't contain values.

write-Host "ExecNonQuery function called with no connection string and/or command text."

}

else

{

write-Host "Creating SQL Connection..."

# Instantiate new SqlConnection object.

$Connection = New-Object System.Data.SQLClient.SQLConnection

# Set the SqlConnection object's connection string to the passed value.

$Connection.ConnectionString = $conStr

# Perform database operations in try-catch-finally block since database operations often fail.

try

{

#write-Host "Opening SQL Connection..."

# Open the connection to the database.

$Connection.Open()

#write-Host "Creating SQL Command..."

# Instantiate a SqlCommand object.

$Command = New-Object System.Data.SQLClient.SQLCommand

# Set the SqlCommand's connection to the SqlConnection object above.

$Command.Connection = $Connection

# Set the SqlCommand's command text to the query value passed in.

$Command.CommandText = $cmdText

#write-Host "Executing SQL Command..."

# Note, to see a result back here, ExecuteNonQuery has been changed to ExecuteScalar

write-host $Command.ExecuteScalar()

}

catch [System.Data.SqlClient.SqlException]

{

# A SqlException occurred. According to documentation, this happens when a command is executed against a locked row.

#write-Host "One or more of the rows being affected were locked. Please check your query and data then try again."

}

catch

{

# An generic error occurred somewhere in the try area.

write-Host "An error occurred while attempting to open the database connection and execute a command."

}

finally {

# Determine if the connection was opened.

if ($Connection.State -eq "Open")

{

write-Host "Closing Connection..."

# Close the currently open connection.

$Connection.Close()

}

# Specify SQL Connection String.

$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;"

$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;"

# Specify SQL Connection String.

$runs = 10

$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()

for ($i=0; $i -le $runs; $i++)

{

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_Users"

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_UsersInRoles"

ExecNonQuery -conStr $core -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [TemplateID]=@p0 OR [TemplateID]=@p1 OR [TemplateID]=@p2 OR [TemplateID]=@p3 OR [TemplateID]=@p4 OR [TemplateID]=@p5 OR [TemplateID]=@p6 OR [ID]=@p7 OR [ParentID]=@p8 OR [TemplateID]=@p9 OR [TemplateID]=@p10 OR [ParentID]=@p11)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier',@p0='AB86861A-6030-46C5-B394-E8F99E8B87DB',@p1='E269FBB5-3750-427A-9149-7AA950B49301',@p2='455A3E98-A627-4B40-8035-E683A0331AC7',@p3='239F9CF4-E5A0-44E0-B342-0F32CD4C6D8B',@p4='A87A00B1-E6DB-45AB-8B54-636FEC3B5523',@p5='F68F13A6-3395-426A-B9A1-FA2DC60D94EB',@p6='B6F7EEB4-E8D7-476F-8936-5ACE6A76F20B',@p7='11111111-1111-1111-1111-111111111111',@p8='11111111-1111-1111-1111-111111111111',@p9='EB06CEC0-5E2D-4DC4-875B-01ADCC577D13',@p10='B2A1FF6C-6B1F-41EF-B2E1-AA598CF7335F',@p11='C74AC643-53C8-4F1E-9508-840CDC72AACA'"

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='734FB52F-33E3-4BCF-ADA9-27F1A4D8B4E1'"

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='11A81FBC-77FB-40D9-A3B3-0078106B07FC'"

write-Host "Query Complete!"

}

$stopWatch.Stop()

Write-Host "total " $stopWatch.ElapsedMilliseconds "ms"

Write-Host "average " ($stopWatch.ElapsedMilliseconds/$runs) "ms"

blog.boro2g .co.uk

Some ideas about coding, dev and all things online.

Category Archives: powershell

Octopus deploy – Script Steps sourced from a package

How to write a basic powershell module that includes multiple scripts

Debugging Azure web-role startup tasks

Running sql queries from Powershell