There are plenty of ways to visualize data. There’s PowerBI, Tableu, and a plethora of other options. What about taking the results of a SQL query and creating a graph in PowerShell? Probably not ideal, but is it possible? Let’s see what this might look like.
The thought occurred to me more out of curiosity than it being something I’d use. Admittedly, I’m not proficient enough in PowerShell to quickly build something from scratch. To get an idea of how it might look, I took this as an opportunity to outsource most of the work to Microsoft Copilot to see if I would get anything useful.
I asked Copilot:
i want a powershell script to visualize the results of a sql server query. i’ll give you a sql server query, you give me the powershell script i can run to have a graph of results. here is the query: SELECT DisplayName, UpVotes FROM Users WHERE Id < 10;
It gave me a script in return that worked, but wasn’t exactly what I was looking for. I made another request with a small date range included:
what if i want the same type of chart for this query: SELECT COUNT(*) AS ‘Count’, CAST(CreationDate AS date) AS ‘Date’ FROM Users WHERE CreationDate <= ‘2008-01-01’ GROUP BY CAST(CreationDate AS date) ORDER BY COUNT(*) DESC
It gave me this PowerShell script in return:
# Load Windows Forms and drawing namespaces
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
# Load the Windows Forms Charting namespace
Add-Type -AssemblyName System.Windows.Forms.DataVisualization
# SQL Server connection details
$ServerInstance = "YourSqlServerInstance"
$Database = "YourDatabase"
$Query = @"
SELECT COUNT(*) AS 'Count', CAST(CreationDate AS date) AS 'Date'
FROM Users
WHERE CreationDate <= '2009-01-01'
GROUP BY CAST(CreationDate AS date)
ORDER BY COUNT(*) DESC
"@
# Create SQL connection
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$ServerInstance; Database=$Database; Integrated Security=True;"
$Connection.Open()
# Execute SQL query
$Command = $Connection.CreateCommand()
$Command.CommandText = $Query
$Result = $Command.ExecuteReader()
# Create a DataTable and load query results
$DataTable = New-Object System.Data.DataTable
$DataTable.Load($Result)
# Close the connection
$Connection.Close()
# Create a new Chart object
$Chart = New-Object System.Windows.Forms.DataVisualization.Charting.Chart
$Chart.Width = 800
$Chart.Height = 600
# Create a ChartArea to display the chart
$ChartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea
$Chart.ChartAreas.Add($ChartArea)
# Create a Series to hold the data
$Series = New-Object System.Windows.Forms.DataVisualization.Charting.Series
$Series.Name = "UserCount"
$Series.ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Column
# Add data to the Series
foreach ($Row in $DataTable.Rows) {
$Series.Points.AddXY($Row["Date"].ToString("yyyy-MM-dd"), $Row["Count"])
}
# Add the Series to the Chart
$Chart.Series.Add($Series)
# Customize the X-axis labels
$ChartArea.AxisX.Interval = 1
$ChartArea.AxisX.IntervalType = [System.Windows.Forms.DataVisualization.Charting.DateTimeIntervalType]::Days
$ChartArea.AxisX.LabelStyle.Format = "yyyy-MM-dd"
$ChartArea.AxisX.LabelStyle.Angle = -45
# Create a form to host the chart
$Form = New-Object Windows.Forms.Form
$Form.Text = "SQL Query Results"
$Form.Width = 850
$Form.Height = 650
# Add the chart to the form
$Chart.Anchor = [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Left -bor [System.Windows.Forms.AnchorStyles]::Right
$Form.Controls.Add($Chart)
# Show the form
[Windows.Forms.Application]::Run($Form)I took the script, filled in my server and database names, and ran it in PowerShell. Here is the graph I received:
It’s basic but it accomplished what I set out to do. For what it’s worth, I tried to see the one source Copilot listed but it was a GitHub page that gave me a 404 error.
Is this type of graph going to “wow” your audience in a PowerPoint presentation? Doubtful. But I thought it was interesting to try and confirm that, for a simple graph, this can work.
Thanks for reading!
Chad Callihan 