PowerShell插入Azure SQL报错：无法将PSObject转换为Byte[]

问题根源 #

你遇到的错误是因为直接将ConvertFrom-Json生成的PSObject赋值给了@JsonContent参数：SQL Server的.NET客户端无法将PSObject类型直接转换为nvarchar(max)对应的字符串类型，导致类型转换失败；而用$jsonContent.ToString()得到空字符串，是因为PowerShell自定义对象（PSObject）的默认ToString()方法只会返回空字符串或类型名称，不会自动序列化回JSON格式。

解决方案 #

有两种可靠方式获取可插入的JSON字符串：

方法1：直接读取JSON文件原始文本（推荐） #

跳过不必要的反序列化/序列化步骤，直接读取文件原始内容，既能保留JSON原始格式，又避免性能损耗：

Get-ChildItem -Path $jsonFilesPath -Filter *.json | ForEach-Object {
    # 读取文件完整原始文本
    $rawJson = Get-Content $_.FullName -Raw
    # 仅反序列化一次用于提取P1、P2属性
    $jsonObject = $rawJson | ConvertFrom-Json -Depth 100

    $Command.Parameters["@P1"].Value = $jsonObject.P1
    $Command.Parameters["@P2"].Value = $jsonObject.P2
    # 直接使用原始JSON文本赋值
    $Command.Parameters["@JsonContent"].Value = $rawJson

    $Command.ExecuteNonQuery()
    Write-Host "Inserted $($_.FullName) into $table"
}

方法2：将PSObject重新序列化为JSON #

如果必须先将JSON转为PSObject处理，可通过ConvertTo-Json重新序列化：

Get-ChildItem -Path $jsonFilesPath -Filter *.json | ForEach-Object {
    $jsonContent = Get-Content $_.FullName | ConvertFrom-Json -Depth 100
    # 重新序列化为JSON字符串，指定Depth避免嵌套层级截断
    $serializedJson = $jsonContent | ConvertTo-Json -Depth 100

    $Command.Parameters["@P1"].Value = $jsonContent.P1
    $Command.Parameters["@P2"].Value = $jsonContent.P2
    $Command.Parameters["@JsonContent"].Value = $serializedJson

    $Command.ExecuteNonQuery()
    Write-Host "Inserted $($_.FullName) into $table"
}

额外优化点 #

• 连接字符串避免换行，写成单行：

  $Connection.ConnectionString = "Server=$server;Database=$database;User ID=$username;Password=$password;Trusted_Connection=False;"
  

• 简化连接打开逻辑：

  if ($Connection.State -ne 'Open') {
      $Connection.Open()
  }
  

修改后完整代码（方法1） #

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Server=$server;Database=$database;User ID=$username;Password=$password;Trusted_Connection=False;"

if ($Connection.State -ne 'Open') {
    $Connection.Open()
}

$insertStatement = "INSERT INTO $table (P1, P2, JsonContent) VALUES (@P1, @P2, @JsonContent)"

$Command = New-Object System.Data.SqlClient.SqlCommand($insertStatement, $Connection)
$Command.Parameters.Add("@P1", [System.Data.SqlDbType]::VarChar, 250)
$Command.Parameters.Add("@P2", [System.Data.SqlDbType]::VarChar, 250)
$Command.Parameters.Add("@JsonContent", [System.Data.SqlDbType]::NVarChar, -1)

Get-ChildItem -Path $jsonFilesPath -Filter *.json | ForEach-Object {
    $rawJson = Get-Content $_.FullName -Raw
    $jsonObject = $rawJson | ConvertFrom-Json -Depth 100

    $Command.Parameters["@P1"].Value = $jsonObject.P1
    $Command.Parameters["@P2"].Value = $jsonObject.P2
    $Command.Parameters["@JsonContent"].Value = $rawJson

    $Command.ExecuteNonQuery()
    Write-Host "Inserted $($_.FullName) into $table"
}

$Connection.Close()

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