Simple alternative to Invoke-Sqlcmd

Today I had to use Invoke-Sqlcmd on a SharePoint server. However as soon as I tried to run this

add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
$countries = Invoke-Sqlcmd -Query "select * from Data.dbo.Countries;" -ServerInstance "localdev"

I was faced with the error.

add-pssnapin : The Windows PowerShell snap-in 'sqlserverprovidersnapin100' is not installed on this computer.
At line:1 char:1
+ add-pssnapin sqlserverprovidersnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (sqlserverprovidersnapin100:String) [Add-PSSnapin], PSArgumentException
    + FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

add-pssnapin : The Windows PowerShell snap-in 'sqlservercmdletsnapin100' is not installed on this computer.
At line:2 char:1
+ add-pssnapin sqlservercmdletsnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (sqlservercmdletsnapin100:String) [Add-PSSnapin], PSArgumentException
    + FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the 
name, or if a path was included, verify that the path is correct and try again.
At line:3 char:14
+ $countries = Invoke-Sqlcmd -Query "select * from Data.dbo.Countries;" -ServerInstance "localde ...
+              ~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

After some googling it became I apparent that I would need to install some subset of Sql Management Tools on the machine. This was not an option for me. In the end I came up with a simple function which works similarly to the Invoke-Sqlcmd

function Read-Query
{
    param (
        [Parameter(Mandatory=$true)]
        [string]$ConnectionString,

        [Parameter(Mandatory=$true)]
        [string]$Query,

        [Parameter(Mandatory=$true)]
        [scriptblock]$Action
    )

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString
    $SqlConnection.Open()
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $reader = $SqlCmd.ExecuteReader()

    while ($reader.Read())
    {
        $x = $null
        $x = @{}

        for ($i = 0; $i -lt $reader.FieldCount; ++$i)
        {
            $x.add($reader.GetName($i), $reader[$i])
        }

        Invoke-Command -ScriptBlock $action -ArgumentList $x
    }

    $SqlConnection.Close()
}

And you would use it like so:

Read-Query -ConnectionString 'Server=localdev;Database=Data;Integrated Security=True' `
    -Query 'select * from Data.dbo.Countries where Active = 1;' `
    -Action {
        Write-Host $args[0].CountryName
    }

Of course it is a lot simpler and doesn’t support all the use cases as the Invoke-Sqlcmd does, but it works for my needs.

22 Bugs