SQL queries

There aren't that much written about using PowerShell as a channel for T-SQL, available on the web, so I thought I might give it a shot.

The scripts below allows you to query a SQL Server (SELECT statements) and use the returned data, as well as perform queries that doesn't return data (INSERT, UPDATE, DBCC *, etc.).
The way these functions are written are in no way ideal, but rather the minimum amount of scripting required to solve needs I've had in certain scripting scenarios. The scripts below include a limited ability to return errors.


The way to use the functions can be described this way:

1. Use New-SQLconnection to establish a connection to a SQL server.
2. Execute Query-SQL or NonQuery-SQL to perform a query against the server.
3. Use Close-SQLquery to close the query.
4. repeat 2. and 3. as many times as needed.
5. Use Remove-SQLconnection to close the connection.

It is only possible to have one open query (ie. not Close-SQLquery'ed) at a
time, but you can have as many SQLconnections as you need.


First we need the ability to create a connection to a SQL server:

Usage:
PS> $conn = New-SQLconnection "Server\Instance" "Database"
Check the state property on the returned conenction to determine if the connection was successfully established:
PS> if ($conn.state -eq "closed") {# Connection failed - handle error}

function New-SQLconnection {
    Param ([string]$server,
        [string]$database = "master",
        [string]$connectionName = $ScriptName)
    if (test-path variable:\conn) {
        $conn.close()
    } else {
        $conn = new-object ('System.Data.SqlClient.SqlConnection')
    }
    $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
    $conn.ConnectionString = $connString
    $conn.StatisticsEnabled = $true
    $conn.Open()
    $conn
}

Then we should be able to perform a query:

Usage:
PS> Query-SQL "SELECT @@version" $conn
If the query returns zero rows "$data -eq $null" will evaluate to true.
If the query cannot be completed the returned object will be of type [int]

function Query-SQL {
    Param ($query, $conn, [int]$CommandTimeout = 30)
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = $CommandTimeout
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    if ($? -eq $false) {
        Close-SQLquery $rset
        [int]$rset = -1
    }
    if ($Rset.hasrows -eq $false) {
        Close-SQLquery $rset
        [string]$rset = ""
    }
    ,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

Execute a SQL statement, where the information returned in on a "x rows affected" level:
If the query returns zero rows "$data -eq $null" will evaluate to true.
If the query cannot be completed the returned object will be of type [int]
Use "if ($data -is [int]) {# handle error}"
The value -1 is returned if a statement that doesnt' return a "x rows affected" is executed
The value -2 is returned if an error occurs.

Usage:
PS> NonQuery-SQL "SELECT @@version" $conn

function NonQuery-SQL {
    Param ($query, $conn, $CommandTimeout = 30)
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = $CommandTimeout
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $RowsAffected = $sqlCmd.ExecuteNonQuery()
    if ($? -eq $false) {
        $RowsAffected = -2
    }
    $RowsAffected
}

Close down an existing query (should be performed after every query):

Usage:
PS> Close-SQL $data

function Close-SQLquery {
    Param ($query)
    $query.close()
    $query = $null
}

And finally close an established connection to a SQL Server:

Usage:
PS> Remove-SQLconnection $conn

function Remove-SQLconnection {
    Param ($connection)
    $connection.close()
    $connection = $null
}


Here is a complete example of how to use the functions above to do a simple query and create a new database:

$ScriptName = $myInvocation.MyCommand.Name
$Server = "MyServer\MyInstance"

$myConn = New-SQLconnection $Server "master"
if ($x.state -eq "closed") {"Failed to establish a connection";EXIT}
Write-Host "First Query" -back magenta
$query = "SELECT fileid, filename, size FROM sysfiles"
$data = Query-SQL $query $myConn
if ($data.gettype() -eq [int]) {"Failed to query SQL server";EXIT}
if ($data.gettype() -eq [string]) {"Zero rows returned";EXIT}
while($data.Read()){
    Write-Host $data.GetValue(0) -back yellow
    Write-Host $data.GetString($data.GetOrdinal("filename")) -back yellow
    Write-Host $data.GetValue(2) -back yellow
}

Close-SQLquery $data

Write-Host "Second Query" -backgroundcolor magenta
$query = "SELECT TOP 5 name, description, backup_start_date FROM msdb.dbo.backupset"
$data = Query-SQL $query $myConn
if ($data.gettype() -eq [int]) {"Failed to query SQL server";EXIT}
if ($data.gettype() -eq [string]) {"Zero rows returned";EXIT}
while($data.Read()){
    Write-Host $data.GetValue(2) -back red
    Write-Host $data.GetValue(1) -back red
    Write-Host $data.GetValue(0) -back red
}

Close-SQLquery $data

Write-Host "First NonQuery" -back magenta
$query = "CREATE DATABASE [MyFirstTestDB] "
$data = NonQuery-SQL $query $myConn
if ($data -eq -2) {
    Write-Host "Run away! Something has gone wrong!!!"
} else {
    Write-Host "$data Rows Affected"
}

Remove-SQLconnection $myConn