Execution of Powershell SQL queries

In one of the project I was working for, I was asked to create general function which can help with executing Powershell SQL queries.
I prepared Powershell function which as an input need SQL server, database and query which should be executed. If database is located on specific instance remember to provide it as a part of SQL server parameter in format SQLServer\Instance
As a result table with filtered rows will be returned.

function Connect-SQL {
        [CMDletbidings()]
        param
        (
            [Parameter(Position=0, Mandatory=$true, HelpMessage="SQL query", ValueFromPipeline = $true)] $query,
            [Parameter(Position=1, Mandatory=$true, HelpMessage="SQL server instance name", ValueFromPipeline = $true)] $SqlSrv,
            [Parameter(Position=2, Mandatory=$true, HelpMessage="SQL database name", ValueFromPipeline = $true)] $SqlDb
        ) 
        $ee = @()
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SqlSrv; Database =$SqlDb; Integrated Security = $True"        
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
            param($sender, $event)
            
        }
        $SqlConnection.add_InfoMessage($handler);
        $SqlConnection.FireInfoMessageEventOnUserErrors = $true;

        try
        {
            $SqlConnection.Open()

            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
       
            $SqlQuery1 = $query
            $SqlCmd.CommandText = $SqlQuery1 
            $SqlCmd.Connection = $SqlConnection 
            
            $SqlDa = New-Object System.Data.SqlClient.SqlDataAdapter
            $SqlDa.SelectCommand = $SqlCmd  
            $Ds = New-Object System.Data.DataSet
            $SQLResultCount = $SqlCmd.ExecuteNonQuery()            
            
            if($SQLResultCount -eq -1){
                [void] $SqlDa.Fill($Ds)
                $QueryResults = @()
                $Ds.Tables[0] | %{$QueryResults += $_}
                $SQLResultCount = $Ds.Tables[0].Rows.Count
            }
            
            $SqlConnection.Close()
            return $QueryResults
        }
        catch
        {
           return 'Error:' + $_.Exception.Message
        }

}

Script of course needs remote access to SQL server, so before run ensure that port to SQL server is open 🙂
I hope that it will be useful for some of you.
Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.