Set Multiple Columns PrimaryKey in a DataTable

Welcome Forums General PowerShell Q&A Set Multiple Columns PrimaryKey in a DataTable

This topic contains 7 replies, has 2 voices, and was last updated by

 
Keymaster
6 months, 1 week ago.

  • Author
    Posts
  • #101933

    Participant
    Points: 0
    Rank: Member

    Hi guys,
    I'm working with DataTable.
    I'm bringing my data from SQL Server Table, I need to iterate through some rows in the table based on specific criteria and modify records accordingly.
    The table has multiple primary key columns.
    I know the syntax for one primary key column but not multiple
    For simplicity, I have my syntax below
    can anyone please tell me how to write the syntax for multiple primary key column?

    Here is my script:

    [System.Data.DataTable]$dtGL = New-Object System.Data.DataTable("GL")
    #Schemas
    $dtGL.Columns.Add("Account", "String") | Out-Null
    $dtGL.Columns.Add("Property", "String") | Out-Null
    $dtGL.Columns.Add("Date", "DateTime") | Out-Null
    $dtGL.Columns.Add("Amount", "Decimal") | Out-Null
    #Records
    $dtGL.Rows.Add('00001','1000','1/1/2018','185') | Out-Null 
    $dtGL.Rows.Add('00001','1000','1/2/2018','486') | Out-Null
    $dtGL.Rows.Add('00001','1001','1/1/2018','694') | Out-Null
    $dtGL.Rows.Add('00001', '1001', '1/2/2018', '259') | Out-Null
    
    #Set Primary Key on Columns: Account|Property|Date
    [System.Data.DataColumn[]] $KeyColumn = New-Object System.Data.DataColumn[3]
    $KeyColumn[0] = $dtGL.Columns["Account"]
    $KeyColumn[1] = $dtGL.Columns["Property"]
    $KeyColumn[2] = $dtGL.Columns["Date"]
    $dtGL.PrimaryKey = $KeyColumn 
    

    If you run it on your end you should get the same error that I'm getting:

    New-Object : Cannot find type [System.Data.DataColumn[3]]: verify that the assembly containing this type is loaded.
    At C:\Users\jabou-ghannam\OneDrive – Island Hospitality\Island Hospitality\PowerShell\Begining PowerShell Scripting for Developers\Working With
    DataTable.ps1:28 char:41
    + ... .Data.DataColumn[]] $KeyColumn = New-Object System.Data.DataColumn[3]
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

  • #101935

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    So... you're -really- just using .NET, here, not PowerShell per se. StackOverflow.com might net you a happier answer :).

    Perusing https://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey(v=vs.110).aspx, it would appear you can pass an array.

    • #101938

      Participant
      Points: 0
      Rank: Member

      Found the answer after multiple trial and error 🙂

      [System.Data.DataColumn[]]$KeyColumn = ($dtGL.Columns["Account"],$dtGL.Columns["Property"],$dtGL.Columns["Date"])
      $dtGL.PrimaryKey = $KeyColumn 
      

      But now I need to query the datatable and I know how to do it in .NET, I just can't figure out how to do it in Powershell 🙁

      [String[]]$RowsToFind = '00001', '1001', '01/01/2018'
      $FoundRows = $dtGL.Rows.Find($RowsToFind)
      
      $FoundRows 
      
      

      I'm getting the following error

      Exception calling "Find" with "1" argument(s): "Expecting 3 value(s) for the key being indexed, but received 1 value(s)."
      At C:\Users\myuser\OneDrive - mycompany\companyname\PowerShell\Samples\Working With DataTable.ps1:38 char:1
      + $FoundRows = $dtGL.Rows.Find($RowsToFind)
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
          + FullyQualifiedErrorId : ArgumentException
      
  • #101944

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    I mean, as I'm reading it, that should work.

    I might have done:

    $RowsToFind = @('00001', '1001', '01/01/2018')

    Instead. That's how you'd normally do an array in PowerShell. At least how I'd do it. But if it's not working, then I dunno. You might ping Robert Cain since you're watching his course :).

    • #101945

      Participant
      Points: 0
      Rank: Member

      Hahahah
      Thanks Don
      For what it's worth I watched your courses too on Pluralsight 🙂

      I posted my question on Stackoverflow

      John

    • #101950

      Participant
      Points: 0
      Rank: Member

      $FoundRows = $dtGL.Rows.Find(@('00001', '1001', '01/01/2018'))
      $FoundRows |Out-GridView
      I had to pass it this way
      It did work Don 🙂

      thank you so much

    • #101951

      Participant
      Points: 0
      Rank: Member

      how do I mark your answer as an "answer"?

  • #101954

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    The forums don't offer that.

The topic ‘Set Multiple Columns PrimaryKey in a DataTable’ is closed to new replies.