Author Posts

January 1, 2012 at 12:00 am

by KaiY at 2013-01-22 07:51:04

Hi Guys,

I need some help scripting a query from two or more datatables.

I've tried to create an example to show you my problem.

I create a dataset ($DataSet) containing 2 Datatables ($Table_1 and $Table_2) connected by the primarykey "name".
Could you help me to get a query datas from both tables.

Thank you very much and best greetings from Germany
Kai



$DataSet = New-Object System.Data.DataSet
$Table_1 = $DataSet.Tables.Add("Table_1")
$Table_2 = $DataSet.Tables.Add("Table_2")

$Properties_1 = @("Name","Salary","Country")
$Properties_1 | foreach {
$Column = New-Object System.Data.DataColumn($_)
$Table_1.Columns.Add($Column)
}

$Properties_2 = @("Name","Car","Fuel")
$Properties_2 | foreach {
$Column = New-Object System.Data.DataColumn($_)
$Table_2.Columns.Add($Column)
}
$Table_1.PrimaryKey = $Table_1.Columns["Name"]
$Table_2.PrimaryKey = $Table_2.Columns["Name"]

$Null=$Table_1.Rows.Add("Huber","45000","USA")
$Null=$Table_1.Rows.Add("Mayer","66000","Germany")
$Null=$Table_1.Rows.Add("Napf","53000","France")
" "
$Null=$Table_2.Rows.Add("Huber","Mercedes","Diesel")
$Null=$Table_2.Rows.Add("Bauer","Honda","Petrol")
$Null=$Table_2.Rows.Add("Napf","BMW","Super Petrol")

$DataView = New-Object System.Data.DataView($Table_1)
$DataView | ft Name, Country -Auto [color=#BF0040] #e.g. column "car" from table_2 should appear here, too[/color]

by DonJ at 2013-01-22 13:42:02

This is probably something better answered by the folks on StackOverflow.com; it's really a .NET Framework question unrelated to PowerShell. Sorry I couldn't be of more help.

by nohandle at 2013-01-23 10:43:11

Hi,
first of all I have to say I like your clean writing style, except personally I'd rather use '> $null' than '$null='. For me it just seems to better describe what is happening.
[quote="KaiY"]#e.g. column "car" from table_2 should appear here, too[/quote] As I see it you need to do a simple join of the tables (I am no db whiz 🙂 ) and for simple join you can use Merge method.
$baseTable = $Table_1.Copy()
$baseTable.Merge($Table_2)
$baseTable | ft -AutoSize

Name Salary Country Car Fuel
---- ------ ------- --- ----
Huber 45000 USA Mercedes Diesel
Mayer 66000 Germany
Napf 53000 France BMW Super Petrol
Bauer Honda Petrol

by KaiY at 2013-01-23 14:42:15

Hi,
thank you for this smart solution. It works exactly as I need it.

I got another solution, which also works but doesn't look so smart as yours


$Relation1 = New-Object System.Data.DataRelation( "bothTables", $Column1, $Column2 )
$DataSet.Relations.Add($Relation1)

ForEach($ParentRow in $Relation1.ParentTable.Rows) {
ForEach($ChildRow in $ParentRow.GetChildRows($Relation1)) {
$a += new-Object -TypeName PSObject -Property @{Name=$ParentRow["Name"];Salary=$ParentRow["Salary"];Country=$ParentRow["Country"];car=$ChildRow["car"];fuel=$ChildRow["fuel"]}
}
}
$a | ft * -auto

by nohandle at 2013-01-24 08:32:00

Great that worked out for you. Just don't forget my solution is probably pretty limited. As Don suggested, asking the experienced devs on other forum is really a good idea.