31 Posts of using Sql Server with Powershell — Post 3: Retrieving Data

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.

image

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
Advertisements

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

%d bloggers like this: