Opérations CRUD SQL dans un tableau de bord PowerShell Universal

Parcourez un tableau de bord CRUD SQL complet dans PowerShell Universal : pagination et filtrage côté serveur New-UDTable avec Invoke-DbaQuery, plus des boutons Ajouter, Modifier et Supprimer branchés sur INSERT, UPDATE et DELETE contre une table User.

29 novembre 2021

Adam Driscoll

Dans cet article, nous allons créer un tableau de bord PowerShell Universal capable d’effectuer des opérations CRUD de base en SQL.

Nous utiliserons une table User simple pour ajouter, mettre à jour, lire et supprimer des enregistrements avec des boutons, des formulaires et une table UD. À la fin de cet exercice, vous aurez un tableau de bord qui ressemble à celui ci-dessous.

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

Prérequis

Cet article suppose que Microsoft SQL Server est installé avec une base de données nommée universal et une table nommée User. La table User comporte les colonnes Id, Name, Role et CreatedDate.

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

Nous allons aussi tirer parti de dbatools pour interroger la base de données :

Install-Module dbatools

Créer le tableau de bord

Commencez par créer un tableau de bord. Cet exemple utilise PowerShell 7.2 et le framework par défaut. Dans PowerShell Universal, cliquez sur User Interfaces > Dashboards > Create New Dashboard. Activez Auto-Deploy pour que le tableau de bord se recharge lorsque vous enregistrez vos modifications.

Une fois le tableau de bord créé, ouvrez-le avec Details et commencez à modifier le code. Créez deux variables pour l’instance SQL et le nom de la base de données. Cet exemple utilise l’authentification intégrée ; vous pouvez aussi transmettre des identifiants pour votre base de données.

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

Afficher les utilisateurs dans une table

New-UDTable prend en charge le traitement côté serveur afin que le tri, le filtrage et la pagination puissent s’effectuer en SQL au lieu de charger chaque ligne dans le navigateur.

Commencez par une fonction qui accepte une instance SQL et un nom de base de données :

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

Ensuite, définissez les colonnes. Name et Role s’affichent tels quels et permettent le filtrage côté serveur. CreatedDate est rendu avec New-UDDateTime pour une date plus lisible.

$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
    }
)

Enveloppez New-UDTable dans New-UDDynamic pour pouvoir recharger la table après des modifications. Passez les colonnes, activez le tri, le filtrage et la pagination, et utilisez le format dense pour réduire les espaces blancs. -LoadData appelle SQL pour charger les lignes correspondant à l’état actuel de la table.

Dans -LoadData, utilisez Invoke-DbaQuery pour compter les enregistrements, appliquer le tri et le filtre, paginer les résultats, et renvoyer les données avec 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
    }
}

Ajoutez la fonction de table au tableau de bord :

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.

Ajouter des utilisateurs à la base de données

Les utilisateurs étant visibles dans la table, ajoutez un bouton pour en créer de nouveaux.

Créez une fonction qui accepte $SqlInstance et $Database :

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

Puis créez un New-UDButton avec une icône, un texte et un gestionnaire OnClick qui ouvre une fenêtre modale :

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

Dans la fenêtre modale, définissez un New-UDForm avec une zone de texte Name et une liste déroulante 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 {
}

Dans le gestionnaire OnSubmit, appelez Invoke-DbaQuery avec les paramètres issus de $EventData, masquez la fenêtre modale, et actualisez la table avec 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'
            }
        }
    }
}

Branchez le bouton dans le tableau de bord au-dessus de la table :

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

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

Modifier des utilisateurs dans la base de données

La modification suit le même modèle de fenêtre modale. Passez la ligne sélectionnée au bouton d’édition, préremplissez le formulaire avec -Value et -DefaultValue, puis exécutez un UPDATE à la soumission.

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'
            }
        }
    }
}

Ajoutez une colonne Edit qui passe la ligne actuelle au bouton :

$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
    }
)

Supprimer des utilisateurs de SQL

Le bouton de suppression ignore la fenêtre modale. Il exécute un DELETE, puis actualise la table :

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'
    }
}

Placez Edit et Delete dans la même colonne :

$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
    }
)

Conclusion

Vous disposez maintenant d’un tableau de bord qui dialogue directement avec SQL pour créer, lire, mettre à jour et supprimer. À partir de là, vous pourriez valider les entrées, utiliser des steppers pour des formulaires plus complexes, ou avertir avant de supprimer un utilisateur. Le code source complet du tableau de bord figure ci-dessous. Cet article a été rédigé avec 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
}

Envie d’essayer par vous-même ? Téléchargez PowerShell Universal depuis le centre de téléchargement.