Use PSTypeNames to alter Invoke-SqlCmd's data elements

Question

I would like to develop a pattern to easily switch between 'views' of data when executing a PowerShell function that connects to a database.

Based on this article, Quick Hits: Set the Default Property Display in PowerShell on Custom Objects, I created:

<#
.NOTES
Assumes a table named `Person` with columns `FirstName`,`LastName`,`Gender`,`BirthDate`,`City`,`Region`,`Telephone`
#>
function Get-CustomObject {

    param(
        [Parameter(Position=0)]
        [ValidateSet('Demographics','Contact','All')]
        [alias('v')]
        [System.String]$View='All'
    )

    Remove-TypeData -TypeName User.Information -ErrorAction SilentlyContinue

    Switch ($View) {
        'Demographics' { Update-TypeData -TypeName User.Information -DefaultDisplayPropertySet FirstName,LastName,Gender }
        'Contact' { Update-TypeData -TypeName User.Information -DefaultDisplayPropertySet City,Region,Telephone }
    }

    Invoke-SqlCmd -ServerInstance ServerName -Database DatabaseName -Query 'SELECT * FROM Person' | 

        ForEach {

            # assign typename
            $_.PSTypeNames.Insert(0,'User.Information')

            # return object
            $_

    } # / ForEach

}

When the -View parameter isn't specified, the All setting is used:

PS> Get-CustomObject

Id         : 20
FirstName  : DCD65A17
LastName   : 05016468
City       : 4DF12729
Region     : MN
Telephone  : 6125551212
Gender     : F
Occupation : abcdefghij
Birthdate  : 1/1/1900 12:00:00 AM

When the -View parameter is specified, the default view is modified:

PS> Get-CustomObject Contact

City     Region Telephone
----     ------ ---------
A78D794C MN     6125551212
FDB79B27 MN     6125551212
49D073FE MN     6125551212
0716DF7E MN     6125551212
29FF9D4E MN     6125551212

Questions:

Is there a more-efficent way to do this?
Can the PSTypeNames be set for the query as a whole, rather than having to assign it to each row (thus eliminating the need for the Foreach)?
Can an alias be assigned to each item in the validate set? For instance, PS> Get-CustomObject d would use the Demographics view.

Frode F. · Answer 1 · 2016-05-14T17:03:39.370

Is there a more-efficent way to do this?

Not as far as I know. If the output from Invoke-SQLCMD is a pscustomobject or another "universal" type like DataRow etc. then you need to modify the typename because views are linked to typenames.

Can the PSTypeNames be set for the query as a whole, rather than having to assign it to each row (thus eliminating the need for the Foreach)?

Not if the cmdlet doesn't support it.

Views in powershell are selected based on the first returned object because of how the pipeline works (doesn't know how many objects are coming), so if you can afford including a dummy-object in the output, you could start by throwing a dummy-object with the correct typename down the pipeline to get the correct view. That way you wouldn't need to modify the other objects.

Be aware that the typename of the other objects would still be pscustomobject, DataRow etc., so this is only a hack for getting the view to work.

Ex:

<#
.NOTES
Assumes a table named `Person` with columns `FirstName`,`LastName`,`Gender`,`BirthDate`,`City`,`Region`,`Telephone`
#>
function Get-CustomObject {

    param(
        [Parameter(Position=0)]
        [ValidateSet('Demographics','Contact','All')]
        [alias('v')]
        [System.String]$View='All'
    )

    Remove-TypeData -TypeName User.Information -ErrorAction SilentlyContinue

    Switch ($View) {
        'Demographics' { Update-TypeData -TypeName User.Information -DefaultDisplayPropertySet FirstName,LastName,Gender }
        'Contact' { Update-TypeData -TypeName User.Information -DefaultDisplayPropertySet City,Region,Telephone }
    }

    #Output dummy-object (you can remove every property except pstypename and ONE random property,
    #but to avoid "null value expcetion" later I would include the properties you know about
    New-Object psobject @{
        pstypename = "User.Information"
        FirstName = "N/A"
        LastName = "N/A"
        Gender = "N/A"
        BirthDate = "N/A"
        City = "N/A"
        Region = "N/A"
        Telephone = "N/A"
    }

    #Real data
    Invoke-SqlCmd -ServerInstance ServerName -Database DatabaseName -Query 'SELECT * FROM Person'

}

Can an alias be assigned to each item in the validate set? For instance, PS> Get-CustomObject d would use the Demographics view.

Not with ValidateSet AFAIK as it validates the exact values. If you have PowerShell 5 you can replace it with an enum which would work.

Sample:

enum GetCustomObjectViews {
    Demographics
    Contact
    All
}

function Get-CustomObject {

    param(
        [Parameter(Position=0)]
        [alias('v')]
        [GetCustomObjectViews]$View='All'
    )

    #....    
}

Get-CustomObject -View d

Use PSTypeNames to alter Invoke-SqlCmd's data elements

1 Answers1