I don’t understand why it was so difficult for me to find an example on this, so I compiled one from three posts so I could put it here:
[string]$query = "SELECT TOP 10 * FROM TableName "
#I'm using integrated security here:
$connString = "Data Source=localhost,port;Initial Catalog=DBNameHere;Integrated Security=True"
#If you want to use a UN/PW use this code:
#$connString = "Data Source=localhost,port;Initial Catalog=DBNameHere; uID=userName; password=1234"
#Create a new connection object like you would always
$connection = new-object system.data.sqlclient.sqlconnection($connString);
#Setup the adapter object like you would always
$adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
#Instantiate a DataSet (I included this just for the demo, I usually use a DataTable directly)
$set = new-object system.data.dataset
#Fill the DataSet
#Instantiate a DataTable
$table = new-object system.data.datatable
#This table gets the value of the first table in the DataSet
$table = $set.Tables
#This line will auto format our table
$table | format-table -AutoSize