SQL Convert unicode encoded varbinary to string

Question

Ok, i have to export REG_BINARY values and store them in SQL and later i need to convert these values to a string..

What i have:

in c# i read the REG_BINARY to a Byte[] data type like:

RegistryKey rk = Registry.CurrentUser.OpenSubKey...

byte[] value = rk.GetValue("TheKey")

then i store it to a SQL VarBinary Field using:

        private void SqlStorePstFileInfo(List<PersonalFolderObject> listOfPstObjects, int? userComputerId)
    {
        using (var sqlConnection = new SqlConnection(GetConnectionString()))
        {
            sqlConnection.Open();
            foreach (PersonalFolderObject pfo in listOfPstObjects)
            {
                string s = "INSERT INTO [InvPstFile] (UserComputerId, Name, Path, Size, OldRegBinary) VALUES(@UserComputerId, @Name, @Path, @Size, @OldRegBinary)";
                using (SqlCommand sqlCmd = new SqlCommand(s, sqlConnection))
                {
                    SqlParameter pUserComputerId = sqlCmd.Parameters.Add("@UserComputerId", SqlDbType.Int);
                    pUserComputerId.Value = userComputerId;
                    SqlParameter pName = sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar);
                    pName.Value = pfo.Name;
                    SqlParameter pPath  = sqlCmd.Parameters.Add("@Path", SqlDbType.NVarChar);
                    pPath.Value = pfo.OldPath;
                    SqlParameter pSize = sqlCmd.Parameters.Add("@Size", SqlDbType.Int);
                    pSize.Value = pfo.Size;
                    SqlParameter pOldRegBinary = sqlCmd.Parameters.Add("@OldRegBinary", SqlDbType.VarBinary);
                    pOldRegBinary.Value = pfo.OldRegBinary;
                    sqlCmd.ExecuteNonQuery();
                }

                //string s = string.Format("INSERT INTO [InvPstFile] (UserComputerId, Name, Path, Size) " +
                //                         "VALUES ('{0}','{1}','{2}','{3}')",
                //                         userComputerId, pfo.Name, pfo.OldPath, pfo.Size);

                //Helpers.Logging.Log.Info(string.Format("SQL: {0}", s));
                //var sqlCmd = new SqlCommand(s, sqlConnection);
                //sqlCmd.ExecuteNonQuery();
            }
            sqlConnection.Close();
        }
    }

when i read the the stored value in SQL Management Studio using:

select oldregbinary from InvPstFile where PersonalFolderId = 73 

i get:

0x0000000038A1BB1005E5101AA1BB08002B2A56C200006D737073742E646C6C00000000004E495441F9BFB80100AA0037D96E0000000043003A005C00550073006500720073005C00630068007200690073007400690061006E002E006D007500670067006C0069005C0044006F00630075006D0065006E00740073005C004F00750074006C006F006F006B002000460069006C00650073005C006F006E0065006D006F0072006500730061006D0070006C0065002E007000730074000000

When i try to cast it to a string using CAST(AboveString as VARCHAR(max)) i get no result (displayed). The reason is the unicode encoding. When i replace/remove all 00 i get:

0x38A1BB15E5101AA1BB082B2A56C26D737073742E646C6C4E495441F9BFB801AA37D96E433A5C55736572735C63687269737469616E2E6D7567676C695C446F63756D656E74735C4F75746C6F6F6B2046696C65735C6F6E656D6F726573616D706C652E707374

when i cast that value to VARCHAR(MAX) i get: 8¡»å¡»+*VÂmspst.dllNITAù¿¸ª7ÙnC:\Users\MYREMOVEPROFILENAME\Documents\Outlook Files\onemoresample.pst

so, any ideas how to convert/cast the unicode encoded value to a string?

(i know you can decode above string to find the replaced MYREMOVEPROFILENAME - it's not worth the effort ;))

Answer 1

You should use GetString() method on specific unicode Text.Encoding to convert it back to string. Cast the column to byte[] from reader and then convert that to string using GetString().

string value;
using (SqlCommand command = new SqlCommand("select * from InvPstFile", sqlConnection))
{
    SqlDataReader sdr = command.ExecuteReader();
    //Assuming we are just reading 1 row here
    sdr.Read();
    var bytes = (byte[])sdr["OldRegBinary"];

    // Based on the original unicode format one of the following should 
    // give you the string value
    value = System.Text.Encoding.UTF8.GetString(bytes);
    value = System.Text.Encoding.Unicode.GetString(bytes);
    value = System.Text.Encoding.Default.GetString(bytes);
}