PowerShell – Create “Clean” Custom Objects From DataTable Object

I recently had a need to create “clean” PowerShell custom objects out of a DataTable object. What does that mean? Well, let me explain. When you try to get data out of SQL Server using PowerShell there are many ways to do it. Either you use Invoke-SQLCmd, SQL Server PowerShell (SQLPS), SQL Server Management Objects (SMO) or .NET (System.Data.SqlClient). Each of these tools have their advantages and disadvantages. There is a post from Shawn Melton which explains more about the different approaches. I like the independent way and therefore I use the .NET approach. But why would I need to have a “clean” custom object? Well, there might be an API which accepts JSON payload and this payload should only contain data that is useful for the API. If you convert the data returned from SQL Server using Convertto-Json cmdlet, there is usually header and data type specific data surrounding the core data. We will see this in the following example.

First to connect I wrote a simple function Connect-SQLServer which returns a connection object. It looks like this…

Function Connect-SQLServer
{
    [CmdletBinding()]
    [OutputType([object])]
 
    param
    (
        [Parameter(Mandatory = $True)][String]$Instance,
        [Parameter(Mandatory = $True)][String]
        [ValidateSet ('OperationsManagerDW', 'OperationsManager')]$Database,
        [Parameter(Mandatory = $False)][String]$User,
        [Parameter(Mandatory = $False)][String]$Password
    )

    Try 
    {
        If([String]::IsNullOrEmpty($User) -or [String]::IsNullOrEmpty($Password))
        {
            $ConnectionString = "Server=$Instance;Database=$Database;Integrated Security=True;"
        }
        Else
        {
            $ConnectionString = "Server=$Instance;uid=$User; pwd=$Password;Database=$Database;Integrated Security=False;"
        }

        $Connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
        $Connection.ConnectionString = $ConnectionString
        $Connection.Open()
        return $Connection
    }
    Catch
    {
        Throw $_.Message
    }
}

So to connect to a specific database (in this case System Center Operations Manager (SCOM) database) I run…

$DBConnection= Connect-SQLServer -Database OperationsManager -Instance "SCOM2016"
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.Connection = $DBConnection
$SQLCommand.CommandText = "Select * from dbo.alert"
$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $SQLCommand
$DataSet = New-Object System.Data.DataSet
$null = $Adapter.Fill($DataSet)
$DBConnection.Close()

This will create a SqlCommand instance, append connection and connection string, instantiate SqlDataAdapter and DataSet object, to finally use the fill() method to add rows to the data set which gets returned. Nice, now we are having a data set containing one (or more) data tables.

In this case we pick the first table…

$DataSet[0].Tables[0]

…in this example I get alert data from SCOM….

If I convert this object into JSON it looks like this…

As you can see there is a lot of special data delivered in a multilevel JSON object. I am just interested in the plain row and column information to build a custom objects having this clean information. Meaning I want to have a one-level deep JSON object,  one custom object per row and the column headers should be the property name. Because of that, I have created this function…

Function New-CustomObject
{
    [CmdletBinding()]
    [OutputType([object])]
    param 
    (
        [Parameter(Mandatory = $True)][Object]$DataTable
    )
    $Objects = @()
    ForEach ($Row in $DataTable)
    {
        $Properties = @{}
        For($i = 0;$i -le $Row.ItemArray.Count - 1;$i++)
        {
            $Properties.Add($DataSet[0].Tables[0].Columns[$i], $Row.ItemArray[$i])
        }
        $Objects += New-Object -TypeName PSObject -Property $Properties   
    }
    return $Objects
}

I am able to call this function like this…

$DBObjects = New-CustomObject -DataTable $DataSet[0].Tables[0]

I will pass the first data table to the function, then I will iterate through each row and in every iteration I am creating a property hash table containing the column header and its corresponding value. The logic behind this approach is independent of row and column count. So it does not matter how many columns are returned nor how many rows there are in the data table.

So if you look at the JSON object after transformation, it is a nice clean, one-level deep JSON object….

$DBObjects | Convertto-Json

I hope you like this way as much as I do. If you have another approach, let me know!