Convert string to System.Data.SqlTypes.SqlBytes C#

Question

I have String variable like a="0xECBDE9721C47B", I want to convert this part in a new variable of type System.Data.SqlTypes.SqlBytes. What I need there is to decompress the value in that string.

For decompress I want to use this function:

/// Decompressing the data
/// </summary>
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true,
    DataAccess = DataAccessKind.None)]
public static SqlBytes BinaryDecompress(SqlBytes input)
{
  if (input.IsNull)
    return SqlBytes.Null;

  int batchSize = 32768;
  byte[] buf = new byte[batchSize];

  using (MemoryStream result = new MemoryStream())
  {
    using (DeflateStream deflateStream = 
      new DeflateStream(input.Stream, CompressionMode.Decompress, true))
    {
       int bytesRead;
       while ((bytesRead = deflateStream.Read(buf, 0, batchSize)) > 0)
         result.Write(buf, 0, bytesRead);
    }
    return new SqlBytes(result.ToArray());
  } 
}

Adam Calvet Bohl · Answer 1 · 2016-04-12T11:36:50.293

Maybe you can try:

public static byte[] GetBinaryFromHexaString (string hexa)
{
    byte[] data = null;
    List<byte> bList = new List<byte>();
    try {
        for (int i = 2; i < hexa.Length - 1; i+=2) {
            string hStr = hexa.Substring(i, 2);
            byte b = byte.Parse(hStr, NumberStyles.HexNumber, CultureInfo.InvariantCulture);
            bList.Add (b);
        }
        data = bList.ToArray();
    }
    catch {}
    return data;
}

var sqlBytes = new System.Data.SqlTypes.SqlBytes (GetBinaryFromHexaString(a));

where a is your input string, starting with 0x.
After that you can:

var decompressed = BinaryDecompress(sqlBytes);

Edit:
Try this one:

public static SqlBytes BinaryDecompress(SqlBytes input)
{
  if (input.IsNull)
    return SqlBytes.Null;

    var outputStream = new MemoryStream();
    using (MemoryStream result = new MemoryStream())
    {
        using (DeflateStream deflateStream =  new DeflateStream(input.Stream, CompressionMode.Decompress))
        {
            deflateStream.CopyTo(outputStream);
        }
    } 

    outputStream.Position = 0;
    return new SqlBytes (outputStream);

}

Edit 2:
I've tried this with your BinaryCompress & BinaryDecompress and is working for me.
My test code:

    /// <summary>
    /// Compressing the data
    /// </summary>
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None)]
    public static SqlBytes BinaryCompress(SqlBytes input)
    {
        if (input.IsNull) return SqlBytes.Null;

        using (MemoryStream result = new MemoryStream())
        {
            using (DeflateStream deflateStream =
                   new DeflateStream(result, CompressionMode.Compress, true))
            {
                deflateStream.Write(input.Buffer, 0, input.Buffer.Length);
                deflateStream.Flush();
                deflateStream.Close();
            }
            return new SqlBytes(result.ToArray());
        }
    }

    /// <summary>
    /// Decompressing the data
    /// </summary>
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None)]
    public static SqlBytes BinaryDecompress(SqlBytes input)
    {
        if (input.IsNull) return SqlBytes.Null;

        int batchSize = 32768;
        byte[] buf = new byte[batchSize];

        using (MemoryStream result = new MemoryStream())
        {
            using (DeflateStream deflateStream =
                   new DeflateStream(input.Stream, CompressionMode.Decompress, true))
            {
                int bytesRead;
                while ((bytesRead = deflateStream.Read(buf, 0, batchSize)) > 0)
                    result.Write(buf, 0, bytesRead);
            }
            return new SqlBytes(result.ToArray());
        }
    }


    public static string GetHexaStringFromBinary (byte[] data)
    {
        string hexData = "0x";
        for (int i = 0; i < data.Length; i++) {
            hexData = string.Concat (hexData, data[i].ToString("X2"));
        }
        return hexData;
    }


    public static byte[] GetBinaryFromHexaString (string hexa)
    {
        byte[] data = null;
        List<byte> bList = new List<byte>();
        try {
            for (int i = 2; i < hexa.Length - 1; i+=2) {
                string hStr = hexa.Substring(i, 2);
                byte b = byte.Parse(hStr, NumberStyles.HexNumber, CultureInfo.InvariantCulture);
                bList.Add (b);
            }
            data = bList.ToArray();
        }
        catch {}
        return data;
    }

And the usage:

string originalStr = "This is a test string!!";
byte[] data = Encoding.ASCII.GetBytes (originalStr);
SqlBytes sbCompressed = BinaryCompress (new SqlBytes (data));

string a = GetHexaStringFromBinary (sbCompressed.Value);
//a = 0x0BC9C82C5600A2448592D4E21285E292A2CCBC74454500

var sqlBytes = new SqlBytes(GetBinaryFromHexaString (a));
SqlBytes deCompressed = BinaryDecompress (sqlBytes);
string finalStr = Encoding.ASCII.GetString (deCompressed.Value);
//finalStr = "This is a test string!!"

Hope it helps...

i have mistake on this line (var sqlBytes = System.Data.SqlTypes.SqlBytes (GetBinaryFromHexaString(a));), write that System.Data.SqlTypes.SqlBytes is a 'type', which is not valid in the given context — VasyPupkin, Apr 12 '16 at 09:14
Sorry! My fault... I've edited the post. Try with: `var sqlBytes = new System.Data.SqlTypes.SqlBytes (GetBinaryFromHexaString(a));` — Adam Calvet Bohl, Apr 12 '16 at 09:32
i add new in line? where was mistake? byt another apeare,like that in this line while ((bytesRead = deflateStream.Read(buf, 0, batchSize)) > 0), it is about InvalidDataException was unhandled, can your help — VasyPupkin, Apr 12 '16 at 09:40
Yes, you shoud add the 'new' keyword. The exception says your data is in an invalid format. Check the data by compressing some known data first and then try to decompress it. — Adam Calvet Bohl, Apr 12 '16 at 10:03
in a i put a='CF86C1852191A10488ED180C198C196C18F4914400' REAL OL THIS like this XML '13', but how i can see that it is XML like i want, when i write var decompressed = BinaryDecompress(sqlBytes); then write System.Console.WriteLine(Decompressed) i can t see anything it print SystemData.SqlTypes.SqlBytes, can your help twice me? — VasyPupkin, Apr 12 '16 at 10:15
an error which I described earlier still is presented (InvalidDataException was unhandled) — VasyPupkin, Apr 12 '16 at 10:18
Can you show also the code you are using to compress the data? By the awy, be sure to add '0x' at the beginning of the string! — Adam Calvet Bohl, Apr 12 '16 at 10:21
i put the code like with 0x at begining al it s work, but how i can convert var decompressed = BinaryDecompress(sqlBytes) to xml, i dont understand, when i System.Console.WriteLine(decompressed) it is print on console like (System.Data.SqlTypes.SqlBytes) — VasyPupkin, Apr 12 '16 at 10:46

score 0 · Answer 2 · answered Apr 12 '16 at 10:33

Code ALL

    /// <summary>
    /// Compressing the data
    /// </summary>
     [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true,
        DataAccess = DataAccessKind.None)]
    public static SqlBytes BinaryCompress(SqlBytes input)
    {
       if (input.IsNull)
        return SqlBytes.Null;

    using (MemoryStream result = new MemoryStream())
    {
        using (DeflateStream deflateStream = 
            new DeflateStream(result, CompressionMode.Compress, true))
        {
            deflateStream.Write(input.Buffer, 0, input.Buffer.Length);
            deflateStream.Flush();
            deflateStream.Close();
        }
        return new SqlBytes(result.ToArray());
    } 
    }

/// <summary>
/// Decompressing the data
/// </summary>
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true,
        DataAccess = DataAccessKind.None)]
public static SqlBytes BinaryDecompress(SqlBytes input)
{
    if (input.IsNull)
        return SqlBytes.Null;

    int batchSize = 32768;
    byte[] buf = new byte[batchSize];

    using (MemoryStream result = new MemoryStream())
    {
        using (DeflateStream deflateStream = 
            new DeflateStream(input.Stream, CompressionMode.Decompress, true))
        {
            int bytesRead;
            while ((bytesRead = deflateStream.Read(buf, 0, batchSize)) > 0)
                result.Write(buf, 0, bytesRead);
        }
        return new SqlBytes(result.ToArray());
    } 
}

Convert string to System.Data.SqlTypes.SqlBytes C#

2 Answers2

Linked