Accessing .NET Provider Objects

[mikeg]

Using an example from Allen White's Blog on sqljunkies.com, I have a working SQL Server connection to a Cache linked server. The script below returns an array element count of 0, 1 or 2 and corresponding number of elements/rows depending up what's in our HL7 queue at the time (see sample output below).
Next I'd like to: (1) assign the array element count to a variable and (2) assign the message_total to variables.
Being a PowerShell novice, I don't know syntax for the right side of the assignment (see places where I have question marks in the commented out switch logic below). I probably need to access the correct array or table element, but the syntax I've tried doesn't work and I don't know where to find a reference for accessing the properties and methods of these objects in PowerShell. I believe the SQL Server connection, data set, data adapter and date table objects here are .NET providers, but I need a reference for how to use them. Any reference and/or example of what to use where I have question marks would be much appreciated!
----------------------------------------------------------------------------------------------# test_script.ps1$cn = New-Object system.data.SqlClient.SqlConnection("Data Source=MySQLServer;Integrated Security=SSPI;Initial Catalog=master");$ds = new-object "System.Data.DataSet" "dsMyDataSet"$q = "SELECT * FROM OPENQUERY("$q = $q + " Cache_Linked_Server,"$q = $q + " 'Select * "$q = $q + " From TEST.HL7.queue_monitor');"$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)$da.Fill($ds)$dtHL7 = new-object "System.Data.DataTable" "dsMyDataSet"$dtHL7 = $ds.Tables[0]$dtHL7 | ForEach-Object { $_.row_id + ", " + $_.interface_mode + ", " + $_.interface_type + ", " + $_.message_total }
# Not sure how to access values where there are question marks below # $array_elements = ?[0]## switch ($array_elements) {# 0 { break }# 1 { $lab_queue = ?[1] }# 2 { $lab_queue = ?[1] $pharmacy_queue = ?[2] }# }
----------------------------------------------------------------------------------------------Sample output from running this script:
Example 1: 0
Example 2: 1 ADT-LAB||LIVE, LIVE, ADT-LAB, 2
Example 3: 2 ADT-LAB||LIVE, LIVE, ADT-LAB, 1 ADT-PHARMACY||LIVE, LIVE, ADT-PHARMACY, 1

[jhicks]

First off, you can pipe any object to Get-Member to discover all of its properties and methods.

As to your code, is the blog entry you refer to using PowerShell or VBScript or something else?

In PowerShell, the steps for working with databases are to create a connection, create a command object, add a query to the command object, tie the command object to the connection, execute.

What you get back is a Reader object. The Read() method returns true to if there are more rows to read. To read a value for a column, you need to use the GetValue() method. But you need to specify the column number that corresponds to what you're looking for. To get that use the GetOrdinal() method.

Here's an example using SQLExpress2005 but the concepts should apply to your situation as well.

Code: Select all

#Demo-Database.ps1

[reflection.assembly]::loadwithpartialname("System.Data") | Out-Null

#create the SQL Connection object
$conn = New-Object system.data.sqlclient.sqlconnection

#Connecting to SQL2005 Express
$conn.connectionstring = "Data Source=$env:computernameSQLEXPRESS;Initial Catalog=JDHDB;Integrated Security=True"
$conn.open()

$cmd = New-Object system.Data.SqlClient.SqlCommand

#join the connection to the command object
$cmd.connection = $conn

#define the SQL query
$cmd.commandtext = "SELECT Name,OS,Model FROM Computers WHERE OS Like '%Vista%'"
# "SELECT Name,OS,Model FROM Computers"
# "SELECT Name,OS,Model FROM Computers WHERE OS Like '%Vista%'"

#execute the query
$reader = $cmd.executereader()

#read the recordset
while ($reader.read()) {
    $computer=$reader.getvalue($reader.getordinal("Name"))
    $os=$reader.getvalue($reader.getordinal("OS"))
    $model=$reader.getvalue($reader.getordinal("Model"))

    $msg="{0} [{1} {2}]" -f $computer,$model,$os
    Write-Host Managing $msg
}

#close connections
$conn.close()
$reader.close()

There is a lot of material on this topic in Windows PowerShell v1.0: TFM 2nd ed.

[jvierra]

Here is a method for using a data table.

Example uses OleDB and SQLServer 2005/AdventureWorks.

Code: Select all

	
function Get-OleTable( [string]$sqlText, [string]$connectString) {
    $da = new-object System.Data.OleDb.OleDbDataAdapter
    $ds = new-object System.Data.DataSet
    $conn = new-object System.Data.OleDb.OleDbConnection($connectString)
    $conn.Open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($sqlText,$conn)
    $cmd.CommandType = "Text"
    $da.SelectCommand = $cmd
    $ret=$da.Fill($ds)
    $ds.Tables[0]
}
	
 
	
$sql = "select * from Person.Contact"
$cs = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=.sqlexpress"
	
$table=Get-OleTable$sql $cs
	
$table | ft
	

The last line fully decode the table object and prints a nice table with headers. Output can be sent to any of the PoSH formatters and data export CmdLets.

Jeff's "Reader" technique is more standard but is not as easy when you are looking for a table-like result.

Underneath the covers the DataAdapter/DataSet method use the reader to fill the dataset because the "Fill" method knows what a DataSet is and how to fill it from an adapter so it knows how to create the reader and execute it. The table object is then extracted.

Since all code is in a function there should be no need to close and destroy the objects.

[jhicks]

Now that I look at your question again I think I have a better idea. You aren't really querying a SQL database. You want to query something in SQL itself. If that's the case my example is not what you want.Are you trying to do dhis:$i=$dtHL7.Count

[mikeg]

Yes, I think that's what I need for the possibly misnamed $array_elements variable. I'll give it a try. Then, I need actual data elements from $_.message_total for $lab_queue and $pharmacy_queue.

I think part of my problem is not getting intellisense to work. When I enter the object $dtHL7. nothing shows from intellisense.