Set Multiple Columns PrimaryKey in a DataTable

PowerShell Help
1

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: (:slight_smile: [New-Object], PSArgumentException
    • FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
2

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.

3

Found the answer after multiple trial and error :slight_smile:

[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 :frowning:

[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
4

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

5

Hahahah
Thanks Don
For what it’s worth I watched your courses too on Pluralsight :slight_smile:

I posted my question on Stackoverflow

John

6 
$FoundRows = $dtGL.Rows.Find(@('00001', '1001', '01/01/2018'))
$FoundRows |Out-GridView

I had to pass it this way
It did work Don :slight_smile:

thank you so much

7

how do I mark your answer as an “answer”?

8

The forums don’t offer that.