Dynamic DataRow

Question

I have a cmdlet in my module that returns a collection of shared DataRows from a database table whose name is specified with the -Name parameter

 # this returns a collection of datarows from table "Orders" Get-Table -Name Orders 

Each time I call the cmdlet in different tables, I process the output on Format-Table , which makes it more readable.

Is it possible to save properties formatted as a table without having to translate each time to Format-Table ? Is there something that PowerShell says to always use a table layout to display a DataRow type?

I already have a ps1xml file with a module containing some format rule:

 <View> <Name>MyType</Name> <ViewSelectedBy> <TypeName>MyNamespace.MyType</TypeName> </ViewSelectedBy> <TableControl> <TableRowEntries> <TableRowEntry> <TableColumnItems> <TableColumnItem> <PropertyName>Property1</PropertyName> </TableColumnItem> <TableColumnItem> <PropertyName>Property2</PropertyName> </TableColumnItem> [...] </TableColumnItems> </TableRowEntry> </TableRowEntries> </TableControl> </View> 

I thought of using a similar rule for the System.Data.DataRow type, but the properties have different names each time, and it seems like I cannot remove the <TableRowEntries> from the above XML template.

Any idea?

Answer 1

I would say that there is too much effort to write a user-format file to force PowerShell to display objects in a table.

If you really want to try, you are already on the right track.

I came up with a simple format definition that displays DataRows in a table format.

However, there is one caveat: I do not think it is possible to have custom labels for each column (you will need to check the schema definition again: https://msdn.microsoft.com/en-us/library/gg580910(v=vs.85 ) .aspx ). In my example, I just use common names such as "Field1", "Field2", etc.

Here is the format definition for 2 columns:

 $TableFormat = @' <Configuration> <ViewDefinitions> <View> <Name>MyTable</Name> <ViewSelectedBy> <TypeName>System.Data.DataRow</TypeName> </ViewSelectedBy> <TableControl> <TableHeaders> <TableColumnHeader> <Label>Field 1</Label> <Width>20</Width> </TableColumnHeader> <TableColumnHeader> <Label>Field 2</Label> <Width>20</Width> </TableColumnHeader> </TableHeaders> <TableRowEntries> <TableRowEntry> <TableColumnItems> <TableColumnItem> <ScriptBlock>$_.Item(0)</ScriptBlock> </TableColumnItem> <TableColumnItem> <ScriptBlock>$_.Item(1)</ScriptBlock> </TableColumnItem> </TableColumnItems> </TableRowEntry> </TableRowEntries> </TableControl> </View> </ViewDefinitions> </Configuration> '@ 

Format Definition Update:

 $FormatPath = Join-Path -Path $PSScriptRoot -ChildPath "DataTable.format.ps1xml" $TableFormat | Set-Content -Path $FormatPath -Encoding Default Update-FormatData -Appendpath $FormatPath 

And an example output:

 $CnStr = "Data Source=Starbase1\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" $Sql = "Select LastName, City From Employees Where City <> 'Seattle'" $Da = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter -ArgumentList $Sql, $CnStr $Ta = New-Object -TypeName System.Data.DataTable $Da.Fill($Ta) $Ta