PowerShell 5.1 handling sql server nvarchar datatype-CodePudding

I am loading a DataTable with data from SQL Server as such:

$queryStr = "SELECT TOP 10 ID, QueryText FROM dbo.DatabaseName";
$dataRows = Invoke-DbaQuery -SqlInstance instance.name -Database databasename -Query $queryStr -As DataSet;

In SQL Server the QueryText is nvarchar(max). In PowerShell, this becomes a string datatype, equal to varchar I think. I think this because when I try to calculate the hash in PowerShell with Get-FileHash, and in SQL Server I calculate the hash on the nvarchar column with SELECT (CONVERT([varchar](70),hashbytes('SHA2_256', QueryText),(1))), the hashes do not match.

They DO match however, if I convert the nvarchar to a varchar(max) in SQL Server.

So the question is, in PowerShell how can I convert the string datatype to match the nvarchar datatype in SQL Server? Because as far as I know, PowerShell does not have a nvarchar datatype, just the generic string datatype.

Added this part after reading comments.

In the DataTable that I retrieve from SQL Server as per the above code I add an extra column to hold the hash that I calculate in PowerShell.

Add extra column to DataTable:

$HashColumn = [System.Data.DataColumn]::new('QueryHashString', [string]);
$dataRows.Tables[0].Columns.Add($HashColumn);

Now I do a foreach to fill this column I just added:

foreach($row in $dataRows.Tables[0]) {
    $stringAsStream = [System.IO.MemoryStream]::new()
    $writer = [System.IO.StreamWriter]::new($stringAsStream)
    $writer.write("$($row.QueryText)")
    $writer.Flush()
    $stringAsStream.Position = 0
    $row.QueryHashString = (Get-FileHash -InputStream $stringAsStream | Select-Object -ExpandProperty Hash)
}

CodePudding user response：

Your code uses StreamWriter that uses the default UTF-8 encoding, which matches what you get with hashing a VARCHAR -- if you stick to ASCII characters. To hash Unicode instead (and for variation, let's use SHA256 directly instead of going through Get-FileHash, and throw in an emoji so we have to deal with surrogates):

$s = "Hello, world! I