PowerShell : CPM – SQL Connection

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.

I started by creating the database and in my previous article I discused how to define a new custom parameter data-type.

In this article I will simplify working with a SQL database.

Reading SQL Data

To easily work with SQL databases I created 2 cmdlets to simplify things.

Get-SSQLConnection

First cmdlet returns a System.Data.SqlClient.SQLConnection object that is ready to connect to any given database on my SQL Server.

Function Get-SSQLConnection {
	[CmdletBinding()]
	Param(
		[Parameter(
			Mandatory = $true,
			Position = 0,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $false
		)] [string] $Database
	)
	Begin {
		$SRV_SQL = 'sqlServerDNSName'
	}
	Process {
		$objConnection = New-Object System.Data.SqlClient.SQLConnection
		$objConnection.ConnectionString = "Server=$SRV_SQL;Database=$Database;Integrated Security=True;Connect Timeout=15"
		Write-Output $objConnection
	}
	End {}
}

Get-SSQLDataTable

The second cmdlet requires a System.Data.SqlClient.SQLConnection object, which I can create using the Get-SSQLConnection cmdlet, and a SQL-query.
This cmdlet returns a System.Data.DataSet object containing the results of the SQL-query.
The System.Data.DataSet object alows you to easily acces and manipulate the data in the resultset.

Function Get-SSQLDataTable {
	[CmdletBinding()]
	Param(
		[Parameter(
			Mandatory = $true,
			Position = 1,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $false
		)] [System.Data.SqlClient.SQLConnection] $Connection,
		[Parameter(
			Mandatory = $true,
			Position = 2,
			ValueFromPipeline = $false,
			ValueFromPipelineByPropertyName = $false
		)] [string] $Query
	)
	Begin {}
	Process {
		$Connection.Open()
		$objCommand = New-Object System.Data.SqlClient.SqlCommand($Query,$Connection)
		$objDataSet = New-Object system.Data.DataSet
		$objAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($objCommand)
		[void]$objAdapter.Fill($objDataSet)
		$Connection.Close()
		Write-Output $objDataSet.Tables
	}
	End {}
}

Parameter Management Module

Now we have a custom object-type, a SQL Database and an easy way to collect data from this database. Lets start making the actual management module.
Next step: Parameter Management Module

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: