?
Solved

update dataset from xml

Posted on 2005-03-17
36
Medium Priority
?
430 Views
Last Modified: 2013-11-19
Hi,

I would like to use a DataSet to read data from the database, update it, and save it back to the database.  However, I would like to do this via xml..  Here's what I do:

1.  Read data from database into DataSet and output to Macromedia Director via XML
2.  Retrieve an xml document from Director back into the dataset

Now can I just call the update method of the dataset?  Or can i hook it back up to an adapter?  I haven't worked with the sqlDataAdapter or DataSet that much....

thanks - Trevor
0
Comment
Question by:trevorhartman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 22
  • 13
36 Comments
 
LVL 15

Expert Comment

by:praneetha
ID: 13569252
well all you need to do it

Dataset ds=new dataset()
ds.ReadXML(xmlpath file)

and update the dataste

and ds.WriteXML(xmlpathfile)
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13569460
i need to update it to the database though..  not save to xml file
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13569489
it's similar to the standard procedure:

setup connection,
setup sqldatadapter,
fill dataset,
make changes to dataset,
call Update() method to save changes to database

except for that the changes to my dataset must come from reading in XML, not from a datagrid or something
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Assisted Solution

by:caball88
caball88 earned 800 total points
ID: 13569503
you're on the right track use a sqldataadapter to hook the data back to the database. you will have to setup the Select, Insert, Update and Delete commands for the data adapter in order to make this seemless. so it might go something like this:


Dim sqlcmd as New SQLCommand("YourSelectStoredProc", SQLConn)
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.Add(New SQLParameter("Param Name", SQLDBType.Int, 4)).Value = YourParamValue

'this sets up the select command for you adapter
Dim da as New SQLDataAdapeter(sqlcmd)

'setup the Insert Command
sqlcmd as New SQLCommand("YourInsertStoredProc", SQLConn)
'the source column represents the datatable column that the InsertCommand will be getting the data from
sqlcmd.Parameters.Add(New SQLParameter("First Param", SQLDBType.Int, 4, "SourceColumn1"))
...'add as many parameters as you will need
da.InsertCommand = sqlcmd

'do the same thing for update and delete
'Now you populate your dataset with the simple fill command

da.Fill(YourDataset)

'to push updates back to the database just use the update method, the data adapter will be smart enough to recognize which rows have been inserted, modified, deleted and perform the appropriate Stored Proc for each row. i specified the name of the datatable just in case but if you only have one datatable in the dataset you do not need to specify the table name.
da.Update(YourDataset.Table("TableName")
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13569522
>>make changes to dataset,

do you mean

if say dataset contains

id text
1-text1
2-text2

and xml file contains
id - text
1-text1 updated
2-text2
3-text3 new row in xml

u should update teh dataset with this xml data...

iif it's exact same columns..you can just use ds.ReadXML()
and do the adap.update

and if it is not exact same columns...you can fill the new dataset with xml and browse thru ir and update with necessary changed...
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13569602
awesome.  a couple things:

1.  this is across multiple requests to asp.net.  i don't know if that makes a difference, but let me explain...  this is for my director app, so here's what happens:
  Director app starts, customer data loads (this is ALWAYS a single row w/ multiple columns, so delete and insert functionality is not required
  customer can go in and change up their data, then hit save.
  director sends the the modified xml doc back to asp.net via HTTPPOST
  asp.net builds an XmlDocument based on the InputStream
  asp.net populates a DataSet using the XmlDocument
  now update!

so do I need to persist a dataset in the cache or anything??  i think i understand what you're talking about, just not completely clear.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13569680
i really don;t know how director program works..hmm

well

asp.net populates a DataSet using the XmlDocument
>>when this happens...if u have this datset associated with dataadapter you can just do update...but if u dont have an dataadapter with it...

  now update!

if all ds built xml contains i sjust 1 row..instead of update..you can even insert using insert command...



0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13569699
asp.net populates a DataSet using the XmlDocument
>>when this happens...if u have this datset associated with dataadapter you can just do update...but if u dont have an dataadapter with it...

>>>> that's where i'm a little lost.  how do i associate a dataadapter with my dataset that was created from reading xml?  or do i just cache the original dataset that outputted the xml, so that it is still associated with the dataadapter?

  now update!

if all ds built xml contains i sjust 1 row..instead of update..you can even insert using insert command...
>>  not sure if i understand.  why would i want to do an insert, when that record is already in the database, and i just want to update it?
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13569709
( you don't really need to know the internal workings of director.  all you need to know is director is getting xml via http and sending it back to asp.net via http..  I already have that part figured out.  you can think of it like asp.net writing an xml file, another program updates the xml file, then asp.net reads the file back in and needs to save it to the database)
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13569726
>>>>  not sure if i understand.  why would i want to do an insert, when that record is already in the database, and i just want to update it?

ok u need to do update here not insert...

>>> how do i associate a dataadapter with my dataset that was created from reading xml?  or do i just cache the original dataset that outputted the xml, so that it is still associated with the dataadapter?

can u just select * from the table and contain all the data in dataste

and when uhave a newdataset from xml file..

you can do dataset.Merge(DataSet)

and update the dataset using adapter object...
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13569773
OK, i'm thinking along those lines too.. but could improve performance by just persisting the dataset and possibly the sqldataadapter in cache, then pulling them out when i need to Merge and update?  or can i just pull that dataset out of cache, and do ReadXml to update it (will it overwrite the existing row like i want it to?), then call update?
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13569820
i think readxml will completely overwrite the dataset..instead of just updating that row...i guess...

say your initial dataset contained 10 rows and u say readxml..it may create a other table in dataset with xml data or overwrite the existing one..i ould think .. not sure...

i guess just doing update command would be easier..
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13569944
well i was thinking of writing the xml schema to file after populating the dataset from the sqldataadapter.  then when asp.net reads the xml back in, i can read it back in using that schema.  the DataSet and DataTable are descriptively named, so my xml is very clean, and should read back in very clean, especially using schema.  right now i only have one table, Customer, in the DataSet and that table only contains one row.  

i'll just start trying it out and see what happens..  i'll let you know how it goes


thanks - Trevor
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13616201
ok i ran into a snag... here's what I'm doing.

1.  director app starts and requests xml from asp.net
2.  asp.net fills a dataset from the db and saves dataset in session (Session["ds"] = ds;)
3.  director gets xml, makes changes, sends it back to asp.net
4.  asp.net loads the dataset back in from the Session, then calls ds.ReadXml(Request.InputStream)
5.  the correct data is loaded, but the CustomerInformation element is ADDED instead of just being updated.  then i have two CustomerInformation elements when i only want one.

attached is the xml that it generates.  i need to know how to make it just update the existing xml instead of write a new element.  i defined cust_id as the primary key, so i thought it'd know that it's supposed to update...

Thanks - Trevor


<Customer>
  <CustomerInformation>
    <cust_id>00000006500</cust_id>
    <last_name>Keith</last_name>
    <first_name>Phil</first_name>
    <address_1>2110 Overland Ave. #117</address_1>
    <city>Billings</city>
    <state>MT</state>
    <zip>59102-</zip>
    <cust_type>PERSON</cust_type>
    <cust_group> </cust_group>
    <cust_status>ACTIVE</cust_status>
    <status_date>2001-11-27T00:00:00.0000000-07:00</status_date>
    <mail_status>INACTIVE</mail_status>
    <mail_status_date>2001-11-27T00:00:00.0000000-07:00</mail_status_date>
    <cac_status>INACTIVE</cac_status>
    <cac_status_date>2001-11-27T00:00:00.0000000-07:00</cac_status_date>
    <nsf_status>NONE</nsf_status>
    <nsf_status_date>2001-11-27T00:00:00.0000000-07:00</nsf_status_date>
    <card_status>ACTIVE</card_status>
    <card_type>BLACK</card_type>
    <card_issued>2005-02-23T00:00:00.0000000-07:00</card_issued>
    <entry_date>2001-11-27T00:00:00.0000000-07:00</entry_date>
    <update_date>2005-03-07T12:37:21.0000000-07:00</update_date>
    <update_source>MAIN</update_source>
    <update_empl>804</update_empl>
    <primary_location>HOME</primary_location>
    <accum_promo>7.0000</accum_promo>
    <accum_payout>50.0000</accum_payout>
    <accum_visits>53</accum_visits>
    <year_promo>7.0000</year_promo>
    <year_payout>50.0000</year_payout>
    <year_visits>53</year_visits>
    <points1_accum>758.4066</points1_accum>
    <points1_used>7.0000</points1_used>
    <points1_update>2005-03-07T12:37:21.0000000-07:00</points1_update>
    <points2_accum>731.3466</points2_accum>
    <points2_used>9.0000</points2_used>
    <credit_card_type />
    <credit_card_expire />
    <updated_ts>AAAAAABSBYA=</updated_ts>
  </CustomerInformation>
  <CustomerInformation>
    <cust_id>00000006500</cust_id>
    <last_name>Keith</last_name>
    <first_name>Phil</first_name>
    <address_1>2110 Overland Ave. #117</address_1>
    <city>Billings</city>
    <state>MT</state>
    <zip>59102-</zip>
    <cust_type>PERSON</cust_type>
    <cust_group />
    <cust_status>ACTIVE</cust_status>
    <status_date>2001-11-27T00:00:00.0000000-07:00</status_date>
    <mail_status>INACTIVE</mail_status>
    <mail_status_date>2001-11-27T00:00:00.0000000-07:00</mail_status_date>
    <cac_status>INACTIVE</cac_status>
    <cac_status_date>2001-11-27T00:00:00.0000000-07:00</cac_status_date>
    <nsf_status>NONE</nsf_status>
    <nsf_status_date>2001-11-27T00:00:00.0000000-07:00</nsf_status_date>
    <card_status>ACTIVE</card_status>
    <card_type>BLACK</card_type>
    <card_issued>2005-02-23T00:00:00.0000000-07:00</card_issued>
    <entry_date>2001-11-27T00:00:00.0000000-07:00</entry_date>
    <update_date>2005-03-07T12:37:21.0000000-07:00</update_date>
    <update_source>MAIN</update_source>
    <update_empl>804</update_empl>
    <primary_location>HOME</primary_location>
    <accum_promo>7.0000</accum_promo>
    <accum_payout>50.0000</accum_payout>
    <accum_visits>53</accum_visits>
    <year_promo>7.0000</year_promo>
    <year_payout>50.0000</year_payout>
    <year_visits>53</year_visits>
    <points1_accum>758.4066</points1_accum>
    <points1_used>7.0000</points1_used>
    <points1_update>2005-03-07T12:37:21.0000000-07:00</points1_update>
    <points2_accum>731.3466</points2_accum>
    <points2_used>9.0000</points2_used>
    <credit_card_type />
    <credit_card_expire />
    <updated_ts>AAAAAABSBYA=</updated_ts>
  </CustomerInformation>
</Customer>


0
 
LVL 15

Expert Comment

by:praneetha
ID: 13616226
>>  director gets xml, makes changes, sends it back to asp.net

so at that point it is updating...so director is updating the existing record...not asp.net...
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13616316
right, director updates the XML doc, sends it to asp.net, and I need asp.net to update the Columns in the single DataRow inside the "Customers" DataTable inside the "CustomerInformation" DataSet.  instead of updating the columns in my SINGLE datarow, it just adds a new datarow.  the xml i gave you is a ds.GetXml() after changes are made...

am i being clear?
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13616343
oops..  i meant:

"CustomerInformation" DataTable inside the "Customers" DataSet.....

so:

director sends back the correct xml with a SINGLE CustomerInformation element, then when ASP.NET tries to read the xml into the ds, it ADDS a CustomerInformation instead of modifying the existing CustomerInformation...
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13616375
hmm can ushow that asp.net code...

starting from reading the xml director sent...to the part when asp.net modifies it...
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13616447
sure:

      // read the dataset from session (this contains a single CustomerInformation
      ds = (DataSet)Session["ds"];
      ds.ReadXmlSchema(Server.MapPath("Customer.xsd"));
      // now read the xml in from Request.InputStream.. This contains a single CustomerInformation,
                // and I want asp.net to modify the existing CustomerInformation in ds, not create a new one
      ds.ReadXml(Request.InputStream, XmlReadMode.ReadSchema);
      
      // find out what asp.net decided to do:
      Response.Write(ds.GetXml());
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13616458
asp.net isn't supposed to modify the xml.  just update the DataSet from the xml.
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13616496
here's a thought:  instead of persisting ds in the session, should i just create a new DataSet, do a ReadXml, then setup an SqlDataAdapter and call SqlDataAdapter.Update()?

0
 
LVL 15

Accepted Solution

by:
praneetha earned 1200 total points
ID: 13616511
ds = (DataSet)Session["ds"];
     ds.ReadXmlSchema(Server.MapPath("Customer.xsd"));
     
ds1=new DataSet();
     ds1.ReadXml(Request.InputStream, XmlReadMode.ReadSchema);

     Response.Write(ds1.GetXml());

and than....

ds.Merge(ds1);
Response.Write(ds.GetXml());

just try and let me know what happens...
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13616720
i tested the concept in the browser, and Merge worked, but that was using identical CustomerInformation elements..

well i try a full test, using director to actually modify the CustomerInformation element and post the xml back to asp.net, i'm sort of working blind because i don't see a browser window to catch errors or view results.  I'm writing xml files based on the results to see what's going on, but it looks like I don't have a Session to work with because i'm not working in a browser.  there's no place to maintain the session.  which means my (DataSet)Session["ds"] doesn't work.  

i think i'll try going the way i suggested above, just doing the new DataSet and setting up the sqlDataAdapter Update command...
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13626433
ok i need a little help on the update.  here's what i'm doing:

SqlConnection cn = Global.GetConnection();
// provide the adapter with a select so the sqlcommandbuilder can do everything else
SqlDataAdapter ada = new SqlDataAdapter("SELECT * FROM Customers", cn);
SqlCommandBuilder builder = new SqlCommandBuilder(ada);

// load in the xml that was sent from director, along with the Schema
DataSet dsTemp = new DataSet();
dsTemp.ReadXmlSchema(Server.MapPath("Customer.xsd"));
dsTemp.ReadXml(Request.InputStream);

// save changes to database
ada.Update(dsTemp, "CustomerInformation");

I get this error:

Violation of PRIMARY KEY constraint 'aaaaacustomers_PK'. Cannot insert duplicate key in object 'customers'. The statement has been terminated.

It's obvious that when i call ada.Update, it's trying to do an Insert.  How do I make it do an update instead?  I think this has to do with me creating a new dataset to load in the xml, instead of using the same dataset that i read everything from originally..

-Trevor
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13626696
i'm going to ask this part as a new question since it sort of goes beyond the scope of this question..
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13656611
i'm stuck on this one... any ideas?
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13656842
so you still get this primary key error..

or somethng else...
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13656850
hey didn't u say merge was working for you...
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13656866
not getting any errors, it just doesn't update...  no changes in the databse, and i don't understand why.  i went with this method of changing the rowstate:

                  // accept changes
                  dsTemp.Tables["CustomerInformation"].AcceptChanges();
                  // set rowstate to modified so that ada.Update will cause an UPDATE in the DB
                  dsTemp.Tables["CustomerInformation"].Rows[0][0] = dsTemp.Tables["CustomerInformation"].Rows[0][0];
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13656876
dsTemp.Tables["CustomerInformation"].AcceptChanges();

i guess ushould not use that line..comment that line for a while and try
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13656882
    dsTemp.Tables["CustomerInformation"].Rows[0][0] = dsTemp.Tables["CustomerInformation"].Rows[0][0];

how is that line changing the rowstate...???
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13656888
i'll accept/close this question; the latest thread of this question is located here:

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21368942.html
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13656908
setting to itself changes the rowstate to modified... i output it and it works
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13656926
i have to keep all lines.

doing this code:

      // check rowstate
      Response.Write(dsTemp.Tables[0].Rows[0].RowState + "<br>");
      // accept changes
      dsTemp.Tables["CustomerInformation"].AcceptChanges();
      // check rowstate
      Response.Write(dsTemp.Tables[0].Rows[0].RowState + "<br>");
      // set rowstate to modified so that ada.Update will cause an UPDATE in the DB
      dsTemp.Tables["CustomerInformation"].Rows[0][0] = dsTemp.Tables["CustomerInformation"].Rows[0][0];
      // check rowstate
      Response.Write(dsTemp.Tables[0].Rows[0].RowState + "<br>");

outputs:

Added
Unchanged
Modified


if i take out acceptchanges, then the rowstate is Added instead of modified.  
0
 
LVL 15

Expert Comment

by:praneetha
ID: 13656944
ok how many rows does the ds contain when it is Added...just one or two...
0

Featured Post

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

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

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

762 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