Working with Basic .NET DataSets and DataTables in PowerShell

This is mostly for my reference, but you can use it, too :)

Create Basic Datatable

$dt = New-Object System.Data.Datatable

# Add a row manually

# Or add an array
$me = "computername","userdomain","username"
$array = (Get-Childitem env: | Where-Object { $me -contains $_.Name }).Value


Create Basic DataSet (Collection of Tables)

# Continuing from above
$dt.TableName = "Me"
$ds = New-Object System.Data.DataSet

$dt2 = New-Object System.Data.Datatable "AnotherTable"




$dt = New-Object System.Data.Datatable

# Change ReleaseYear to an int, if you want.
$dt.Columns["ReleaseYear"].DataType = [int]

# Add an array
$array = "Tim Smooth","Southern Rap","I Gotsta' Have It",1991

# Or add rows manually
[void]$dt.Rows.Add("Poison","Glam Metal","Open Up and Say... Ahh!",1988)
[void]$dt.Rows.Add("Cinderella","Night Songs","Flesh & Blood",1986)
[void]$dt.Rows.Add("Eazy-E","Gangsta Rap","Eazy-Duz-It",1988)

# Uses SQL syntax
$dt.Select("Genre like '%Rap'")
$dt.Select("ReleaseYear > 1988")
$dt.Select("Artist = 'Poison'")

# Change Eazy-E's genre
$eazy = ($dt.Select("Artist = 'Eazy-E'"))
$eazy[0]["Genre"] = "West Coast Rap"

# Find this new genre
$dt.Select("Genre like '%Coast%'")

Create Datatable from CSV

Using this method, you can add 140k rows a second.

# CSV variables
$csvfile = "C:\perf\100krow.tsv"
$csvdelimiter = "`t"
$firstRowColumns = $false

# Do it
$dt = New-Object System.Data.Datatable
$reader = New-Object System.IO.StreamReader $csvfile
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)

foreach ($column in $columns) {
	if ($firstRowColumns -eq $true) { 
	} else { [void]$dt.Columns.Add() }

# Read in the data, line by line
while (($line = $reader.ReadLine()) -ne $null)  {


Managing Duplicates

$columns = "Second","Third"

$distinct = New-Object "System.Data.DataTable" 
$onlydupes = New-Object "System.Data.DataTable" 
$hash = new-object "System.Collections.Generic.HashSet[string]"

foreach ($column in $dt.Columns) {

foreach ($row in $dt.rows) {
	$columnsToCheck = [string]($row | Select-Object $columns)
	$result = $hash.Add($columnsToCheck)

	if ($result -eq $false) { 
	} else {
		# This works about as fast as $all.DefaultView.ToTable($true,"Column2","Column3")
		[void]$onlydupes.Rows.Add($row.ItemArray) }


Create new table using another datatable’s schema

$dupetable = $dt.Clone()
foreach ($row in $select) { $dupetable.ImportRow($row) }
$dupetable = $dt.Copy()

Filling DataTables and DataSets with data from SQL Server

# Set variables
$sqlserver = "sqlserver"
$database = "master"
$connstring = "Server=$sqlserver;Database=$database;Integrated Security=True"
$query = "select SERVERPROPERTY('InstanceDefaultDataPath') as data, SERVERPROPERTY('InstanceDefaultLogPath') as log"
# You can use SQL Connection/ SQL Command
$sqlconn = New-Object System.Data.SqlClient.SqlConnection
$sqlconn.ConnectionString = $connstring

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlconn
$sqlcmd.CommandText = $query

# load it right into a DataTable
$dt = New-Object System.Data.DataTable

# Or alternatively, load a table into a DataSet
$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqladapter.SelectCommand = $sqlcmd
$ds = New-Object System.Data.DataSet

# Close connection
$defaultdata = $ds.Tables[0].rows[0].data
$defaultlog = $ds.Tables[0].rows[0].log
# Or you can use SMO
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
$ds = $server.ConnectionContext.ExecuteWithResults($query)
$defaultdata = $ds.Tables[0].rows[0].data
$defaultlog = $ds.Tables[0].rows[0].log

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server