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());
  } 
}

Answer 1

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...

Answer 2

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());
    } 
}