3 Replies - 1285 Views - Last Post: 25 August 2014 - 05:00 PM Rate Topic: -----

#1 henryvuong   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 05-April 09

Read XML from database to dataset, modify and save back to database

Posted 23 August 2014 - 05:54 PM

I have a table "Items" in my database with the following fields:

ItemID (PK, int)	Title (nvarchar)	ItemSpecifics (XML)
101			           Item A		<SelectedValues><SelectedValue><Name>...
102			           Item B		<SelectedValues><SelectedValue><Name>...
105			           Item F		<SelectedValues><SelectedValue><Name>...
110			           Item H		<SelectedValues><SelectedValue><Name>...
200			           Item X		<SelectedValues><SelectedValue><Name>...





The XML value of one of the rows looks like this:

<SelectedValues>
  <SelectedValue>
    <Name>Brand</Name>
    <Value>Nike</Value>
  </SelectedValue>
  <SelectedValue>
    <Name>Item Condition</Name>
    <Value>New</Value>
    <ValueID>3000</ValueID>
  </SelectedValue>
  <SelectedValue>
    <Name>Details</Name>
    <Value />
  </SelectedValue>
</SelectedValues>




What I want to do:
1) Read the data from the database and load it into a dataset
2) Bind the dataset to a datagridview so a user can modify the data
3) Save the changes back to database

The main point of this operation is to add some text value into the last node of the XML field, "Details". So when reading data from table "Items", I only want to get the value of "Details" node, like so:

SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

string str = @"SELECT  ItemID, Title, 
               ItemSpecifics.value('(/SelectedValues/SelectedValue[Name=""Details""]/Value)[1]', 'varchar(max)') 
               AS Details FROM Items";
try
{
	sqlCmd = new SqlCommand(str, connection);
	da.SelectCommand = sqlCmd;
}
catch (SqlException ex)
{
	MessageBox.Show(ex.ToString());
 }

da.Fill(ds, "Items");

//bind the dataset to a datagridview:
BindingSource bs= new BindingSource();
bs.DataSource = ds;
bs.DataMember = "Items";
dataGridView1.DataSource = bs;




When the program runs, user can add text to the "Details" column and click a button to save the changes back to database. This is the method to save changes back to database:

private void UpdateItems()
{
            try
            {
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.Update(ds.Tables["Items"]);
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString()); 
            }
}



My expectation is that when user added text to the "Details" column in datagridview and click "save" button to call the method UpdateItems(), whatever added to the details of any item should be saved to the value of the "Details" node in XML column of the database table, but it's not the case. The program runs fine but the XML in database is not updated. What am I missing?

Is This A Good Question/Topic? 0
  • +

Replies To: Read XML from database to dataset, modify and save back to database

#2 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6292
  • View blog
  • Posts: 21,622
  • Joined: 05-May 12

Re: Read XML from database to dataset, modify and save back to database

Posted 23 August 2014 - 06:10 PM

What does your UpdateCommand look like?
Was This Post Helpful? 0
  • +
  • -

#3 henryvuong   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 05-April 09

Re: Read XML from database to dataset, modify and save back to database

Posted 25 August 2014 - 04:16 PM

Do I need the UpdateCommand? I thought when I did the SqlDataAdapter.Update(dataTable) the database should be updated? At least that's what I did with other datatable that does not involve XML and it worked.
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6292
  • View blog
  • Posts: 21,622
  • Joined: 05-May 12

Re: Read XML from database to dataset, modify and save back to database

Posted 25 August 2014 - 05:00 PM

Given the behavior you are seeing, it seems like that assumption maybe incorrect. Anyway, one way to tell is to either do a SQL trace, or inspect the update command that the adapter ends up using when trying to update the table. See what it does with that column where you did extra work to extract data out of the XML.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1