Working with Basic .NET DataSets and DataTables in PowerShell

 1$dt = New-Object System.Data.Datatable
 2[void]$dt.Columns.Add("First")
 3[void]$dt.Columns.Add("Second")
 4[void]$dt.Columns.Add("Third")
 5
 6# Add a row manually
 7[void]$dt.Rows.Add("1","2","3")
 8
 9# Or add an array
10$me = "computername","userdomain","username"
11$array = (Get-Childitem env: | Where-Object { $me -contains $_.Name }).Value
12[void]$dt.Rows.Add($array)

 1# Continuing from above
 2$dt.TableName = "Me"
 3$ds = New-Object System.Data.DataSet
 4$ds.Tables.Add($dt)
 5
 6$dt2 = New-Object System.Data.Datatable "AnotherTable"
 7[void]$dt2.Columns.Add("MyColumn")
 8[void]$dt2.Rows.Add("MyRow")
 9$ds.Tables.Add($dt2)
10
11$ds.tables["Me"]
12$ds.tables["AnotherTable"]

 1$dt = New-Object System.Data.Datatable
 2[void]$dt.Columns.Add("Artist")
 3[void]$dt.Columns.Add("Genre")
 4[void]$dt.Columns.Add("Album")
 5[void]$dt.Columns.Add("ReleaseYear")
 6
 7# Change ReleaseYear to an int, if you want.
 8$dt.Columns["ReleaseYear"].DataType = [int]
 9
10# Add an array
11$array = "Tim Smooth","Southern Rap","I Gotsta' Have It",1991
12[void]$dt.Rows.Add($array)
13
14# Or add rows manually
15[void]$dt.Rows.Add("Poison","Glam Metal","Open Up and Say... Ahh!",1988)
16[void]$dt.Rows.Add("Cinderella","Night Songs","Flesh & Blood",1986)
17[void]$dt.Rows.Add("Eazy-E","Gangsta Rap","Eazy-Duz-It",1988)
18
19# Uses SQL syntax
20$dt.Select("Genre like '%Rap'")
21$dt.Select("ReleaseYear > 1988")
22$dt.Select("Artist = 'Poison'")
23
24# Change Eazy-E's genre
25$eazy = ($dt.Select("Artist = 'Eazy-E'"))
26$eazy[0]["Genre"] = "West Coast Rap"
27
28# Find this new genre
29$dt.Select("Genre like '%Coast%'")

 1# CSV variables
 2$csvfile = "C:\perf\100krow.tsv"
 3$csvdelimiter = "`t"
 4$firstRowColumns = $false
 5
 6# Do it
 7$dt = New-Object System.Data.Datatable
 8$reader = New-Object System.IO.StreamReader $csvfile
 9$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
10
11foreach ($column in $columns) {
12	if ($firstRowColumns -eq $true) { 
13		[void]$dt.Columns.Add($column)
14		$reader.ReadLine()
15	} else { [void]$dt.Columns.Add() }
16}
17
18# Read in the data, line by line
19while (($line = $reader.ReadLine()) -ne $null)  {
20	[void]$dt.Rows.Add($line.Split($csvdelimiter))
21} 
22
23$dt.rows.count

 1$columns = "Second","Third"
 2
 3$distinct = New-Object "System.Data.DataTable" 
 4$onlydupes = New-Object "System.Data.DataTable" 
 5$hash = new-object "System.Collections.Generic.HashSet[string]"
 6
 7foreach ($column in $dt.Columns) {
 8	[void]$distinct.Columns.Add($column.ToString())
 9	[void]$onlydupes.Columns.Add($column.ToString())
10}
11
12foreach ($row in $dt.rows) {
13	$columnsToCheck = [string]($row | Select-Object $columns)
14	$result = $hash.Add($columnsToCheck)
15
16	if ($result -eq $false) { 
17		[void]$distinct.Rows.Add($row.ItemArray) 
18	} else {
19		# This works about as fast as $all.DefaultView.ToTable($true,"Column2","Column3")
20		[void]$onlydupes.Rows.Add($row.ItemArray) }
21}
22
23$dt.rows.count
24$distinct.rows.count
25$onlydupes.rows.count

 1$dupetable = $dt.Clone()
 2foreach ($row in $select) { $dupetable.ImportRow($row) }
 3#or
 4$dupetable = $dt.Copy()
 5
 6#### Filling DataTables and DataSets with data from SQL Server
 7
 8# Set variables
 9$sqlserver = "sqlserver"
10$database = "master"
11$connstring = "Server=$sqlserver;Database=$database;Integrated Security=True"
12$query = "select SERVERPROPERTY('InstanceDefaultDataPath') as data, SERVERPROPERTY('InstanceDefaultLogPath') as log"
13 
14 
15# You can use SQL Connection/ SQL Command
16$sqlconn = New-Object System.Data.SqlClient.SqlConnection
17$sqlconn.ConnectionString = $connstring
18$sqlconn.Open()
19
20$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
21$sqlcmd.Connection = $sqlconn
22$sqlcmd.CommandText = $query
23
24# load it right into a DataTable
25$dt = New-Object System.Data.DataTable
26$dt.Load($sqlcmd.ExecuteReader())
27$dt.data
28$dt.log
29
30# Or alternatively, load a table into a DataSet
31$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
32$sqladapter.SelectCommand = $sqlcmd
33$ds = New-Object System.Data.DataSet
34[void]$sqladapter.Fill($ds)
35
36# Close connection
37$sqlconn.Close()
38 
39$defaultdata = $ds.Tables[0].rows[0].data
40$defaultlog = $ds.Tables[0].rows[0].log
41 
42# Or you can use SMO
43[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
44$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
45$ds = $server.ConnectionContext.ExecuteWithResults($query)
46$ds.Tables[0]
47 
48$defaultdata = $ds.Tables[0].rows[0].data
49$defaultlog = $ds.Tables[0].rows[0].log