如何通过PowerShell将Web服务数据流式传输到SQL Server VARBINARY列

原有PowerShell脚本可将Web服务的JSON数据流式写入SQL Server的NVARCHAR(max)列，但修改为写入VARBINARY(max)列时，执行ExecuteNonQuery抛出错误：

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

问题原因 #

VARBINARY参数要求传入字节流类型，但你使用的StreamReader是字符流处理对象，二者类型不兼容；同时流式写入VARBINARY列需要直接传递二进制流，而非经过字符编码转换的流对象。

修改方案 #

• 移除StreamReader，直接使用Web响应返回的原始二进制流
• 将二进制流包装为System.Data.SqlTypes.SqlBytes对象（专为SQL Server二进制类型设计，支持流式传输），作为VARBINARY参数的值
• 保持参数类型为[Data.SQLDBType]::VARBINARY，Size设为-1（对应max长度）

修改后完整代码 #

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

# 创建SQL连接与命令对象
$SQLConn = New-Object System.Data.SqlClient.SqlConnection $ConnStr
$SQLConn.Open() 

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

# 准备Web请求，获取原始二进制流
$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() # 保留原始二进制流，无需转字符流

# 绑定SQL参数
$SeqNo = 1

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

# 将二进制流包装为SqlBytes，赋值给VARBINARY参数
$sqlBytes = New-Object System.Data.SqlTypes.SqlBytes $WebStream
$SQLCommand.Parameters.Add("@WSData", [Data.SQLDBType]::VARBINARY, -1).Value = $sqlBytes

# 执行流式插入
$ExecOutput = $SQLCommand.ExecuteNonQuery();

# 关闭资源，避免泄漏
$WebResponse.Close()
$SQLConn.Close()

补充说明 #

• 使用SqlBytes而非直接传递Stream，是因为它是SQL Server二进制类型的标准适配对象，流式传输兼容性更好，且不会一次性将整个流加载到内存，符合原有方案的流式设计
• 务必确保在操作完成后关闭Web响应和SQL连接，避免资源泄漏

内容的提问来源于stack exchange，提问作者Gordon M