• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

update dataset from xml

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
trevorhartman
Asked:
trevorhartman
  • 22
  • 13
2 Solutions
 
praneethaCommented:
well all you need to do it

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

and update the dataste

and ds.WriteXML(xmlpathfile)
0
 
trevorhartmanAuthor Commented:
i need to update it to the database though..  not save to xml file
0
 
trevorhartmanAuthor Commented:
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
Industry Leaders: 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!

 
caball88Commented:
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
 
praneethaCommented:
>>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
 
trevorhartmanAuthor Commented:
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
 
praneethaCommented:
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
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
( 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
 
praneethaCommented:
>>>>  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
 
trevorhartmanAuthor Commented:
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
 
praneethaCommented:
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
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
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
 
praneethaCommented:
>>  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
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
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
 
praneethaCommented:
hmm can ushow that asp.net code...

starting from reading the xml director sent...to the part when asp.net modifies it...
0
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
asp.net isn't supposed to modify the xml.  just update the DataSet from the xml.
0
 
trevorhartmanAuthor Commented:
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
 
praneethaCommented:
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
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
i'm going to ask this part as a new question since it sort of goes beyond the scope of this question..
0
 
trevorhartmanAuthor Commented:
i'm stuck on this one... any ideas?
0
 
praneethaCommented:
so you still get this primary key error..

or somethng else...
0
 
praneethaCommented:
hey didn't u say merge was working for you...
0
 
trevorhartmanAuthor Commented:
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
 
praneethaCommented:
dsTemp.Tables["CustomerInformation"].AcceptChanges();

i guess ushould not use that line..comment that line for a while and try
0
 
praneethaCommented:
    dsTemp.Tables["CustomerInformation"].Rows[0][0] = dsTemp.Tables["CustomerInformation"].Rows[0][0];

how is that line changing the rowstate...???
0
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
setting to itself changes the rowstate to modified... i output it and it works
0
 
trevorhartmanAuthor Commented:
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
 
praneethaCommented:
ok how many rows does the ds contain when it is Added...just one or two...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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