PowerShell: CPM – Parameter Management Module

In the intro I discussed my need to manage all script parameters centrally. To do this I want to create a PowerShell module to manage the parameters and to store the data in a SQL database.

In my previous articles I explained how I created a new SQL table, a new custom data-type, and simplified collecting data from a SQL database.

So now it is time for the final step: creating the management module.

Managing the SParameter object

The module will enable you to set, create and delete custom parameter objects.

Note: I tampered with the layout a bit and left out the comment based help of the cmdlets so they are more readable, but the help is included in the downloadable package on my SkyDrive.

New-SParameter

To work with these new custom parameter objects we first need to be able to create a new instance of the SManagement.SParameter object as defined in the .cs-file.
The New-SParameter cmdlet takes the name and value of the custom parameter as input and also has the option to set a description. It returns a SManagement.SParameter object.

Function New-SParameter {
	[CmdletBinding()]
	Param(
		[Parameter(
			Mandatory = $true,
			Position = 1,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $true
		)] [string] $Name,
		[Parameter(
			Mandatory = $true,
			Position = 2,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $true
		)] [string] $Value,
		[Parameter(
			Mandatory = $false,
			Position = 3,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $true
		)] [string] $Description
	)
	Begin {}
	Process {
		# Create the new Parameter object
		$objParameter = New-Object SManagement.SParameter
		# Set Properties
		$objParameter.Name = $Name
		$objParameter.Value = $Value
		$objParameter.Description = $Description
		
		# Return object
		Write-Output $objParameter
	}
	End {}
}

Get-SParameter

The Get-SParameter cmdlet can either return all SParameters stored in the SQL database or uses its input to query the SQL database and return an array of SParameters that have the given input value in their name or ID.
To return these SParameter objects the Get-SParameter cmdlet will retrieve the data from the SQL database, using the Get-SSQLConnection and Get-SSQLDataTable cmdlets, and put this data into a new SParameter instance who are then added to the array.

Function Get-SParameter {
	[CmdletBinding()]
	Param(
		[Parameter(
			Mandatory = $false,
			Position = 0
		)]
		[Alias("Name","ID")]
		[string] $InputString,
		[switch] $All
	)
	Begin {
		$SQL_DBScriptParameter = 'databaseNameScriptParameter'
		$SQL_TBLScriptParameter = 'tableNameScriptParameter'
		$SQL_SParameterPropName = 'Name'
		$SQL_SParameterPropValue = 'Value'
		$SQL_SParameterPropDescription = 'Description'
	}
	Process {
		$arrayReturnObject = @()
		if ($All) {
			$strQuery = "
				SELECT *
				FROM $SQL_TBLScriptParameter"
			$tblPar = Get-SSQLDataTable (
					Get-SSQLConnection $SQL_DBScriptParameter
				) $strQuery
			$arrayReturnObject = ($tblPar.Rows | New-SParameter)
		} else {
			if ($InputString.Length -eq 0){
				throw "No Parameter specified!"
			} else {
				$strQuery = "
					SELECT *
					FROM $SQL_TBLScriptParameter
					WHERE $SQL_SParameterPropName
					LIKE `'%$InputString%`'"
				$tblPar = Get-SSQLDataTable (
						Get-SSQLConnection $SQL_DBScriptParameter
					) $strQuery
				$arrayReturnObject = (
					$tblPar.Rows | New-SParameter
				)
			}
		}
		Write-Output $arrayReturnObject
	}
	End {}
}

Set-SParameter

The Set-SParameter cmdlet will use the values from an SParameter object to set a global variable in the current session.
It can take either a string or an SParameter object as input. If the input is a string the input will be piped to the Get-SParameter cmdlet and all returned SParameter objects will be set.

Function Set-SParameter {
	[CmdletBinding()]
	Param(
		[Parameter(
			Mandatory = $false,
			Position = 1,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $true
		)]
		[Alias("Name","ID")]
		[string] $InputString,
		[Parameter(
			Mandatory = $false,
			ValueFromPipeline = $true,
			ValueFromPipelineByPropertyName = $false
		)] [SManagement.SParameter] $Parameter
	)
	Begin {}
	Process {
		$objToSet = $null
		if($Parameter -ne $null){
			$objToSet = $Parameter
		} elseif($InputString -ne $null){
			$objToSet = (Get-SParameter $InputString)
		} else {
			throw "Invalid request"
		}
		foreach($objItem in $objToSet){
			Write-Verbose ($objItem.Name + " = " + $objItem.Value)
			Set-Variable `
				-Name $objItem.Name `
				-Value $objItem.Value `
				-Visibility 'Public' `
				-Scope "Global"
		}
	}
	End {}
}

Add-SParameter

The Add-SParameter cmdlet will use the given input to create an entry in the SQL Database.
The SParameter instance can then be retrieved by using the Get-SParameter cmdlet with the name of the new SParameter object as input.

Function Add-SParameter {
	[CmdletBinding()]
	Param(
		[Parameter(
			Mandatory = $true,
			Position = 1,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $false
		)]
		[ValidateScript({$_.IndexOf('$') -eq -1})] 
		[string] $Name,
		[Parameter(
			Mandatory = $true,
			Position = 2,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $false
		)] [string] $Value,
		[Parameter(
			Mandatory = $false,
			Position = 3,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $false
		)] [string] $Description
	)
	Begin {
		$SQL_DBScriptParameter = 'databaseNameScriptParameter'
		$SQL_TBLScriptParameter = 'tableNameScriptParameter'
		$SQL_SParameterPropName = 'Name'
		$SQL_SParameterPropValue = 'Value'
		$SQL_SParameterPropDescription = 'Description'
	}
	Process {
		$objCon = Get-SSQLConnection $SQL_DBScriptParameter
		$objCon.Open()
		$objCmd = $objCon.CreateCommand()
		$objCmd.CommandText = "
			INSERT INTO $SQL_TBLScriptParameter (
				$SQL_SParameterPropName,
				$SQL_SParameterPropValue,
				$SQL_SParameterPropDescription
			)
			VALUES (
				`'$Name`',
				`'$Value`',
				`'$Description`'
			)"
		$objCmd.ExecuteNonQuery()
		$objCon.Close()
	}
	End {}
}

Remove-SParameter

The Remove-SParameter cmdlet will use the given input to remove an entry from the SQL Database.
It takes either a string or an SParameter object as input. If the input is a string the input will be piped to the Get-SParameter cmdlet and all returned SParameter objects will be removed.

Function Remove-SParameter {
	[CmdletBinding(
		SupportsShouldProcess=$true,
		ConfirmImpact="High"
	)]
	Param(
		[Parameter(
			Mandatory = $false,
			Position = 1,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $true
		)]
		[ValidateScript({$_.IndexOf('$') -eq -1})]
		[string] $Name,
		[Parameter(
			Mandatory = $false,
			ValueFromPipeline = $true,
			ValueFromPipelineByPropertyName = $false
		)] [SManagement.SParameter] $Parameter
	)
	Begin {
		$SQL_DBScriptParameter = 'databaseNameScriptParameter'
		$SQL_TBLScriptParameter = 'tableNameScriptParameter'
		$SQL_SParameterPropName = 'Name'
		$SQL_SParameterPropValue = 'Value'
		$SQL_SParameterPropDescription = 'Description'
	}
	Process {
		$objToSet = $null
		if($Parameter -ne $null){
			$objToSet = (Get-SParameter $Parameter.Name)
			Write-Verbose ("Parameter: " + $objToSet)
		} elseif($Name -ne $null){
			$objToSet = (Get-SParameter $Name)
			Write-Verbose ("Name: " + $objToSet)
		} else {
			throw "Invalid request"
		}
		$objCon = Get-SSQLConnection $SQL_DBScriptParameter
		$objCon.Open()
		foreach($objItem in $objToSet){
			Write-Verbose ("Removing " + $objItem.Name)
			$strName = $objItem.Name
			$strValue = $objItem.Value
			$objCmd = $objCon.CreateCommand()
			$objCmd.CommandText = "
				DELETE FROM $SQL_TBLScriptParameter 
				WHERE $SQL_SParameterPropName=`'$strName`'
				AND $SQL_SParameterPropValue=`'$strValue`'"
			if ($pscmdlet.ShouldProcess($objItem.Name)) {
				$objCmd.ExecuteNonQuery()
			}
		}
		$objCon.Close()
	}
	End {}
}

Result

The result is a centralised parameter management using SQL and a PowerShell module.
See all the steps and a download link on the intro page of Centralised Parameter Management.

Advertisements

Tags: , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: