Interacting with SQL Databases in PowerShell: Invoke-SqlCommand

Fri, Oct 19, 2007 4-minute read

Jeffrey McManus recently wrote about database queries with PowerShell – a small script that lets you query a SQL data store. This is really powerful. Rather than context switch into SQL Express (or TOAD, or your other favourite administration tool,) you can do what you need from PowerShell.

It goes even further, though. A lot of PowerShell’s built-in commands have a set-oriented flavour: Where-Object, Select-Object, Group-Object, and Sort-Object. I also blogged about a set intersection script here: http://www.leeholmes.com/blog/CreatingSQLsJoinlikeFunctionalityInMSH.aspx. After spending a ton of time in Oracle databases during an internship at General Electric, I remember always wishing that DIR supported a WHERE clause. Well, with PowerShell, it does!

One thing Jeffrey’s post doesn’t fully highlight is that the .NET Framework can also return fully structured objects that represent the results of your query. If your SELECT query returns a recordset with Name and Address columns, you can get back objects with Name and Address properties. With that, you can slice and dice the results even further in the shell.

PS D:\Temp> Invoke-SqlCommand.ps1 -Sql "SELECT TOP 5 * FROM Orders" | Format-Table

    OrderID CustomerID   EmployeeID OrderDate   RequiredDat ShippedDate     ShipVia     Freight
                                                e
    ------- ----------   ---------- ---------   ----------- -----------     -------     -------
      10248 VINET                 5 7/4/1996... 8/1/1996... 7/16/199...           3       32.38
      10249 TOMSP                 6 7/5/1996... 8/16/199... 7/10/199...           1       11.61
      10250 HANAR                 4 7/8/1996... 8/5/1996... 7/12/199...           2       65.83
      10251 VICTE                 3 7/8/1996... 8/5/1996... 7/15/199...           1       41.34
      10252 SUPRD                 4 7/9/1996... 8/6/1996... 7/11/199...           2        51.3

The .NET Framework supports more than just SQL servers, though. It supports Access databases and Excel workbooks, too. Their connection strings are a black-art, so it is natural to wrap all of that magic in a script.

So natural that I include in the upcoming PowerShell Cookbook: Invoke-SqlCommand. (PS: Expect some good news about the book VERY soon.)

##############################################################################
##
## Invoke-SqlCommand.ps1
##
## From Windows PowerShell Cookbook (O'Reilly)
## by Lee Holmes (http://www.leeholmes.com/guide)
##
## Return the results of a SQL query or operation
##
## ie:
##
##    ## Use Windows authentication
##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders"
##
##    ## Use SQL Authentication
##    $cred = Get-Credential
##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred
##
##    ## Perform an update
##    $server = "MYSERVER"
##    $database = "Master"
##    $sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248"
##    Invoke-SqlCommand $server $database $sql
##
##    $sql = "EXEC SalesByCategory 'Beverages'"
##    Invoke-SqlCommand -Sql $sql
##
##    ## Access an access database
##    Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users"
##    
##    ## Access an excel file
##    Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$]'
##
##############################################################################

param(
    [string] $dataSource = ".\SQLEXPRESS",
    [string] $database = "Northwind",
    [string] $sqlCommand = $(throw "Please specify a query."),
    [System.Management.Automation.PsCredential] $credential
  )


## Prepare the authentication information. By default, we pick
## Windows authentication
$authentication = "Integrated Security=SSPI;"

## If the user supplies a credential, then they want SQL
## authentication
if($credential)
{
    $plainCred = $credential.GetNetworkCredential()
    $authentication =
        ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
}

## Prepare the connection string out of the information they
## provide
$connectionString = "Provider=sqloledb; " +
                    "Data Source=$dataSource; " +
                    "Initial Catalog=$database; " +
                    "$authentication; "

## If they specify an Access database or Excel file as the connection
## source, modify the connection string to connect to that data source
if($dataSource -match '\.xls$|\.mdb$')
{
    $connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; "

    if($dataSource -match '\.xls$')
    {
        $connectionString += 'Extended Properties="Excel 8.0;"; '

        ## Generate an error if they didn't specify the sheet name properly
        if($sqlCommand -notmatch '\[.+\$\]')
        {
            $error = 'Sheet names should be surrounded by square brackets, and ' +
                       'have a dollar sign at the end: [Sheet1$]'
            Write-Error $error
            return
        }
    }
}

## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()

## Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()

## Return all of the rows from their query
$dataSet.Tables | Select-Object -Expand Rows