Serialize/deserialize DataSet (as a way to backup my data)

Question

Hi all,

I have written the contents of a table in my database to the disk using the following function.

private static Stream GetTableAsBinary(string table, OleDbConnection oleDbConnection)
{
    var oleDbDataAdapter = new OleDbDataAdapter(string.Format("select * from {0}", table), oleDbConnection);
    var dataSet = new DataSet();
    oleDbDataAdapter.Fill(dataSet, table);
    dataSet.RemotingFormat = SerializationFormat.Binary;
    var format = new BinaryFormatter();
    var memStream = new MemoryStream();
    format.Serialize(memStream, dataSet);
    return memStream;          
}

The purpose of this function is to make a backup of the table.

Now I want to do the reverse: read the table back into an (empty) database. I've searched around and tried a couple of things, but still unsuccessful.

Before you tell me that there are other ways to do a backup: A couple of dozen customers already backup their database using the method above, so I need a reverse function. And don't tell me it isn't possible :)

Answer 1

BinaryFormatter has Deserialize function.

Answer 2

private static T DeserializeObject<T>(MemoryStream memory) where T : class // might need to check the constraint.
{
BinaryFormatter formatter = new BinaryFormatter();
return formatter.Deserialize(stream) as T;
}

usage:

DataSet ds = DeSerializeObject<DataSet>(stream);

For the OleDbAdapter part have a look at http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx and http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.aspx - you'd end up with something like this:

var oleDbAdapter = new OleDbAdapter("select * from...", table, oleDbConnection);
oleDbAdapter.InsertCommand = new OleDbCommand("insert into mytable values (?,?)");
oleDbAdapter.UpdateCommand = new OleDbCommand("update mytable values foo = ?, bar =? where mykey = ?");
oleDbAdapter.DeleteCommand = new OleDbCommand("delete from mytable where mykey = ?");

oleDbAdapater.InsertCommand.Paramaters.Add(...);
oleDbAdapater.UpdateCommand.Paramaters.Add(...);
oleDbAdapater.DeleteCommand.Paramaters.Add(...);

oleDbAdapater.Update(ds);