>Hey, Bonnie, you like datasets ...
>
>Playing with a "wide and shallow" concept for child data.
>
>In my proof-of-concept app I want to stored a dataset ( untyped - schema created in prop sheet - bound to datagridviews on form ) to a strongly typed property of my business object which will then be stored as part of the record in a SQL 2008 table.
>
>Should I use varchar(max) or varbinary(max) to store this object ? Do I want to write it out as xml or just bitstream it out as a byte() array?
>
>In this first iteration I am looking for the simplest, cleanest way to get the dataset into the prop and back out again. I almost had it working, I thought, using these functions to serialize and deserialize to xml varchar(max) but for some reason the last row of the grid doesn't properly write changes in the xml. Quirky. Perhaps it is something else I"m doing wrong on that one so if you say this looks good to you I may bang on it some more
>
>http://codebetter.com/blogs/jay.kimble/archive/2004/03/30/10383.aspx
>
>Anyway, suggestions - and especially code (vb or c#) very welcome.
>
>Dataset to SQL column via strongly typed prop
>
>TIA
>
>Cetin - ne var ne yok ?

Thanks I am fine:)

I would use varbinary(max). Here is a sample (sample assumes that you have created a windows form with a DataGridView and 2 buttons on it + you have created NorthwindDataSet with Customers,Orders and Order Details tables in it- an untyped set would do too):

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private DataSet LoadDataset()
        {
            DataSet ds = null;
            SqlConnection con = 
               new SqlConnection(@"server=.\SQL2008;Trusted_connection=yes;Database=test");
            SqlCommand cmd = new SqlCommand("select * from DSStore", con);
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read())
            {
                byte[] myDs = (byte[])rdr["DataSet"];
                MemoryStream ms = new MemoryStream(myDs);
                BinaryFormatter bf = new BinaryFormatter();
                ds = (DataSet)bf.Deserialize(ms);
            }
            con.Close();
            return ds;
        }

        private void SaveDataset()
        {
            byte[] ds = SerializeDS( GetDataSet() );
            SqlConnection con = 
              new SqlConnection(@"server=.\SQL2008;Trusted_connection=yes;Database=test");
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into DSStore ([DataSet]) values (@ds)",con);
            cmd.Parameters.AddWithValue("@ds", ds);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        private byte[] SerializeDS(DataSet ds)
        {
            BinaryFormatter bf = new BinaryFormatter();
            MemoryStream ms = new MemoryStream();

            bf.Serialize(ms, ds);

            ms.Close();
            byte[] data = ms.ToArray();
            return data;
        }

        private DataSet GetDataSet()
        {
            NorthwindDataSet ds = new NorthwindDataSet();

            SqlConnection con = 
              new SqlConnection(@"server=.\SQL2008;Trusted_connection=yes;database=Northwind");
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandText = "select * from customers";
            ds.Tables["Customers"].Load(cmd.ExecuteReader());
            cmd.CommandText = "select * from orders";
            ds.Tables["Orders"].Load(cmd.ExecuteReader());
            cmd.CommandText = "select * from [Order Details]";
            ds.Tables["Order Details"].Load(cmd.ExecuteReader());

            con.Close();
            return ds;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SaveDataset();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataSet ds = LoadDataset();
            dataGridView1.DataSource= ds.Tables["Orders"];
        }
    }
}

In summary, use VarBinary(max) as storage type and BinaryFormatter() as serializer/deserializer (in binary form -maybe encrypted- you could also save to local isolated storage). You could even use DeflateStream() to compress/uncompress what you serialize/deserialize.

PS: You know that I don't tend towards using DataSets:) I would use Linq and serialize/deserialize Linq objects. Check ideablade Devforce (express is free). It has tons of samples saving data to a local store and working disconnected.

Cetin