?
Solved

A deserialized DataSet is not updating the database

Posted on 2009-02-09
3
Medium Priority
?
531 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

0
Comment
Question by:purplesoup
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
talker2004 earned 2000 total points
ID: 23594139
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.
0
 

Author Comment

by:purplesoup
ID: 23596471
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
0
 
LVL 7

Expert Comment

by:talker2004
ID: 23597853
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.


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question