DataRow with dynamic properties

Question

I have a cmdlet in my module which returns a collection of generic DataRows from a database table, whose name is specified with -Name parameter

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

Every time I call the cmdlet on different tables I pipe the output to Format-Table which makes it more readable.

Is there any way to keep the properties formatted as a table without having to pipe every time to Format-Table? Something that tells PowerShell to always use table layout to display DataRow type?

I already have a ps1xml file together with the module, containing some format rule like this:

<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 to use a similar rule for System.Data.DataRow type, but the properties have different names every time and it seems I can't remove the <TableRowEntries> tag from the above XML template.

Any idea?

Answer 1

I would say that it is too much effort to write a custom format file just to make PowerShell outputting objects as a table.

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

I came up with a simple format definition that outputs DataRows as a table.

There is one caveat though: I don't think its possible to have custom labels for each column (you would have to check the schema definition again: https://msdn.microsoft.com/en-us/library/gg580910(v=vs.85).aspx). In my example I am just using generic names like "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>
'@

Updating the format definition:

$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