c# - Create insert statement to insert varbinary blob -

i want export data db , create insert-statement insert data db c#.

i made script working (and -type) when try use in 1 of tables wich has field named contentos of type varbinary(max) can't work.

now have select data, realised using method converts system.binary[] (wich getting when reading column) hex-string.

but unfortunately i'm failing when comes inserting data...

i'm trying this:

insert [my.cool.dba].[dbo].[documentos] ([id], [someid], [name], [contentos], [lastchange], [lastchangeid]) values ('693c9644-f2b5-4c74-a633-f4942fc1d8e5', 'bca2ac27-71e0-4641-a4b0-3c3e39916e71', 'whats that', [--> here goes content <--], '07.08.15 11:18:34:383', '693c9344-f8b5-4d74-a633-f4942fc1d8e5'); 

the content (for example):

'504b03041400000008091043e288e9e0d9ec7c1ceb6cf6a7e9290d5a4b04179e2a1b8fe350341542ea67dccd96dc5e682fd14e7a61e70000002a0300000000'

result: cannot implicitly convert type varchar varbinary(max). use convert-function

okay, let's it! content (for example):

convert(varbinary(max), '504b03041400000008091043e288e9e0d9ec7c1ceb6cf6a7e9290d5a4b04179e2a1b8fe350341542ea67dccd96dc5e682fd14e7a61e70000002a0300000000')

result:

error @ converting uniqueidentifier.

the schema this:

• id : uniqueidentifier
• someid: uniqueidentifier
• name: nvarchar(max)
• contentos: varbinary(max)
• lastchange: datetime
• lastchangeid: uniqueidentifier

no matter do, won't work.

so please know how to

1. properly read information reader ( i'm using: ) sb.append(string.format("'{0}'", bytearraytohexvialookup32((byte[])reader[j])));
2. properly create insert query wich insert data db :)

many in advance!!!

[edit]

here's thing worked me:

string.format("convert(varbinary(max), 0x{0})", bytearraytohexconverter.bytearraytohexvialookup32((byte[])value));

the bytearraytohexconverter has nothing special, converting bytearray hex (as name says :b) had let server convert hex string original byte[]

note: need able execute generated statements out of text file. if don't have requirements, suggest take @ answer thomas levesque

you should use parameterized query:

insert [my.cool.dba].[dbo].[documentos] ([id], [someid], [name], [contentos], [lastchange], [lastchangeid]) values (@id, @someid, @name, @contentos, @lastchange, @lastchangeid);

you should use parameterized query:

insert [my.cool.dba].[dbo].[documentos] ([id], [someid], [name], [contentos], [lastchange], [lastchangeid]) values (@id, @someid, @name, @contentos, @lastchange, @lastchangeid); 

add parameters sqlcommand using add or addwithvalue method on command.parameters.

using parameterized query has multiple advantages:

• security: protects against sql injection attacks
• performance: since text of query same, sql server can cache execution plan
• reliability: don't need worry how values (numbers, dates...) should formatted, handled automatically. there no risk of incorrect formatting due different culture, instance.

---

the format blob literals in sql server 0x<hex-value> (no quotes). in case 0x504b03041400000008091043e288e9e0d9ec7c1ceb6cf6a7e9290d5a4b04179e2a1b8fe350341542ea67dccd96dc5e682fd14e7a61e70000002a0300000000.