SQL CRUD-handlinger i et PowerShell Universal-dashboard

Gennemgå et komplet SQL CRUD-dashboard i PowerShell Universal: server-side paging og filtrering i New-UDTable med Invoke-DbaQuery samt knapperne Add, Edit og Delete, der er koblet til INSERT, UPDATE og DELETE mod en User-tabel.

29. november 2021

Adam Driscoll

I det her indlæg bygger vi et PowerShell Universal-dashboard, der kan udføre grundlæggende CRUD-handlinger i SQL.

Vi bruger en simpel User-tabel til at tilføje, opdatere, læse og slette poster med knapper, formularer og en UD-tabel. Når du er færdig med øvelsen, har du et dashboard, der ligner det nedenfor.

PowerShell Universal-dashboardet Users med en Add-knap og en sorterbar brugertabel.

Forudsætninger

Indlægget forudsætter, at du har Microsoft SQL Server installeret med en database, der hedder universal, og en tabel, der hedder User. Tabellen User har kolonnerne Id, Name, Role og CreatedDate.

SQL Server-tabellen User med kolonnerne Id, Name, Role og CreatedDate.

Vi bruger også dbatools til at forespørge databasen:

Install-Module dbatools

Opret dashboardet

Opret først et dashboard. Eksemplet bruger PowerShell 7.2 og standardframeworket. I PowerShell Universal klikker du på User Interfaces > Dashboards > Create New Dashboard. Slå Auto-Deploy til, så dashboardet genindlæses, når du gemmer ændringer.

Når dashboardet findes, åbner du det med Details og begynder at redigere koden. Opret to variabler til SQL-instansen og databasenavnet. Eksemplet bruger integreret godkendelse. Du kan også sende legitimationsoplysninger til din database.

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

Vis brugere i en tabel

New-UDTable understøtter server-side-behandling, så sortering, filtrering og paging kan ske i SQL i stedet for at indlæse alle rækker i browseren.

Start med en funktion, der tager en SQL-instans og et databasenavn:

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

Definér derefter kolonnerne. Name og Role vises, som de er, og tillader filtrering på serveren. CreatedDate gengives med New-UDDateTime, så datoen er lettere at læse.

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

Læg New-UDTable ind i New-UDDynamic, så du kan genindlæse tabellen efter redigeringer. Send kolonnerne med, slå sortering, filtrering og paging til, og brug det kompakte format for at mindske mellemrum. -LoadData kalder SQL for at indlæse rækker til tabellens aktuelle tilstand.

Inde i -LoadData bruger du Invoke-DbaQuery til at tælle poster, anvende sortering og filter, sideinddele resultaterne og returnere data med 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
    }
}

Føj tabelfunktionen til dashboardet:

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

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

PowerShell Universal-tabel med SQL-brugere og kolonnerne Name, Role og Created.

Tilføj brugere til databasen

Når brugerne er synlige i tabellen, tilføjer du en knap til at oprette nye.

Opret en funktion, der tager $SqlInstance og $Database:

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

Opret derefter en New-UDButton med et ikon, en tekst og en OnClick-handler, der åbner en modal:

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

Inde i modalen definerer du en New-UDForm med et tekstfelt til Name og en select til 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 {
}

I handleren OnSubmit kalder du Invoke-DbaQuery med parametre fra $EventData, skjuler modalen og opdaterer tabellen med 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'
            }
        }
    }
}

Sæt knappen ind i dashboardet over tabellen:

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

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

Redigér brugere i databasen

Redigering følger det samme modalmønster. Send den valgte række ind i redigeringsknappen, udfyld formularen på forhånd med -Value og -DefaultValue, og kør derefter en UPDATE, når formularen sendes.

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

Tilføj en Edit-kolonne, der sender den aktuelle række ind i knappen:

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

Slet brugere fra SQL

Slet-knappen springer modalen over. Den udfører en DELETE og opdaterer derefter tabellen:

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

Læg både Edit og Delete i den samme kolonne:

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

Konklusion

Du har nu et dashboard, der taler direkte med SQL for at oprette, læse, opdatere og slette. Herfra kan du validere input, bruge steppers til mere komplekse formularer eller advare, før en bruger slettes. Den fulde kildekode til dashboardet står nedenfor. Indlægget er skrevet mod 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
}

Vil du prøve det selv? Hent PowerShell Universal i downloadcentret.