CODEWITHSUNDEEP
c#sqlsql-serversql-server-2012
Kumee
Kumee

Reputation: 211

How to get the original binary value back to string

My SQL query returns the value as below 

Declare @ID varbinary(18)
Declare @newReturnID NvarCHAR(100) 
set @ID =  CONVERT(VARBINARY(18), CONVERT(VARCHAR(2), CONVERT(VARBINARY(2), 41)) +  CONVERT(VARCHAR(64), NEWID())) 
select  @ID ID

ID(Column name)

0x002936354446393642302D333936312D3436

I need to assign an variable as the above value i am trying to convert into an string but i get an different value ?

foreach (DataRow dr in dt.Rows)
{
    byte[] bytes = (byte[])dr["ID"];
    string strID = Convert.ToBase64String(bytes, 0, bytes.Length);
}

By my string strID holds different value, not as the above one?

Let me know where I am going wrong?

Upvotes: 2

Views: 3019

Answers (1)

par
par

Reputation: 1121

You are converting Bytes to Base64 string while 0x002.. is hex representation fo bytes.

If it's not compulsory to convert string in c# code, try select converting string in SQL select statement.

select convert(varchar(max), @ID, 1) as  ID

Refer this link (Binary Style) for detail for 3rd argument in Convert.

Edit:

Also, in c# you will need to convert byte array to hex string. here is answer already available for this to convert byte array to hex string. One function posting from that link.

public static string ByteArrayToString(byte[] ba)
{
  StringBuilder hex = new StringBuilder(ba.Length * 2);
  foreach (byte b in ba)
    hex.AppendFormat("{0:x2}", b);
  return hex.ToString();
}

Upvotes: 6

Related Questions