Add a calculated field to result data from a database

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
minimen456
Posts: 5
Last visit: Tue Apr 27, 2021 4:04 am
Has voted: 1 time

Add a calculated field to result data from a database

Post by minimen456 »

Hello,

I would like to add a calculated field to a table I've pulled from a database. I'm using "| Select @{" to build a custom object with a calculated field. I call it the object $x. But when I sent it to ConvertTo-DataTable function it throws an error. Though Out-GridView works fine with $x. How do I build $x properly so it can be attached to $datagridview1?

Code: Select all

function Update-Grid_Database4_ComputersTable
{
	param
	(
		[Parameter(Mandatory = $true,
				   Position = 1)]
		[string[]]$ComputerName
	)
	#Database Query
	$QueryString = @"
		select * from [ComputersTable] where [ComputerName] in ('{0}')
"@ -f ($ComputerName -join "','")
	#Database Connection String
	$ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb;Password=;User ID=Admin'
	
	$command = New-Object System.Data.OleDb.OleDbCommand ($QueryString, $ConnectionString)
	$adapter = New-Object System.Data.OleDb.OleDbDataAdapter ($command)
	
	#Load the Dataset
	$dataset = New-Object System.Data.DataSet
	[void]$adapter.Fill($dataset)
	
	
	#Use the Result Table as the DataSource
	
	$x = $dataset.Tables[0] | select *, @{ n = "calculated_field"; e = { "$($_.ComputerName) test" } } -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors, name
	$datagridview1.DataSource = ConvertTo-DataTable -InputObject $x -FilterWMIProperties
	
	#$x | Out-GridView
}
Last edited by minimen456 on Sat Apr 03, 2021 2:33 am, edited 4 times in total.

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Add a calculated field to result data from a database

Post by jvierra »

There is no need to convert a data table to a DataTable as it is already a DataTable.

When adding fields to a query it is always better to add the fields in the query by just adding them in the SQL SELECT statement.

minimen456
Posts: 5
Last visit: Tue Apr 27, 2021 4:04 am
Has voted: 1 time

Re: Add a calculated field to result data from a database

Post by minimen456 »

In this sample $datagridview1 and Out-GridView works:

Code: Select all

$x = $dataset.Tables[0] 
$datagridview1.DataSource = $x
$x | Out-GridView
In this sample $datagridview1 displays nothing, Out-GridView works.

Code: Select all

$x = $dataset.Tables[0] | select *, @{ n = "calculated_field"; e = { "$($_.calculated_field) test" } } -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors, name
$datagridview1.DataSource = $x	
$x | Out-GridView
In other words, after building a custom object with select *, @{ n = I can't throw it into $datagridview1 as it would display nothing.

As for adding calculated field using SQL SELECT statement. Imagine there is part of a data in MS Access database and another part in Active Directory. I can't merge them in a single SQL SELECT statement, can I?

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Add a calculated field to result data from a database

Post by jvierra »

Here is a simple example that doesn't alter the table after it is built except to assign the correct values.

Code: Select all

function Update-Grid_Database4_ComputersTable{
	param(
		[Parameter(Mandatory)]
		[string[]]$ComputerName
	)
    
	$connStr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb;Password=;User ID=Admin'
    
    $computers = "'" + ($ComputerName -join "','") + "'"
	$sql = "select *,[temp text] As computed_field from ComputersTable where ComputerName in ($computers)"
	
	$cmd = New-Object System.Data.OleDb.OleDbCommand ($sql, $connStr)
    $rdr - $cmd.ExecuteReader()
`	$dt = New-Object System.Data.DataTable
	[void]$rdr.Load($dt)
    
    # Assign values to added field
    $dt | ForEach-Object{
        $_.computed_field = #< new computed value >
    }
	$datagridview1.DataSource = $dt
	 
}
Of course I have absolutely no idea of what you are intending to accomplish. From what you have posted this is my best guess.

minimen456
Posts: 5
Last visit: Tue Apr 27, 2021 4:04 am
Has voted: 1 time

Re: Add a calculated field to result data from a database

Post by minimen456 »

Thanks, I've got the idea. The solution where I create a temporary field in SQL Select statement and then alter it in ForEach-Object cycle is acceptable. Though the sample doesn't work. $rdr doesn't have load method. It seems one suppose to $dt.load($rdr)

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Add a calculated field to result data from a database

Post by jvierra »

Sorry. I typed it backwards when I typed quickly.

Yes, the DataTable is what gets loaded and not the Reader. The reader is already loaded it just needs to be converted from a stream to a table.

Locked