SQL Data Grid

An example of a data grid that queries and updates data in SQL.

This example uses Invoke-DbaQuery from dbatools to query and update data in a SQL database. It implements paging, sorting and filtering via the data grid's -LoadRows event handler. It also renders buttons within the table that updates the database based on selections.

Additionally, bulk actions are performed using checkbox selection.

This example requires the following table.

CREATE TABLE [dbo].[Certificates](
	[CertificateId] [int] NULL,
	[Decision] [varchar](255) NULL,
	[UserName] [varchar](255) NULL,
	[GroupName] [varchar](255) NULL,
	[AppName] [varchar](255) NULL
) ON [PRIMARY]

The data grid is defined within a dynamic region with some basic properties.

New-UDDynamic -Id "DynamicTable" {
      New-UDDataGrid -ShowPagination -PageSize 10 -RowsPerPageOptions @(10, 25, 50, 100, 200) -CheckboxSelection -LoadRows {

The -LoadRows event handler does much of the work but custom columns also provide an avenue for updating the database. We'll break down this example below.

Columns

The columns used in this example are basic, aside from a decision column that is used to update the SQL database.

The basic columns to display look like this. Properties that are returned from the SQL query will be available in render methods but will not be displayed.

New-UDDataGridColumn -HeaderName 'User' -Field 'UserName' -Filterable -Sortable -Description 'UserName'
New-UDDataGridColumn -HeaderName 'Group' -Field 'GroupName'  -Filterable -Sortable
New-UDDataGridColumn -HeaderName 'App' -Field 'AppName' -Filterable -Sortable

We will implement a complex column to update the SQL database. The $EventData variable will include the entire row's data. We can use this data to render buttons based on its state. If the certificate is approved, we will create green buttons. If the certificate is revoked, we will create red buttons. Clicking the buttons will run an UPDATE command and then reload the dynamic region.

New-UDDataGridColumn -HeaderName 'Decision' -Field 'Decision' -MinWidth 300 -Render {     
    New-UDDynamic -Id $EventData.CertificateId -Content {
        function New-DecisionButton {
            param(
                $Text,
                $Icon,
                $Style,
                $CertificateId,
                $Decision
            )

            New-UDButton -Text $Text -Icon $Icon -Style $Style -OnClick {
                $Session:updateButton = $true
                $Query = @"
            UPDATE Certificates
            SET Decision = $Decision
            WHERE CertificateId = $CertificateId;
"@

                Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -Query $Query
                Sync-UDElement -Id $CertificateId
            } -ShowLoading
        }

        if ($Session:updateButton) {
            $item = Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -Query ('SELECT * FROM [dbo].[Certificates] WHERE CertificateId = {0}' -f $EventData.CertificateId)
            $EventData.Decision = $item.Decision
            $Session:updateButton = $false
        }

        if ($EventData.Decision -eq 1) {
            New-DecisionButton -Text 'Approve' -Icon $Page:IconApprove -Style $Page:BtnGreenApprove -CertificateId $EventData.CertificateId -Decision 'NULL'
            New-DecisionButton -Text 'Revoke' -Icon $Page:IconRevokeNeutral -Style $Page:BtnWhiteRevoke -CertificateId $EventData.CertificateId -Decision '0'
        }
        elseif ($EventData.Decision -eq 0) {
            New-DecisionButton -Text 'Approve' -Icon $Page:IconApproveNeutral -Style $Page:BtnWhiteApprove -CertificateId $EventData.CertificateId -Decision '1'
            New-DecisionButton -Text 'Revoke' -Icon $Page:IconRevoke -Style $Page:BtnRedRevoke -CertificateId $EventData.CertificateId -Decision 'NULL'
        }
        else {
            New-DecisionButton -Text 'Approve' -Icon $Page:IconApproveNeutral -Style $Page:BtnWhiteApprove -CertificateId $EventData.CertificateId -Decision '1'
            New-DecisionButton -Text 'Revoke' -Icon $Page:IconRevokeNeutral -Style $Page:BtnWhiteRevoke -CertificateId $EventData.CertificateId -Decision '0'
        }
    }
}

Filtering

Filtering is performed by accessing the $EventData variable (assigned to $Context in this example). Filters are available as a property of the context. Filters also include the logic operator used. This will be a and or or operator. We construct a SQL where expression and populate a hashtable of parameters.

$SqlFilter = ""
$Parameters = @{}
if ($Context.Filter.Items.Count -gt 0) {
    $count = 1
    $ParameterName = "P$count"
    foreach ($filter in $Context.Filter.Items) {
        if ($count -gt 1) {
            $SqlFilter += " $($Context.Filter.logicOperator) "
        }
        else {
            $SqlFilter += " WHERE "
        }

        $Value = $Filter.Value

        switch ($filter.Operator) {
            "contains" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) LIKE CONCAT('%', @$ParameterName, '%') " }
            "equals" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) = @$ParameterName " }
            "startsWith" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) LIKE CONCAT(@$ParameterName, '%') " }
            "endsWith" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) LIKE CONCAT('%', @$ParameterName) " }
            "isAnyOf" {
                $Value = $Value -join ','
                $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) IN (SPLIT_STRING(@$ParameterName)) "
            }
            "isempty" { $SqlFilter += " TRIM ($(ConvertTo-ProperCase $filter.Field)) IS NULL " }
            "isnotempty" { $SqlFilter += " TRIM ($(ConvertTo-ProperCase $filter.Field)) IS NOT NULL " }
            "notequals" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) != @$ParameterName " }
            "notcontains" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) NOT LIKE CONCAT('%', @$ParameterName,'%') " }
        }

        $Parameters[$ParameterName] = $Value
        $count += 1
    }
}

The ConvertTo-ProperCase function adjusts the field name, which will be lower case, into the correct case used for the SQL server columns.

Paging

Paging data is also sent via the context. We can calculate the offset and page size using the following code.

$PageSize = $Context.PageSize
$Offset = $Context.Page * $PageSize
$sqlPage = "OFFSET $Offset ROWS FETCH NEXT $($Context.PageSize) ROWS ONLY;"

Sorting

Columns used for sorting and their direction are also included in the context. This example uses single column sorting but the sort value is an array if multi-column sort is enabled.

if ($Context.Sort[0].field -ne $null) {
    $sqlSort = "ORDER BY $(ConvertTo-ProperCase $Context.Sort[0].field) $($Context.Sort[0].sort) "
}
else {
    $sqlSort = "ORDER BY (SELECT NULL)"
}

Execution

Once we have constructed the SQL query portions and the parameters from the context, we can invoke our query. First, we need to get a total count of rows.

$Query = '{0} {1}' -f $Query, "$SqlFilter $sqlSort $sqlPage"
$Count = Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -As SingleValue -Query "SELECT COUNT(*) as count FROM dbo.Certificates $SqlFilter" -SqlParameter $Parameters

Once we have our count, the next step is to run the query. We return the rows as PSObjects. We need to format the return data to include the rows and the count.

$Data = Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -As PSObjectArray -Query $Query -SqlParameter $Parameters

@{
    rows     = [Array]$Data
    rowCount = $Count
}

Bulk Actions

Using the selection feature of the data grid, we can record the row selections and then perform bulk actions using a select dropdown.

The first step is to store the selections in session state. The $EventData variable of OnSelectionChanged is a list of the IDs of the rows. The data grid requires an ID to be defined for each row. If no ID property is found on a row, a random one is generated. Since we want deterministic selections, we use the CertificateID property as an ID in the select to allow for us to use that in queries. Below we store the selected IDs in a session variable and update a label for the select dropdown.

-OnSelectionChange {
    $Session:Selections = $EventData
    if ($Session:Selections.Count -le 0) {
        $Page:TickedCount = ''    
    }
    else {
        $Page:TickedCount = ' ({0})' -f $Session:Selections.Count
    }                
    Sync-UDElement -Id 'DropDownDynamic'
} 

The select drop down uses the selections and then issues an UPDATE command for each of this. This could be optimized to execute a single command with a list of IDs. Once the code the OnChange is run, the table is updated and the drop down is refreshed.

New-UDDynamic -Id "DropDownDynamic" {
    New-UDSelect -Id 'DropDown' -DefaultValue 'Bulk Decisions' -Option {
        New-UDSelectOption -Name "Bulk Decisions$Page:TickedCount" -Value "Bulk Decisions"
        New-UDSelectOption -Name 'Approve' -Value 'Approve'
        New-UDSelectOption -Name 'Revoke' -Value 'Revoke'
    } -OnChange {
        $Choice = $EventData
        $DecisionBit = if ($Choice -eq 'Approve') { 1 } else { 0 }
        $Selected = $Session:Selections
        # Show-UDToast ('{0}' -f $Session:Selections | Out-String) -Persistent
        foreach ($Row in $Selected) {
            $Query = @"
                UPDATE Certificates
                SET Decision = $DecisionBit
                WHERE CertificateId = $Row;
"@
            Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -Query $Query
        }
        Start-Sleep -Seconds 1
        $Page:TickedCount = ''
        $Session:Selection = @()
        Sync-UDElement -Id 'DropDownDynamic'
        Sync-UDElement -Id 'DynamicTable'
    }
}

Demo

Below is a demo of this app.

Demo of SQL Data Grid

Complete Example

The entire implementation of the app can be found below.

New-UDApp -Content {

    $Session:Selections = @()
    #endregion ID Verification and Database Connection
    #region Button and Chip Values and Styles
    $Session:SaveBtnClickedFromOpen = $false
    $Session:SaveBtnClickedFromReview = $false

    $Session:ChipLabelUnderOpen = ''
    $Session:ChipLabelUnderReview = ''

    # White
    $Page:BtnWhiteApprove = @{'background-color' = '#FFFFFF'; 'color' = '#717171'; Width = "90px"; Height = "28px"; 'margin-left' = '0px'; 'margin-right' = '2px' }
    $Page:BtnWhiteRevoke = @{'background-color' = '#FFFFFF'; 'color' = '#717171'; Width = "90px"; Height = "28px"; 'margin-left' = '2px'; 'margin-right' = '0px' }

    # Green
    $Page:BtnGreenApprove = @{'background-color' = '#00842b'; 'color' = '#FFFFFF'; Width = "90px"; Height = "28px"; 'margin-left' = '0px'; 'margin-right' = '2px' }

    # Red
    $Page:BtnRedRevoke = @{'background-color' = '#EB0A1E'; 'color' = '#FFFFFF'; Width = "90px"; Height = "28px"; 'margin-left' = '2px'; 'margin-right' = '0px' }

    # Modal
    $Page:BtnWhiteModal = @{'background-color' = '#FFFFFF'; 'color' = '#717171'; Width = "10px"; Height = "28px"; 'margin-left' = '5px'; 'margin-right' = '0px'; 'min-width' = '10px' }

    $Page:IconModal = New-UDIcon -Icon 'bars' -Color '#717171' -Title 'Approve' -Solid

    $Page:IconApproveNeutral = New-UDIcon -Icon 'thumbs-up' -Color '#717171' -Title 'Approve' -Solid
    $Page:IconApprove = New-UDIcon -Icon 'thumbs-up' -Color '#FFFFFF' -Title 'Approve' -Solid

    $Page:IconRevokeNeutral = New-UDIcon -Icon 'thumbs-down' -Color '#717171' -Title 'Revoke' -Solid
    $Page:IconRevoke = New-UDIcon -Icon 'thumbs-down' -Color '#FFFFFF' -Title 'Revoke' -Solid

    $Page:TextApprove = 'Approve'
    $Page:TextRevoke = 'Revoke'

    #endregion Button and Chip Values and Styles
    #region CSS Styles
    New-UDHtml -Markup '<style>
.my-custom-tabs .MuiTab-root {
    width: 100px;  /* Fixed width */
    text-align: center; /* Center text */
    height: 48px;  /* Fixed height */
    margin-top: -10px;  /* Move text upwards on the page */
}
.my-custom-tabs .MuiTab-root.Mui-selected {
    background-color: #FFFFFF;
    color: #1f2328;
    font-weight: bold;
}
.my-custom-tabs .css-7mcwe1-MuiTabs-indicator {
    background-color: #EB0A1E;
}
.css-nmu7bk-MuiTableRow-root,
.css-1gjuw6o-MuiTableRow-root {
    vertical-align: baseline !important;
}
.my-badge-container {
    position: relative;
    display: flex;
    align-items: center;
}
.my-chip-open {
    position: absolute;
    top: 119px;
    left: 75px;
    z-index: 1000;
    color: #FFFFFF;
    background-color: #666666;
    width: 52px;  /* Fixed width */
    height: 15px;
    text-align: center;  /* Center the text horizontally */
    font-size: 10px;     /* Adjusted font size */
    font-weight: bold;   /* Set font weight to bold */
}
.my-chip-review {
    position: absolute;
    top: 119px;
    left: 174px;
    z-index: 1000;
    color: #FFFFFF;
    background-color: #666666;
    width: 52px;  /* Fixed width */
    height: 15px;
    text-align: center;  /* Center the text horizontally */
    font-size: 10px;     /* Adjusted font size */
    font-weight: bold;   /* Set font weight to bold */
}
</style>'



    #endregion CSS Styles
    # Adding line breaks to push the content down
    New-UDHtml -Markup '<br><br>'
    #region Grid Container (1) Wraps all code
    New-UDGrid -Container -Content {
        New-UDPaper -Elevation 1 -Content {
            #endregion Grid Container (1) Wraps all code
            #region Grid Item (1.1) Tabs
            New-UDGrid -Item -ExtraSmallSize 12 -Content {
                #endregion Grid Item (1.1) Tabs
                New-UDHtml -Markup '<div class="my-badge-container">'  # Open container div
                #region Grid Container (2) Dropdown and Button
                New-UDGrid -Container -Content {
                    New-UDPaper -Elevation 1  -Content {
                        #endregion Grid Item (2) Dropdown and Button
                        #region Grid Item (2.1) Dropdown and Button
                        New-UDGrid -Item -ExtraSmallSize 3 -Content {
                            #endregion Grid Item (2.1) Dropdown and Button
                            #region Dropdown Select
                            New-UDDynamic -Id "DropDownDynamic" {
                                New-UDSelect -Id 'DropDown' -DefaultValue 'Bulk Decisions' -Option {
                                    New-UDSelectOption -Name "Bulk Decisions$Page:TickedCount" -Value "Bulk Decisions"
                                    New-UDSelectOption -Name 'Approve' -Value 'Approve'
                                    New-UDSelectOption -Name 'Revoke' -Value 'Revoke'
                                } -OnChange {
                                    $Choice = $EventData
                                    $DecisionBit = if ($Choice -eq 'Approve') { 1 } else { 0 }
                                    $Selected = $Session:Selections
                                    # Show-UDToast ('{0}' -f $Session:Selections | Out-String) -Persistent
                                    foreach ($Row in $Selected) {
                                        $Query = @"
                                            UPDATE Certificates
                                            SET Decision = $DecisionBit
                                            WHERE CertificateId = $Row;
"@
                                        Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -Query $Query
                                    }
                                    Start-Sleep -Seconds 1
                                    $Page:TickedCount = ''
                                    $Session:Selection = @()
                                    Sync-UDElement -Id 'DropDownDynamic'
                                    Sync-UDElement -Id 'DynamicTable'
                                }
                            }
                        }
                        #endregion Dropdown Select
                    }
                }
                #region Grid Container (3) Table
                New-UDGrid -Container -Content {
                    #endregion Grid Container (3) Table
                    #region Grid Item (3.1) Table
                    New-UDGrid -Item -ExtraSmallSize 12 -Content {
                        New-UDPaper -Elevation 1  -Content {
                            #endregion Grid Item (3.1) Table
                            #region Table + Filter and Sorts
                            New-UDDynamic -Id "DynamicTable" {
                                New-UDDataGrid -ShowPagination -PageSize 10 -RowsPerPageOptions @(10, 25, 50, 100, 200) -CheckboxSelection -LoadRows {

                                    function ConvertTo-ProperCase {
                                        param($Field)

                                        switch ($Field) {
                                            "username" { "UserName" }
                                            "groupname" { "GroupName" }
                                        }
                                    }

                                    $Context = $EventData

                                    $SqlFilter = ""
                                    if ($Context.Filter.Items.Count -gt 0) {
                                        $count = 1
                                        foreach ($filter in $Context.Filter.Items) {
                                            if ($count -gt 1) {
                                                $SqlFilter += " $($Context.Filter.logicOperator) "
                                            }
                                            else {
                                                $SqlFilter += " WHERE "
                                            }
                                            switch ($filter.Operator) {
                                                "contains" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) LIKE '%$($filter.Value)%' " }
                                                "equals" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) = '$($filter.Value)' " }
                                                "startsWith" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) LIKE '$($filter.Value)%' " }
                                                "endsWith" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) LIKE '%$($filter.Value)' " }
                                                "isAnyOf" {
                                                    $count = 1
                                                    foreach ($val in $filter.Value) {
                                                        if ($count -gt 1) {
                                                            $list += ", '$val'"
                                                        }
                                                        else {
                                                            $list += "'$val'"
                                                        }  
                                                        $count += 1
                                                    }
                                                    $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) IN ($($list)) "
                                                }
                                                "isempty" { $SqlFilter += " TRIM ($(ConvertTo-ProperCase $filter.Field)) IS NULL " }
                                                "isnotempty" { $SqlFilter += " TRIM ($(ConvertTo-ProperCase $filter.Field)) IS NOT NULL " }
                                                "notequals" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) != '$($filter.Value)' " }
                                                "notcontains" { $SqlFilter += " $(ConvertTo-ProperCase $filter.Field) NOT LIKE '%$($filter.Value)%' " }
                                            }
                                            $count += 1
                                        }
                                    }

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

                                    #endregion Table + Filter and Sorts
                                    #region Create Table Query Herestring
                                    $Query = @"
                                    SELECT
                                        CertificateId,
                                        CertificateId as id,
                                        Decision,
                                        GroupName,
                                        UserName,
                                        AppName
                                    FROM 
                                        dbo.Certificates
"@
                                    #endregion Create Table Query Herestring                                 
                                    #region Table Construct Database Query with `Where`, `Paging`, and `Count`

                                    if ($Context.Sort[0].field -ne $null) {
                                        $sqlSort = "ORDER BY $(ConvertTo-ProperCase $Context.Sort[0].field) $($Context.Sort[0].sort) "
                                    }
                                    else {
                                        $sqlSort = "ORDER BY (SELECT NULL)"
                                    }

                                    $sqlPage = "OFFSET $Offset ROWS FETCH NEXT $($Context.PageSize) ROWS ONLY;"

                                    $Query = '{0} {1}' -f $Query, "$SqlFilter $sqlSort $sqlPage"
                                    $Count = Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -As SingleValue -Query "SELECT COUNT(*) as count FROM dbo.Certificates $SqlFilter"

                                    Show-UDToast $Query -Persistent
                                    #endregion Table Construct Database Query with `Where's` and `Paging`
                                    #region Query the Database and Pass `$Session:Data` to Out-UDTableData
                                    $Session:Data = Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -As PSObjectArray -Query $Query

                                    @{
                                        rows     = [Array]$Session:Data
                                        rowCount = $Count
                                    }
                                    #endregion Query the Datbase and Pass `$Session:Data` to Out-UDTableData
                                    #region Table Columns
                                } -Columns @(
                                    New-UDDataGridColumn -HeaderName 'User' -Field 'UserName' -Filterable -Sortable -Description 'UserName'
                                    New-UDDataGridColumn -HeaderName 'Group' -Field 'GroupName'  -Filterable -Sortable
                                    New-UDDataGridColumn -HeaderName 'App' -Field 'AppName' -Filterable -Sortable
                                    New-UDDataGridColumn -HeaderName 'Decision' -Field 'Decision' -MinWidth 300 -Render { 
                                        New-UDDynamic -Id $EventData.CertificateId -Content {

                                            function New-DecisionButton {
                                                param(
                                                    $Text,
                                                    $Icon,
                                                    $Style,
                                                    $CertificateId,
                                                    $Decision
                                                )

                                                New-UDButton -Text $Text -Icon $Icon -Style $Style -OnClick {
                                                    $Session:updateButton = $true
                                                    $Query = @"
                                                UPDATE Certificates
                                                SET Decision = $Decision
                                                WHERE CertificateId = $CertificateId;
"@

                                                    Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -Query $Query
                                                    Sync-UDElement -Id $CertificateId
                                                } -ShowLoading
                                            }

                                            if ($Session:updateButton) {
                                                $item = Invoke-DbaQuery -SqlInstance '(localdb)\MSSQLLocalDB' -Database PSU -Query ('SELECT * FROM [dbo].[Certificates] WHERE CertificateId = {0}' -f $EventData.CertificateId)
                                                $EventData.Decision = $item.Decision
                                                $Session:updateButton = $false
                                            }

                                            if ($EventData.Decision -eq 1) {
                                                New-DecisionButton -Text 'Approve' -Icon $Page:IconApprove -Style $Page:BtnGreenApprove -CertificateId $EventData.CertificateId -Decision 'NULL'
                                                New-DecisionButton -Text 'Revoke' -Icon $Page:IconRevokeNeutral -Style $Page:BtnWhiteRevoke -CertificateId $EventData.CertificateId -Decision '0'
                                            }
                                            elseif ($EventData.Decision -eq 0) {
                                                New-DecisionButton -Text 'Approve' -Icon $Page:IconApproveNeutral -Style $Page:BtnWhiteApprove -CertificateId $EventData.CertificateId -Decision '1'
                                                New-DecisionButton -Text 'Revoke' -Icon $Page:IconRevoke -Style $Page:BtnRedRevoke -CertificateId $EventData.CertificateId -Decision 'NULL'
                                            }
                                            else {
                                                New-DecisionButton -Text 'Approve' -Icon $Page:IconApproveNeutral -Style $Page:BtnWhiteApprove -CertificateId $EventData.CertificateId -Decision '1'
                                                New-DecisionButton -Text 'Revoke' -Icon $Page:IconRevokeNeutral -Style $Page:BtnWhiteRevoke -CertificateId $EventData.CertificateId -Decision '0'
                                            }
                                        }
                                    }
                                    #region Table OnRowSelection
                                ) -OnSelectionChange {
                                    $Session:Selections = $EventData
                                    if ($Session:Selections.Count -le 0) {
                                        $Page:TickedCount = ''    
                                    }
                                    else {
                                        $Page:TickedCount = ' ({0})' -f $Session:Selections.Count
                                    }                
                                    Sync-UDElement -Id 'DropDownDynamic'
                                } 
                            }
                        }
                    }
                }
                #endregion Table OnRowSelection
            }
        }
    }

}