[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 879
  • Last Modified:

Need help using tableadapter update to save imported xml data back to sql server compact 3.5 database.

I need to be able to add import/export functionality to an application so that several users can keep their data "sync'ed" up.  The users will never be connected to the same network, and an "online" server is not a feasable option.  

I use a typed dataset for the data application and can write it out to xml.  I thought that perhaps I could use the dataset.ReadXml function and then do an update on the table adapters to get this data into another users database.  

New records are inserted just fine (based on primary keys).  However, how do I get the tableadapter to update the database on changed records.  

Here is the import code:

            loomixDataSet ds = new loomixDataSet();
            string _connstr = Properties.Settings.Default.connstr;
            //ds.EnforceConstraints = false;

            ds.ReadXml("c:\\loomix.xml", XmlReadMode.ReadSchema);

            ProductsTableAdapter prods = new ProductsTableAdapter();
            prods.Connection.ConnectionString = _connstr;
            foreach(DataRow dr in ds.Products)
            {
                try
                {
                    prods.Update(dr);
                }
                catch
                {
                 
                }
            }

I am looking for some code to insert into the catch block to force the table adapter to call the update command instead of an insert command.
0
stwokie
Asked:
stwokie
1 Solution
 
stwokieAuthor Commented:
I have a workaround but would welcome a better solution!
        private void cmdImport_Click(object sender, EventArgs e)
        {
            loomixDataSet ds = new loomixDataSet();
            string _connstr = Properties.Settings.Default.connstr;
            //ds.EnforceConstraints = false;
 
            ds.ReadXml("c:\\loomix.xml", XmlReadMode.ReadSchema);
 
            ProductsTableAdapter prods = new ProductsTableAdapter();
            prods.Connection.ConnectionString = _connstr;
            
            foreach(DataRow dr in ds.Products)
            {
                try
                {
                    prods.Update(dr);
                    //prods.Update(dr["Name"].ToString(), DateTime.Parse(dr["PriceStartDate"].ToString()), DateTime.Parse(dr["PriceEndDate"].ToString()), dr["Price"].ToString(), dr["Unit"].ToString(), dr["ProductType"].ToString(), Int32.Parse(dr["ProductID"].ToString()));
                }
                catch(System.Exception ex)
                {
                   
                    SqlCeConnection cn = prods.Connection;
                    cn.ConnectionString = _connstr;
                    cn.Open();
                    string cmdtxt = "UPDATE [Products] SET  [PriceStartDate] = '" + DateTime.Parse(dr["PriceStartDate"].ToString()) + "' , [PriceEndDate] = '" + DateTime.Parse(dr["PriceEndDate"].ToString()) +  "', [Price] = '" + dr["Price"].ToString() + "', [Unit] = '" + dr["Unit"].ToString() + "', [ProductType] = '" + dr["ProductType"].ToString() +"' WHERE (([Name] = '"+  dr["Name"].ToString() +"'))";
                    SqlCeCommand cm = new SqlCeCommand(cmdtxt, cn);                  
                    
                    try
                    {
                    cm.ExecuteNonQuery();
                    }
                    catch (System.Exception ex2)
                    {
                        MessageBox.Show(ex2.ToString());
                    }
                    cn.Close();
                }
            }
            textBox1.Text =  " Added to Products";
 
 
        }

Open in new window

0

Featured Post

Technology Partners: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now