MSH + SQL Server

Hi niners,

Came up with a little MSH script (MSH is sooo cool) that uses sql to retrieve a list of object that can be used together with all the other command line tools.

A simple call:

$connectionString = "Server=(local)\SQLEXPRESS;Database=AdventureWorks;Integrated Security='SSPI'"
$sqlText = "SELECT * FROM Production.Product"

Get-Sql $connectionString $sqlText

Format as table

Get-Sql $connectionString $sqlText | format-table

Export CSV

Get-Sql $connectionString $sqlText | export-csv "test.csv"

Loop Thru Items

$products = (Get-Sql $connectionString $sqlText)

foreach ($product in $products) {
 write-host $product.Name
}

Heres the actual function:

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();
}


Enjoy