---
title: Simple select from database with PowerShell
date: 2015-07-14 14:14:00 +0200 CEST
draft: false
author: John Roos
----

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"

That’s how simple it can be to use a select statement with SQL Server.

Full script

# Variables for server, database and query
$server   = 'localhost\sqlexpress'
$database = 'test'
$query    = 'select top 10 * from fjutt'

# Set up the objects needed
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$adapter    = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
$dataset    = New-Object -TypeName System.Data.DataSet

# Set the connection string (using single sign on) and open the connection
$connection.ConnectionString = "Server=$server;Database=$database;Trusted_Connection=True;"
$connection.Open()

# Create a command object and assign the query
$command = $connection.CreateCommand()
$command.CommandText = $query

# Fill the dataset and count the rows returned at the same time
$rowCount = $adapter.Fill($dataset)

# Since the dataset is populated we can now close the connection
$connection.Close()

# Return the results
$dataset.Tables.rows

# Print amount of rows to screen (optional)
Write-Host "Rows returned: $rowCount"