Hexadecimal data in SqlServer is implicitly converted to a bytearray in C#. How to keep data as it is?

Question

This is how our data is kept in the DB:

I'm getting the DB table in a datatable and looping through. The same data is implicitly converted to a byte array. This is from debug session:

I want the data as is. I will write it to a file. Currently I can access to bytearray with row.ItemArray[0]. Its value is {byte[70]}. There's no way to use index on row.ItemArray[0]. row.ItemArray[0][0] does not give you 86. I don't need 86 anyway. If I write row.ItemArray[0] to a file, it looks System.Byte[]

Your data type is binary, SSMS just shows it in hex in the query result window. — Crowcoder, Dec 27 '16 at 11:23
Yes, but what are you trying to write to file? Because byte array is already what you need to write to file. — Crowcoder, Dec 27 '16 at 11:30
I want the data in a text file to look the same how ssms shows us, if possible — Jude, Dec 27 '16 at 11:33
http://stackoverflow.com/questions/311165/how-do-you-convert-byte-array-to-hexadecimal-string-and-vice-versa — Crowcoder, Dec 27 '16 at 11:34

score 0 · Answer 1 · answered Dec 27 '16 at 12:00

0

Why are you converting binary data to a string. It will never work. Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;


namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.dat";
        static void Main(string[] args)
        {
            DataTable datatable = new DataTable(); //just for testing, use your current table
            FileStream stream = new FileStream(FILENAME, FileMode.Create);
            BinaryWriter writer = new BinaryWriter(stream, Encoding.UTF8);
            foreach (DataRow row in datatable.AsEnumerable())
            {
                writer.Write(row.Field<byte[]>(0));
            }
            writer.Flush();
            writer.Close();
        }

    }

}

answered Dec 27 '16 at 12:00

jdweng

33,250
2
15
20

OP states (in comments) that he wants data in file to look exactly like in SSMS, so - as a hex string. – Evk Dec 27 '16 at 12:04
It is not a hex string!!! it start with 0x......... The code I posted will not change data and will work with any data type. – jdweng Dec 27 '16 at 12:07
I mean in SSMS display it looks like hex string (with "0x", but that doesn't matter) and OP wants it to look exactly the same in a TEXT file (despite it being byte array in database). – Evk Dec 27 '16 at 12:13
The title says "keep it as it is". Changing from bytes to characters is not keeping it as is. – jdweng Dec 27 '16 at 13:34

Hexadecimal data in SqlServer is implicitly converted to a bytearray in C#. How to keep data as it is?

1 Answers1