How Do I Insert a Byte Into an SQL Server VARBINARY Column

Question

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

User · Answer

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 ... '

User · Answer

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

User · Answer

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

User · Answer

Try this    0x    BitConverter ToString arraytoinsert  Replace  -         Although you should really be using a parameterised query rather than string concatenation of course

User · Answer

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

[arrays] How Do I Insert a Byte[] Into an SQL Server VARBINARY Column

Examples related to arrays

Examples related to sql-server