Anders Hesselbom

Reading out SQL Data with named columns

2011-12-02

This code reads out names from the Employees table of the Northwind database. You must correct the Data Source property in the connection string for it to run.

#Create a connection object and open it.
[String]$cns="Data Source=XXX;Initial Catalog=Northwind;
   Integrated Security=True"
[System.Data.SqlClient.SqlConnection]$connection=New-Object 
   -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $cns
$connection.Open()

#Create a command and execute it.
[String]$query="SELECT FirstName, LastName
   FROM dbo.Employees ORDER BY LastName, FirstName" 
[System.Data.SqlClient.SqlCommand]$command=New-Object
   -TypeName System.Data.SqlClient.SqlCommand
$command.Connection=$connection
$command.CommandText=$query
$r=$command.ExecuteReader()

#Iterate the result.
while($r.Read()) {
    
    #Read out first name.
    [String]$firstname=""
    if( -not $r.IsDBNull(0)) {
        $firstname=$r.GetString(0)
    }
    
    #Read out last name.
    [String]$lastname=""
    if( -not $r.IsDBNull(1)) {
        $lastname=$r.GetString(1)
    }
    
    #Display.
    Write-Output ($firstname + " " + $lastname)
}

#Close the reader.
$r.Close()

$connection.Close()
$connection.Dispose()

Here I use column indexes when referring to columns. A change in the SQL query will produce errors in the code that reads out the result. The solution is to call the GetOrdinal function of the reader to get the indexes of the columns, like this:

#Create a connection object and open it.
[String]$cns="Data Source=XXX;Initial Catalog=Northwind;
   Integrated Security=True"
[System.Data.SqlClient.SqlConnection]$connection=New-Object 
   -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $cns
$connection.Open()

#Create a command and execute it.
[String]$query="SELECT FirstName, LastName
   FROM dbo.Employees ORDER BY LastName, FirstName" 
[System.Data.SqlClient.SqlCommand]$command=New-Object 
   -TypeName System.Data.SqlClient.SqlCommand
$command.Connection=$connection
$command.CommandText=$query
$r=$command.ExecuteReader()

#Get use named columns.
[int]$Index_FirstName=$r.GetOrdinal("FirstName")
[int]$Index_LastName=$r.GetOrdinal("LastName")

#Iterate the result.
while($r.Read()) {
    
    #Read out first name.
    [String]$firstname=""
    if( -not $r.IsDBNull($Index_FirstName)) {
        $firstname=$r.GetString($Index_FirstName)
    }
    
    #Read out last name.
    [String]$lastname=""
    if( -not $r.IsDBNull($Index_LastName)) {
        $lastname=$r.GetString($Index_LastName)
    }
    
    #Display.
    Write-Output ($firstname + " " + $lastname)
}

#Close the reader.
$r.Close()

$connection.Close()
$connection.Dispose()

Now, if you change the the query, the reader will still find the desired columns.

Categories: PowerShell