Ask for Example of New-UDDataGrid with standard MSSQL PS Module

PowerShell Universal
1 
Product: PowerShell Universal
Version: 3.3.4

Hi,

does anyone have an example of New-UDDataGrid using the standard mssql powershell module instead of DBATools module?

Thanks

2

So using Invoke-sqlcmd?

The example in the documentation using DBATools should be pretty applicable, just swap out invoke-dbaquery with invoke-sqlcmd.

Though I do recommend using dbatools as it has some extra nice features (Such as returning the object as a PSObject).

3

There is SQLParameters and i dont see thats working with Invoke-sqlcmd…

But i didnt know till now :wink:

Yeah… here is my Part… ASIS:

    function Out-UDSQLDataGrid {
        param(
            [Parameter(Mandatory)]
            $Context, 
            [Parameter(Mandatory)]
            [string]$Table, 
            [Parameter(Mandatory)]
            [string]$SqlInstance,
            [Parameter(Mandatory)]
            [string]$Database
        )
        End {
            $SqlFilter = "WHERE "
            #$SqlParameters = @{}
            $Filter = $Context.Filter 
            foreach ($item in $Filter.Items) {
                $Property = $item.columnField
                $Value = $item.Value
                if ($Null -eq $Value){$Value = ""}
                $BadChar = "'"
                #$Parameter = "Param" + $SqlParameters.Count
                if ($item.operatorValue -eq 'contains') {
                    #$SqlParameters.Add($Parameter, "%$Value%") | Out-Null
                    $Parameter = "'%$($Value.replace($BadChar,''))%'"
                } else {
                    #$SqlParameters.Add($Parameter, $Value) | Out-Null
                    $Parameter = "'$($Value.replace($BadChar,''))'"
                }
                switch ($item.operatorValue) {
                    "contains" { $SqlFilter += "[$Property] LIKE $Parameter AND " } 
                    "equals" { $SqlFilter += "[$Property] = $Parameter AND " }  
                    "isEmpty" { $SqlFilter += "[$Property] IS NULL AND" }
                    "isNotEmpty" { $SqlFilter += "[$Property] IS NOT NULL AND" }
                    "startsWith" { $SqlFilter += "[$Property] LIKE '$($Value.replace($BadChar,''))%' AND " }
                    "endsWith" { $SqlFilter += "[$Property] LIKE '%$($Value.replace($BadChar,''))' AND " } 
                    "isAnyOf" {
                        if (![string]::IsNullOrEmpty($Item.Value)){
                            $SqlFilter += "("
                            foreach ($v in $Item.Value){
                                $SqlFilter += "[$Property] LIKE '$($V.replace($BadChar,''))' OR " 
                            }
                            $SqlFilter = $SqlFilter.TrimEnd("OR ")
                            $SqlFilter += ") AND "
                        }
                    }
                }
            }
            if ($Filter.linkOperator -eq "or") { 
                $SqlFilter = $SqlFilter.Replace(" AND "," OR ") 
                $SqlFilter = $SqlFilter -replace "(.*) OR (.*)", '$1 AND $2'
                }
            $SQLFilter += " 1 = 1"
            $TotalCount = (Invoke-Sqlcmd -ServerInstance $SqlInstance -Query "SELECT COUNT(*) As Count FROM $Table $SqlFilter" ).Count 
            $Sort = $Context.Sort[0]
            if (![string]::IsNullOrEmpty($Sort)) {
                $SqlSort = "ORDER BY $($Sort.field) $($Sort.Sort) "
            } else {
                $SqlSort = "ORDER BY 1 "
            }
            $SqlPage = "OFFSET $($Context.Page * $Context.PageSize) ROWS FETCH NEXT $($Context.PageSize) ROWS ONLY;"
            $Query = "SELECT * FROM $Table $SqlFilter $SqlSort $SqlPage"
            #Show-UDToast $Query -Duration 50000
            $Rows = Invoke-Sqlcmd -ServerInstance $SqlInstance -Query $Query 
            if (($rows | Measure-Object).count -gt 0){
                $SQLSpalten = @()
                $SQLSpalten += ($rows | Get-Member | Where-Object { $_.MemberType -eq "Property" }).Name
                @{
                    rows     = [Array]$Rows | Select-Object $SQLSpalten
                    rowCount = $TotalCount
                }
            }
            else {
                @{
                    rows     = $Null
                    rowCount = 0
                }
            }
        }    
    }
    New-UDPaper -Children {
        New-UDDataGrid -LoadRows {  
            Out-UDSqlDataGrid -Context $EventData -SqlInstance "localhost" -Database "[Database]" -Table "[Table]"
        } -Columns @(
            @{ field = "id"; }
            @{ field = "servername"; }
            @{ field = "dpm"; render = {
                    if ($EventData.dpm -eq 1) {
                        New-UDAlert -Severity 'Success' -Text 'Ja'
                    }
                    if ($EventData.dpm -eq 0) {
                        New-UDAlert -Severity 'Error' -Text 'Nein'
                    }
                } 
            }
        ) -AutoHeight -Pagination
    } -Elevation 4 -Id "PaperDataGrid"
4

Ah, I missed that when looking at it. But looks like you’ve figured it out (just inserting the variables into the query, rather than using @param in the query and specifying the variables like you can do with dbatools).