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! 
Cheers!
Steve.
@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
}
$Column
}
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
Cheers,
Steve.
2 Likes