Dataview with Datas from several Tables

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 3 months ago.

  • Author
    Posts
  • #6001

    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.

You must be logged in to reply to this topic.