Convert a byte[] array into DataTable

Question

I saved an object of type DataTable into SQL 2005 database in a field of type varbinary. I want to retrieve it back but I wasn't able to type cast it. This is how i saved it.

MemoryStream memStream = new MemoryStream();
    StreamWriter sw = new StreamWriter(memStream);

sw.Write(dt);
con.Open();
using (SqlCommand cmd = new SqlCommand("INSERT INTO Tables(TableName, TableData, QuestionID) VALUES (@TableName, @TableData, @QuestionID)", con))
{
    cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = "a new table";
    cmd.Parameters.Add("@TableData", SqlDbType.VarBinary,Int32.MaxValue).Value = memStream.GetBuffer();
    cmd.Parameters.Add("@QuestionID", SqlDbType.VarChar).Value = "2";
    cmd.ExecuteNonQuery();

}

The 'dt' is the DataTable object instance.

Answer 1

I'm afraid I'll have to disappoint you, at least until you tell us that you've created an extension method for the StreamWriter class that handles data tables.

The only overload of the Write method that accepts a DataTable instance is the one that takes an object, and per the MSDN documentation, it only saves the "text representation" of the object.

So, let's hope that the .ToString method of DataTable outputs a string in a format that contains all the contents of the DataTable instance, but alas. The .ToString method only returns the contents of the TableName property, and a display expression, if one is present.

So what you've saved is not the contents of the DataTable instance all, only the name of the table.

You should look into Serialization, it should be able to produce a binary or XML-representation of all the contents of the DataTable object.

Answer 2

What your talking about is Binary Serialization and Deserialization. Maybe this will help.

using System;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data;
using System.Text;

namespace Serial
{
    public class Ser
    {
     public static byte[] StrToByteArray(string str)
     {
      UTF8Encoding  encoding = new UTF8Encoding ();
      return encoding.GetBytes(str);
     }

     public static string ByteArrayToStr(byte[] barr)
     {
      UTF8Encoding  encoding = new UTF8Encoding ();
      return encoding.GetString(barr, 0, barr.Length);
     }

     public static void Main(String[] args)
     {
      DataTable dt = new DataTable();
      DataRow dr;

      dt.Columns.Add(new DataColumn("IntegerValue", typeof(Int32)));
      dt.Columns.Add(new DataColumn("StringValue", typeof(string)));
      dt.Columns.Add(new DataColumn("DateTimeValue", typeof(DateTime)));
      dt.Columns.Add(new DataColumn("BooleanValue", typeof(bool)));

      for (int i = 1; i <= 1; i++) 
      {

       dr = dt.NewRow();

       dr[0] = i;
       dr[1] = "Item " + i.ToString();
       dr[2] = DateTime.Now;
       dr[3] = (i % 2 != 0) ? true : false;

       dt.Rows.Add(dr);
      }

      //Serialize
      BinaryFormatter bformatter = new BinaryFormatter();
      MemoryStream  stream = new MemoryStream();

      string s;
      bformatter.Serialize(stream, dt);
      byte[] b = stream.ToArray();
      s = ByteArrayToStr(b);
      stream.Close();
      dt = null;

      //Now deserialise
      bformatter = new BinaryFormatter();
      byte[] d;
      d = StrToByteArray(s);
      stream = new MemoryStream(d);
      dt = (DataTable)bformatter.Deserialize(stream);
      stream.Close();
     }
    }
}

Answer 3

I assume your code just serialized the data table and you have to deserialize it. I don't know what formatter was used, so you will have to look at the content of you binary field. If it is binary, use the BinaryFormater (see the example code down the page). If it is not binary, try SoapFormatter and XmlSerializer.

Answer 4

You might be better off using XmlSerializer or BinaryFormatter to serialize your DataTable and then store in a binary column.

Links to do that:
http://sadeveloper.net/forums/p/439/1772.aspx
http://bytes.com/topic/net/answers/428472-serializing-datatable