PowerShell in Depth, Second Edition (2015)

Part 4. Advanced PowerShell

Chapter 36. Accessing databases

This chapter covers

· Using the .NET Framework to connect to a database

· Querying databases with PowerShell

· Adding, deleting, and modifying databases with PowerShell

· Running stored procedures with PowerShell

It’s not uncommon to need to access something that’s in some kind of a database, so that’s what we’ll show you in this chapter. Now, as with many other chapters, we need to set some boundaries for what we’re going to cover (and not cover). This chapter won’t teach you the SQL query language, nor will it teach you how to create, maintain, or manage a database. This chapter is about using PowerShell to access the data that’s in a database and nothing more. We also assume you already have the necessary database drivers installed on your computer and that you can physically connect to whatever database you need. You don’t need any particular commands or extensions—you don’t have to install SQL Server’s PowerShell stuff, for example—but if you’re accessing a database such as Oracle or MySQL, you’ll need the appropriate drivers installed for .NET. Having said that, if you can get your hands on the SQL server tools it’ll make your life much easier when accessing SQL Server databases.

Something else to keep in mind is that there are no native database access commands in PowerShell. Instead, we’ll be using the underlying .NET Framework classes, which means the information in this chapter will look a lot like .NET Framework programming. Right now, PowerShell has no help for that.

36.1. Native SQL vs. OLEDB

The first thing you’ll need to decide is what kind of database you’ll be connecting to. At this point, the decision comes down to two simple choices: Microsoft SQL Server and everything else. The .NET Framework has specific code built into it for accessing SQL Server (called SQL Server Native Client); everything else gets access through Object Linking & Embedding Database (OLEDB), and you’ll have to have the appropriate .NET Framework–compatible OLEDB drivers installed.

For most of this chapter, we’re going to focus on Microsoft SQL Server. That means you’ll see .NET class names like System.Data.SqlClient.SqlConnection; if you wanted to change any of those examples to OLEDB, simply change SqlClient to OleDb and change Sql toOleDb. In other words, if you wanted to work with an Oracle database, instead of using the System.Data.SqlClient.SqlConnection class in your PowerShell expressions, you’d use System.Data.OleDb.OleDbConnection. It’s that easy!

36.2. Connecting to data sources

Your first step will be to connect to a database. To do this, you need to know four things:

· The server name where the database lives (and it might be something like “localhost” if it’s on the same machine). In the case of SQL Server, which can run multiple named copies of itself on a computer, you may also need to know the instance name you want to connect to. For example, SQL Server Express installs, by default, to a named instance called SQLEXPRESS, meaning the total server name might look something like SERVER\SQLEXPRESS. You can see the instance names by running Get-Service in PowerShell and looking for SQL Server services; there will be one service per instance, and the service name will be something like “SQLSERVER$SQLEXPRESS” with a dollar sign between the service name and the instance name. You just need the instance name—in this case, SQLEXPRESS.

· The name of the database you want to connect to.

· Whether or not you’re using Windows integrated security (an option SQL Server provides) or standard security (which could be SQL Server or almost anything else).

· If you’re using standard security, then you’ll also need a username and password.

If you don’t know all this information, you’ll need to speak with whoever “owns” the database, or database server, to get it.

Once you have that information, you’ll use it to build a connection string. That’s a set of instructions that tells .NET what kind of database you’re using and passes along the information. We never remember the syntax for these things—we always look them up athttp://ConnectionStrings.com.

Tip

You will find many variations on a theme when looking at code for connecting to databases. We use the approach presented here because it works for us. If you prefer a slightly different way of coding, please feel free to use it. The basic ideas are the same however you end up cutting the code.

Once you have the correct connection string, you’ll fill in the information and create a Connection object. Set its ConnectionString property to your connection string, and you’re ready to go. For example, a SQL Server (2008 and later) connection, using standard security, might look like this:

PS C:\> $connection = New-Object

-TypeName System.SqlClient.SqlConnection

-Property @{'ConnectionString'='Data Source=myServerAddress;

InitialCatalog=myDataBase;

User Id=myUsername;Password=myPassword;'}

And a connection that uses Windows integrated security might look like this:

PS C:\> $connection = New-Object

-TypeName System.Data.SqlClient.SqlConnection

-Property @{'ConnectionString'=' Data Source=myServerAddress;

InitialCatalog=myDataBase;

Integrated Security=SSPI;'}

Both of these create the Connection object, assign its connection string, and put the resulting object into the $connection variable. From there, you just need to open the connection to start using it:

PS C:\> $connection.Open()

At some point, make sure you close it, too—otherwise you’ll use all the connections and whoever runs the database will likely get extremely upset with you:

PS C:\> $connection.Close()

A single connection can only be held open for a single operation. That is, if you’re using a connection to read data out of the database, you can’t use that same connection to send changes back to the database. So it’s not uncommon to have two connections open: one for reading and another for writing. Just be sure you put each into its own variable, and make sure you close them both when you’ve finished!

Tip

We add the Close() command to our scripts as soon as we open the database so that we don’t forget it!

36.3. Querying data

To read data from the database, you send a SQL language query to the database and then read through whatever comes back. SQL is largely the same across different database platforms, but not entirely. Though there are standards for the language, different vendors implement different levels of the standard, leaving a bit of inconsistency between database products. If you’re not familiar with the language, www.w3schools.com/SQl/default.asp is a good place to start. It does a fine job of pointing out the minor—but obviously important—differences between various platforms and covers the syntax quite well, with lots of great examples.

There are two broad techniques for reading data with .NET. The first involves something called a DataReader object, whereas the second involves something called a DataAdapter. We’re going to show you both of them, but we tend to prefer the DataAdapter approach, so we’ll start there.

Either way, we suggest getting your SQL language query into a variable. Keep in mind that PowerShell lets you do some fancy variable-replacement tricks when you use double quotes. That’s convenient, because SQL delimits strings in single quotes, so you can build queries like this:

PS C:\> $query = "SELECT computername,version

FROM inventory

WHERE version = $version

AND manufacturer = '$mfg'"

This gets you a completed query in the $query variable. The capitalization of SELECT, FROM, and WHERE isn’t strictly necessary, but it does make the query easy to read. Notice that the values within $version and $mfg, which you’ve presumably populated elsewhere, are inserted into this query. You need to be careful that $version and $mfg don’t contain any single quotation marks, or it’ll mess up the final query syntax.

Warning

Dynamically building a query by inserting variables’ contents into the query can be dangerous. If you’re populating those variables from user-provided input, you’re leaving yourself open to a kind of attack called SQL injection, in which a malicious user deliberately corrupts a query in order to delete data, queries data they shouldn’t, and more. We’re assuming you’re the only one populating your query’s contents, and so you can make sure you’re not doing anything stupid. But if you plan to accept input from outside, untrusted sources, just be aware of the danger.

With your query in a variable, you’re ready to begin.

36.3.1. Databases with DataAdapters

DataAdapters are neat because they basically turn the data into a set of objects, not unlike importing a CSV file into PowerShell. We think that makes the resulting data a lot easier to work with. Here’s how you create one with your query (assuming your opened connection object is in the variable $connection and your query is in $query):

$command = $connection.CreateCommand()

$command.CommandText = $query

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataset)

$table = $dataset.Tables[0]

Write-Output $table

That code will output one object for each row of data your query returned, and each column you queried will become a property of those objects. You could use a ForEach construct (or the ForEach-Object cmdlet) to enumerate those rows and do something with each one in turn. We’ll get into a more complete example toward the end of this chapter, when we provide you with some wrapper functions to make DataAdapters even easier to work with.

36.3.2. Databases with DataReaders

DataReaders are a slightly more procedural way of getting to data. You’ll start with the same connection (in $connection for this example) and SQL query (in $query). Again, make sure the connection is opened first:

$command = New-Object System.Data.SqlClient.SqlCommand

$command.Connection = $connection

$command.CommandText = $query

$reader = $command.ExecuteReader()

while ($reader.Read()) {

$reader.GetValue(0)

}

$reader.Close()

The $reader.GetValue(0) is reading the data from a column, in this case the first column you queried. In our example, that would be the computerName column of the table, because that’s the first column listed in the query string. You’d change the 0 to 1 to read the second column, and so forth. The $reader.Read() method will move you to the next row and return $True if there’s another row waiting after that. So, in your While loop, you’re basically reading through all available rows. The important thing to remember with a DataReader is that it’s a one-way street—you can’t go backward.

36.4. Adding, changing, and deleting data

Changes to databases are made with INSERT, UPDATE, and DELETE queries. Again, our goal isn’t to teach you that query syntax—you need to either know it already or learn it on your own. Our goal is to show you how to use these queries with PowerShell. There’s one simple technique we’ll show you. Again, assuming you have a connection object in $connection and your query in $query, you’d do this:

$command = $connection.CreateCommand()

$command.CommandText = $query

$command.ExecuteNonQuery()

And that’s it.

36.5. Calling stored procedures

Many databases include stored procedures, which are bits of code, usually written in SQL, that run on the database server itself. These are often an effective way to accomplish tasks, because the procedure can bundle up all the necessary business logic needed. You can kind of think of a procedure as a sort of server-side SQL command: You need to know the command name and its parameters, and then you can run it. Sadly, there’s no built-in help, so you’ll have to know what you’re doing up front to make use of them. Procedures are also a safer way to execute code that contains user-provided input, because they’re less susceptible to SQL injection attacks.

Let’s assume you have a stored procedure (or stored proc, as database folks like to call them) named DoSomething that you want to run. It has a single input argument, which expects a computer name. Assuming you have an open connection in $connection, here’s how you’d execute the procedure:

$command = $connection.CreateCommand()

$command.commandText = 'DoSomething'

$param = New-Object System.Data.SqlClient.SqlParameter '@computer',

'localhost'

$command.Parameters.Add($param)

$command.ExecuteNonreader()

You’ve used ExecuteNonReader() assuming that the procedure does something but doesn’t return any rows of data. Plenty of procedures do return data, though—so to capture that output you’d do something like this instead:

$command = $connection.CreateCommand()

$command.commandText = 'DoSomething'

$param = New-Object System.Data.SqlClient.SqlParameter '@computer',

'localhost'

$command.Parameters.Add($param)

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter

$adapter.SelectCommand = $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataset)

$table = $dataset.Tables[0]

This syntax isn’t all that different from what we showed you earlier; there’s just a bit extra involved to add the parameter to the procedure before running it to get the results.

36.6. A module to make it easier

Dealing with the database code can be ugly sometimes, and so to make things easier we proudly present the DataAccess module! Just save the following listing in your user PowerShell modules folder in a folder called DataAccess. You should end up with a path like \Users\<username>\Documents\WindowsPowerShell\Modules\Data-Access\ DataAccess.psm1.

Listing 36.1. DataAccess.psm1

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 System.Data.SqlClient.SqlDataAdapter $command

} else {

$adapter = New-Object 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 System.Data.OleDb.OleDbConnection

}

$connection.ConnectionString = $connectionString

$command = $connection.CreateCommand()

$command.CommandText = $query

$connection.Open()

$command.ExecuteNonQuery()

$connection.close()

}

Whenever you need to use these commands, just run Import-Module DataAccess to load them into the shell. They’re designed to work with either SQL Server or OLEDB connection strings; you’ll pass in the connection string that you want to use.

For example, suppose you have a SQL Server Express instance running on the local computer. Because you’re an admin, you can rely on Windows authentication to get you into it. In the database, you have a table named Computers. That table has several columns: Computer (which you expect to contain computer names), as well as OSVersion, BIOSSerial, OSArchitecture, and ProcArchitecture. Here’s how you might use the DataAccess module to query all of the computer names:

Import-Module DataAccess

Get-DatabaseData -verbose –connectionString `

'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' `

-isSQLServer -query "SELECT * FROM Computers"

Warning

The connection strings were kind of long, so to make all of this fit in the book we had to use PowerShell’s escape character. Make sure you type that “backtick” correctly and that it’s immediately followed by a carriage return—no tabs or spaces after it, please!

Here’s how you’d insert a new computer into the table:

Invoke-DatabaseQuery -verbose –connectionString `

'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' `

-isSQLServer -query "INSERT INTO Computers (computer) VALUES('win7')"

And finally, re-query the computers to see that your addition is in there:

Get-DatabaseData -verbose -connectionString `

'Server=localhost\SQLEXPRESS;Database=Inventory;Trusted_Connection=True;' `

-isSQLServer -query "SELECT * FROM Computers"

So you basically have two functions, Get-DatabaseData and Invoke-DatabaseQuery. The first is for reading data with a SELECT query (or to execute a stored procedure that returns data), and the second is for running INSERT, UPDATE, and DELETE queries (or a stored procedure that doesn’t return data). Each has a –connectionString parameter and a –query parameter, and each has a –isSQLServer switch that you use when the connection string points to a SQL Server machine. Omit –isSQLServer if you’re providing an OLEDB connection string.

36.7. Summary

We’ve given you a concise introduction to using databases. Our expectation is that you’re a general-purpose administrator and that databases aren’t your first love—and they might even just be a necessary evil! Hopefully, the DataAccess module we’ve given you will make it even easier to incorporate databases into your scripts.

Oh, and before we forget—don’t forget to check that you’ve closed all the database connections you opened.