If an object is completely replaced with a made-up PSCustomObject, the object type is lost; this is important when another command requires an object of a specific type as input. Attempting to override properties and methods on a real instance of the object may be used to work around this problem.

The following snippet creates an instance of an SQL connection object, then overrides the Open method and State properties:

$sqlConnection = [System.Data.SqlClient.SqlConnection]::new()
$sqlConnection | Add-Member State -MemberType NoteProperty -Force -Value 'Closed' 
$sqlConnection | Add-Member Open -MemberType ScriptMethod -Force -Value { 
    $this.State = 'Open' 
} 

The State property cannot be set by default, so overriding both is required to simulate use. The normal methods may be seen by looking beneath the PowerShell object; an example is as follows:

PS> $sqlConnection = [System.Data.SqlClient.SqlConnection]::new()
PS> $sqlConnection | Add-Member State -MemberType NoteProperty -Force -Value 'Open'
PS> $sqlConnection.State
Open

PS> $sqlConnection.PSBase.State
Closed

This technique can be used to create a disarmed object of the correct type.

The following function expects an SQL connection object as input:

function Invoke-SqlQuery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [String]$Query,

        [Parameter(Mandatory)]
        [System.Data.SqlClient.SqlConnection]$Connection
    )
    
    try {
        $Connection.Open()

        $sqlCommand = $Connection.CreateCommand()
        $sqlCommand.CommandText = $Query

        $dataTable = New-Object System.Data.DataTable
        $sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlCommand)
        $sqlDataAdapter.Fill($dataTable) | Write-Verbose

        $dataTable
    } catch {
        $pscmdlet.ThrowTerminatingError($_)
    } finally {
        if ($Connection.State -eq 'Open') {
            $Connection.Close()
        }
    }
}

To create unit tests for the Invoke-SqlQuery function, an SQL connection object must be created and supplied. In addition, SqlDataAdapter must be mocked.

The tests shown as follows provide a modified SQL connection object, and a version of the data adapter with the Fill method replaced:

Describe Invoke-SqlQuery {
    BeforeAll {
        Mock New-Object -ParameterFilter { $TypeName -like '*SqlDataAdapter' } -MockWith {
            [System.Data.SqlClient.SqlDataAdapter]::new() |
                Add-Member Fill -MemberType ScriptMethod -Force -PassThru -Value {
                    $null = $args[0].Columns.Add('ColumnName')
                    $row = $args[0].NewRow()
                    $row.ColumnName = 'value'
                    $args[0].Rows.Add($row)

                    $args[0].Rows.Count
                }
        }

        $defaultParams = @{
            Query = 'SELECT * FROM Table1'
             Connection = [System.Data.SqlClient.SqlConnection]::new() |
                 Add-Member State -MemberType NoteProperty -Force -PassThru -Value { 'Closed' } |
                 Add-Member Open -MemberType ScriptMethod -Force -PassThru -Value {
                     $this.State = 'Open'
                 } |
                 Add-Member Close -MemberType ScriptMethod -Force -PassThru -Value {
                     $this.State = 'Closed'
                 }
        }
    }

    It 'Executes a query and returns the results' {
        $output = Invoke-SqlQUery @defaultParams

        $output.Rows.Count | Should -Be 1
        $output[0].ColumnName | Should -Be 'value'
    }

    Context 'Error handling' {
        BeforeAll {
            $contextParams = $defaultParams.Clone()
            $contextParams.Connection = $contextParams.Connection |
                Add-Member Open -MemberType ScriptMethod -Force -PassThru -Value {
                    throw 'Connection failed'
                }
        }

        It 'When the connection fails, throws an error' {
            { Invoke-SqlQUery @contextParams } | Should -Throw 'Connection failed'
        }
    }
}