Database Reference
In-Depth Information
The last line of the function returns the
DataTable
using
return ,$dt
; the comma
("unary comma") is important as it prevents PowerShell from unrolling the collection of rows
of
DataTable
. By default PowerShell unrolls enumerable types, meaning that without the
comma the return type of this method would actually be
Object[]
(the
Object
array of
DataRow
) or a
DataRow
if only one record.
Generally this unrolling is desirable, as it is useful with piping so script blocks downstream
in the pipeline can start operating on items one at a time for faster output and perceived
performance. In the case of this script this is avoided since a small amount of data is
completely loaded in memory, there is no heavy processing, and the function implies
returning a consistent type of
DataTable
(with all of its functionality available).
Get-DataTable
is passed the open connection object and an inline SQL string; for more
complex SQL you might want to use a multiline string or read the SQL from a ile. The output
from the function is captured in the
$dt
variable and the script outputs the count of rows and
then uses
$dt | ft -auto
to format the
DataTable
output as a table with the columns
auto-sized to the data.
Finally the script uses a
foreach
loop to iterate through each
DataRow
and perform
processing and close the connection when inished. The processing here is an arbitrary
calculation of eligibility based on being hired for 5 years or longer, with output based on
being eligible. Note that with PowerShell we were able to use
$dr.hire_date
to refer
to the
hire_date
ield without having to use
$dr["hire_date"]
.
There's more...
There are a few more basics to look at including other ways to iterate the data and handling
null checking.
Unrolling and null checking
Let's look at an example where we are not preventing the unrolling of the collection:
1.
First we replace
return ,$dt
in
Get-DataTable
with
return $dt
(removing the comma).
2.
Next we change the code between the
Connect-Oracle
(Get-ConnectionString)
and
$conn.Close()
lines to the following:
$sql = "select city, state_province from locations order by city,
state_province"
Get-DataTable $conn $sql | foreach {
$cityState = $_.city
if ($_.state_province -ne [DBNull]::Value)
{
$cityState += ", " + $_.state_province
Search WWH ::
Custom Search