How to insert byte [] in a VARBINARY SQL Server column

Question

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

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 ); 

Answer 1

My solution would be to use a parameterized query, since the connection objects correctly format the data (including providing the correct data type and escaping "dangerous" characters, if 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 "using" statement for wrapping suggested by John Sanders to properly dispose of SqlCommand after it has finished using

Answer 2

Try the following:

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

Although you really should use a parameterized query rather than string concatenation, of course ...

Answer 3

There is no problem if all the arrays that you are going to use in this scenario are small, as in your example.

If you use this for large blocks (for example, for storing large binary files that have many Mbs or even Gbs of VARBINARY size), you probably would be much better off using specific SQL Server support to read / write subsections of such large drops. Things like READTEXT and UPDATETEXT , or in current versions of SQL Server SUBSTRING .

For more information and examples, see my 2006 .NET article ( "BLOB + Stream = BlobStream" in Dutch with full source code), or a translation into English and a generalization of this to CodeProject by Peter de Jonge. Both are linked to my weblog .

Answer 4

You can do something like this, a very simple and effective solution: I really used a parameter instead of the main placeholder, created an SqlParameter object and used another existing execution method. For example, for example, in your script:

 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 if you have an open sql connection.

Answer 5

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

STEP1: I created a field of type varbinary in a table

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

STEP3: On my asp.net page at my end, I created an sql data source parameter of an 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 am trying to load FileBytes from FileUpload Control through this stored procedure call using the 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 ... '