AssertEqual two SQL Queries

I am a novice PowerShell, C#, and .NET scripter and am trying to learn by
example. I have several years of various languages under my belt, including
Pascal, C++, WATIR, BASH shell, and some application-specific languages.

I am verifying a series of SQL query results, and need to assert that the
results are equal. At the moment, I am just comparing the count of rows in
the same table in two separate database instances. I can easily get the
results instantiated in objects of type System.Data.DataSet, but am having an
interesting time trying to compare them.

I am using a customized version of Lee Holmes' Invoke-SqlCommand.ps1 (see
http://www.leeholmes.com/blog/Intera...qlCommand.aspx)
to run my queries, but the basic design is the same. The query results are
returned in a couple of instances of System.Data.DataSet called $expDataset
and $actDataset.

I am also using Adam Geras' PsExpect (http://www.codeplex.com/psexpect).

########################################################################################################################
##
## AssertEqual-TableCount
##
## Compares the values of two table counts in separate databases to
determine if they are equal.
##
##############################################################################
##
## Arguments:
##
## * $expDatabase = The expected value is the "Gold Standard" (a.k.a
baseline) database. This
## is the database verified fop the last major release. E.g.,
"maintenance44". The default
## value is stored in C:\global\SPi\SPiTestLibConstants.ps1.
## * $actDatabase = The actual value is the "database-under-test". E.g.,
"maintenance56". The default
## value is stored in C:\global\SPi\SPiTestLibConstants.ps1.
## * $table = is the table to be compared in each database. E.g., "Item",
"ItemSet", etc.
## * $label = is the test label. This is a PSExpect variable used in
logging results and limiting tests to run.
## * $intent = is the expected result ("SHOULDPASS" or "SHOULDFAIL"). This
is a PSExpect variable used in logging
## and displaying results.
## * $credential = If the user supplies a credential, then they want SQL
authentication; by default, we use
## Windows authentication, but this allows an override, just in case.
##
##############################################################################
##
## Tested with TestAssertEqual-TableCount.ps1
##
##############################################################################

function global:AssertEqual-TableCount(
[string] $server = $SpiServer,
[string] $expDatabase = $BaselineDatabase,
[string] $actDatabase = $TestDatabase,
[string] $table = $(throw "Please specify the table to be compared."),
[string] $label = $(throw "Please specify the name of this test."),
[string] $intent = "SHOULDPASS",
[System.Management.Automation.PsCredential] $credential)
{
##########################################
## Default server, database, query, and authentication values
## * Default server & database: stored in
C:\global\SPi\SPiTestLibConstants.ps1.
## * NOTE: Script will prompt for table to be compared, test label, and
test intention
## if none are specified. This prompt kills batch tests.
## * Uses Windows authentication by default, but can be overridden with
$credential
##########################################

##########################################
## Form the queries for each database
##########################################

$expTable = $expDatabase + ".dbo." + $table
$actTable = $actDatabase + ".dbo." + $table

$countQuery = "select count(*) from "

$expQuery = $countQuery + $expTable
$actQuery = $countQuery + $actTable

##########################################
## Form the PSExpect arguments
##########################################

$result = "PASSED"
if ($intent -eq "SHOULDFAIL")
{
$result = "FAILED"
}


##########################################
## Prepare the authentication information. By default, we pick Windows
authentication
##########################################
$authentication = "Integrated Security=SSPI;"

##########################################
## Prepare the connection string out of the information they provide for
the expected database
##########################################
$expConnectionString = "Provider=sqloledb; " +
"Data Source=$server; " +
"Initial Catalog=$expDatabase; " +
"$authentication; "

##########################################
## Connect to the expected database and open it
##########################################
$expConnection = New-Object System.Data.OleDb.OleDbConnection
$expConnectionString
$expCommand = New-Object System.Data.OleDb.OleDbCommand
$expQuery,$expConnection
$expConnection.Open()

##########################################
## Fetch the results, and close the connection
##########################################
$expAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $expCommand
$expDataset = New-Object System.Data.DataSet
[void] $expAdapter.Fill($expDataset)
$expConnection.Close()

##########################################
## Prepare the connection string out of the information they provide for
the actual database
##########################################
$actConnectionString = "Provider=sqloledb; " +
"Data Source=$server; " +
"Initial Catalog=$actDatabase; " +
"$authentication; "

##########################################
## Connect to the actual database and open it
##########################################
$actConnection = New-Object System.Data.OleDb.OleDbConnection
$actConnectionString
$actCommand = New-Object System.Data.OleDb.OleDbCommand
$actQuery,$actConnection
$actConnection.Open()

##########################################
## Fetch the results, and close the connection
##########################################
$actAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $actCommand
$actDataset = New-Object System.Data.DataSet
[void] $actAdapter.Fill($actDataset)
$actConnection.Close()

##########################################
## Assert the equality of the two results
##########################################
## I have tests up to here, and everything is working correctly and as
expected. It's below, when
## I try to compare the two output statements that things start falling
apart.

write-host " "
write-host "*****"
write-host " "
write-host "* Test: $label"
write-host " "
write-host "expQuery = $expQuery"
## $expDataset.Tables | Select-Object -Expand Rows
write-host "actQuery = $actQuery"
## $actDataset.Tables | Select-Object -Expand Rows

AssertEqual $expDataset $actDataset -Label $label -Intent $intent
if ($actDataset -ne $null)
{
if ($actDataset -eq $expDataset) {write-host "actDataset = expDataset"}
else {write-host "actDataset != expDataset"}
if ($actDataset.Equals($expDataset)) {write-host
"actDataset.Equals(expDataset) = True"}
else {write-host "actDataset.Equals(expDataset) = False"}
} else {write-host "actDataset is NULL"}

AssertEqual $expDataset.Tables $actDataset.Tables -Label $label -Intent
$intent
if ($expDataset.Tables -ne $null)
{
if ($actDataset.Tables -eq $expDataset.Tables) {write-host
"actDataset.Tables = expDataset.Tables"}
else {write-host "actDataset.Tables != expDataset.Tables"}
if ($actDataset.Tables.Equals($expDataset.Tables)) {write-host
"actDataset.Tables.Equals(expDataset.Tables) = True"}
else {write-host "actDataset.Tables.Equals(expDataset.Tables) =
False"}
} else {write-host "expDataset.Tables is NULL"}

AssertEqual $expDataset.Tables[0] $actDataset.Tables[0] -Label $label
-Intent $intent
if ($expDataset.Tables[0] -ne $null)
{
if ($actDataset.Tables[0] -eq $expDataset.Tables[0]) {write-host
"actDataset.Tables[0] = expDataset.Tables[0]"}
else {write-host "actDataset.Tables[0] != expDataset.Tables[0]"}
if ($actDataset.Tables[0].Equals($expDataset.Tables[0])) {write-host
"actDataset.Tables[0].Equals(expDataset.Tables[0]) = True"}
else {write-host "actDataset.Tables[0].Equals(expDataset.Tables[0])
= False"}
} else {write-host "expDataset.Tables[0] is NULL"}

AssertEqual $expDataset.Tables[0].Table $actDataset.Tables[0].Table -Label
$label -Intent $intent
if ($actDataset.Tables[0].Table -ne $null)
{
if ($actDataset.Tables[0].Table -eq $expDataset.Tables[0].Table)
{write-host "actDataset.Tables[0].Table = expDataset.Tables[0].Table"}
else {write-host "actDataset.Tables[0].Table !=
expDataset.Tables[0].Table"}
if ($actDataset.Tables[0].Table.Equals($expDataset.Tables[0].Table))
{write-host "actDataset.Tables[0].Table.Equals(expDataset.Tables[0].Table) =
True"}
else {write-host
"actDataset.Tables[0].Table.Equals(expDataset.Tables[0].Table) = False"}
} else {write-host "actDataset.Tables[0].Table is NULL"}

AssertEqual $expDataset.Tables[0].Table.Row
$actDataset.Tables[0].Table.Row -Label $label -Intent $intent
if ($actDataset.Tables[0].Table.Row -ne $null)
{
if ($actDataset.Tables[0].Table.Row -eq
$expDataset.Tables[0].Table.Row) {write-host "actDataset.Tables[0].Table.Row
= expDataset.Tables[0].Table.Row"}
else {write-host "actDataset.Tables[0].Table.Row !=
expDataset.Tables[0].Table.Row"}
if
($actDataset.Tables[0].Table.Row.Equals($expDataset.Tables[0].Table.Row))
{write-host
"actDataset.Tables[0].Table.Row.Equals(expDataset.Tables[0].Table.Row) =
True"}
else {write-host
"actDataset.Tables[0].Table.Row.Equals(expDataset.Tables[0].Table.Row) =
False"}
} else {write-host "actDataset.Tables[0].Table.Row is NULL"}

RaiseAssertions
}

********************************************************************************************************************
********************************************************************************************************************
Output to Console:
********************************************************************************************************************
********************************************************************************************************************

*****

expQuery = select count(*) from maintenance44.dbo.Process
45
actQuery = select count(*) from maintenance56.dbo.Process
45
actDataset.Tables != expDataset.Tables
actDataset != expDataset
actDataset.Tables != expDataset.Tables
actDataset.Tables[0] != expDataset.Tables[0]
actDataset.Tables[0].Table = expDataset.Tables[0].Table
actDataset.Tables[0].Table.Row = expDataset.Tables[0].Table.Row
1/18/2008 11:12:46 AM,SHOULDPASS,FAILED,TC-Process-test,System.Data.DataSet
expected but was System.Data.DataSet
1/18/2008 11:12:46
AM,SHOULDPASS,FAILED,TC-Process-test,System.Data.DataTableCollection expected
but was System.Data.DataTableCollection
1/18/2008 11:12:46 AM,SHOULDPASS,FAILED,TC-Process-test,Table expected but
was Table
1/18/2008 11:12:47 AM,SHOULDPASS,PASSED,TC-Process-test
1/18/2008 11:12:47 AM,SHOULDPASS,PASSED,TC-Process-test

*****

expQuery = select count(*) from maintenance44.dbo.ProcessLog
156067
actQuery = select count(*) from maintenance56.dbo.ProcessLog
156224
actDataset.Tables != expDataset.Tables
actDataset != expDataset
actDataset.Tables != expDataset.Tables
actDataset.Tables[0] != expDataset.Tables[0]
actDataset.Tables[0].Table = expDataset.Tables[0].Table
actDataset.Tables[0].Table.Row = expDataset.Tables[0].Table.Row
1/18/2008 11:12:47 AM,SHOULDFAIL,FAILED,TC-ProcessLog-Should display on
console in yellow-test,System.Data.DataSet expected but was
System.Data.DataSet
1/18/2008 11:12:47 AM,SHOULDFAIL,FAILED,TC-ProcessLog-Should display on
console in yellow-test,System.Data.DataTableCollection expected but was
System.Data.DataTableCollection
1/18/2008 11:12:47 AM,SHOULDFAIL,FAILED,TC-ProcessLog-Should display on
console in yellow-test,Table expected but was Table
1/18/2008 11:12:47 AM,SHOULDFAIL,PASSED,TC-ProcessLog-Should display on
console in yellow-test,Expected 'FAILED' but was 'PASSED'.
1/18/2008 11:12:47 AM,SHOULDFAIL,PASSED,TC-ProcessLog-Should display on
console in yellow-test,Expected 'FAILED' but was 'PASSED'.

*****


I have also tried Compare-Object on the two System.Data.Dataset objects. I
get no output regardless of whether the data is the dame or different in the
two objects.

Q1: Am I misusing System.Data.Dataset? In other words do I need to read the
data from it into a new object of some other class type?
Q2: What is System.Data.Dataset.Equal() evaluating? The data or the data
structure?
Q3: How can I determine whether the results of two SQL queries are equal?
Right now I will settle for evaluating two 1-row, 1-column results, but I am
also going to need to compare 2D table/matrix data.

BTW - the only reason I was deep-checking the entire data structure was
because I was not getting the results I expected. I'd love to be able to do
one and only one assert on the *appropriate* object for the verification.

Thanks in advance for any help on this.

C. Kirsten Whitworth
Senior Quality Assurance Automation Specialist
Legal
SPi

"Kibi78704" wrote:

Try exporting the datasets to XML and then comparing
--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Kibi78704" wrote:

Try exporing the datasets to XML and then compare the xml files
--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Kibi78704" wrote:

Have you tried exporting the datasets to XML and then comparing the XML
--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Kibi78704" wrote:

I have it working now.

"Kibi78704" wrote: