Invoke-DbaQuery: Add new parameter to convert byte-arrays to hex strings

[andreasjordan]

Summarize Functionality

This is a follow up to #8453.

The output of querys like Invoke-DbaQuery -SqlInstance SQL01 -Query "SELECT sql_handle FROM sys.dm_exec_query_stats" -As PSObject returns an array of bytes, which is not good to read for the user. Instead the same conversion that the SSMS does would be nice as an optional parameter when using the conversion to PSObject.

I'm not a C# expert and need help with this. I searched and played a little bit and these two solutions seem to work:

$cSharp = @'
using System;
using System.Text;
using System.Data;
using System.Management.Automation;

public class DBNullScrubber
{
    public static PSObject DataRowToPSObject(DataRow row, bool bytes2string)
    {
        PSObject psObject = new PSObject();

        if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
        {
            foreach (DataColumn column in row.Table.Columns)
            {
                Object value = null;
                if (!row.IsNull(column))
                {
                    if (bytes2string && row[column] is Byte[]) {
                        byte[] bytes = (byte[])row[column];
                        StringBuilder sb = new StringBuilder();
                        sb.Append("0x");
                        for (int i = 0; i < bytes.Length; i++)  
                        {  
                            sb.Append(bytes[i].ToString("X2"));  
                        }  
                        value = sb.ToString();
                    } else {
                        value = row[column];
                    }
                }

                psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
            }
        }

        return psObject;
    }
}
'@

$cSharp = @'
using System;
using System.Data;
using System.Management.Automation;

public class DBNullScrubber
{
    public static PSObject DataRowToPSObject(DataRow row, bool bytes2string)
    {
        PSObject psObject = new PSObject();

        if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
        {
            foreach (DataColumn column in row.Table.Columns)
            {
                Object value = null;
                if (!row.IsNull(column))
                {
                    if (bytes2string && row[column] is byte[]) {
                        value = BitConverter.ToString((byte[])row[column]).Replace("-", "");
                    } else {
                        value = row[column];
                    }
                }

                psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
            }
        }

        return psObject;
    }
}
'@

The first uses "StringBuilder" from "System.Text", the second uses "BitConverter". Both "work on my machine", but I don't know if they work on older PowerShell versions. Who can help?

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

No response

[niphlod]

I'm reeeeally not sure here, as this changes the format. I get most are used to see binaries in SSMS in a certain way but the underlying data is binary nonetheless, and then people are going to ask the reversal "because I want to loop several instances and dump the data in a central database and I need to keep the format intact".

I'd like to keep "query" and "formatting" clearly separated. nullscrubber exists because null and dbnull are different types but the same concept, and can be easily "autoconverted" in both directions. This binary representation isn't .

[andreasjordan]

I agree, let's not mix "query" and "formatting".

So for now, I will use a filter like the following if needed in projects:

filter Convert-ByteArrayToString {
    if ($_.GetType().Name -eq 'PSCustomObject') {
        foreach ($property in $_.PSObject.Properties.Name) {
            if ($null -ne $_.$property -and $_.$property.GetType().Name -eq 'Byte[]' -and $_.$property.Count -gt 0) {
                $string = '0x'
                foreach ($byte in $_.$property) {
                    $string += '{0:X2}' -f $byte
                }
                $_.$property = $string
            }
        }
        $_
    } elseif ($_.GetType().Name -eq 'Byte[]') {
        $string = '0x'
        foreach ($element in $_) {
            if ($null -ne $element -and $element.GetType().Name -eq 'Byte') {
                $string += '{0:X2}' -f $element
            }
        }
        $string
    } elseif ($_.GetType().Name -eq 'Byte') {
        '0x{0:X2}' -f $_
    } else {
        Write-Warning -Message "Input datatype $($_.GetType().FullName) is not supported"
    }
}

Sample code:

$sql = "SELECT TOP(5) query_hash, query_plan_hash, total_elapsed_time FROM sys.dm_exec_query_stats"
$resultObject = Invoke-DbaQuery -SqlInstance SQL01 -Query $sql -As PSObject
$queryHash = $resultObject[0].query_hash
$queryHashFirstByte = $queryHash[0]

$resultObject | Convert-ByteArrayToString
, $queryHash | Convert-ByteArrayToString
$queryHashFirstByte | Convert-ByteArrayToString

Maybe we evolve this to a dbatools command in the future...

[niphlod]

Output-DbaQueryAsSSMS ^_^

[wsmelton]

Create a utility command like: Convert-DbaQueryOutput -Property. Then the user can control when it happens on Invoke command via parameter -PrettyOutput or SsmsOutput of some sort.

The C# code, or any used for this would need to be added to the core DLL library (dbatools.dll).