Thursday, February 05, 2009

T-SQL query with object-based result

Two small functions that can be used to make an T-SQL query, and have the result returned as a standard PowerShell object. This can be useful if - for some reason - you would like to use Sort-Object or similar on the result afterwards.

function ConnectSQL {
    Param ($server, $query, $database)
    $conn = new-object ('System.Data.SqlClient.SqlConnection')
    $connString = "Server=$server;Integrated Security=SSPI;Database=$database"
    $conn.ConnectionString = $connString
    $conn.Open()
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    ,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

function QuerySQL {
    Param ($server, $query, $database = "master")
    $data = ConnectSQL $server $query $database
    while ($data.read() -eq $true) {
        $max = $data.FieldCount -1
        $obj = New-Object Object
        For ($i = 0; $i -le $max; $i++) {
            $name = $data.GetName($i)
            $obj | Add-Member Noteproperty $name -value $data.GetValue($i)
     }
     $obj
    }
}

The function is used like this:

QuerySQL "MyServer" "SELECT * FROM sysdatabases"

Or maybe more like this:

$data = QuerySQL "MyServer" "SELECT * FROM sysdatabases"
$data | Format-Table -AutoSize

2 comments:

Baodad said...

Thanks! This is just what I was looking for: the ability to query SQL data into a Powershell object for further manipulation and work.

Shinigami said...

Absolutely beautiful!!! This is exactly what I needed!!!