Windows PowerShell: Access Databases from PowerShell

You can configure Windows PowerShell to use data stored in a SQL Server database. Here’s how.

Don Jones

One question I’m asked all the time is how to have Windows PowerShell use data from a database, such as a SQL Server-hosted database. In fact, that question comes up so frequently that I decided to write a chapter about just how to do this for my self-published “Windows PowerShell Scripting and Toolmaking” (lulu.com, 2011).

My solution was to create two functions—one for querying data and another for changing data (insert, delete and update tasks). That way, the functionality is self-contained and you can easily reuse it between projects. Because database capabilities are built into the Microsoft .NET Framework, you don’t need to install anything beyond Windows PowerShell itself for these to work.

Database Functions

To make these functions easier to use, save them as a module. For example, I saved mine in \MyDocuments\WindowsPowerShell\Modules\DataTools\DataTools.psm1.

Make sure you save this to My Documents, not Shared Documents. That way Windows PowerShell can find it correctly. Then, to load the file, just run Import-Module DataTools (see Figure 1).

Figure 1 Saving and running this function as a module makes it easier to find.

function Get-DatabaseData {
	[CmdletBinding()]
	param (
		[string]$connectionString,
		[string]$query,
		[switch]$isSQLServer
	)
	if ($isSQLServer) {
		Write-Verbose 'in SQL Server mode'
		$connection = New-Object-TypeName System.Data.SqlClient.SqlConnection
	} else {
		Write-Verbose 'in OleDB mode'
		$connection = New-Object-TypeName System.Data.OleDb.OleDbConnection
	}
	$connection.ConnectionString = $connectionString
	$command = $connection.CreateCommand()
	$command.CommandText = $query
	if ($isSQLServer) {
		$adapter = New-Object-TypeName System.Data.SqlClient.SqlDataAdapter $command
	} else {
		$adapter = New-Object-TypeName System.Data.OleDb.OleDbDataAdapter $command
	}
	$dataset = New-Object-TypeName System.Data.DataSet
	$adapter.Fill($dataset)
	$dataset.Tables[0]
}
function Invoke-DatabaseQuery {
	[CmdletBinding()]
	param (
		[string]$connectionString,
		[string]$query,
		[switch]$isSQLServer
	)
	if ($isSQLServer) {
		Write-Verbose 'in SQL Server mode'
		$connection = New-Object-TypeName System.Data.SqlClient.SqlConnection
	} else {
		Write-Verbose 'in OleDB mode'
		$connection = New-Object-TypeName System.Data.OleDb.OleDbConnection
	}
	$connection.ConnectionString = $connectionString
	$command = $connection.CreateCommand()
	$command.CommandText = $query
	$connection.Open()
	$command.ExecuteNonQuery()
	$connection.close()
}

To use these, just call either the Get-DatabaseData or the Invoke-DatabaseQuery commands. Each of those commands has an –isSQLServer parameter you have to use when querying a SQL Server. Otherwise, Windows PowerShell will assume you’re using a non-SQL Server OleDB source. You must also provide a –connectionString parameter and a –query parameter.

For example, to delete all data in a table named MyTable in a database named MyDatabase on a SQL Server machine named Server1, using a SQL Server non-default instance named SQLEXPRESS, I would run the following command (note that I’m providing a SQL Server-style login and password, instead of an integrated login):

Invoke-DatabaseQuery –query "DELETE FROM MyTable" –isSQLServer –connectionString "Data Source=SERVER1\SQLEXPRESS;Initial Catalog=MyDatabase;User Id=myLogin;Password=P@ssw0rd"

Here’s a hint: I can never remember the connection string syntax. I don’t even try. I just go to connectionstrings.com and look up the example I need.

The Invoke-DatabaseQuery command doesn’t produce any output. The command Get-DatabaseData does. Specifically, it will return a DataTable. That’s essentially a collection of objects where each object is a row from your query results. Each object’s properties correspond to the columns of your query results.

Testing, Testing

Let’s run through a complete test. Use the free SQL Server Express Edition, which installs itself to a named instance called SQLEXPRESS. I’ve downloaded and installed the version that includes the Express Management Studio tool. This all happened on my local computer. I’ll rely on Windows authentication for this example, instead of SQL Server authentication.

I open Management Studio and connect to the localhost\SQLEXPRESS instance (I was logged on as Administrator and just used that credential to access the database server). I open a new query window and run the the query shown in Figure 2 to create a sample database.

Figure 2. Create a sample database.

CREATE DATABASE [Inventory] ON  PRIMARY 
( NAME = N'Inventory', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Inventory.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'Inventory_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Inventory_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE [Inventory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Computers](
[computer] [nvarchar](50) NULL,
[osversion] [nvarchar](100) NULL,
[biosserial] [nvarchar](100) NULL,
[osarchitecture] [nvarchar](5) NULL,
[procarchitecture] [nvarchar](5) NULL
) ON [PRIMARY]

GO

Next, I delete the query shown in Figure 2 from the query window and run the following query:

Use [Inventory]
Go
INSERT INTO Computers (computer) VALUES ('localhost')
INSERT INTO Computers (computer) VALUES ('localhost')
INSERT INTO Computers (computer) VALUES ('not-online')

That adds three rows of data to the table. Then I switch to Windows PowerShell. After importing the DataTools module into my shell, I run the following commands:

Get-DatabaseData -verbose -connectionString 'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' -isSQLServer-query "SELECT * FROM Computers"
Invoke-DatabaseQuery -verbose -connectionString 'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' -isSQLServer-query "INSERT INTO Computers (computer) VALUES('win7')"
Get-DatabaseData -verbose -connectionString 'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' -isSQLServer-query "SELECT * FROM Computers"

This confirms the table was initially populated with three computers, and I successfully added another.

Databases Made Easy

Putting the work into making these two functions saves me a lot of time. Whenever I need to read or write to any database, I just import my module and it’s ready to go.

You don’t have to store the module in the specific folder I mentioned here. You can save it anywhere, if you’re willing to specify the full path of the .psm1 file when running Import-Module. However, by using that specific folder, I can just import the module by name, without specifying a path. That’s another time-saver.

Don Jones

Don Jones is a Microsoft MVP Award recipient and author of “Learn Windows PowerShell in a Month of Lunches” (Manning Publications, 2011), a book designed to help any administrator become effective with Windows PowerShell. Jones also offers public and on-site Windows PowerShell training. Contact him through his Web site at ConcentratedTech.com, or bit.ly/AskDon.