Alright, so I gave up on trying to understand DataTables without truly understanding DataTables. So here I go in creating a DataTable in PowerShell, adding columns and rows, and editing the values within the rows.
So let’s start with… Creating a new DataTable
CREATE A DATATABLE
$myDT = New-Object System.Data.DataTable
Boom! DataTable accomplished. But alas, it is a boring and bland DataTable.
If you enter $myDT.Rows into the console, you’ll see that there is nothing within our new DataTable. It has no gut, no gold, no OOMPH! What is a DataTable if it holds nothing of importance? Let’s add columns to our poor DataTable!
ADD A COLUMN TO THE DATATABLE
Columns.Add() will accept the name of a new column you wish to add. In this case, I have chosen to add a new column named AVENGERS. If we are to use $myDT.Rows again however, we still have a whole lot… nothing. We can do $myDT.Columns and verify that we do indeed have a new column, but that isn’t sufficient to say that we have any form of data. So lets add a new row to our DataTable and enter an Avenger into our (at least my) AVENGERS column.
ADD A ROW TO THE DATATABLE
$rowOne = $myDT.NewRow()
$rowOne[“AVENGERS”] = “THE HULK”
First, a variable containing the DataTable.NewRow() – $rowOne = $myDT.NewRow(), DataTable being the DataTable (as the example show, I used $myDT) you’ll be adding the row to. The reason for doing this is that this creates a new row object that replicates the schema from that DataTable. This allows us to add to the column by name on the next line. $rowOne[“AVENGERS”] = “THE HULK” Allows us to target the column name in the bracers[“”] and set the value for that column in the row. Now that we’ve effectively created a new row that matches our DataTables schema and has a value in it, it needs to be added to the DataTable. This is done via $myDT.Rows.Add($rowOne) Which tells the DataTable you want to add the new row you just created based off it’s schema.
Entering $myDT.Rows now will display to use the new data we have entered into our DataTable
AVENGERS -------- THE HULK
Let’s go ahead and add another row of data to that
$rowTwo = $myDT.NewRow()
$rowTwo[“AVENGERS”] = “IRON MAN”
And now our DataTable will display
AVENGERS -------- THE HULK IRON MAN
ADDING MORE COLUMNS TO THE DATATABLE
Ok cool. So we’ve got some definitive data now in our DataTable. But let’s say we want to expand upon what we have and add another column? Well, if we run the Column.Add() again I bet we’ll get another column.
This’ll get me another column inserted to the right
AVENGERS IDENTITY -------- -------- THE HULK IRON MAN
Now, another column doesn’t do us any good if there’s no data in it. Found a few ways to enter data into the new column. Here seems to be the easiest.
ADDING A NEW VALUE TO A ROW
$rowOne.IDENTITY = “Bruce Banner”
This takes the row that was created earlier, it’s smart enough to know that there’s a new column in the DataTable and you’re allowed to set that value as it’s a property of the row object. You can do the same for the second row.
$rowTwo.IDENTITY = “Tony Stark”
But here’s the catch. What if you had a DataTable in which you didn’t create in this fashion and you had to update the values in a row? You wouldn’t be able to refer to a variable such as $rowOne. You can refer to the row’s index place via it’s property on the DataTable.
$myDT.Rows.IDENTITY = “Bruce Banner”
This has the exact same effect as $rowOne.IDENTITY = “Bruce Banner” except it doesn’t require that you have the row object on hand.
Here’s a way to add a column then add a value to each, such as an incrementing ID number.
$counter = 0
foreach ($PSITEM in $myDT.Rows)
$myDT.Rows[$counter].Id = $counter + 1
$counter += 1
– The first line, $myDT.Columns.Add(“Id”), adds the Id column.
– The second line creates a counter.
– The third line begins the foreach which says for each item, $PSITEM, found in the DataTables collections of rows, $PSITEM in $myDT.Rows
– The first line in the loop says that on the row equal to the counter, set the column ID equal to the counter plus one. Since the index of the rows in a DataTable is 0 based, I started the counter of with 0. Due to the fact that I don’t want the ID number to start off as 0, I set the value equal to the counter plus one.
– The second line of the loop increments the counter.