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

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

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

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           lt tr gt           lt td gt              UPLOAD DOCUMENT lt  td gt           lt td gt               lt asp FileUpload ID  FileUpload1  runat  server    gt               lt asp Button ID  btnUpload  runat  server  Text  Upload    gt               lt asp SqlDataSource ID  sqldsFileUploadConn  runat  server                   ConnectionString   lt    ConnectionStrings    gt                    InsertCommand  ph SaveDocument                      InsertCommandType  StoredProcedure  gt                   lt InsertParameters gt                       lt asp Parameter Name  DocBinaryForm  Type  Object    gt                   lt  InsertParameters gt                 lt  asp SqlDataSource gt           lt  td gt           lt td gt               amp nbsp  lt  td gt       lt  tr gt    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

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