vs2005 c# .net 2.0: can I store data table result some where

Question

This is just for debugging purpose. We have an abused stored proc that takes for ever to return resultset. Optimizing the proc is out of question at this moment. Is it possible for me to store loaded dataset result some where so I dont have to keep calling the stored proc over and over again. And I could just continue testing my .net code. I dont want to cache it in memory but store it in a file locally.

DataSet ds = db.ExecuteDataSet(cmdobject);
//Store this result some where
//so next time I dont have to call the above method. Just pick up the stored result //from some where and continue with my testing.

Any code example would help.

Answer 1

Well DataSet implements the ISerializable interface, so you could serialize it to a file.

Example:

  DataSet ds = new DataSet();
  if (File.Exists("SavedDataSet.dat"))
  {
     // If the serialized object exists, read it from the file
     // and deserialize it
     DataSet ds;
     Stream stream = File.Open("SavedDataSet.dat", FileMode.Open);
     BinaryFormatter bFormatter = new BinaryFormatter();
     ds = (DataSet)bFormatter.Deserialize(stream);
     stream.Close();
  }
  else
  {
     // If the serialized object doesn't exist, run the stored 
     // procedure and then serialize the result    
     ds = db.ExecuteDataSet(cmdobject);
     Stream stream = File.Open("SavedDataSet.dat", FileMode.Create);
     BinaryFormatter bFormatter = new BinaryFormatter();
     bFormatter.Serialize(stream, ds);
     stream.Close();
  }

Answer 2

DataSet.WriteXml()?

MSDN link

Answer 3

I can't see the details of your stored proc, but I suggest modifying it to create a new result table.

vis: (basturdized sql)

INSERT INTO CachedTable (SELECT /* whatever the stored proc did */);