How Do I Insert A Byte[] Into an SQL Server VARBIN

问题:

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

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!

回答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

回答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.

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.

回答4:

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:

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.

回答5:

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

STEP1: I created a field of type varbinary in table

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

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

        <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

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

               ' ... code continues ... '