Problem in converting byte[]to stringBuilder

Problem in converting byte[]to stringBuilder

• Dlloyd

  Thanks for your reply.

  What Am I trying to do

  I have written  an smo app that does few things that help my daily programming life,such generate Stored procedures ,some scripting etcs.

  I have also added a functionality that when you right click on a table you can script the object Data and generate insert statements.This works fine till I have a field with binary data like the photo field in the Northwind.Employees table.Now reading your answer it says that it by using encoding it will bloat my data.

  Is there a way a can convert byte[ ] to a string and have the same output as if you would do "Select photo from Employees where EmployeeID=1"

  If I havent used some class other than my code is more lack of knowledge that anything else.

   

  Thanks for any feedback

   

  
  

• Amethyste

  Thanks a lot for your very good replies.

  I definetely do not want to waste lots of time and effort for something that is not that extremely important.I just wanted to add this functionality to my app as I found myself few times in situation where I had to script data from table.

  Thanks again for yor replies .Now everthing is more clear.

  
  

• Pikker1981

  Thanks for providing some context. That helps in envisioning what you're trying to accomplish. Basically you want to generate an INSERT statement in a SQL query that handles binary data. Unfortunately that's not possible, Base64 encoding or not. The reason is simple - your SQL text is exactly that - text. Converting binary data to text corrupts it. For instance, there are many non-printable characters in ASCII back from the days of terminals. Another example is text editors converting CR (carriage return) to CR-LR (CR-line feed). (Mac, Windows, and Unix all use different line endings. So many editors convert between them. If this is text, no big deal. If this is really binary data, you have bytes being added or removed from your data stream!)
  
  So you can't simply take the image data from Northwind and plunk it into a SQL query, with or without encoding. (If you encode it as Base64, you won't lose any data, but SQL Server will have no clue to decode the Base64 data back to binary when it executes the INSERT. So you'll still have corrupted data.) One solution would be to save the image data as physical files and use bcp in a batch script (LoadData.bat) to insert the data. You could also do it using .NET code and ADO.NET as another poster to this thread mentioned.
  
  The last option is that you could make SQL Server understand Base64 encoding and encode the data as a string. The advantage here is that your script would be self-contained without requiring image files from the file system. Base64 encoding only bloats the data by 33%. So unless you're loading thousands of records, I personally wouldn't be too concerned. This technique would only work in SQL Server 2005 and you would need to turn on the CLR on your SQL Server. You would write a Base64ToImage function in C# uasing the Convert class. Load up the assembly in SQL Server. Then wrap your Base64 encoded data in the method call.
  
  INSERT INTO Pictures
  (id, thumbnail, photo)
  VALUES (42, Base64ToImage('QUOTEDBASE64DATAHERE'), Base64ToImage('QUOTEDBASE64DATAHERE'))
  
  I've honestly never tried it. So there's probably something wrong in the syntax. It will only work in SQL Server 2005. I also might be missing something obvious which would cause this not to work. Still it would be interesting to give it a try.
  
  Last option... How often do you deal with image data in tables If not frequently, I would take the easy road out and just throw up a message saying, "Sorry, but image data is not supported in the current version." When you start dealing with lots of image data, then considering one of the implementation techniques above.
  
  Hope that helps.
  
  
  

• aimless

  I dont know what type the obj object is but I wrote up a small example

  [Serializable]

  public class Employee

  {

  string m_name;

  public string Name

  {

  get

  {

  return m_name;

  }

  set

  {

  m_name = value;

  }

  }

  }

  public class Test

  {

  static byte[] bytes;

  public static void Main()

  {

  Employee obj = new Employee();

  obj.Name = "Abcd";

  MemoryStream fs = new MemoryStream();

  BinaryFormatter formatter = new BinaryFormatter();

  try

  {

  formatter.Serialize(fs, obj);

  bytes = fs.ToArray();

  Encoding enc = Encoding.UTF8;

  string text = enc.GetString(bytes); //Notice that text returns a proper string

  StringBuilder b = new StringBuilder();

  b.Append(text);

  text = b.ToString();

  }

  catch (SerializationException ex)

  {

  }

  finally

  {

  fs.Close();

  }

  }

  Can you kindly see if can write to a text file using BinaryFormatter and see if the file has content

  }

  
  

• sgcook

  I dont see a problem with this bit of code-I get an array of bytes and am able to successfully convert it back to a string.

  byte[] bytes = File.ReadAllBytes("c:\\po.xml");//Change it to a valid file

  Encoding enc = Encoding.UTF8;

  string text = enc.GetString(bytes);

• GSGIMD

  Hi,

  I guess the basic need is comparing the photo stored in the database. First, the idea of comparing what "Select Photo From Employee Where EmployeeID = 1" with a String is totally wrong. The data returned by that query is Binary and NOT string (query analyzer shows the binary data only and thats why you see 0x3F45240E6...). And if you need to compare that value then it should be either binary or in Base64 string. Hence there are two ways to do that...

  1. Straightforward : Compare Binary with Binary

  Fire that SQL against the database and get the data loaded into a DataReader.GetValue (0) which returns an object. Cast that into a byte[] and compare that value with the "item" you get for comparison.

  2. Unnecessary: Compare Base64 string with Base64 string

  Somehow convert the stored photo into a Base64 string from within the SQL which i dont think is feasible since there are no readymade functions available. Moreover, if you are using SQL 2000 then there is a limit of 8000 chars limit on Varchar so there is not way of returning more than and you cant create variables of type "text". If you are able to get a base64 from database then its pretty easy to do DataReader.GetString(0) and compare it with Convert.ToBase64String(item) // item = that you get in method.

  HTH

  
  

• vinayshetty

   
  Hi,
  Thanks for you code.Yours works  and I made mine to work.
  what I am trying to do is script the Northwind.Employees table data which has a field called "Photo",
  to test if i can script images.I have posted only the relevant bits for clarity.
  Now I get the bytes but when compare the binary in the database with my ones it doesnt much and also the script will not run as a consequence.

  Bottom line
  I want to put in string the "photo" field=Byte[].
  Why Do I get different bytes the one in the db are like "0x151C2F00020000000D000E0 etc
        mine is "000000fffff etc and much longer
  

  Any suggestions

  Bcse I have found the code on the net I am not sure if the toString method is correct and I dont understand what X2 stands for.

  do you or anybody there

   

   

  
               case "Byte[]":
                          {
                             // StringBuilder sbTemp = HexEncoding.ObjectToStringBuilder(item);
                              byte[] bytes = ObjectToByteArray(item);
                              sbValues.Append(ToString(bytes));
                             break;
                          }             

  
   public static byte[] ObjectToByteArray(object obj)
          {
              MemoryStream fs = new MemoryStream();
              BinaryFormatter formatter = new BinaryFormatter();
              try
              {
                  formatter.Serialize(fs, obj);
                  return fs.ToArray();
              }
              catch (SerializationException ex)
              {

                  return null;
              }
              finally
              {
                  fs.Close();
              }
          }

   

   public static string ToString(byte[] bytes)
          {
              StringBuilder sb = new StringBuilder();
              for (int i = 0; i < bytes.Length; i++)
              {
                  sb.Append(bytes.ToString("X2"));   sb.Append(bytes.ToString("X2"));
  
              }
              return sb.ToString();
          }

  any Ideas

  thanks in advance to anybody who can help

  
  

• nyo2x

  Thanks for your reply.

  There is no need to use the bitconverter but I got lost on how to call that function "ConvertByteArrayToString"that i found in the snippet editor.Its expecting byte[] and I have an object called "Item".

  I put together as follows but I get empty string why

  
  case "Byte[]":
                          {
                              byte[] tmpByte = ObjectToByteArray(item);
                              string tmp = ConvertByteArrayToString(tmpByte);
                             sbValues.Append(tmp);

   

  
  private static byte[] ObjectToByteArray(object obj)
          {
              MemoryStream fs = new MemoryStream();
              BinaryFormatter formatter = new BinaryFormatter();
              try
              {
                  formatter.Serialize(fs, obj);
                  return fs.ToArray();
              }
              catch (SerializationException ex)
              {

                  return null;
              }
              finally
              {
                  fs.Close();
              }
          }
          private static string ConvertByteArrayToString(byte[] byteArray)
          {
              Encoding enc = Encoding.UTF8;
              string text = enc.GetString(byteArray);
              return text;
          }
      

  Thanks a lot 

   

   

   

  
  

• LCasselle

  I noticed that you are trying to generate SQL Insert statements to insert the data and want to insert data into Photo field which is of type Image. I think there is no way of inserting data into Image using simple Insert...Vaues statement. That is why batch files were supplied with MSSQL Server to update Photo into Northwind database. You will have to send in binary stream using the SqlCommand and adding a SqlParameter to Parameters and setting the type to SqlDbType.Image.

  HTH

  
  

• Linto Poulose E

  Could we take a step back for a moment What exactly are you trying to accomplish Serialization of an object as a string If that's the case, why not use the XmlSerializer If you simply need to serialize and you know that you're going to be reading it using .NET as well, then use the BinarySerializer. Is there a reason that the built-in serialization functionality in the .NET Framework is insufficient for your needs. (It sometimes is, but most often when you need to interop with a non-.NET implementation.)
  
  That said, the basic problem as I see it is that you're taking a photo (JPG, GIF, or other) as a raw byte stream, interpreting it as character data, and then trying to convert it back to a raw byte stream. This will not work. If you need to convert your binary data to text for whatever reason, you need to encode it using Base64 or a similar encoding. This encoding bloats your data by 33%. (Every 3 bytes of binary data is encoded as 4 ASCII characters.) If you're manipulating it in memory, all strings are treated as 16-bit Unicode. So you'll use 8 bytes to store 3 bytes of binary data. Not terribly efficient, but good for serialization formats (such as XML) that can only handle character data. The .NET Framework has built-in support for Base64. Take a look at Convert.ToBase64String and Convert.FromBase64String.
  
  http://msdn2.microsoft.com/en-us/library/system.convert.tobase64string.aspx
  http://msdn2.microsoft.com/en-us/library/system.convert.frombase64string.aspx
  
  Hope that helps.
  
  
  

• avner ben-zvi

  Encoding enc = Encoding.UTF8;
  enc.GetString(item);
  Am I missing something

  This code is dangerous. Not all sequences of bytes are valid UTF8.

  And, even if it were valid, I'm not sure that I'd want to try passing unusual characters in the Unicode 3.0 character set around as strings. (Unicode byte-order markers, for example).

  A much better solution for encoding arrays of byte as strings to use Base64 encoding, using these APIs:

  System.Convert.ToBase64CharArray

  System.Convert.FromBase64CharArray

  Is it efficient Not particularly, but it is, at least, guaranteed not to suffer from character-set encoding problems, and is marginally more efficient than split-hex coding.

  
  
• Problem in converting byte[]to stringBuilder