How to stream web service data via PowerShell into a SQL Server VarBinary column-CodePudding

We have a working solution in PowerShell to stream web service (WS) data into a SQL Server 2019 table with a column of datatype NVARCHAR(max). This WS data is just business data in Json format. I'd like to enhance this solution to also be able to stream the WS data into a VARBINARY(max) column.

The currently working code looks like this (abbreviated):

$SQL_ImpInsert = @"
Insert Into [Database].dbo.ImportTable  (SeqNo, BulkDataNVarChar)
Values  (@SeqNo, @WSData)
"@

# Create the SQL objects.
$SQLConn = New-Object System.Data.SqlClient.SqlConnection $ConnStr
$SQLConn.Open() 

$SQLCommand = $SQLConn.CreateCommand()
$SQLCommand.CommandText = $SQL_ImpInsert 

# Prep the stream
$WS_URL = 'https://a_valid_ws_request'

$WebRequest = [System.Net.WebRequest]::Create($WS_URL)
$WebRequest.ClientCertificates.Add($WS_Cert) | Out-Null
$WebRequest.Method = "Get"

$WebResponse  = $WebRequest.GetResponse()
$WebStream    = $WebResponse.GetResponseStream()
$WebStreamRdr = New-Object System.IO.StreamReader $WebStream

# Prep the SQL command to use the stream
$SeqNo = 1

$SQLCommand.Parameters.Clear()
$SQLCommand.Parameters.Add("@SeqNo",  [Data.SQLDBType]::INT, -1).Value = $SeqNo
$SQLCommand.Parameters.Add("@WSData", [Data.SQLDBType]::NVARCHAR, -1).Value = $WebStreamRdr

# Perform the streamed insert from the WS into the SQL table
$ExecOutput = $SQLCommand.ExecuteNonQuery();

To attempt to enhance the solution I added a VARBINARY column to the table and changed the command parameter datatype to VARBINARY as follows (only changes to above code shown):

$SQL_ImpInsert = @"
Insert Into [Database].dbo.ImportTable  (SeqNo, BulkDataBlob)
Values  (@SeqNo, @WSData)
"@

$SQLCommand.Parameters.Add("@WSData", [Data.SQLDBType]::VARBINARY, -1).Value = $WebStreamRdr

When I run the new version of the PowerShell script I get the following error raised during the ExecuteNonQuery statement:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Failed to convert parameter value from a StreamReader to a Byte[]."

I've looked into the command Parameters.Add arguments to see if there was anything I needed to adjust there. There are some variations to the Add method but I don't know\think they are relevant.

I don't know enough about the streaming calls to know if there is anything that should be tweaked there. Since I'm only changing the destination SQL column datatype I'm expecting the setup of the stream to remain the same.

I checked the WS data and there shouldn't be any data bigger than the SQL datatype VARBINARY can handle. I don't think the error has anything to do about the data size coming in though.

Any ideas on how to adjust this code to stream the WS data into the VARBINARY parameter and therefore the SQL column?

CodePudding user response：

StreamReader implements a TextReader, which gives you a string that will fail to convert to your varbinary byte array. I am guessing that your first example using nvarchar succeeds because you get an implicit conversion of the string.

You can likely refactor this to not use the StreamReader at all, but to answer the question you will need to convert that string to a byte array:

$output = $WebStreamRdr.ReadToEnd() # todo: flush & close 
$bytes = [system.Text.Encoding]::UTF8.GetBytes($output)

Then use $bytes:

$SQLCommand.Parameters.Add("@WSData", [Data.SQLDBType]::VARBINARY, -1).Value = $bytes

You could also let the string pass into the command as before:

$SQLCommand.Parameters.Add("@WSData", [Data.SQLDBType]::NVARCHAR, -1).Value = $WebStreamRdr

Then let the command convert it to varbinary:

$SQL_ImpInsert = @"
Insert Into [Database].dbo.ImportTable  (SeqNo, BulkDataBlob)
Values  (@SeqNo, cast(@WSData as varbinary(max)))
"@