Operaciones CRUD de SQL en un panel de PowerShell Universal

Recorra un panel CRUD de SQL completo en PowerShell Universal: paginación y filtrado del lado del servidor con New-UDTable e Invoke-DbaQuery, más botones Add, Edit y Delete conectados a INSERT, UPDATE y DELETE sobre una tabla User.

29 de noviembre de 2021

Adam Driscoll

En esta entrada crearemos un panel de PowerShell Universal que pueda realizar operaciones CRUD básicas en SQL.

Usaremos una tabla User sencilla para agregar, actualizar, leer y eliminar registros con botones, formularios y una tabla UD. Al final de este ejercicio, tendrá un panel como el que se muestra a continuación.

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

Requisitos previos

Esta entrada supone que tiene Microsoft SQL Server instalado con una base de datos llamada universal y una tabla llamada User. La tabla User tiene las columnas Id, Name, Role y CreatedDate.

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

También aprovecharemos dbatools para consultar la base de datos:

Install-Module dbatools

Crear el panel

Primero, cree un panel. Este ejemplo usa PowerShell 7.2 y el framework predeterminado. En PowerShell Universal, haga clic en User Interfaces > Dashboards > Create New Dashboard. Active Auto-Deploy para que el panel se recargue cuando guarde los cambios.

Cuando exista el panel, ábralo con Details y empiece a editar el código. Cree dos variables para la instancia SQL y el nombre de la base de datos. Este ejemplo usa autenticación integrada; también puede pasar credenciales para su base de datos.

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

Ver usuarios en una tabla

New-UDTable admite procesamiento del lado del servidor para que la ordenación, el filtrado y la paginación puedan realizarse en SQL en lugar de cargar cada fila en el navegador.

Empiece con una función que acepte una instancia SQL y un nombre de base de datos:

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

A continuación, defina las columnas. Name y Role se muestran tal cual y permiten filtrado del lado del servidor. CreatedDate se representa con New-UDDateTime para una fecha más legible.

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

Envuelva New-UDTable en New-UDDynamic para poder recargar la tabla tras las ediciones. Pase las columnas, active la ordenación, el filtrado y la paginación, y use el formato dense para reducir el espacio en blanco. -LoadData llama a SQL para cargar las filas del estado actual de la tabla.

Dentro de -LoadData, use Invoke-DbaQuery para contar registros, aplicar ordenación y filtro, paginar los resultados y devolver datos 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
    }
}

Agregue la función de tabla al panel:

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.

Agregar usuarios a la base de datos

Con los usuarios visibles en la tabla, agregue un botón para crear otros nuevos.

Cree una función que acepte $SqlInstance y $Database:

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

Luego cree un New-UDButton con un icono, texto y un controlador OnClick que abre un modal:

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

Dentro del modal, defina un New-UDForm con un cuadro de texto Name y un selector 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 {
}

En el controlador OnSubmit, llame a Invoke-DbaQuery con parámetros de $EventData, oculte el modal y actualice la tabla 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'
            }
        }
    }
}

Conecte el botón en el panel encima de la tabla:

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

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

Editar usuarios en la base de datos

La edición sigue el mismo patrón de modal. Pase la fila seleccionada al botón de edición, rellene previamente el formulario con -Value y -DefaultValue, y ejecute un UPDATE al enviar.

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

Agregue una columna Edit que pase la fila actual al botón:

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

Eliminar usuarios de SQL

El botón de eliminación omite el modal. Emite un DELETE y luego actualiza la tabla:

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

Coloque Edit y Delete en la misma columna:

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

Conclusión

Ahora tiene un panel que habla directamente con SQL para crear, leer, actualizar y eliminar. A partir de aquí podría validar entradas, usar steppers para formularios más complejos o avisar antes de eliminar un usuario. El código fuente completo del panel está abajo. Esta entrada se escribió 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
}

¿Quiere probarlo usted mismo? Obtenga PowerShell Universal en el centro de descargas.