For the 3rd post, we’re going to look at retrieving data from Sql Server. It’s not much more complicated than the second post, but it does involve some new objects.
On the last post, we finished off with inserting some data into the database using a ServerConnection object. Now, we’re going to execute a query to get that data back out. When you call the ExecuteReader method of the ServerConnection object and pass it in a SELECT statement, it will return you a SqlDataReader object:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
while($sdr.Read()){
$sdr.GetInt32(0)
$sdr.GetString(1)
}
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
This does a while loop on the SqlDataReaders’ Read() method, which will return $true as long as there are records to be read.
While using the SqlDataReader is fast and efficient, it’s not the most user-friendly object to use for reading data in powershell. For ease of use, you can’t beat a DataTable.
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$dataTable = New-Object System.Data.DataTable
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
$dataTable.Load($sdr);
$dataTable | ft -AutoSize
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
In this code, we’re still executing the same statement and loading the SqlDataReader object, but then we create a new System.Data.Datatable object and call the Load method on the DataTable and pass in the SqlDataReader. This object is probably the easiest object to work with for reading data from Sql Server with powershell. Here’s another example using a DataTable to export data to a csv file:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$dataTable = New-Object System.Data.DataTable
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
$dataTable.Load($sdr);
$dataTable | Export-Csv -Path "$env:windir\temp\TestCSV1.csv" -Force -NoTypeInformation
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
The –Force will overwrite the file if it already exists. The –NoTypeInformation does what it says, it omits type information from the file. If you don’t specify this, you’ll see extra type information output into the csv file that you probably don’t want.
You can also filter on the values in the DataTable via the where-object clause in powershell as well:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$dataTable = New-Object System.Data.DataTable
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
$dataTable.Load($sdr);
$dataTable | where{$_.ID -gt 3} | %{
$_
}
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
Sql Server Powershell 