Insert Bytes array INTO varbinary(max) record

Question

I want to INSERT INTO a table in a record varbinary(max) a Byte array How can I do that?

Answer 1

Using a stored procedure, just create a parameter of type varbinary(max) and insert it into the table as you would any data type.

In your c# (or vb or whatever) code, add a parameter to your sql command object and set the byte array as the parameter value:

command.Parameters.AddWithValue("@parameter_name", myByteArray);

If not using a stored procedure, you can probably do the same with a parameterized sql statement, but I have never tried that, so I can't give an example.

Edit:

You are using a parameterized query, which is not my thing, so I can't guarantee this will work. But, here is some code that should get you going.

RemoteSQLcmd = New SqlCommand("INSERT INTO Table(1) Values (newid(), ProductID, @bin_value", RemoteSQLConn)
RemoteSQLcmd.Parameters.AddWithValue(@bin_value", imSource) ;

The binary value must be represented as a parameter (@bin_value), and the value is set by the AddWithValue statement. The parameter name does not have to match the column name.

A couple of notes: I would suggest using columns names in your insert statement rather than depending on column position, Also, I don't know what you mean by 'table(1)' - is that actually the name of the table?

Answer 2

Assuming:

CREATE TABLE [dbo].[varb_table] (
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [BinaryData] [varbinary](max) NULL,
  CONSTRAINT [PK_varb_table] PRIMARY KEY CLUSTERED 
)

Use:

INSERT INTO varb_table 
  (ID, BinaryData)
VALUES 
  (NULL, 0x);

DECLARE @pk
SET @pk = @@IDENTITY;

UPDATE varb_table
   SET BinaryData.Write (@your_byte_array, 0, DATALENGTH(BinaryData))
 WHERE ID = @pk