PowerShell Read SQL Server data into Dataset

This blog article shows you how to use PowerShell to read the SQL Server database table records into DataSet. Below is the sample code snippet to read record into a from the database table to Dataset and print it out. The Products table is the same as the one in Northwind database.

# Declare Sql Connection object

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

# Declare Connection string

$SqlConnection.ConnectionString = “Server=localhost;Database=poc;Integrated Security=True”

# Declare Sql Command Object

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

# Declare Sql Command statement

$SqlCmd.CommandText = ” SELECT * FROM Products”

# Assign the Sql Connection object to Sql Command

$SqlCmd.Connection = $SqlConnection

$SqlCmd.CommandTimeout = 0

# Declare Sql Adapter object

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

# Assign the Sql Command object Sql Adapter object

$SqlAdapter.SelectCommand = $SqlCmd

# Declare DataSet object

$DataSet = New-Object System.Data.DataSet

# Execute Sql Adapter object to fill the DataSet

$SqlAdapter.Fill($DataSet)

# Close the database connection

$SqlConnection.Close()

# List out the data

$DataSet.Tables[0]

You should see something like the below when you execute the script.

Reference: https://social.technet.microsoft.com/Forums/ie/en-US/603af83a-8db5-48ce-b19c-74a823ebbece/running-sql-via-powershell?forum=winserverpowershell/?WT.mc_id=DP-MVP-36769

https://blog.netnerds.net/2015/02/working-with-basic-net-datasets-in-powershell/?WT.mc_id=DP-MVP-36769