Operazioni CRUD SQL in un dashboard PowerShell Universal

Percorrere un dashboard CRUD SQL completo in PowerShell Universal: paging e filtro lato server di New-UDTable con Invoke-DbaQuery, più pulsanti Add, Edit e Delete collegati a INSERT, UPDATE e DELETE su una tabella User.

29 novembre 2021

Adam Driscoll

In questo post creeremo un dashboard PowerShell Universal in grado di eseguire operazioni CRUD di base in SQL.

Useremo una tabella User semplice per aggiungere, aggiornare, leggere ed eliminare record con pulsanti, form e una tabella UD. Al termine di questo esercizio, avrete un dashboard come quello mostrato di seguito.

PowerShell Universal Users dashboard with an Add button and a sortable user table.

Prerequisiti

Questo post presuppone che Microsoft SQL Server sia installato con un database chiamato universal e una tabella chiamata User. La tabella User ha le colonne Id, Name, Role e CreatedDate.

SQL Server User table with Id, Name, Role, and CreatedDate columns.

Sfrutteremo anche dbatools per interrogare il database:

Install-Module dbatools

Creare il dashboard

Per prima cosa, create un dashboard. Questo esempio usa PowerShell 7.2 e il framework predefinito. In PowerShell Universal, fate clic su User Interfaces > Dashboards > Create New Dashboard. Abilitate Auto-Deploy in modo che il dashboard si ricarichi quando salvate le modifiche.

Quando il dashboard esiste, apritelo con Details e iniziate a modificare il codice. Create due variabili per l’istanza SQL e il nome del database. Questo esempio usa l’autenticazione integrata; potete anche passare le credenziali per il database.

New-UDDashboard -Title 'Users' -Content {
    $SqlInstance = '(localdb)\MSSQLLocalDB'
    $Database = 'universal'
}

Visualizzare gli utenti in una tabella

New-UDTable supporta l’elaborazione lato server in modo che ordinamento, filtro e paging possano avvenire in SQL invece di caricare ogni riga nel browser.

Iniziate con una funzione che accetta un’istanza SQL e un nome di database:

function New-UserTable {
    param($SqlInstance, $Database)
}

Quindi definite le colonne. Name e Role vengono visualizzati così come sono e consentono il filtro lato server. CreatedDate viene reso con New-UDDateTime per una data più leggibile.

$TableColumns = @(
    New-UDTableColumn -Title 'Name' -Property 'Name' -Filter
    New-UDTableColumn -Title 'Role' -Property 'Role' -Filter
    New-UDTableColumn -Title 'Created' -Property 'CreatedDate' -Render {
        New-UDDateTime $EventData.CreatedDate
    }
)

Avvolgete New-UDTable in New-UDDynamic così potete ricaricare la tabella dopo le modifiche. Passate le colonne, abilitate ordinamento, filtro e paging, e usate il formato dense per ridurre gli spazi bianchi. -LoadData chiama SQL per caricare le righe dello stato corrente della tabella.

Dentro -LoadData, usate Invoke-DbaQuery per contare i record, applicare ordinamento e filtro, paginare i risultati e restituire i dati con Out-UDTableData:

function New-UserTable {
    param($SqlInstance, $Database)

    $TableColumns = @(
        New-UDTableColumn -Title 'Name' -Property 'Name' -Filter
        New-UDTableColumn -Title 'Role' -Property 'Role' -Filter
        New-UDTableColumn -Title 'Created' -Property 'CreatedDate' -Render {
            New-UDDateTime $EventData.CreatedDate
        }
    )

    New-UDDynamic -Id 'UserTable' -Content {
        New-UDTable -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 = ' '

            $SqlParameters = $null
            $CountSqlParameters = $null

            if ($TableData.Filters) {
                $SqlParameters = @()
                $CountSqlParameters = @()
                $Where = 'WHERE '
                foreach ($filter in $TableData.Filters) {
                    $SqlParameters += New-DbaSqlParameter -Name $filter.Id -Value "%$($filter.Value)%"
                    $CountSqlParameters += New-DbaSqlParameter -Name $filter.Id -Value "%$($filter.Value)%"
                    $Where += $filter.id + " LIKE @$($filter.Id) AND "
                }
                $Where += ' 1 = 1'
            }

            $PageSize = $TableData.PageSize
            $Offset = $TableData.Page * $PageSize

            $Parameters = @{
                SqlInstance = $SqlInstance
                Database    = $Database
                Query       = "SELECT COUNT(*) AS count FROM [User] $Where"
                SqlParameter = $CountSqlParameters
            }

            $Count = Invoke-DbaQuery @Parameters

            $Parameters = @{
                SqlInstance = $SqlInstance
                Database    = $Database
                Query       = "SELECT * FROM [User] $Where ORDER BY $OrderBy $OrderDirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY"
                SqlParameter = $SqlParameters
            }

            $Data = Invoke-DbaQuery @Parameters
            $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties
        } -Columns $TableColumns -Sort -Filter -Paging -Dense
    }
}

Aggiungete la funzione tabella al dashboard:

New-UDDashboard -Title 'Users' -Content {
    $SqlInstance = '(localdb)\MSSQLLocalDB'
    $Database = 'universal'

    New-UserTable -SqlInstance $SqlInstance -Database $Database
}

PowerShell Universal table listing SQL users with Name, Role, and Created columns.

Aggiungere utenti al database

Con gli utenti visibili nella tabella, aggiungete un pulsante per crearne di nuovi.

Create una funzione che accetta $SqlInstance e $Database:

function New-AddUserButton {
    param($SqlInstance, $Database)
}

Poi create un New-UDButton con un’icona, del testo e un gestore OnClick che apre un modal:

New-UDButton -Icon (New-UDIcon -Icon 'UserPlus') -Text 'Add' -OnClick {
    Show-UDModal -Content {
    }
}

Nel modal, definite un New-UDForm con una casella di testo Name e un selettore Role:

New-UDForm -Content {
    New-UDTextbox -Id 'Name'
    New-UDSelect -Id 'Role' -Option {
        New-UDSelectOption -Name 'Administrator' -Value 'Admin'
        New-UDSelectOption -Name 'Human Resources' -Value 'HR'
        New-UDSelectOption -Name 'Development' -Value 'Dev'
    }
} -OnSubmit {
}

Nel gestore OnSubmit, chiamate Invoke-DbaQuery con i parametri da $EventData, nascondete il modal e aggiornate la tabella con Sync-UDElement:

function New-AddUserButton {
    param($SqlInstance, $Database)

    New-UDButton -Icon (New-UDIcon -Icon 'UserPlus') -Text 'Add' -OnClick {
        Show-UDModal -Content {
            New-UDForm -Content {
                New-UDTextbox -Id 'Name'
                New-UDSelect -Id 'Role' -Option {
                    New-UDSelectOption -Name 'Administrator' -Value 'Admin'
                    New-UDSelectOption -Name 'Human Resources' -Value 'HR'
                    New-UDSelectOption -Name 'Development' -Value 'Dev'
                }
            } -OnSubmit {
                $Name = New-DbaSqlParameter -Name 'name' -Value $EventData.Name
                $Role = New-DbaSqlParameter -Name 'role' -Value $EventData.Role

                $Parameters = @{
                    SqlInstance  = $SqlInstance
                    Database     = $Database
                    Query        = 'INSERT INTO [User] (Name, Role, CreatedDate) VALUES (@name, @role, GetDate())'
                    SqlParameter = @($Name, $Role)
                }

                Invoke-DbaQuery @Parameters | Out-Null
                Hide-UDModal
                Sync-UDElement -Id 'UserTable'
            }
        }
    }
}

Collegate il pulsante nel dashboard sopra la tabella:

New-UDDashboard -Title 'Users' -Content {
    $SqlInstance = '(localdb)\MSSQLLocalDB'
    $Database = 'universal'

    New-AddUserButton -SqlInstance $SqlInstance -Database $Database
    New-UserTable -SqlInstance $SqlInstance -Database $Database
}

Modificare gli utenti nel database

La modifica segue lo stesso schema del modal. Passate la riga selezionata al pulsante di modifica, precompilate il form con -Value e -DefaultValue, poi eseguite un UPDATE all’invio.

function New-UserEditButton {
    param($EventData, $SqlInstance, $Database)

    New-UDButton -Icon (New-UDIcon -Icon 'UserEdit') -Text 'Edit' -OnClick {
        $RecordId = $EventData.Id
        Show-UDModal -Content {
            New-UDForm -Content {
                New-UDTextbox -Value $EventData.Name -Id 'Name'
                New-UDSelect -DefaultValue $EventData.Role -Id 'Role' -Option {
                    New-UDSelectOption -Name 'Administrator' -Value 'Admin'
                    New-UDSelectOption -Name 'Human Resources' -Value 'HR'
                    New-UDSelectOption -Name 'Development' -Value 'Dev'
                }
            } -OnSubmit {
                $Name = New-DbaSqlParameter -Name 'name' -Value $EventData.Name
                $Role = New-DbaSqlParameter -Name 'role' -Value $EventData.Role

                $Parameters = @{
                    SqlInstance  = $SqlInstance
                    Database     = $Database
                    Query        = "UPDATE [User] SET name = @name, role = @role WHERE Id = $RecordId"
                    SqlParameter = @($Name, $Role)
                }

                Invoke-DbaQuery @Parameters | Out-Null
                Hide-UDModal
                Sync-UDElement -Id 'UserTable'
            }
        }
    }
}

Aggiungete una colonna Edit che passa la riga corrente al pulsante:

$TableColumns = @(
    New-UDTableColumn -Title 'Name' -Property 'Name' -Filter
    New-UDTableColumn -Title 'Role' -Property 'Role' -Filter
    New-UDTableColumn -Title 'Created' -Property 'CreatedDate' -Render {
        New-UDDateTime $EventData.CreatedDate
    }
    New-UDTableColumn -Title 'Edit' -Property 'Edit' -Render {
        New-UserEditButton -EventData $EventData -SqlInstance $SqlInstance -Database $Database
    }
)

Eliminare utenti da SQL

Il pulsante di eliminazione salta il modal. Esegue un DELETE, poi aggiorna la tabella:

function New-UserDeleteButton {
    param($EventData, $SqlInstance, $Database)

    New-UDButton -Icon (New-UDIcon -Icon 'UserTimes') -Text 'Delete' -OnClick {
        $RecordId = $EventData.Id

        $Parameters = @{
            SqlInstance = $SqlInstance
            Database    = $Database
            Query       = "DELETE FROM [User] WHERE Id = $RecordId"
        }

        Invoke-DbaQuery @Parameters | Out-Null
        Sync-UDElement -Id 'UserTable'
    }
}

Mettete Edit e Delete nella stessa colonna:

$TableColumns = @(
    New-UDTableColumn -Title 'Name' -Property 'Name' -Filter
    New-UDTableColumn -Title 'Role' -Property 'Role' -Filter
    New-UDTableColumn -Title 'Created' -Property 'CreatedDate' -Render {
        New-UDDateTime $EventData.CreatedDate
    }
    New-UDTableColumn -Title 'Edit' -Property 'Edit' -Render {
        New-UserEditButton -EventData $EventData -SqlInstance $SqlInstance -Database $Database
        New-UserDeleteButton -EventData $EventData -SqlInstance $SqlInstance -Database $Database
    }
)

Conclusione

Ora avete un dashboard che parla direttamente con SQL per create, read, update e delete. Da qui potreste convalidare gli input, usare gli stepper per form più complessi o avvisare prima di eliminare un utente. Il codice sorgente completo del dashboard è sotto. Questo post è stato scritto con PowerShell Universal 2.5.5.

function New-AddUserButton {
    param($SqlInstance, $Database)

    New-UDButton -Icon (New-UDIcon -Icon 'UserPlus') -Text 'Add' -OnClick {
        Show-UDModal -Content {
            New-UDForm -Content {
                New-UDTextbox -Id 'Name'
                New-UDSelect -Id 'Role' -Option {
                    New-UDSelectOption -Name 'Administrator' -Value 'Admin'
                    New-UDSelectOption -Name 'Human Resources' -Value 'HR'
                    New-UDSelectOption -Name 'Development' -Value 'Dev'
                }
            } -OnSubmit {
                $Name = New-DbaSqlParameter -Name 'name' -Value $EventData.Name
                $Role = New-DbaSqlParameter -Name 'role' -Value $EventData.Role

                $Parameters = @{
                    SqlInstance  = $SqlInstance
                    Database     = $Database
                    Query        = 'INSERT INTO [User] (Name, Role, CreatedDate) VALUES (@name, @role, GetDate())'
                    SqlParameter = @($Name, $Role)
                }

                Invoke-DbaQuery @Parameters | Out-Null
                Hide-UDModal
                Sync-UDElement -Id 'UserTable'
            }
        }
    }
}

function New-UserEditButton {
    param($EventData, $SqlInstance, $Database)

    New-UDButton -Icon (New-UDIcon -Icon 'UserEdit') -Text 'Edit' -OnClick {
        $RecordId = $EventData.Id
        Show-UDModal -Content {
            New-UDForm -Content {
                New-UDTextbox -Value $EventData.Name -Id 'Name'
                New-UDSelect -DefaultValue $EventData.Role -Id 'Role' -Option {
                    New-UDSelectOption -Name 'Administrator' -Value 'Admin'
                    New-UDSelectOption -Name 'Human Resources' -Value 'HR'
                    New-UDSelectOption -Name 'Development' -Value 'Dev'
                }
            } -OnSubmit {
                $Name = New-DbaSqlParameter -Name 'name' -Value $EventData.Name
                $Role = New-DbaSqlParameter -Name 'role' -Value $EventData.Role

                $Parameters = @{
                    SqlInstance  = $SqlInstance
                    Database     = $Database
                    Query        = "UPDATE [User] SET name = @name, role = @role WHERE Id = $RecordId"
                    SqlParameter = @($Name, $Role)
                }

                Invoke-DbaQuery @Parameters | Out-Null
                Hide-UDModal
                Sync-UDElement -Id 'UserTable'
            }
        }
    }
}

function New-UserDeleteButton {
    param($EventData, $SqlInstance, $Database)

    New-UDButton -Icon (New-UDIcon -Icon 'UserTimes') -Text 'Delete' -OnClick {
        $RecordId = $EventData.Id

        $Parameters = @{
            SqlInstance = $SqlInstance
            Database    = $Database
            Query       = "DELETE FROM [User] WHERE Id = $RecordId"
        }

        Invoke-DbaQuery @Parameters | Out-Null
        Sync-UDElement -Id 'UserTable'
    }
}

function New-UserTable {
    param($SqlInstance, $Database)

    $TableColumns = @(
        New-UDTableColumn -Title 'Name' -Property 'Name' -Filter
        New-UDTableColumn -Title 'Role' -Property 'Role' -Filter
        New-UDTableColumn -Title 'Created' -Property 'CreatedDate' -Render {
            New-UDDateTime $EventData.CreatedDate
        }
        New-UDTableColumn -Title 'Edit' -Property 'Edit' -Render {
            New-UserEditButton -EventData $EventData -SqlInstance $SqlInstance -Database $Database
            New-UserDeleteButton -EventData $EventData -SqlInstance $SqlInstance -Database $Database
        }
    )

    New-UDDynamic -Id 'UserTable' -Content {
        New-UDTable -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 = ' '

            $SqlParameters = $null
            $CountSqlParameters = $null

            if ($TableData.Filters) {
                $SqlParameters = @()
                $CountSqlParameters = @()
                $Where = 'WHERE '
                foreach ($filter in $TableData.Filters) {
                    $SqlParameters += New-DbaSqlParameter -Name $filter.Id -Value "%$($filter.Value)%"
                    $CountSqlParameters += New-DbaSqlParameter -Name $filter.Id -Value "%$($filter.Value)%"
                    $Where += $filter.id + " LIKE @$($filter.Id) AND "
                }
                $Where += ' 1 = 1'
            }

            $PageSize = $TableData.PageSize
            $Offset = $TableData.Page * $PageSize

            $Parameters = @{
                SqlInstance  = $SqlInstance
                Database     = $Database
                Query        = "SELECT COUNT(*) AS count FROM [User] $Where"
                SqlParameter = $CountSqlParameters
            }

            $Count = Invoke-DbaQuery @Parameters

            $Parameters = @{
                SqlInstance  = $SqlInstance
                Database     = $Database
                Query        = "SELECT * FROM [User] $Where ORDER BY $OrderBy $OrderDirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY"
                SqlParameter = $SqlParameters
            }

            $Data = Invoke-DbaQuery @Parameters
            $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties
        } -Columns $TableColumns -Sort -Filter -Paging -Dense
    }
}

New-UDDashboard -Title 'Users' -Content {
    $SqlInstance = '(localdb)\MSSQLLocalDB'
    $Database = 'universal'

    New-AddUserButton -SqlInstance $SqlInstance -Database $Database
    New-UserTable -SqlInstance $SqlInstance -Database $Database
}

Volete provarlo voi stessi? Scaricate PowerShell Universal dal centro download.