How to Run SQL Queries in PowerShell

1. Using the Invoke-SqlCmd Cmdlet in PowerShell
2. Running CRUD Operations in PowerShell
3. Alternative Method Using the .NET Framework in PowerShell

Windows PowerShell features many one-line commands for working with SQL Server. They can be helpful in many development contexts where we need to execute scripts or test code quickly.

This article explains how to invoke commands to an SQL server, perform CRUD operations, and other alternative ways to query SQL.

Using the Invoke-SqlCmd Cmdlet in PowerShell

When you connect to SQL Server, we will use a trusted connection, or an SQL Server authenticated user. Therefore, if we try to log onto SQL Server, we see these as the first two options when connecting.

Example Code:

$SQLServer = "SQL-01"
$db1 = "TestDB1"
$qcd = "PRINT 'This is output'"

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $qcd -Verbose

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $qcd -Username "User01" -Password "Password01" -Verbose

We see two essential basics of calling this function from the three side-by-side examples: how we specify our credentials and whether we want to output.

Whether we use SQL Server authentication or integrated security, our users must have permission to use the objects they call, such as views, tables, stored procedures, etc.

Likewise, verbose may help us see the output of what’s happening as we are calling Invoke-SqlCmd, and without it, we may not obtain the confirmation we need.

Running CRUD Operations in PowerShell

We can use this one-line function to execute many SQL commands, from creating objects to running CRUD operations. For example, in the snippet below, we run five statements to complete the following: create a table, insert, update, delete, and select some data.

Example Code:

$SQLServer = "SQL-01"
$db1 = "TestDB1"
$drop_table = "DROP TABLE invokeTable"
$create_table = "CREATE TABLE invokeTable (Id TINYINT, IdData VARCHAR(5))"
$insert_data = "INSERT INTO invokeTable VALUES (1,'A'), (2,'B'), (3,'C'), (4,'E'),(5,'F')"
$update_data = "UPDATE invokeTable SET IdData = 'D' WHERE Id = 4"
$delete_data = "DELETE FROM invokeTable WHERE Id = 5"
$select_data = "SELECT Id, IdData FROM invokeTable"

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $create_table

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $insert_data

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $update_data

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $delete_data

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $select_data

Alternative Method Using the .NET Framework in PowerShell

Using the Invoke-SqlCmd cmdlet comes with a few drawbacks, and we may prefer to use a different method of scripting. One example of this is the -QueryTimeout parameter, which is the length of time a query that we must run before timing out.

According to Microsoft, we must specify an integer between 1 and 65535 to avoid an error from returning. Specific situations may occur where we want the command timeout to be determined at 0.

Similarly, we may wish to hardcode commands that we can achieve through a custom function or wrap-around Invoke-SqlCmd.

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
    )

    $connectionString = "Data Source=$dataSource; " +
    "Integrated Security=SSPI; " +
    "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

}

In addition to having complete control over parameters, including restricting some parameters, custom functions that use underlying .NET libraries seldom experience as many changes as built-in functions.

If we choose to use native functions like Invoke-SqlCmd, we must be aware that these may change and rework any call.

With underlying .NET libraries, some of these may change if bugs or security holes are discovered, but we don’t see as many changes to these as we see with built-in PowerShell functions. The SqlClient namespace is an excellent example of this.

We have been using this for years, and while Microsoft has made it more vital over the years, the underlying structure matches. This isn’t to say that it won’t ever be deprecated, but functions built on top have changed, whereas it has experienced minor change.