Have you ever been in a situation where you needed to compare two DataTables? No? Well, if you ever do, I have your back! This function will take two DataTables and compare them, either only schema or a full compare.

When doing a full compare all data are checked, and you will notice that it will take a bit longer to run. The output is a boolean only, so it will not give you an overview of what if different between the two.

Give it a spin, and let me know what you think!

function Compare-DataTable {
<#
.SYNOPSIS
Compare two DataTables.
.DESCRIPTION
Compare two DataTables.
.EXAMPLE
Compare-DataTable -ReferenceTabel $table1 -DifferenceTable $table2 -CompareMode 'Full'
Performes a full compare of $table1 and $table2
.NOTES
Author: Øyvind Kallstad
Date: 20.10.2014
Version: 1.0
#>
[CmdletBinding()]
[OutputType([System.Boolean])]
param (
[Parameter()]
[System.Data.DataTable] $ReferenceTable,
[Parameter()]
[System.Data.DataTable] $DifferenceTable,
# Schema only compares the table structure while Full compares the data as well.
[Parameter()]
[ValidateSet('Full','Schema')]
[string] $CompareMode = 'Schema'
)
if (-not (Compare-DataTableSchema ReferenceTable $ReferenceTable DifferenceTable $DifferenceTable)) {
return $false
}
if($CompareMode -eq 'Full') {
foreach ($row in $ReferenceTable.Rows) {
if (-not(Compare-DataRow ReferenceRow $row DifferenceRow ($DifferenceTable.Rows | Where-Object {$_.Name -eq $row.Name}))) {
return $false
}
}
}
return $true
}
# Helper function for Compare-DataTable
function Compare-DataColumn {
[CmdletBinding()]
[OutputType([System.Boolean])]
param (
[System.Data.DataColumn] $ReferenceColumn,
[System.Data.DataColumn] $DifferenceColumn
)
if ($ReferenceColumn.ColumnName -ne $DifferenceColumn.ColumnName) {
return $false
}
if ($ReferenceColumn.DataType -ne $DifferenceColumn.DataType) {
return $false
}
return $true
}
# Helper function for Compare-DataTable
function Compare-DataTableSchema {
[CmdletBinding()]
[OutputType([System.Boolean])]
param (
[System.Data.DataTable] $ReferenceTable,
[System.Data.DataTable] $DifferenceTable
)
if ($ReferenceTable.Columns.Count -ne $DifferenceTable.Columns.Count) {
return $false
}
else {
$referenceTableColumns = $ReferenceTable.Columns
$differenceTableColumns = $DifferenceTable.Columns
foreach ($column in $referenceTableColumns) {
if (-not (Compare-DataColumn ReferenceColumn $column DifferenceColumn ($differenceTableColumns | Where-Object {$_.Ordinal -eq $column.Ordinal}))) {
return $false
}
}
}
return $true
}
# Helper function for Compare-DataTable
function Compare-DataRow {
[CmdletBinding()]
[OutputType([System.Boolean])]
param (
[System.Data.DataRow] $ReferenceRow,
[System.Data.DataRow] $DifferenceRow
)
foreach ($property in ($ReferenceRow | Get-Member MemberType 'Property')) {
$propertyName = $property.Name
if ($ReferenceRow.$propertyName -ne $DifferenceRow.$propertyName) {
return $false
}
}
return $true
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s