We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

A deserialized DataSet is not updating the database

Medium Priority
544 Views
Last Modified: 2013-12-17
I am using the code in WriteNewXml below to serialize a DataSet and save the contents in a local file.
This appears to work ok - the file is created containing XML data representing the DataSet.
However when I use the code in ReadNewXml to read the data back into a DataSet and then in turn update the table, the table is not updated.

Interestingly the DataSet is being loaded correctly from the file, as my "debug_serialize.xml" file does contain the XML data from the previously saved file, therefore the problem is in the database update part.
protected void btnSerializeExport_Click(object sender, EventArgs e)
        {
            SqlConnection c = GetSqlConnection();
            c.Open();
            string sql = "select * from MyTable";
            SqlCommand cmd = new SqlCommand(sql, c);
            SqlDataAdapter a = new SqlDataAdapter(cmd);
            System.Data.DataSet ds = new System.Data.DataSet();
            a.Fill(ds);
            string filename = string.Format("New_{0}.xml", txtTableName.Text);
            string path = Server.MapPath(filename);
            WriteNewXml(path, ds);
            c.Close();
        }
 
        private void WriteNewXml(string path, DataSet ds)
        {
            ds.RemotingFormat = SerializationFormat.Xml;
            XmlSerializer serializer = new XmlSerializer(ds.GetType());
 
            Stream myStream = new FileStream(path, FileMode.Create);
            serializer.Serialize(myStream, ds);
            myStream.Close();
        }
 
        protected void btnSerializeImport_Click(object sender, EventArgs e)
        {
            SqlConnection c = GetSqlConnection();
            c.Open();
 
            // delete all the data in the table
            SqlCommand delData = new SqlCommand("DELETE FROM MyTable",c);
            delData.ExecuteNonQuery();
            
            // populate the select command
            string sql = "select * from MyTable";
            SqlCommand cmd = new SqlCommand(sql, c);
            SqlDataAdapter a = new SqlDataAdapter(cmd);
 
            string filename = string.Format("New_{0}.xml", txtTableName.Text);
            string path = Server.MapPath(filename);
            System.Data.DataSet ds = ReadNewXml(path);             
 
            ds.WriteXml(Server.MapPath("debug_serialize.xml"));
 
            a.Fill(ds);
            SetOtherCommands(a);
            a.Update(ds);
            c.Close();
        }
 
        private System.Data.DataSet ReadNewXml(string path)
        {
            System.Data.DataSet ds = new DataSet();
            ds.RemotingFormat = SerializationFormat.Xml;
            XmlSerializer serializer = new XmlSerializer(ds.GetType());
            Stream myStream = new FileStream(path, FileMode.Open);
            ds = serializer.Deserialize(myStream) as DataSet;
            myStream.Close();
            return ds;
        }
 
        private static void SetOtherCommands(SqlDataAdapter a)
        {
            SqlCommandBuilder b = new SqlCommandBuilder(a);
            a.InsertCommand = b.GetInsertCommand();
            a.UpdateCommand = b.GetUpdateCommand();
            a.DeleteCommand = b.GetDeleteCommand();
        }

Open in new window

Comment
Watch Question

Hard to say without seeing your insert and update commands.

Perhaps you are losing the dirty flags when you are reading it in from the xml file.

Right before you update your datasource try to run this test.


      foreach (DataRow dr in ds.Tables(0).Rows) {
        Interaction.MsgBox(dr.RowState);

   

What is the state of the rows? They should be showing up as either added  or modified.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
purplesoupProgrammer

Author

Commented:
Well that is the problem. The rowstate is set to unchanged for all of the rows.

If I run a similar test using the ReadXml method on the DataSet I get all the rows flagged as "added".

I found there is a method SetAdded (also SetModified) which fixes the problem, explained here

http://msmvps.com/blogs/williamryan/archive/2006/01/14/81063.aspx
Yeah, i have had issues with the dirty flags depending upon the way you read in the xml. In some cases we use the setadded or setmodified when we know exactly what we are going to be doing with each record before we call the update method.


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.