Save Binary Data to an Existing SQL Row

Question

I know how to insert binary data of a .zip file into a new SQL Server row into a column of datatype NVARCHAR(MAX).

Need help to update existing column of NVARCHAR(MAX) of row with binary data from a .zip file. Code below works but converting binary to file again renders an defect zip file:

byte[] file;

using (var stream = new FileStream(zipFile, FileMode.Open, FileAccess.Read))
{
    using (var reader = new BinaryReader(stream))
    {
        file = reader.ReadBytes((int)stream.Length);
    }
}

rowColumn = file;

While this works with INSERT with code:

cmd.Parameters.Add("@rowColumn", SqlDbType.VarBinary, file.Length).Value = file;

If you look at the the hex values of the first 100 bytes or so of the file before and after, how are they different? — Joel Coehoorn
– Joel Coehoorn, Commented Jan 7, 2023 at 20:23
If you want to store binary data - you should use a binary datatype!! Use VARBINARY(MAX) - not VARCHAR(MAX) (which is string-based - NOT binary!) — marc_s
– marc_s, Commented Jan 7, 2023 at 20:31
NVARCHAR(MAX) is type used in SQL Table, also SqlDbType.VarBinary is the working part of code, sorry if I miss something — SoftWar
– SoftWar, Commented Jan 7, 2023 at 20:35
Ooh, missed that. Putting binary data in character field is definitely gonna mess it up. @marc_s should list that as the answer. But if you can't change the column, the other option is to base-64 encode the bytes before saving them, and base-64 decode the bytes after retrieval. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 7, 2023 at 20:39
tried type change with no success, a piece of code for updating binary data or redirecting me to a similar question with accepted solution would be highly appreciated, thanks all the kind people here. — SoftWar
– SoftWar, Commented Jan 8, 2023 at 5:55

Joel Coehoorn · Accepted Answer · 2023-01-07 20:56:39Z

3

It sounds like there's a mistake with the column definition. If the database column is varchar(max), you cannot just write binary data there without it getting mangled. There are many different ways to encode character data, but a database column will have a specific format it wants to use so it can know how to do text comparisons and sorting. When load the binary data, it WILL re-encode the bytes to match the character encoding on the server.

You have two choices:

Change the column type.
Base-64 encode the bytes before saving them to column (and update the parameter code to match so it is varchar, instead of varbinary), and then base-64 decode the data again when you retrieve it.

edited Jan 7, 2023 at 20:56

answered Jan 7, 2023 at 20:50

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Martin Smith Over a year ago

Storing binary data in a string column is not to be recommended but it will round trip fine. There is no re-encoding if it is just inserted as binary and cast to binary when selected. But of course there is no point doing this as should just use the correct binary datatype

Martin Smith Over a year ago

dbfiddle.uk/Q1U7atme

Martin Smith Over a year ago

Only exception would be that storing an odd number of bytes in an nvarchar column will end up giving an extra 0x00 which might be their issue

SoftWar Over a year ago

@MartinSmith could you add also UPDATE of BLOB please in fiddle tool, thanks.

Collectives™ on Stack Overflow

Save Binary Data to an Existing SQL Row

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related