使用 Powershell 将图像插入到 VARBINARY(MAX) 类型的 SQL Server 列中 [英] Inserting image into SQL Server column of type VARBINARY(MAX) using Powershell

问题描述

我被卡住了.我正在尝试将一张图片从我的硬盘插入到 VARBINARY(MAX) 类型的 SQL Server 列中.我把它转换成某种东西，但我什至不确定它是什么.结果看起来像81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81"但更长.在我的更新命令中，如果我用 01234 替换 $file 更新没有任何问题，所以我几乎可以肯定这是将其转换为正确格式的问题.

I'm stuck. I'm trying to insert a picture from my hard drive to a SQL Server column of type VARBINARY(MAX). I have it converting to something, but I'm not even sure what it is. What comes out looks like "81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81" but much longer. In my update command if I replace $file with 01234 that updates without any problem so I'm almost sure it's a matter of converting it into the proper format whatever that may be.

$i = 1
$shape|foreach{
if ($shape.Item($i).name.Substring(0, 7) -eq 'Picture')
    {
        #write-host $shape.Item($i).name
        $shape.Item($i).copy()

        #write-host $firstChart.name
        $firstChart.paste()
        $firstChart.Export("c:\temp\pictures\image1.jpg", "JPG")
        #$firstChart.Delete

        [Byte[]]$file = get-content -Encoding Byte C:\TEMP\pictures\image1.jpg

        #$file = [convert]::ToBase64String((get-content C:\TEMP\pictures\image1.jpg -encoding byte))

        $cmd.CommandText ="UPDATE QuoteData SET PII_Image1 = $file Where QuoteNumber =  '"+$WorkSheet.Range('G7').Text.replace("'","''")+"'"
        $cmd.ExecuteNonQuery()
    }
    $i++
}

推荐答案

您的字节数组需要转换为十六进制表示.请注意添加了 $hexString 行并且更改了 $cmd.CommandText.

Your byte array needs converted into a hexadecimal representation. Please note the $hexString line was added and the $cmd.CommandText was changed.

    [Byte[]]$file = get-content -Encoding Byte C:\TEMP\pictures\image1.jpg
    $hexString = ($file|ForEach-Object ToString X2) -join ''
    $hexString = '0x'+$hexString 

    $cmd.CommandText ="UPDATE QuoteData SET PII_Image1 = $hexString Where QuoteNumber =  '"+$WorkSheet.Range('G7').Text.replace("'","''")+"'"

这篇关于使用 Powershell 将图像插入到 VARBINARY(MAX) 类型的 SQL Server 列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！