views:

1944

answers:

4

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.

+1  A: 

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.

Lasse V. Karlsen
+1  A: 

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();
     }
    }
}
CptSkippy
What is the use of the strig 'S'?
Ahmad Farid
@Ahmad : In the example a DataTable is serialized into a byte[] then encoded as a UTF8 string then back to byte[] then DataTable. Since you're storing the bytes in the DB, it's irrelevant but if you wanted to store it as ntext or insert it into XML this would be how you'd do it.
CptSkippy
A: 

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.

Daniel Brückner
A: 

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

Michael Todd