How to query a SQL database with Powershell

You can use PowerShell to read or execute query to various database like SQL, MySQL, or SQLite.

The following example shows you one of the possibilities of retrieving data from a SQL database:


[string]$db_server = "My_SQL_Server"
[string]$db_name = "My_Database_Name"
[string]$db_query = "My SQL Query"
[string]$db_connectionstring = "Data Source=$db_server;Database=$db_name;Integrated Security=true;"
$db_data_adapter = New-Object System.Data.SqlClient.SqlDataAdapter($db_query,$ db_connectionstring )
$db_dataset = New-Object System.Data.DataSet
[void]$db_data_adapter.Fill($db_dataset)
$db_dataset.Tables[0]

Let's have a look at the different steps involved in this script.

First of all, you need to define your 3 parameters: location of the database, database name and your SQL query.

[string]$db_server = "My_SQL_Server"
[string]$db_name = "My_Database_Name"
[string]$db_query = "My SQL Query"

Using the first two parameters, you'll be able to create your connection string:
[string]$db_connectionstring = "Data Source=$db_server;Database=$db_name;Integrated Security=true;"


Integrated Security = true means you'll use the Windows account running the script to authenticate on the database; if another type of authentication is required, look at System.Data.SqlClient.SqlCredential.

You create a Data Adapter object to retrieve the data from the database mentioned in the connection string using the query (3rd parameter):
$db_data_adapter = New-Object System.Data.SqlClient.SqlDataAdapter($db_query,$ db_connectionstring )

Then, you initiate a DataSet object in order to receive the data from the Data Adapter
$db_dataset = New-Object System.Data.DataSet

You store the data from the Data Adapter to the DataSet object using the Fill method:
[void]$db_data_adapter.Fill($db_dataset)

And finally, you display the result:
$db_dataset.Tables[0]

Another option should be to use the SqlDataReader but it seems to be less efficient and also slower; it keeps the database connection opened longer than the SqlDataAdapter.

Once your script gets more complicated, you might need to use the SqlConnection and SqlCommand objects to gain more control about within your script and connection, but for a simple query, this solution will do it.

Hope this was helpful.