Missing row when using ExecuteReader to fill a DataTable

I’ve been using the following PowerShell code to retrieve the contents of an SQL Server table and store it in a DataTable for fast local processing:

$BuildDBConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Server=RCMSQL01;Database=TSBuild;Integrated Security=SSPI"
$BuildDBConnection.Open()
# Get Main table
$SQLCommand = $BuildDBConnection.CreateCommand()
$SQLCommand.CommandText = ("SELECT * FROM Main")
$SQLReader = $SQLCommand.ExecuteReader()
if($SQLReader.Read()){
    $MainTable = New-Object System.Data.DataTable
    $MainTable.Load($SQLReader)
    $SQLReader.Close()
}
$MainTable | ft -AutoSize

It looked like it had been working fine. However, today I noticed that some data was missing. Specifically the first row of data as seen if you view the SQL table via a Select query (e.g. via SQL Server Management Studio).

I eventually tracked this down to being caused by the if statement, and thus probably the the $SQLReader.Read() is retrieving the first row of the table, and then the $DataTable.Load() is getting everything else.

I have two fixes so far:

1) Don’t use the if statement, just try and load the $SQLReader object straight into the DataTable.

2) Use a SQLDataAdapter instead:

$BuildDBConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Server=RCMSQL01;Database=TSBuild;Integrated Security=SSPI"
$BuildDBConnection.Open()
# Get Main table
$SQLCommand = $BuildDBConnection.CreateCommand()
$SQLCommand.CommandText = ("SELECT * FROM Main")
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SQLCommand
$MainTable = New-Object System.Data.DataTable
$SQLAdapter.Fill($MainTable)
$MainTable | ft -AutoSize
This entry was posted in PowerShell, Scripting and tagged , , , , , , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s