Help reading SQL data

Question

Trying to read data from as SQL database and .ExecuteReader() appears to be successful, but while .Read() must be returning False. Please review the code below and explain what might be wrong. Thanks

Here is my code:

Import-Module Adaxes

$databaseHost = "[i]dbHostname[/i]"
$databaseName = "[i]dbName[/i]"
$tableName = "[i]tableName[/i]"
$userIDField = "EMPID"

$databaseUsername = $NULL
$databasePassword = $NULL

# Connect to the database
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $connectionString = $connectionString + "Integrated Security=SSPI;"
}
else
{
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"
}

try
{
    $connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
    $connection.Open()

    # Check whether the user exists in the database
    try
    {
        $command = $connection.CreateCommand()
        $command.CommandText = "SELECT EMPID, LASTNAME, FIRSTNAME FROM $tableName WHERE $tableName`.$userIDField = @UserID"
     #   $command.Parameters.Add("@UserID", "%employeeID%") | Out-Null;
        $command.Parameters.Add("@UserID", "500333") | Out-Null;
        $Context.LogMessage($command.CommandText, "Information")
        $reader = $command.ExecuteReader()  
    }
    finally
    {
        # Close the search and release resources
        $command.Dispose()
    }

    if ($reader -eq $NULL)
    {
        $Context.LogMessage("A user with employee ID " + "%employeeID%" + " does not exist in the database", "Warning")
        return # Exit script
    }
    else
    {   $Context.LogMessage("before While", "Information")
       while ($reader.Read()){
           $Context.LogMessage("inside while loop reading records", "Information")
        $empID=$reader.GetString(0)
        $lName=$reader.GetString(1)
        $fName=$reader.GetString(2)
       # $deptNum = $reader.GetString(3)
       $Context.LogMessage( $fName,"Information")
}
    }
}
catch [System.Exception]
{
    $ErrorMessage = $_.Exception.Message
    [string]$LineNumber = $_.InvocationInfo.ScriptLineNumber
    [string]$Offset = $_.InvocationInfo.OffsetInLine
    [string]$errLine = $_.InvocationInfo.Line

    Write-Error $ErrorMessage
    $Context.LogMessage("At Line #: " + $LineNumber + " at char " + $Offset, "Error")
    $Context.LogMessage("Executing: " + $errLine, "Error") 
}
    finally
    {
        # Close the database connection and release resources
        $command.Dispose()
    $connection.Close()
}

Answer 1

Hello,

The thing is that an instance of the SqlDataReader class represented by the $reader variable exposes the MoveNext iterator. It is used to enumerate the records returned by the database. When you call the $reader variable on this line:

if ($reader -eq $NULL)

this forces PowerShell to enumerate all members of the $reader SqlDataReader class (i.e. properties, methods). When doing so, it also enumerates all records returned by the database and moves the MoveNext iterator to the very last line returned by the database. When you further call the $reader.Read() method, it also tries to call MoveNext, but since it is already the last record, there is nowhere to move further. This is where the error occurs.

Here's an updated script that resolves the issue:

$databaseHost = "[i]dbHostname[/i]"
$databaseName = "[i]dbName[/i]"
$tableName = "[i]tableName[/i]"
$userIDField = "EMPID"

$databaseUsername = $NULL
$databasePassword = $NULL

# Connect to the database
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $connectionString = $connectionString + "Integrated Security=SSPI;"
}
else
{
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"
}

try
{
    $connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
    $connection.Open()

    # Check whether the user exists in the database
    $command = $connection.CreateCommand()
    $command.CommandText = "SELECT EMPID, LASTNAME, FIRSTNAME FROM $tableName WHERE $tableName`.$userIDField = @UserID"
    # $command.Parameters.Add("@UserID", "%employeeID%") | Out-Null;
    $command.Parameters.Add("@UserID", "500333") | Out-Null;
    $Context.LogMessage($command.CommandText, "Information")
    $reader = $command.ExecuteReader()

    if ($reader.Read())
    {
        $empID=$reader.GetString(0)
        $lName=$reader.GetString(1)
        $fName=$reader.GetString(2)
        # $deptNum = $reader.GetString(3)
        $Context.LogMessage( $fName,"Information")
    }
    else
    {
        $Context.LogMessage("A user with employee ID " + "%employeeID%" + " does not exist in the database", "Warning")
        return # Exit script
    }
}
catch [System.Exception]
{
    $ErrorMessage = $_.Exception.Message
    [string]$LineNumber = $_.InvocationInfo.ScriptLineNumber
    [string]$Offset = $_.InvocationInfo.OffsetInLine
    [string]$errLine = $_.InvocationInfo.Line

    Write-Error $ErrorMessage
    $Context.LogMessage("At Line #: " + $LineNumber + " at char " + $Offset, "Error")
    $Context.LogMessage("Executing: " + $errLine, "Error")
}
finally
{
    # Close the database connection and release resources
    if ($reader) { $reader.Close() }
    if ($command) { $command.Dispose() }
    if ($connection) { $connection.Close() }
}