# SQL

## Server-side SQL Table

This example uses Universal Dashboard.

This example takes advantage of a SQL server and the `New-UDTable` cmdlet to create a table that retrieves data from a database table. The filtering, sorting and paging take place within the database.

This example assumes that we have a database called podcasts running in the local MS SQL Server. It has a table called shows that includes a column called host and a column called name.

```
Start-PSUServer -Port 8080 -Configuration {
    New-PSUDashboard -Name 'SQL' -BaseUrl "/" -Framework 'UniversalDashboard:Latest' -Content {
        New-UDDashboard -Title 'SQL' -Content {
            New-UDTable -Title 'Shows' -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 = ""
                if ($TableData.Filters) 
                {
                    $Where = "WHERE "

                    foreach($filter in $TableData.Filters)
                    {
                        $Where += $filter.column.field + " LIKE '%" + $filter.value + "%' AND "
                    }

                    $Where += " 1 = 1"
                }

                $PageSize = $TableData.PageSize 
                # Calculate the number of rows to skip
                $Offset = $TableData.Page * $PageSize
                $Count = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT COUNT(*) as count FROM shows $Where"

                $Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT * FROM shows $Where ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" | ForEach-Object {
                    @{ 
                        name = $_.name 
                        host = $_.host
                    }
                } 
                $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties
            } -Columns @(
                New-UDTableColumn -Property 'name' -Sort $true -Filter $true
                New-UDTableColumn -Property 'host' -Sort $true -Filter $true
            ) -Sort -Filter
        }
    }
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.powershelluniversal.com/v1/examples/sql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.