SQL Query dashboard

Product: PowerShell Universal
Version: 1.5.14

Hi all, what I’m trying to do is create a SQL Query dashboard using the New-UDCodeEditor cmdlet and New-UDTable

For browser-side queries it’s working great, I can create dynamic columns based upon the query that the user enters into the code editor, even using “top” like…


For server-side table loading with -LoadData and Out-UDTableData I can get filtering and order working all ok (no “top” sql query allowed though because it doesn’t work with “offset” to handle the paging). However, I can’t work out how to create the columns dynamically, I can only get it working if I hardcode the New-UDColumn but that won’t work because the user can write whatever query they want so I’d like to dynamically create the columns with something like:

-Columns @(
    (Invoke-DbaQuery -As 'psobject' -SqlCredential $cache:cfgSQLcreds -SqlInstance $cache:cfgSQLserver -Database $cache:cfgSQLdatabase -Query "SELECT top 1 * FROM ( $((Get-UDElement -Id 'codeeditor').Code) ) AS subquery;")[0].psobject.Properties | foreach-object {
        New-UDTableColumn -Property $_.Name #-IncludeInSearch -ShowSort -IncludeInExport

But when that is set along with the -LoadData I get log errors:
An error occurred: Cannot bind argument to parameter 'Columns' because it is an empty array.

I’ve tried setting a $cols variable before the New-UDTable and within the -LoadData block but have the same issue.

This is perhaps something that isn’t possible?

Or if someone has an idea to create a “SQL Management Studio” lite experience via a dashboard using New-UDCodeEditor and New-UDTable that can handle server-side processing please let me know! :slight_smile:


@pharnos I’ve been working on a similar problem myself. I haven’t yet found a full working solution unfortunately, as I’ve been spending my spare time fooling around with 1.6-beta2 but I’ll definitely be interested to see if anyone has any useful suggestions. My particular use case is one in which I can give the columns ahead of time, I just prefer to make everything as dynamic as possible for future use.

I’ve tried numerous ways to dynamically generate the columns. Even when it does create the columns, I can’t get them displayed in any type of order…
Below is as close as I feel I’ve gotten, as it generates an array of columns, but even this has been unfruitful for my needs. Perhaps it works for you?

$RelevantProperties  = @('ComputerName', 'Property1') #... This is example data, not my actual code...
$Columns = $MyArrayOfObjects[0].PSObject.Properties.Name.Where{ $PSitem -in $RelevantProperties }.ForEach{
    $Column = New-UDTableColumn -Id $PSItem -Title $PSItem -Property $PSItem -ShowSort -ShowFilter -IncludeInSearch -IncludeInExport -FilterType text
    if ($PSItem -eq 'ComputerName') { 
        $Column.isDefaultSortColumn = $true 
        $Column.Id = '0' -f $Column.Id

I’m using this in the context of New-UDDynamic and an auto-refreshing Endpoint, and as of right now, even this is quite glitchy.

1 Like

Thanks for the reply @rbleattler - I spent the good part of a day trying to sort this out but I’ve got this working using both New-UDTable with -LoadData as well as using the component New-UDSQLTable. The New-UDTable with -LoadData method seems to give more customisation options so I’m using that instead…

I’ll throw all the code in here for the New-UDTable with -LoadData way, as it might help others out. A part I was tripped up on for a while was that the dbatools cmdlet Invoke-DbaQuery doesn’t seem to work in a dashboard when you to change the returned type from the default ‘DataRow’ to a ‘PSObject’ (or anything else like DataSet etc.).


(I’ve taken out a bunch of extra code like loading modals and things, the below should be enough for it to work with dynamically generated columns)

New-UDPage -Name "SQLQueries" -Content {

    Import-Module dbatools

    New-UDGrid -Container -Content {

        New-UDGrid -Item -ExtraSmallSize 12 -SmallSize 12 -MediumSize 12 -LargeSize 12 -Content {

            New-UDElement -Tag div -Attributes @{style=@{marginTop='20px'}} -Content {

                New-UDTypography -Text "SQL Queries Server-Side" -Variant 'h2' 


            New-UDStyle -Style "        


                    border-style: dashed;

                    border-color: hotpink;

                    padding: 10px;


                " -Content {

                    New-UDCodeEditor -Id 'codeeditorsql' -Height '200' -Language 'sql' -code "select route_id,route_desc from route"


            New-UDButton -Text "Run query using New-UDTable and -LoadData" -OnClick {

                [string]$session:query = $null

                [array]$session:UDColumns = $null

                $columnNamesFromQuery = $null

                Sync-UDElement -Id 'sqlTableServerSide'

                $querySplit = ((Get-UDElement -Id 'codeeditorsql').Code) -Split '\r\n'

                foreach($line in $querySplit) {

                    if(-not($line.StartsWith("--"))) {

                        $session:query += $line



                Show-UDToast -Message "query to run: $session:query"

                $columnNamesFromQuery = (Invoke-DbaQuery -SqlCredential $cache:cfgSQLcreds -SqlInstance $cache:cfgSQLserver -Database $cache:cfgSQLdatabase -Query "SELECT top 1 * FROM ( $session:query ) AS subquery;" -ReadOnly).Table.Columns | Select-Object -ExpandProperty ColumnName

                [array]$session:UDColumns = @()

                if($null -ne $columnNamesFromQuery) {

                    foreach($colName in $columnNamesFromQuery) {

                        [array]$session:UDColumns += New-UDTableColumn -Property $colName -IncludeInExport -IncludeInSearch -ShowFilter -FilterType text


                } else {

                    [array]$session:UDColumns += "col names blank"


                Sync-UDElement -Id 'sqlTableServerSide'

            } # end run query button


            New-UDDynamic -Id 'sqlTableServerSide' -Content {

                New-UDTable -Id 'dummyIdForTable' -Title 'Query result using New-UDTable and -LoadData...' -Columns @($session:UDColumns) -Paging -PageSize 10 -ShowSort -Dense -Export -ExportOption "csv,xlsx" -LoadData {

                    $TableData = ConvertFrom-Json $Body

                    $OrderBy = $TableData.orderBy.field

                    if ($OrderBy -eq $null) {

                        $OrderBy = $TableData.properties[0]


                    $OrderDirection = $TableData.OrderDirection

                    if ($OrderDirection -eq $null) {

                        $OrderDirection = 'asc'


                    $Where = ""

                    if ($TableData.Filters) {

                        $Where = "WHERE "

                        foreach($filter in $TableData.Filters) {

                            $Where += $filter.id + " LIKE '%" + $filter.value + "%' AND "


                        $Where += " 1 = 1"


                    $PageSize = $TableData.PageSize 

                    # Calculate the number of rows to skip

                    $Offset = $TableData.Page * $PageSize

                    $Count = Invoke-DbaQuery -SqlCredential $cache:cfgSQLcreds -SqlInstance $cache:cfgSQLserver -Database $cache:cfgSQLdatabase -Query "SELECT COUNT(*) FROM ( $session:query $Where ) AS subquery;" -ReadOnly

                    $Data = Invoke-DbaQuery -SqlCredential $cache:cfgSQLcreds -SqlInstance $cache:cfgSQLserver -Database $cache:cfgSQLdatabase -Query "$session:query $Where ORDER BY $OrderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY;" -ReadOnly

                    #Show-UDToast "page=$($TableData.page), pagesize=$($TableData.pagesize), totalcount=$($Count.Column1), filters=$($TableData.filters), search=$($TableData.search)" -Duration 3000

                    #Show-UDToast "FINAL QUERY: $session:query $Where ORDER BY $OrderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" -Duration 3000

                    $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Column1 -Properties $TableData.properties


            } # end dynamic block for server side query 

        } # end grid item

    } # end main grid container

} -NavigationLayout Permanent -LoadNavigation $Navigation # end this page