Simple select from database with PowerShell

There are a few ways of getting data from SQL Server in PowerShell. This is one way of doing it which is quite simple and does not require the SQL Server module. The sample below shows how to get started. If you are planning on creating a script for serious use you need to add error handling and investigate a bit more what each line actually does so that you are in full control. Note that this is only for select statements. Insert, update and delete works slightly different, but I might get to that in the future.

To get started I have added three variables which you can use to adapt to your environment.
$server   = 'localhost\sqlexpress'
$database = 'myDB'
$query    = 'select * from myTable'
After that we need to set up the objects needed to communicate with the database.
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$adapter    = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
$dataset    = New-Object -TypeName System.Data.DataSet
Next is the connection string, which should work with most installations of SQL Server. After the connection string is created we open the connection to the server.
$connection.ConnectionString = "Server=$server;Database=$database;Trusted_Connection=True;"
$connection.Open()
The query need to be a proper command object, so lets do that.
$command = $connection.CreateCommand()
$command.CommandText = $query
Fill the dataset with the result from the database. Lets also count the rows while we are at it.
$rowCount = $adapter.Fill($dataset)
Now we dont need the connection to the database anymore, so lets close it.
$connection.Close()
Now we are done! Return the rows.
$dataset.Tables.rows
Optionally you can also print the row count to the screen.
Write-Host "Rows returned: $rowCount"
Thats how simple it can be to use a select statement with SQL Server.

Full script

No comments

Post a Comment