如何将一个字节插入到一个SQL Server VARBINARY列中?

I have a byte array highlighted below, how do I insert it into a SQL Server database Varbinary column?

下面突出显示了一个字节数组，如何将它插入到SQL Server数据库Varbinary列中?

byte[] arraytoinsert = new byte[10]{0,1,2,3,4,5,6,7,8,9};

string sql = 
    string.format
    (
    "INSERT INTO mssqltable (varbinarycolumn) VALUES ({0});",WHATTODOHERE
    );

Thanks in advance guys!

提前谢谢伙计们!

5 个解决方案

#1

My solution would be to use a parameterised query, as the connectivity objects take care of formatting the data correctly (including ensuring the correct data-type, and escaping "dangerous" characters where applicable):

我的解决方案是使用参数化查询，因为连接性对象负责正确地格式化数据(包括确保正确的数据类型，以及在适用的情况下转义“危险”字符):

// Assuming "conn" is an open SqlConnection
using(SqlCommand cmd = new SqlCommand("INSERT INTO mssqltable(varbinarycolumn) VALUES (@binaryValue)", conn))
{
    // Replace 8000, below, with the correct size of the field
    cmd.Parameters.Add("@binaryValue", SqlDbType.VarBinary, 8000).Value = arraytoinsert;
    cmd.ExecuteNonQuery();
}

Edit: Added the wrapping "using" statement as suggested by John Saunders to correctly dispose of the SqlCommand after it is finished with

编辑:添加John Saunders建议的包装“using”语句，以在SqlCommand完成后正确地处理它

#2

Try this:

试试这个:

"0x" + BitConverter.ToString(arraytoinsert).Replace("-", "")

Although you should really be using a parameterised query rather than string concatenation of course...

虽然您确实应该使用参数化查询，而不是字符串连接……

#3

No problem if all the arrays you are about to use in this scenario are small like in your example.

如果您要在这个场景中使用的所有数组都很小，就没有问题了。

If you will use this for large blobs (e.g. storing large binary files many Mbs or even Gbs in size into a VARBINARY) then you'd probably be much better off using specific support in SQL Server for reading/writing subsections of such large blobs. Things like READTEXT and UPDATETEXT, or in current versions of SQL Server SUBSTRING.

如果您将它用于大型blobs(例如，将许多mb大小的大型二进制文件存储到VARBINARY中，甚至将Gbs存储到VARBINARY中)，那么您最好使用SQL Server中的特定支持来读取/写入此类大型blobs的子部分。比如READTEXT和UPDATETEXT，或者SQL Server子字符串的当前版本。

For more information and examples see either my 2006 article in .NET Magazine ("BLOB + Stream = BlobStream", in Dutch, with complete source code), or an English translation and generalization of this on CodeProject by Peter de Jonghe. Both of these are linked from my weblog.

有关更多信息和示例，请参阅我2006年在。net杂志上发表的文章(“BLOB + Stream = BlobStream”，在荷兰语中，有完整的源代码)，或者是Peter de Jonghe在CodeProject上对该文章的英文翻译和概括。这两者都与我的博客联系在一起。

#4

check this image link for all steps https://drive.google.com/open?id=0B0-Ll2y6vo_sQ29hYndnbGZVZms

检查此图像链接的所有步骤https://drive.google.com/open?id=0B0-Ll2y6vo_sQ29hYndnbGZVZms

STEP1: I created a field of type varbinary in table

步骤1:我在表中创建了一个varbinary类型字段

STEP2: I created a stored procedure to accept a parameter of type sql_variant

步骤2:我创建了一个存储过程来接受一个sql_variant类型的参数。

STEP3: In my front end asp.net page, I created a sql data source parameter of object type

步骤3:在我的前端asp.net页面中，我创建了一个对象类型的sql数据源参数

        <tr>
        <td>
            UPLOAD DOCUMENT</td>
        <td>
            <asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Button ID="btnUpload" runat="server" Text="Upload" />
            <asp:SqlDataSource ID="sqldsFileUploadConn" runat="server" 
                ConnectionString="<%$ ConnectionStrings: %>" 
                InsertCommand="ph_SaveDocument"     
               InsertCommandType="StoredProcedure">
                <InsertParameters>
                    <asp:Parameter Name="DocBinaryForm" Type="Object" />
                </InsertParameters>

             </asp:SqlDataSource>
        </td>
        <td>
            &nbsp;</td>
    </tr>

STEP 4: In my code behind, I try to upload the FileBytes from FileUpload Control via this stored procedure call using a sql data source control

步骤4:在后面的代码中，我尝试使用sql数据源控件通过这个存储过程调用从FileUpload控件上载文件字节

      Dim filebytes As Object
      filebytes = FileUpload1.FileBytes()
      sqldsFileUploadConn.InsertParameters("DocBinaryForm").DefaultValue = filebytes.ToString
      Dim uploadstatus As Int16 = sqldsFileUploadConn.Insert()

               ' ... code continues ... '

#5

You can do something like this, very simple and efficient solution: What i did was actually use a parameter instead of basic placeholder, created a SqlParameter object and used another existing execution method. For e.g in your scenario:

您可以这样做，非常简单和高效的解决方案:我实际上使用了一个参数而不是基本的占位符，创建了一个SqlParameter对象并使用了另一个现有的执行方法。为e。g在你的场景:

string sql = "INSERT INTO mssqltable (varbinarycolumn) VALUES (@img)";
SqlParameter param = new SqlParameter("img", arraytoinsert); //where img is your parameter name in the query
ExecuteStoreCommand(sql, param);

This should work like a charm, provided you have an open sql connection established.

如果您已经建立了一个开放的sql连接，那么这应该是一种魅力。

#1