SQL CRUD Operations in PowerShell Universal Dashboard

Image Description

Daily PowerShell #44

Daily PowerShell SQL Universal Dashboard PowerShell Universal

November 29, 2021

quote Discuss this Article

In this post, we’ll create a PowerShell Universal dashboard that can perform basic CRUD operations in SQL.

We’ll use a basic User table to add, update, read and delete records from the SQL table using buttons, forms and a UD table. By the end of this exercise, you’ll have a dashboard that looks like the one below.

Pre-requisites

This blog post assumes that you have Microsoft SQL Server installed with a database called universal created and a table called User. The User table has Id, Name, Role and CreatedDate columns.

We are also going to take advantage of DbaTools to query the database.

Install-Module dbatools

Creating the Dashboard

First, we’ll create a dashboard. My dashboard is using PowerShell 7.2 and the default framework. You can create a dashboard in PowerShell Universal by clicking User Interfaces \ Dashboards \ Create New Dashboard. I have also enabled Auto-Deploy so that my dashboard reloads when changes are made.

Once the dashboard has been created, we can start to edit the dashboard code by clicking the Details button for the dashboard. I’ll create two variables for the SQL Instance and database name. I’m using integrated authentication but you could also specify credentials for your database.

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

View Users in a Table

Now that we have our dashboard, we can start to add functionality.

New-UDTable supports server-side processing to allow for efficient interaction with systems like SQL. Instead of retrieving all data within the browser and sorting, filtering and paging locally, we can use SQL to do so at the server.

First, let’s create a function for our table. That accepts a SQL Instance and database name.

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

Next, let’s setup the columns we want to display. The Name and Role columns will be displayed as-is and allow for server-side filtering. The CreatedBy column will be rendered using the New-UDDateTime component to display the date in a more human-readable format.

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

We’ll wrap New-UDTable in a New-UDDynamic to allow reload of the table after we edit the data. We’ll also pass in our columns, enable sorting, filtering and paging and also use the dense format to reduce whitespace. -LoadData will be used to call SQL to load data within the table.

    New-UDDynamic -Id 'UserTable' -Content {
        New-UDTable -LoadData {
        }
    } -Columns $TableColumns -Sort -Filter -Paging -Dense

Within the -LoadData parameter we will use Invoke-DbaQuery to get a count of records, sort, filter, page and query the data. You can review the code below.

# Get information about the current state of the table
$TableData = ConvertFrom-Json $Body

# Check to see if we are sorting by a column, if not use the name. 
$OrderBy = $TableData.orderBy.field
if ($OrderBy -eq $null)
{
    $OrderBy = "Name"
}

# Check to see if we are sorting in a particular direction, if not sort ascending
$OrderDirection = $TableData.OrderDirection
if ($OrderDirection -eq $null)
{
    $OrderDirection = 'asc'
}

$Where = " "

$SqlParameters = $null
$CountSqlParameters = $null

# If we have filters defined, create some SQL parameters and update the WHERE clause
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"
}

# Check the current page size as selected in the table
$PageSize = $TableData.PageSize 

# Calculate the number of rows to skip
$Offset = $TableData.Page * $PageSize

# Setup parameters for counting the number of rows
$Parameters = @{
    SqlInstance = $SqlInstance 
    Database = $Database 
    Query = "SELECT COUNT(*) AS count FROM [User] $Where"
    SqlParameter = $CountSqlParameters
}

# Count the number of rows
$Count = Invoke-DbaQuery @Parameters

# Setup parameters for querying the data 
$Parameters = @{
    SqlInstance = $SqlInstance 
    Database = $Database 
    Query = "SELECT * FROM [User] $Where ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" 
    SqlParameter = $SqlParameters
}

# Query the data
$Data = Invoke-DbaQuery @Parameters

# Format the data using Out-UDTableData
$Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties

The entire table code can be viewed below.

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 
            # Calculate the number of rows to skip
            $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
    } 
}

Once we have our table function defined, we can add it to our dashboard.

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

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

Adding Users to the Database

Now that we can view users within a table, let’s add a button to create new users.

First, create a function that accepts a $SqlInstance and $Database parameter.

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

Next, we can create a button with an icon, some text and an OnClick event handler that will show a modal.

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

Within the modal, we can define a form with a Name textbox and a Select input for roles.

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

Finally, in the OnSubmit event handler, we can invoke SQL using Dbatools’ Invoke-DbaQuery. The $EventData variable will include the values passed in from the form.

# Create SQL parameters for each value passed in
$Name = New-DbaSqlParameter -Name 'name' -Value $EventData.Name
$Role = New-DbaSqlParameter -Name 'role' -Value $EventData.Role

# Setup parameters for Invoke-DbaQuery
$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 the modal once the SQL has been invoked
Hide-UDModal

# Reload the user table
Sync-UDElement -Id 'UserTable'

The full code for the add user button can be found below.

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'

            }
        }
    }
}

Now that we have our add user button defined, we can add it to our dashboard.

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

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

Editing Users in the Database

Now that we can view and add users, let’s add the functionality to edit them. Similar to the add form, we will create a form in a modal and display the selected user’s information. Submitting the form will update the database.

We will create a function that accepts the SQL instance, database name and record we are editing.


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

We can then define a button with an OnClick event handler that displays a form. Notice that we are using the -Value and -DefaultValue parameters of the input controls so that the current value will be displayed to the user.

When the form is submitted, we issue an UPDATE rather than an INSERT.

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

Once we have our edit function defined, we can add a new column to our table and place the edit button in each row. You can see we are passing the current row value to the edit button.

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

Delete Users from SQL

Finally, we will define a button to delete users. The delete button does not open a modal. Instead, it issues the DELETE against the database and refreshes the 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'
    }
}

We also need to add the delete button to each row in the table.

$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

The result of the exercise is a dashboard that directly integrates with a SQL database to allow for create, read, update and delete operations. This dashboard could be improved to validate inputs, use steppers for more complex inputs and warnings about deleting users. You’ll find the full source for the dashboard below.

Source Code Reference

The full source code can be found below. This blog post was created using 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 -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 = "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 
            # Calculate the number of rows to skip
            $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
}