SQL Convert unicode varbinary encoding to string

Question

Ok, I need 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 REG_BINARY for the Byte [] data type, for example:

RegistryKey rk = Registry.CurrentUser.OpenSubKey ...

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

then I will save it in 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 stored value in SQL Management Studio using:

 select oldregbinary from InvPstFile where PersonalFolderId = 73 

I get:

0x0000000038A1BB1005E5101AA1BB08002B2A56C200006D737073742E646C6C00000000004E495441F9BFB80100AA0037D96E0000000043003A005C00550073006500720073005C00630068007200690073007400690061006E002E006D007500670067006C0069005C0044006F00630075006D0065006E00740073005C004F00750074006C006F006F006B002000460069006C00650073005C006F006E0065006D006F0072006500730061006D0070006C0065002E007000730074000000

When I try to pass it to a string using CAST (AboveString as VARCHAR (max)), I don't get any result (displayed). The reason is Unicode encoding. When I replace / delete all 00, I get:

0x38A1BB15E5101AA1BB082B2A56C26D737073742E646C6C4E495441F9BFB801AA37D96E433A5C55736572735C63687269737469616E2E6D7567676C695C446F63756D656E74735C4F75746C6F6F6B2046696C65735C6F6E656D6F726573616D706C652E707374

when I pass this value to VARCHAR (MAX), I get: 8 ¡"å¡" + * VÂmspst.dllNITAù¿¸ª7nnC: \ Users \ MYREMOVEPROFILENAME \ Documents \ Outlook Files \ onemoresample.pst

So, any ideas on how to convert / distinguish a unicode encoded value to a string?

(I know that you can decode the above strings to find the replaced MYREMOVEPROFILENAME - this is not worth the effort;))

Answer 1

You must use the GetString() method for a specific Unicode Text.Encoding to convert it to a string. Drop the column in byte[] from the reader, and then convert it to a 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); }