Dataview with Datas from several Tables

PowerShell Help
1

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 :slight_smile: ) 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.