Query SQL Server from Powershell using ADO.NET objects

If you want to execute a query against a SQL Server database using Powershell there are a couple of options to consider.

1. SQL Server Invoke-Sqlcmd cmdlet

You can always use the Invoke-Sqlcmd cmdlet, but you will need to have the SQL Server client tools installed, get some paths correct, and add the cmdlet so your script can access it.

2. Use ADO.NET objects.

Since ADO.NET has been part of .NET since the beginning and Powershell can access .NET objects, no additional objects are needed to execute a database query.

Here is a function for reuse.

function Invoke-SQL {

    param(

        [string] $dataSource = ".\SQLEXPRESS",

        [string] $username = $(throw "Please specify a username."),

        [string] $password = $(throw "Please specify a password."),

        [string] $database = "master",

        [string] $sqlCommand = $(throw "Please specify a query.")

      )

    $connectionString = "Data Source=$dataSource; " +

            "User Id=$username; Password=$password; " +

            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)

    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

    $dataset = New-Object System.Data.DataSet

    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()

    $dataSet.Tables

}

Here is how you call it.

Invoke-SQL "serverHere" "userHere" "pwdHere" "dbHere" "SELECT GETDATE() AS TimeOfQuery;"  

NOTE: The basis of this script is from here. I did extend it a little bit to work with named users instead of using integrated security.