Database Access within PowerShell

Want to use PowerShell to read or write to a database? Here's a quick-and-dirty tutorial:

First, you're going to need a connection string, which tell's the .NET Framework's database bits where to find your database. I use ConnectionStrings.com to look them up. Next, you may need to load the actual database bits into the shell:

[system.reflection.assembly]::LoadWithPartialName("System.Data")

Third, create a Connection. Now, a connection can only be used for one operation at a time - reading and making changes are two different things, so if you want to be reading and writing data all at once, you'll need two connections (one to read from, and the other to issue changes/additions/whatever). There are two kinds of connection you can make: One to SQL Server, and one to "anything else." The SQL Server one looks like this:

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Your connection string here"
$connection.Open()

If you need another kind of database, use a System.Data.OleDb.OleDbConnection instead. That pattern will persist for the remaining examples: I'll use the OleDb syntax, but you can substitute in the SQL Server ones if you prefer.

With an open connection, you can build a command. You're going to need to issue a SQL language query, and that's a bit beyond the scope of this article. There's a free language tutorial here, and I also did a video training series on the language. So, assuming you have a query ready to go, you build the command like this:

$command = New-Object System.Data.OleDb.OleDbCommand
$command.Connection = $connection
$command.CommandText = "Your query here"

Keep in mind that if you're opening multiple connections, you'll need each one in its own variable ($connection2, $connection3, etc), and each will also need a unique command ($command2, etc). 

What you do next depends on the query you issued. Action queries - INSERT, UPDATE, and DELETE queries - are executed like this:

$command.ExecuteNonQuery()

And that's it - you're done. SELECT queries, which return data, look something like this:

$reader = $command.ExecuteReader()
while ($reader.read()) {
  $reader.GetValue(0) # gets the value for the first column in the query
}

Because you want to stay on good terms with your DBA, make sure you close the data results and the connection when you're done:

$reader.close()
$connection.close()

For a complete, practical example, check out Chapter 41 of my PowerShell v1 book (proving that v1 books are still valuable). An expanded version of that chapter is in the v2 book.

Discuss this Blog Entry 1

on Jan 7, 2012
Another good SQL tutorial is at http://www.1keydata.com/sql/sql.html

Please or Register to post comments.

What's PowerShell with a Purpose Blog?

Don Jones demystifies Windows PowerShell.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×