Tuesday, January 10, 2012

Query SQL Server using PowerShell

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
$adapter.Fill($set)

#Instantiate a DataTable
$table = new-object system.data.datatable

#This table gets the value of the first table in the DataSet
$table = $set.Tables[0]

#This line will auto format our table
$table | format-table -AutoSize


Enjoy

No comments:

Post a Comment