PowerShell: Creating and Editing DataTables with ADO.NET

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

$myDT.Columns.Add(“AVENGERS”)

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”
$myDT.Rows.Add($rowOne)

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”
$myDT.Rows.Add($rowTwo)

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.

$myDT.Columns.Add(“IDENTITY”)

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[0].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.

$myDT.Columns.Add(“Id”)
$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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s