How to save a table(DataTable) into a single cell in a database?

Question

Is it possible to save a DataTable into SQL database in one cell of type binary for example and read it back again into a DataTable?

Answer 1

why on earth would you want to?

If this is an operation you are going to do more than once, just save it out to a new sql table and read from the table into your DataTable later.

Answer 2

I would create, if possible, a xml field inside the sql database and save the datatable as xml

XML Support in Microsoft SQL Server 2005

and

C# and Vb.net example for XML data type tips in SQL Server 2005

should help you

another example took from here

   protected bool LoadXml(SqlConnection cn, XmlDocument doc)
   {
    //Reading the xml from the database
    string sql =  @"SELECT Id, XmlField  FROM TABLE_WITH_XML_FIELD WHERE Id = @Id";
    SqlCommand cm = new SqlCommand(sql, cn);
    cm.Parameters.Add(new SqlParameter("@Id",1));
    using (SqlDataReader dr = cm.ExecuteReader())
    {
             if (dr.Read())
            {
                      SqlXml MyXml= dr.GetSqlXml(dr.GetOrdinal("XmlField"));
                      doc.LoadXml( MyXml.Value);
                      return true;
            }
            else
            {
                      return false;
            }
     }
    }

Answer 3

It just breaks normalization rules- the value in the cell is not atomic.

I break that rule myself all the time, but still, it's important to understand alternative approaches.

You could have a related table to store their answers instead of storing all their answer values in a single cell.

Regarding your comment below, you can still do it with a related table. Just use a three-column table: tableID, fieldID, value. Each tableID can have its own set of fieldIDs. The trade-off is with the value datatype- it needs to be a string, which means you don't get the advantages of date or numeric data type enforcement on your back end.