CODEWITHSUNDEEP
c#sql-serverblobsql-insertvarbinary
probird
probird

Reputation: 169

Create insert statement to insert varbinary blob

I want to export some data from my DB and create an Insert-statement to insert those data into another DB with C#.

I made the script for working for nearly everything (and -type) but when I try to use it in one of my tables wich has a field named Contentos which is of type VARBINARY(max) I can't get it to work.

Now I have to select that data, which I realised by using a method which converts a System.Binary[] (wich I am getting when reading the column) to a hex-string.

But unfortunately I'm failing when it comes to inserting that data...

I'm trying something like this:

INSERT INTO [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 the content <--], '07.08.15 11:18:34:383', '693c9344-f8b5-4d74-a633-f4942fc1d8e5');

The content (for example):

'504B03041400000008091043E288E9E0D9EC7C1CEB6CF6A7E9290D5A4B04179E2A1B8FE350341542EA67DCCD96DC5E682FD14E7A61E70000002A0300000000'

Result: Cannot implicitly convert type varchar to varbinary(max). Use the CONVERT-function

Okay, let's do it! The content (for example):

CONVERT(varbinary(max), '504B03041400000008091043E288E9E0D9EC7C1CEB6CF6A7E9290D5A4B04179E2A1B8FE350341542EA67DCCD96DC5E682FD14E7A61E70000002A0300000000')

Result:

Error at converting to uniqueidentifier.

The schema is like this:

No matter what I do, this won't work.

So I please would like to know how to

  1. properly read the information from the reader ( I'm currently using: ) sb.Append(String.Format("'{0}'", ByteArrayToHexViaLookup32((Byte[])reader[j])));
  2. properly create an insert query wich will insert the data into the db :)

Many thanks in advance!!!

[EDIT]

Here's the thing that finally worked for me:

string.Format("CONVERT(VARBINARY(max), 0x{0})", ByteArrayToHexConverter.ByteArrayToHexViaLookup32((byte[])value));

The ByteArrayToHexConverter has nothing special, just converting a Bytearray to Hex (as the name says it :b) Also I had to let the server convert it back from the hex string to the original byte[]

Note: I need this to be able to execute generated statements out of a text file. So if you don't have this requirements, I suggest you take a look at the answer from Thomas Levesque

You should use a parameterized query:

INSERT INTO [My.Cool.DBA].[dbo].[Documentos] ([Id], [SomeID], [Name], [Contentos], [LastChange], [LastChangeId]) VALUES (@Id, @SomeId, @Name, @Contentos, @LastChange, @LastChangeId);

Upvotes: 1

Views: 3759

Answers (1)

Thomas Levesque
Thomas Levesque

Reputation: 292405

You should use a parameterized query:

INSERT INTO [My.Cool.DBA].[dbo].[Documentos] ([Id], [SomeID], [Name], [Contentos], [LastChange], [LastChangeId])
VALUES (@Id, @SomeId, @Name, @Contentos, @LastChange, @LastChangeId);

Add the parameters to the SqlCommand using the Add or AddWithValue method on command.Parameters.

Using a parameterized query has multiple advantages:

  • security: this protects against SQL injection attacks
  • performance: since the text of the query is always the same, SQL Server can cache the execution plan
  • reliability: you don't need to worry about how the values (numbers, dates...) should be formatted, this is handled automatically. There is no risk of incorrect formatting due to a different culture, for instance.

The format for BLOB literals in SQL Server is 0x<hex-value> (no quotes). So in your case it will be 0x504B03041400000008091043E288E9E0D9EC7C1CEB6CF6A7E9290D5A4B04179E2A1B8FE350341542EA67DCCD96DC5E682FD14E7A61E70000002A0300000000.

Upvotes: 1

Related Questions