Returning Results from SQL as a Table

Description

A great powershell script from "Greg" that shows how to produce tabulated results from a SQL query.

Source Code

function Get-Sql {
param ([string]$conn, [string]$sql)

# setup connection
$connObj = new-object System.Data.SqlClient.SqlConnection
$connObj.ConnectionString = $conn
# setup command $cmdObj = new-object System.Data.SqlClient.SqlCommand $cmdObj.Connection = $connObj
$cmdObj.CommandText = $sql
$connObj.Open()
$reader = $cmdObj.ExecuteReader()
while ($reader.Read()) {
$result = new-object System.Management.Automation.MshObject
for ($i=0;$i -lt ($reader.FieldCount) ;$i++) {
$fld = new-object System.Management.AUtomation.MshNoteProperty $reader.GetName($i), $reader[$i] $result.MshObject.Members.Add($fld); }
$result
}
$reader.Close()
$reader.Dispose()
$connObj.Close();
}
$connectionString = "Server=(local);Database=AdventureWorks;Integrated Security=SSPI"
$sqlText = "select top 10 * from Person.Contact"
Get-Sql $connectionString $sqlText format-table