PowerShell Custom Class for Querying a SQL Server

Here is a handy custom class I created for PowerShell 5+ that can query an SQL database. The class creates a SQL connection using the System.Data.SqlClient.SqlConnection class in .Net, and gives you full access to this object for information or to manipulate parameters. The class can also query the list of tables and views available in the database to help you prepare your SQL query.

Here’s a quick how-to:

Instantiate the Class

Create an instance of the object. You can create an empty instance like this:


$SQLQuery = [SQLQuery]::new()

You can then specify the SQL Server name and Database name:


$SQLQuery.SQLServer = "SQL-01\Inst_SCCM"
$SQLQuery.Database = "CM_ABC"

And then add a query:


$SQLQuery.Query = "Select top 5 * from v_R_System"

Or you can pass these parameters when you create the object, like:


$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC")

Or..


$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC","Select top 5 * from v_R_System")

Execute the Query

Now execute the query to return the results:


$SQLQuery.Execute()

The result will be displayed in the console window, or you can pipe to GridView for easier viewing:


$SQLQuery.Execute() | Out-GridView

Grid1

The query results will be stored to the object in the Result parameter so you can retrieve them later.


$SQLQuery.Result

The results are stored in a DataTable format with rows and columns which is an ideal format for SQL query results.

Load a Query from a File

You can load in a SQL query from a “.sql” file like so, passing the location of the file to the method:


$SQLQuery.LoadQueryFromFile("C:\Scripts\SQLScripts\OSD_info.sql")

Then execute the query as before.

Change the Connection String

By default, the connection string will be created for you, but you can add your own custom connection string using the ConnectionString parameter:


$SQLQuery.ConnectionString = "Server=SQL-01\inst_sccm;Database=CM_ABC;Integrated Security=SSPI;Connection Timeout=5"

Timeout Values

There are two timeout parameters which can be set:


$SQLQuery.ConnectionTimeout
$SQLQuery.CommandTimeout

The ConnectionTimeout parameter is the maximum time in seconds PowerShell will try to open a connection to the SQL server.

The CommandTimeout parameter is the maximum time in seconds PowerShell will wait for the SQL query to execute.

Get a List of Views or Tables in the Database

The following two methods can be used to retrieve a list of views or tables in the database:


$SQLQuery.ListViews()
$SQLQuery.ListTables()

The views or tables will be stored to the parameter of the same name in the object, so you can retrieve them again later.

You can filter the list to search for a particular view or table, or group of. On the command line you could do:


$SQLQuery.ListViews()
$SQLQuery.Views.Rows | where {$_.Name -match "v_Client"}

Or as a one-liner:


$SQLQuery.ListViews().Where({$_.Name -match "v_Client"})

You could also output to GridView for filtering:


$SQLQuery.ListViews() | Out-GridView

Grid2

Hide the Results

By default, the Execute(), ListViews() and ListTables() methods will return the results to the console after execution. You can turn this off by setting the DisplayResults parameter to $False. This scenario may be useful for scripting where you may not wish to display the results right away but simply have them available in a variable.


$SQLQuery.DisplayResults = $False

Access the SQL Objects

You can access the SQLConnection object, or the SQLCommand object to view or set parameters, or execute any of the methods contained in those objects. These objects are only available once the SQL query has been executed.

SQLConnection

SQLCommand

You can re-use the same SQLQuery object to run other queries; only the results for the most recent query will be stored in the object itself.

SQLQuery Custom Class

PowerShell Tip! Create Background Jobs with a Custom Class

PowerShell 5 introduced the capability to create custom classes. Classes are a convenient way to create an object with your own custom definition, and can include properties and methods. There could be many potential use cases for such a custom class, but here’s one that is handy: background jobs. PowerShell comes with some cmdlets for running jobs in the background, such as Start-Job, or the -AsJob parameter, but if you’ve ever worked directly with runspaces in PowerShell, you know they perform better than jobs in a multi-threading scenario.

I put together a quick custom class that can be used in a similar way to the PowerShell job cmdlets, but by creating and managing its own PowerShell instance and runspace.

First off, here’s the class (PowerShell 5 or greater required)


class BackgroundJob
{
    # Properties
    hidden $PowerShell = [powershell]::Create()
    hidden $Handle = $null
    hidden $Runspace = $null
    $Result = $null
    $RunspaceID = $This.PowerShell.Runspace.ID
    $PSInstance = $This.PowerShell

    # Constructor (just code block)
    BackgroundJob ([scriptblock]$Code)
    {
        $This.PowerShell.AddScript($Code)
    }

    # Constructor (code block + arguments)
    BackgroundJob ([scriptblock]$Code,$Arguments)
    {
        $This.PowerShell.AddScript($Code)
        foreach ($Argument in $Arguments)
        {
            $This.PowerShell.AddArgument($Argument)
        }
    }

    # Constructor (code block + arguments + functions)
    BackgroundJob ([scriptblock]$Code,$Arguments,$Functions)
    {
        $InitialSessionState = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault()
        $Scope = [System.Management.Automation.ScopedItemOptions]::AllScope
        foreach ($Function in $Functions)
        {
            $FunctionName = $Function.Split('\')[1]
            $FunctionDefinition = Get-Content $Function -ErrorAction Stop
            $SessionStateFunction = New-Object -TypeName System.Management.Automation.Runspaces.SessionStateFunctionEntry -ArgumentList $FunctionName, $FunctionDefinition, $Scope, $null
            $InitialSessionState.Commands.Add($SessionStateFunction)
        }
        $This.Runspace = [runspacefactory]::CreateRunspace($InitialSessionState)
        $This.PowerShell.Runspace = $This.Runspace
        $This.Runspace.Open()
        $This.PowerShell.AddScript($Code)
        foreach ($Argument in $Arguments)
        {
            $This.PowerShell.AddArgument($Argument)
        }
    }

    # Start Method
    Start()
    {
        $THis.Handle = $This.PowerShell.BeginInvoke()
    }

    # Stop Method
    Stop()
    {
        $This.PowerShell.Stop()
    }

    # Receive Method
    [object]Receive()
    {
        $This.Result = $This.PowerShell.EndInvoke($This.Handle)
        return $This.Result
    }

    # Remove Method
    Remove()
    {
        $This.PowerShell.Dispose()
        If ($This.Runspace)
        {
            $This.Runspace.Dispose()
        }
    }

    # Get Status Method
    [object]GetStatus()
    {
        return $This.PowerShell.InvocationStateInfo
    }
}

To create an object with this class definition, we need to instantiate it using a constructor. There are a couple of constructor overloads to give us some different options when we create the object.

The simplest way it to just pass a block of code you want to run in the background job:


$Code = {
    Test-Connection 10.25.24.27 -Count 4
}

$Job = [BackgroundJob]::New($Code)

To start the job, simply call the Start() method:


$Job.Start()

To check the status of the job, we can call the GetStatus() method:


$Job.GetStatus()

pic
Check the job status

When the job has completed, we can receive any results outputted by the code:


$Job.Receive()

receive
Receive the job results

Then we close off the job. This is important to properly dispose of the PowerShell instance.


$Job.Remove()

If the job is running too long, we can close the pipeline to stop the job:


$Job.Stop()

The PowerShell instance that we created gets added to the object as the PSInstance property, so we can explore it further if we want. Here I am viewing the command that was run in the job:


$Job.PSInstance.Commands.Commands.CommandText

job
Browse the background PowerShell Instance

Another constructor allows us to pass arguments for the code when we create the object. Since the background job is running in a separate thread, it does not have access to the variables in our main thread, so we need to pass them. Remember to add a Param() block to your code when passing variables.


$ComputerName = "PC001"
$Code = {
    Param($ComputerName)
    Test-Connection $ComputerName -Count 4
}

$Job = [BackgroundJob]::New($Code,$ComputerName)

We can also pass multiple variables in an array like this, but in the Param() block remember to keep them in the same order you pass them.


$ComputerName = "PC001"
$Count = 10
$Code = {
    Param($ComputerName,$Count)
    Test-Connection $ComputerName -Count $Count
}

$Job = [BackgroundJob]::New($Code,@($ComputerName,$Count))

Finally, we can also pass custom functions to the background job:


Function Ping-Computers {
    Param([String[]]$ComputerName, $Count)
    $ComputerName | foreach {
        Test-Connection -ComputerName $_ -Count $Count
    }
}

$ComputerName = "PC001","PC002","PC003"
$Count = 10

$Code = {
    Param($ComputerName,$Count)
    Ping-Computers -ComputerName $ComputerName -Count $Count
}

$Job = [BackgroundJob]::New($Code,@($ComputerName,$Count),"Function:\Ping-Computers")

There are many possibilities for customising this class further, but using a custom class like this is a convenient way to spin up a job in the background, or to add some multi-threading capability to a script.

Here’s a simple example of multi-threading. First we add jobs to a hash table, then we start each job. Next we wait until each job is completed, and then return the output. The whole process takes much less time than running the commands synchronously (one after another).


# Create multiple jobs
$Jobs = @{
    Job1 = [BackgroundJob]::New({Test-Connection -ComputerName 10.20.17.129 -Count 5})
    Job2 = [BackgroundJob]::New({Test-Connection -ComputerName 10.1.16.86 -Count 5})
    Job3 = [BackgroundJob]::New({Test-Connection -ComputerName 10.21.17.216 -Count 5})
}

# Start each job
$Jobs.GetEnumerator() | foreach {
    $_.Value.Start()
    }

# Wait for the results
Do {}
Until (($Jobs.GetEnumerator() | foreach {$_.Value.GetStatus().State}) -notcontains "Running")

# Output the results
$Jobs.GetEnumerator() | foreach {$_.Value.Receive()}

multi
Multiple simultaneous jobs

The job result will be saved to the Result property after the Receive() method is called, so they are stored in the variable and can be retrieved later if needed.

You can also create a kind of class library, which you can import each time you run a PowerShell session, so that this custom class is always available to you, which I blogged about here.

Good job, what!

Ping Multiple Computers Rapidly with a Custom PowerShell Class

Today I published a custom class for users of PowerShell 5 that allows you to ping multiple computers very quickly using a custom PS object.  It uses .Net runspaces to create a parallel processing environment to ensure speedy execution, and stores useful information about the ping results in the custom object, such as:

  • The full “Test-Connection” results
  • The list of online machines
  • The list of offline machines
  • The count and percentage of online and offline machines
  • The execution time of the command

capture

Check it out here:

https://smsagent.wordpress.com/posh-5-custom-classes/power-ping/