How to retrieve varbinary from a string data?

Question

I am trying to write an application wherein I have a table that I export to a tab-delimited text file. I have a column that is of type varbinary(max) in the table.

I convert it to varchar which just drops the 0x from the varbinary string and stores it in the file. I run this select query using sqlcmd on command line and store the result in a file.

SELECT AuthorizationId
   ,Name 
   ,CONVERT(VARCHAR(85), [ownersid], 2) AS ownersid
   ,[ownerSidWhereDefined]
   ,[AuthorizationType]
   ,ISNULL(CONVERT(NVARCHAR(10), [ValidFrom]), '') AS [ValidFrom]
   ,ISNULL(CONVERT(NVARCHAR(10), [ValidTo]), '') AS [ValidTo]
from Authorization

Sample row - 72 Administrator 01050000000000051500000026761E2FEB25792C07E53B2B24060000 4 1

After the export is done, I am writing a C# application that would read this file and perform a sqlbulkcopy to insert the data to a table in another database. At this point, the file contains the correct value that I expect it to be from the table.

This is the code I run to the same -

  public class AuthorizationsFmt
    {
        public string AuthorizationId;
        public string ownerSid;
        public byte[] ownerSidWhereDefined;
        public string Name;
        public string AuthorizationType;
        public string ValidFrom;
        public string ValidTo;       
     }
   IEnumerable<AuthorizationsFmt> FileData = from l in 
                   File.ReadLines(file)
                    let x = l.Split( '\t')                        
                    select new AuthorizationsFmt()
                    {
                        AuthorizationId = x.ElementAt(0),
                        Name = x.ElementAt(1),
                        ownerSid = Encoding.UTF8.GetBytes(x.ElementAt(2)),
                        ownerSidWhereDefined = x.ElementAt(3),
                        AuthorizationType = x.ElementAt(4),
                        ValidFrom = x.ElementAt(5),
                        ValidTo = x.ElementAt(6)
                    };
        return FileData;

After this, I convert the Ienumerable to a datatable, use sqlbulkcopy to insert data into a table. The issue here is that the conversion between the string to varbinary is not correct while doing the sqlbulkcopy.

The original value from the file now becomes '0x30783031303530303030303030303030303531353030303030303236373631453246454232353739324330374535334232423234303630303030' when I see in the database. Also, this value is the same for all the records in the table.

Answer 1

This is what I used to convert to byteArray and then insert into the database

  public static byte[] ByteArrayFromHexaString(string hex)
    {
        int NumberChars = hex.Length;
        byte[] bytes = new byte[NumberChars / 2];
        for (int i = 0; i < NumberChars; i += 2)
            bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
        return bytes;
    }