SQL

SQL examples for PowerShell Universal.

Server-side SQL Table

This example uses Universal Dashboard.

This example takes advantage of a SQL server and the New-UDTable cmdlet to create a table that retrieves data from a database table. The filtering, sorting and paging take place within the database.

This example assumes that we have a database called podcasts running in the local MS SQL Server. It has a table called shows that includes a column called host and a column called name.

Start-PSUServer -Port 8080 -Configuration {
    New-PSUDashboard -Name 'SQL' -BaseUrl "/" -Framework 'UniversalDashboard:Latest' -Content {
        New-UDDashboard -Title 'SQL' -Content {
            New-UDTable -Title 'Shows' -LoadData {
                $TableData = ConvertFrom-Json $Body

                $OrderBy = $TableData.orderBy.field
                if ($OrderBy -eq $null)
                {
                    $OrderBy = "name"
                }

                $OrderDirection = $TableData.OrderDirection
                if ($OrderDirection -eq $null)
                {
                    $OrderDirection = 'asc'
                }

                $Where = ""
                if ($TableData.Filters) 
                {
                    $Where = "WHERE "

                    foreach($filter in $TableData.Filters)
                    {
                        $Where += $filter.column.field + " LIKE '%" + $filter.value + "%' AND "
                    }

                    $Where += " 1 = 1"
                }

                $PageSize = $TableData.PageSize 
                # Calculate the number of rows to skip
                $Offset = $TableData.Page * $PageSize
                $Count = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT COUNT(*) as count FROM shows $Where"

                $Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT * FROM shows $Where ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" | ForEach-Object {
                    @{ 
                        name = $_.name 
                        host = $_.host
                    }
                } 
                $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties
            } -Columns @(
                New-UDTableColumn -Property 'name' -Sort $true -Filter $true
                New-UDTableColumn -Property 'host' -Sort $true -Filter $true
            ) -Sort -Filter
        }
    }
}